Topic: MS Access - Forms questions (Page 1 of 1)

I figure with all the coder types in here someone might be able to help me out with an Access question.

I am creating a database of pharmacists that work at my company. I have one table that stores information about the pharmacist and one that stores information on their licenses (a pharmacist can be licensed in more than one state at a time.) The two tables are linked on Employee ID# since that is a unique number.

I have one form that users fill out to add a new pharmacist and one they fill out to add a new license. What I would like to do is to have the user fill out the pharmacist information first and then click a button to open a new form to fill out the license information. Currently it works like that but when the license form opens the user has to retype the Employee ID for each new license they add.

Ideally, I'd like the Employee ID to automatically populate based on what they type when they're adding the new pharmacist.

Is that clear as mud?

The VB code on the "Add New License" button so far is:

Private Sub Command22_Click()
    DoCmd.OpenForm "frmLicenseAdd", acNormal, , , acFormAdd
End Sub

:::11oh1:::

i'd suggest creating a global (or public - depending on who you talk to) variable for the Employee ID number. That way you can reference that employee no matter how many forms you open. If you don't know how to set up global variables in Access, I'll post the instructions.

In your first form you can use it like this:

Private Sub Command22_Click()

    gblEmployeeID = me.txtEmployeeID.Value

    DoCmd.OpenForm "frmLicenseAdd", acNormal, , , acFormAdd

End Sub

Then, in the next form:

Private Sub Form_Open (Cancel as Integer)

me.txtEmployeeID.value = gblEmployeeID

End Sub

I've used this method in a lot of the Access apps that I've put together, and it works pretty well.

Thanks Moon. I ended up finding something online right after I posted this. Here's how I did it for future reference:

OnClick for the new license button:

DoCmd.OpenForm "frmLicenseAdd", acNormal, , , acFormAdd, , Me!EmployeeID

OnLoad for the license add form:

Me!EmployeeID = Me.OpenArgs
Me("NextTextboxInTabOrderName").SetFocus
Me!EmployeeID.Enabled = False

Thanks for the help though!

:::11oh1:::