error 2465 in combobox after update

[mdavid]

Hi,


I'm trying to change the content of a listbox in a subform when the selection in the combobox on the main form changes, here's the combobox after update event

Private Sub cmbMempSpecies_AfterUpdate()
    DoCmd.SearchForRecord , "", acFirst, "[SpeciesID] = " & cmbMempSpecies
Forms![MempBercMainFrm2].[Form]![MempBercListBox].[Form]![lstbMempComplaints].RowSource = "SELECT " _
      & "ComplaintID, SpeciesID, Complaint, Preparation, Administration, " _
      & "ComplaintEdited, PreparationEdited, AdministrationEdited FROM MempComplaintTom " _
      & "WHERE SpeciesID = " & [Me].[SpeciesID]
Forms![MempBercMainFrm2].[Form]![MempBercListBox].[Form]![lstbMempComplaints].Requery
End Sub

I get error 2465: Microsoft Access can't find the field '|1' referred to in your expression

Red text is highlited

Any ideas?

David

[pbaldy]

I'd use a variable for the SQL and this to see how it's ending up:

http://www.baldyweb.com/ImmediateWindow.htm

[pbaldy]

And "MempBercListBox" is the name of the subform control?

[Micron]

I say it's because you wrapped Me in square brackets. The first line of your expression doesn't look right either. It almost reads like it's for a subform on a subform. For a single level subform design, the subform reference is
[Forms]![Main form name]![subform control name].[Form]![control name on subform]

[mdavid]

Thanks for your help.
I misspelled a textbox name - all gets a bit blurry at 3 am.
This is what worked:

Forms![MempBercMainFrm2].[Form]![MempBercListBox].[Form]![lstbMempComplaints].RowSource = "SELECT " _
      & "ComplaintID, SpeciesID, Complaint, Preparation, Administration, " _
      & "ComplaintEdited, PreparationEdited, AdministrationEdited FROM MempComplaintTom " _
      & "WHERE SpeciesID = " & Forms![MempBercMainFrm2].[txtSpeciesID]
Forms![MempBercMainFrm2].[Form]![MempBercListBox].[Form]![lstbMempComplaints].Requery

I finally used Expression Builder - that someone here suggested - to reference the controls, a real time saver.
Thanks
David