Enter Parameter popup, but not sure why

[BlueJacket]

I realize this may not be the best way to go about this, but here is my situation...

I have a Client Information form with a combo box for the client name, as well as other generic information text fields. If I want to add a new client, it's my understanding that I can't type in a new client directly into the combo box (since it has two columns), but I have to add in the client name to the table first.

So I created a New Client button that opens a New Client popup form and closes the Client Information form. This part works fine.

The New Client form only asks for the client name with an "OK" button. It also has a "txtID" text field that is not visible. The button has the following code:

Private Sub butOK_Click()

    If Me.Dirty Then
        Me.Dirty = False
    End If

    Dim stDocName As String
    Dim stNewRecord As String

    stDocName = "frmClientInfo"
    
    stNewRecord = "[ID] = " & Me![txtID]
    DoCmd.OpenForm stDocName, , , stNewRecord
    
    DoCmd.Close acForm, Me.Name
    
End Sub

But when I click on the "OK" button after entering in a new client name, I get an "Enter Parameter Value" box asking me for the ID value. The debugger highlights " DoCmd.OpenForm stDocName, , , stNewRecord" but I don't see what's wrong. When I hover my mouse over the variables, it has the correct new value for the ID field.

Any input on why this is happening? Am I going about this the wrong way?

 

[mjdemaris]

The variable is a string, ID is an integer.

DoCmd.OpenForm stDocName, , , stNewRecord

could try:

DoCmd.OpenForm stDocName, , , "[ID] = " & Me.txtID

If you want to pass parameters:

DoCmd.OpenForm "frmAddLineItems", acNormal, , , acFormEdit, acDialog, "RequestID_FK|" & RequestID

Then, in the Form Load event:

    Dim intPos As Integer
    Dim strCtrlName As String
    Dim strValue As String
    
    If Len(Me.OpenArgs) > 0 Then
        intPos = InStr(Me.OpenArgs, "|")
        
        If intPos > 0 Then
            strCtrlName = Left$(Me.OpenArgs, intPos - 1)
            strValue = Mid$(Me.OpenArgs, intPos + 1)
            Me(strCtrlName) = strValue
            rID = CInt(strValue)
        End If
        
    End If

Where I use rID as a module level Integer.

 

[BlueJacket]

I'm dumb. I just missed putting the ID field in the original form's record source. Such a simple mistake that took me so long to find.

 

[BlueJacket]

The variable is a string, ID is an integer.

DoCmd.OpenForm stDocName, , , stNewRecord

could try:

DoCmd.OpenForm stDocName, , , "[ID] = " & Me.txtID

If you want to pass parameters:

DoCmd.OpenForm "frmAddLineItems", acNormal, , , acFormEdit, acDialog, "RequestID_FK|" & RequestID

Then, in the Form Load event:

    Dim intPos As Integer
    Dim strCtrlName As String
    Dim strValue As String
    
    If Len(Me.OpenArgs) > 0 Then
        intPos = InStr(Me.OpenArgs, "|")
        
        If intPos > 0 Then
            strCtrlName = Left$(Me.OpenArgs, intPos - 1)
            strValue = Mid$(Me.OpenArgs, intPos + 1)
            Me(strCtrlName) = strValue
            rID = CInt(strValue)
        End If
        
    End If

Where I use rID as a module level Integer.

I'm curious about what's going on here though. I'm still in the beginning stages of learning VBA, as you can probably tell. I haven't done much with integers in VBA, but is this manually entering a value for the ID field? Instead of having the autonumber generating one?

 

[mjdemaris]

No, the last section of code passes the ID and the control name as ONE value - a string.

So, the If statement in the form called parses out the ID value and the control name - where to put that value (by using the pipe symbol as a separator).

Oh, and the called form is unbound. Sorry - I forgot to mention that earlier. Sometimes it's easier to use unbound forms.