set recordsource of another form before opening it - AND show a specific record

[mattpem]

I have two forms -- formA and formB.

I want to create a button on formA that will open formB, change the recordsource, and then match the customerID

My situation is similar to the one in this thread, but with the added
aspect of matching the customerID. I can't find a way to make it work.

(I'm a solopreneur, and this is my customer database. I'm not a full-time programmer. My understanding of VBA is limited. But I can usually search forums like this one and achieve what I need. However this time, I'm stumped.)

Here is my code. (This works for pulling up the correct record in formB with the default recordsource. Now I need it to change the recordsource first, and then match customerID.)

'------------------------------------------------------------
' Command30_Click
'
'------------------------------------------------------------
Private Sub Command30_Click()
On Error GoTo Command30_Click_Err

    Docmd.OpenForm "formB", acNormal, "", "[customerID]=" &" '" [&customerID] & "'", , acNormal


Command30_Click_Exit:
    Exit Sub

Command30_Click_Err:
    MsgBox Error$
    Resume Command30_Click_Exit

End Sub

Is there a way to do this?

Thanks.

 

[MajP]

You could open it and set the recordsource. Then apply the filter

Docmd.OpenForm "formB", acNormal
with Forms!FormB
.recordsource = "newRecordsource"
.requery
.filter = "[customerID]=" &" '" [&customerID] & "'"
'remove single quotes if ID is numerice
.filteron = true
end with

 

[MajP]

In that thread there are other ways to do it posted by Duane Hookum. You could have the form based on a saved query and prior to opening it change the sql of the saved query. Something like

dim db as dao.database
dim qdf as dao.querydef
dim strSql as string
set db = current db
strSql = "Select..... from tblName where customerID = '" & me.customerID & "'"
db.querydefs("yourqueryname").sql = strsql

now open form