Forms: Calling up a second form, entering new data, then returning 1

[SMHSleepy]

I'm creating what I thought was a simple database (and probably is for a non-newbie like me). Here's my situation: I have a main table and form for patient demographic information. I have a second table and form for referring physician information, which uses an autonumber generated key field called "PhysID". I added a combo box on the main form to select the referring physician and return the PhysID value to the corresponding field on the main table. If the physician is not listed, I'd like to be able to open the form, enter the new physician information, then post the PhysID to the main table upon exit. Could someone please help? Thank you.

 

[dhookom]

You can open a different form in acDialog mode and enter the new physician information. Then after the form is closed, your code can continue with a requery of the combo box and possibly setting the value of the combo box to the Max of PhysID.

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

Thank you dhookom, that procedure makes perfect sense; however, I have very limited programming knowledge. Could you post some code to help me create this? Thanks.

 

[dhookom]

Start by adding a command button to open your physician form. Then modify the code to change the mode to acDialog. If you can't figure this out, come back with the code generated by the command button wizard as well as the names of significant tables, fields, and your combo box.

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

Thank you! I managed to create a command button which opens the referring physician form in acDialog view and requeries the combo box upon return - works great!. Now, how can I set the value of my combo box to the Max of PhysID? Thanks again.

 

[dhookom]

Can you share the code that opens the new form and requeries? Your max line of code would follow the requery.

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

It's actually an embedded macro: (Action -> Argument)
OpenForm -> Refdr2, Form, , , Add, Dialog
Requery -> Combo51

The wizard created the first line and I just added the Requery. I can't figure out how to convert this macro to code.

 

[dhookom]

You can generally right-click a macro and convert it to code.

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

Excellent, thank you. Here is the code:

'------------------------------------------------------------
' Command59_Click
'
'------------------------------------------------------------
Private Sub Command59_Click()
On Error GoTo Command59_Click_Err

DoCmd.OpenForm "Refdr2", acNormal, "", "", acAdd, acDialog
DoCmd.Requery "Combo51"
DoCmd.Requery "Combo57"


Command59_Click_Exit:
Exit Sub

Command59_Click_Err:
MsgBox Error$
Resume Command59_Click_Exit

End Sub

 

[dhookom]

IMO, you really need to update the names of your controls. Command59, Combo51, and Combo57 don't provide any indication of their purpose.

'------------------------------------------------------------
' Command59_Click
'
'------------------------------------------------------------
Private Sub Command59_Click()
On Error GoTo Command59_Click_Err

    DoCmd.OpenForm "Refdr2", acNormal, "", "", acAdd, acDialog
    DoCmd.Requery "Combo51"
    DoCmd.Requery "Combo57"
    Me.Combo51 = DMax("PhysID","tblPhysicians")

Command59_Click_Exit:
    Exit Sub

Command59_Click_Err:
    MsgBox Error$
    Resume Command59_Click_Exit

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

Thank you so much! I applied the code AND renamed my controls. Everything is working exactly as I wanted.