dialog box to fill in text box in report

[sasoderl]

I would like to create one or more dialog boxes to use to fill in several text boxes in a report. I cannot find any information of how to do this using MS Help function nor in any book I have. Does anyone know if this is possible and if so, how can I attempt it?

 

[CT Witter]

You probably want to use the inputbox method. It has the syntax: InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

So you could do something like:

Function TestInputbox()
Dim var1
var1 = InputBox("Enter a Value!", "Test")
MsgBox var1, vbOKOnly, "Value from InputBox"
End Function

But for the report you would set the inputbox equal to the report field.

HTH,
CT

 

[sasoderl]

Just curious (this may seem like a stupid question), how do I get the input box to "pop up" and where would I write the VB code?

 

[CT Witter]

Access "knows" to have the input box as a dialog box that pop-up.

you would put the code in the reports ON_Open event. Go to the report properties window and find the On Open. If you select this you will see the elipse (...) appear. Click on this and another dialog will open asking "Choose Builder". Select "Code Builder" Paste the code. This will now run everytime you open the report.


To test this code. Go to Module-> New. Paste the code and run(F5).

HTH,
CT

 

[sasoderl]

Thanks for your help. I have one more (hopefully only one more) question for you. I have a field in my report called DateField, when I set the inputBox = to the DateField and try testing the code, it doesnt appear in the field. And actually, the field is a label. Could this be my problem?

Here is the code (only slightly modified as of now while testing):

Private Sub Report_Open(Cancel As Integer)

Dim DateField
DateField = InputBox("Enter a Value!", "Test")
MsgBox DateField, vbOKOnly, "Value from InputBox"
End Sub

Have any ideas??

 

[CT Witter]

Ok, Here is a different approach.

Remove everything from the On_Open event.

In the report code/module add the following:

Function AddInputBoxText() As String
AddInputBoxText = InputBox("Enter a Value!", "Test")
MsgBox AddInputBoxText, vbOKOnly, "Value from InputBox"
End Function

Now on your field (must be textbox) that you want to place the data, make the control source something like:

="Report Period From" & " " & AddInputBoxText()

Now run your report. You should get the input box, then the msgbox, then the textbox on your report should say Report Period From (inputbox text).

HTH,
CT

 

[sasoderl]

Hey, that was great! Thank you very much. I have one more question for you (for real this time)! I have two fields on each page of each report that require the same input data (specifically, the date field) Is there a way to have them both fill in without the function for the input box being called twice to fill the date in?

Thanks again

 

[CT Witter]

To do this reference the first textbox from the second. IE textboxes are named text0 and text1

text0="Report Period From" & " " & AddInputBoxText()

text1=[text0]

HTH,
CT

 

[sasoderl]

Unfortunately, that did not solve the problem. It references the text box which calls the function, therefore, the function is still being called twice. I wonder, do I need to create a variable and store the value???

 

[CT Witter]

Something else is probably running. Check to make sure that you aren't calling the function twice by using on_open or other methods.

HTH,
CT