RecordSource error in After Update Event Procedure 1

[cimoli]

I am trying to make a fancy After Update event in a lookup field that is on a main form.
I am kind of trying to make a template for myself.

Can you review and see my mistake? I tried 2 methods. The 1st method WORKS and retrieves One ID and all the fields needed.
I have a criteria in the underlying query qryCustomerInfo.

Private Sub cboID_AfterUpdate()
'1/18/2015 method 1 works with a criteria in the qry.
'The qry criteria in the ID field is [Forms]![frmCustomer]![cboID]
Me.RecordSource = "qryCustomerInfo"
Me.Refresh
End Sub


HOWEVER, I am next trying a method #2 to avoid having a Criteria in the underlying qry named qryCustomerInfo.
Then i can use qryCustomerInfo for other lookup fields in this main form.

In method #2, I enter an ID number in the lookup field cboID and hit enter. I get an error message of :

"Access cannot find the field 'recordsource' referred to in your expression".


Private Sub cboID_AfterUpdate()

'method 2. ID and cboID are numbers.

On Error GoTo Err_cmdCboID_Click

Dim strSQL As String

Me!RecordSource = "qryCustomerInfo"

strSQL = Me!RecordSource = "Select * From [qryCustomerInfo]" & _
"Where [ID]=[frmCustomer]![cboID]"

'remember to use Order BY for sorting a different issue.

DoCmd.Maximize

Exit_cmdCboID_Click:
Exit Sub

Err_cmdCboID_Click:
MsgBox Err.Description
Resume Exit_cmdCboID_Click

End Sub


thank you.

 

[dhookom]

You have two "=" in this line:

strSQL = Me!RecordSource = "Select * From [qryCustomerInfo]" & _
 "Where [ID]=[frmCustomer]![cboID]"

I would try:

[COLOR=#4E9A06]' assuming ID is a numeric field[/color]
Me!RecordSource = "Select * From [qryCustomerInfo]" & _
 "Where [ID]= " & [frmCustomer]![cboID]

If the code is running in frmCustomer, you can replace frmCustomer with Me.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

It also looks like you are missing a space

[COLOR=#4E9A06]' assuming ID is a numeric field[/color]
Me!RecordSource = "Select * From [qryCustomerInfo][highlight #FCE94F] [/highlight]" & _
 "Where [ID]= " & [frmCustomer]![cboID]

Duane
Hook'D on Access
MS Access MVP

 

[cimoli]

Duane - I must have another error. When I am on frmCustomer and type in an ID number like 173162 for example,
and hit enter, I get a message : "access cannot find the field 'RecordSource' referred to in your expression.

I tried taking out the Me!RecordSource = "qryCustomerInfo" but got the same error message.

Here is the revised After Update in the lookup field cboID. thanks much.

Private Sub cboID_AfterUpdate()

' Duane 1/20/2015 'ID and cboID are numbers.

On Error GoTo Err_cmdCboID_Click

Me!RecordSource = "qryCustomerInfo"

' assuming ID is a numeric field
Me!RecordSource = "Select * From [qryCustomerInfo]" & _
"Where [ID]= " & Me![cboID]

' reminder to use Order BY for sorting a different issue.

DoCmd.Maximize

Exit_cmdCboID_Click:
Exit Sub

Err_cmdCboID_Click:
MsgBox Err.Description
Resume Exit_cmdCboID_Click

End Sub

 

[dhookom]

Use

Me[highlight #FCE94F].[/highlight]Recordsource

and don't forget to add the space I suggested in the SQL statement.

Duane
Hook'D on Access
MS Access MVP

 

[cimoli]

Thanks Duane, It worked. Here is the final result.

Private Sub cboID_AfterUpdate()

' Duane 1/20/2015 'ID and cboID are numbers.

On Error GoTo Err_cmdCboID_Click

' assuming ID is a numeric field
Me.RecordSource = "Select * From [qryCustomerInfo]" & _
"Where [ID]= " & Me![cboID]

' reminder to use Order BY for sorting a different issue.

Exit_cmdCboID_Click:
Exit Sub

Err_cmdCboID_Click:
MsgBox Err.Description
Resume Exit_cmdCboID_Click

End Sub