Error when opening a form with selection from combobox

[dstorms]

Hi,

I am trying to create a command button that:

1. Opens the form as selected from a combobox,
2. Creates a new record, and
3. Inserts the EmployeeID automatically.

   Dim stItem As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    
    stItem = Forms!frmDigitalEquipment.CategoryList
    stDocName = "frmDE" & stItem
    stLinkCriteria = "[EmployeeID]=" & Forms!frmDigitalEquipment.EmployeeID

    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

But when I click on the button I get the error message: "Applicatio n-defined or object-defined error." After troubleshooting the code, I've narrowed problem down to line 5, but now I don't know how to fix it.

Your help would be much appreciated.

dstorms

[RuralGuy]

If this code is on the frmDigitalEquip ment form then you can reference the controls on the form by just using Me. ie: Me.CategoryList

Does this EmployeeID already exist in the RecordSource of the next form? Do you really have a bunch of forms named frmDE...? Why?

[dstorms]

This took care of 1 & 2:

5.    stItem = Me!Combo9.Column(0)

Going to try fix step 3. The code opens the correct form in Add mode, but doesn't automatically fill in the EmpolyeeID field.

[RuralGuy]

Does this EmployeeID already exist in the RecordSource of the next form? Do you really have a bunch of forms named frmDE...? Why?

If not then you will need to pass the EmployeeID in the OpenArgs argument and catch it in the OnLoad event of the next form and put it in the control you want.

[dstorms]

If this code is on the frmDigitalEquip ment form then you can reference the controls on the form by just using Me. ie: Me.CategoryList

Does this EmployeeID already exist in the RecordSource of the next form? Do you really have a bunch of forms named frmDE...? Why?

I figured out that the name of the combobox control was actually Combo9.

From what I understand, clicking on the button would open the from in add mode with all the data fields blank. I want to be able to copy the data from the EmployeeID field in the DIgitialEquipme nt form to the EmployeeID field in the newly opened form.

The frmDE prefix was used to keep these forms together in the database window, and I had several forms because I had separate tables for each type of digital equipment.

[RuralGuy]

Don't use a WhereCondition argument and here's a link on how to reference a control on another open form.

http://www.mvps.org/access/forms/frm0031.htm

[dstorms]

If not then you will need to pass the EmployeeID in the OpenArgs argument and catch it in the OnLoad event of the next form and put it in the control you want.

I got the first half right, but the OnLoad event may not be working the way I'd hoped. I've tried these two expressions:
=[Me].[frmDigitalEquip ment]![EmployeeID]
=[EmployeeID]=[Me].[frmDigitalEquip ment]![EmployeeID]
but neither would create the new record. I think it is because the EmployeeID control is a combo box that displys the name but stores a number for the value.
(Like a pulldown menu.) Would that have an effect on the script?

[RuralGuy]

Why would the EmployeeID control be a ComboBox in the 2nd form? You are not using Lookup Fields are you?

http://www.mvps.org/access/lookupfields.htm