Link several forms through ID number 2

[bklyn123]

Hi,

I'm relatively new to programming in Access (meaning I dont really know how to use modules yet)...Nevertheless, I'm trying to build a database with several forms for data entry, and I want to enter an id number in the first form and have it automatically fill in as the data entry staff open additional forms.

I dont know exactly what the procedure is called, which is slowing my search considerably. Any advice greatly appreciated.

 

[HitechUser]

bklyn123,

How are you opening the forms? One way is to pass the value to the form as an argument (help text OnOpen).

Another way is to "retrieve it's value using the "[Forms}!" reference.

I hope this helps.

 

[PaulBricker]

If the other forms are not subforms of the first one, you will have to do the work using the On Open Event for each form to grab the value from whatever form is open and has the value available. To do this, you need to open the second form in Design View. Open the properties box and make sure it says Form at the top of the box. If it doesn't, you can select Form from the dropdown box in the properties box. Then scroll down to the Open Event. Click on the line next to the event and then click on the little elipsis button. The one with the three dots (...)
You will get another dialog box. Select Code Builder. This should open a module that has the event already started with

Private Sub Form_Open(Cancel As Integer)

End Sub

In between these two lines, you will put the code that will grab the value from the previous form. You will use a line like

Me.TextboxNameToFill = Forms!Form1!FieldToGetValueFrom

Back in the Properties box, you will see the words Event Procedure on the line next to the On Open event. If for some reason it's not there, click on the dropdown box for that event and select the value from the list.
Of course you will need to change the names in the code to the names you need.
Then when the second form opens, it will fill the textbox with the value from the textbox on the first form.
Try it out and post back with specific problems.

Paul

 

[bklyn123]

Thanks very much both of you...couldn't figure out how to retrieve the value through [Forms] so I tried what Paul suggested.

Private Sub Form_Open(Cancel As Integer)
Me.HOSPITAL_ID = Forms![INITIAL FORM]!HOSPITAL_ID
End Sub

I get a message box saying cant find INITIAL FORM. My form is named INITIAL FORM with a space, but my field is HOSPITALID with no underscore (the text wizard suggested the underscore). In any case, it doesn't seem to work. I should add that I am opening the new form (PRESCREEN) from a command button on INITIAL FORM (I'm navigating through ten or so forms this way). I tried moving the form-open in front of the command, but still no luck...

any advice? thanks

 

[dbelsey]

bklyn,

Have you tried OpenArgs?

If
the initial form is [INITIAL FORM] and
[INITIAL FORM] contains a text box named HOSPITALID and
a value is entered for HOSPITALID in [INITIAL FORM] and
clicking a command button on [INITIAL FORM] opens a second form named PRESCREEN and
PRESCREEN also contains a textbox called HOSPITALID and
you want the new HOSPITALID to have the same value as the one on [INITIAL FORM]
then...you'll have some code behind that command button to open PRESCREEN. Try this:
(presumes the command button is cmdOpenPrescreen)

Private Sub cmdOpenPrescreen_Click()
DoCmd.OpenForm "PRESCREEN",,,,,,HOSPITALID
End Sub

Then in the On Open event in PRESCREEN, you'll have this:

Private Sub Form_Open(Cancel As Integer)
Me!HOSPITALID = OpenArgs
End Sub

You'll get an error if [INITIAL FORM]!HOSPITALID contains no value, so you'll want to stick in some code to check it (If Len(HOSPITALID & "") = 0 Then ...), but that's about the only gotcha. Every subsequent form must be opened setting OpenArgs equal to the ID and every On Open event would have to have the value of its ID set to OpenArgs.

Regarding your previous note, it doesn't much matter what the wizard suggests, you have to refer to the control by using the correct name. HOSPITAL_ID is NOT the same as HOSPITALID. If the control is actually named HOSPITALID, referring to it as HOSPITAL_ID will cause you grief.

Suggestions: (you didn't ask for this but you get it free of charge)
You might want to consider a different naming convention. That initial form should probably be called frmInitialForm (no need for brackets that way)(In general it's a good idea to avoid spaces in the name of ANY object). HOSPITALID should probably be called txtHospitalID since it's a text box. Unfortunately these suggestions may come a little late if you're trying to repair someone else's damage.

Dave

 

[PaulBricker]

Double check the name again. Make sure you don't have an extra space in the name of the form. Also, if your field is named HOSPITALID, then don't put an underscore in the code name. Just use HOSPITALID.
Not sure what you mean by you "tried moving the form-open in front of the command? Can you explain that a little.
The code to set the value for the textbox on form PRESCREEN has to be in the open event for the form PRESCREEN and not for the form [INITIAL FORM].

Post back and let us know how it went.

Paul

 

[bklyn123]

Hi and thanks again...

I've built a very basic model of the database I have, just to test the open event as you described, and I've eliminated the command buttons. I now have simple three-form database with a few questions in each, all linked by HOSPITALID. Here is the code in the code builder:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
Me.HOSPITALID = Forms!INITIAL!HOSPITALID
End Sub

Private Sub HOSPITALID_BeforeUpdate(Cancel As Integer)

End Sub


When I try to close the design view, I get a message of a run time error "You can't assign a value to this object" I have tried changing the field from numeric to text, but get the same message. Again, any advice greatly appreciated...

 

[PaulBricker]

Is Me.HospitalID a bound field. That might make a difference.


Paul