use combobox lookup value on one form to populate another form

[erniemack]

I have a form with a combobox using the following query:

SELECT   JobNumber,
         CustomerName,
         [PO],
         [Customer Motor #],
         assm_status
FROM     tblJob
WHERE    assm_status<>"Done"
ORDER BY CustomerName;

The bound column is column 1

Private Sub CustomerSearch_AfterUpdate()
On Error GoTo Err_customersearch_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim acct As Integer
    acct = Me.CustomerSearch.Value
    stDocName = "frmOESJOBmaint"
    DoCmd.OpenForm stDocName, , , "[jobnumber] = acct"
Exit_customersearch_Click:
    Exit Sub
Err_customersearch_Click:
    MsgBox Err.Description
    Resume Exit_customersearch_Click
End Sub

When you run this you are asked for the value for parameter "acct". If you input a jobnumber, the proper form/record is presented. I would like to eliminate having to input the parameter value again since the wanted record was selected in the combobox.

Thanks

[NeoPa]

Your code is not passing the value just stored in your variable acct. It is passing a reference into SQL of acct. As SQL has no idea what this may be (it has no link into your running VBA code) it requests a value from the operator.

Try instead for line #8 :

DoCmd.OpenForm stDocName, , , "[jobnumber] = " & acct

Welcome to Bytes!

[erniemack]

Thanks that works like a champ!!!

[NeoPa]

Always a pleasure. I hope you have a clearer understanding of why the previous version was having difficulties :)