Cbox Wizard (Acc2k) code - error 3077

[TGHockett]

As many of you are probably aware, I have been much challenged to come up with a "simple" combo box to move to a selected record on the same form, so I tried again with the combobox wizard today, which works ok for a while, then eventually (after 3-7 correct selections) crashes.

Anyway, the error I get is "Runtime 3077 - Syntax error (missing operator) in expression"

The code the wizard created is:

Private Sub Combo183_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MSP_ID] = '" & Me![Combo183] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Where MSP_ID is the primary key on the table the master form is based on. BTW, the debugger pts to the line starting with "rs.findfirst etc" as the source of the error.

Any ideas?

TGH

 

[Rich]

This may be the cause of your problem though I doubt it http://www.mvps.org/access/bugs/bugs0012.htm
the other thought is to try
Go to control Forms!etc,
Go to control textbox name.
HTH

 

[zunan]

What I have done, in A2K, and I know works, is programmatically set the RecordSource of the form with an SQL statement in the "Open" event. Populate the cbo with a distinct list of a particular field. In the "AfterUpdate" event of the cbo re-set the RecordSource of the form to the original SQL with a "WHERE" clause: WHERE [FieldName] = Forms!MyFormName!cboComboBoxName
Then: Me.Refresh
You can also have a CommandButton to ShowAllRecords: just re-set the RecordSource back to the original.
I have a search form with 12 Combo Boxes, each searching a different field of the table.

 

[TGHockett]

Thanks Guys for the feedback ... will tryout Zunan's ideas on Monday.

Rich -- the article you pointed to re the bookmark bug is for Acc97 and before right? I thought I read somewhere here that MS fixed it in Acc2k? Am I wrong? Would MS let the combo wizard create bad code? (dumb question?

)

Sidenote to Zunan - based on a MS article re list/cbo performance enhancements (article# is at the office), I based the recordsource on a query (changed after the Wizard did its thang)-- would that have any impact on your suggestions? It's a very basic select query for the unique MSP_IDs and another field I would like to display for user information (e.g., MSPstatus).

Thanks again folks for jumping in ...

TGH

 

[TGHockett]

Zunan -- Being very new to VBA, I couldn't quite follow all your suggestions, but after looking at it again today, it sounds very similar to one of the suggestions from MSN that I tried and it worked fine for a while, but then locked up on me. Admittedly it was not identical to yours via VBA, but it did involve using your SQL/filter approach.

And somewhat more frustrating, I worked from my home office today, and the cbox worked perfectly -- not one occurance (after 20+ rapid cbox selections) of the Runtime 3077 -- Syntex errors previously mentioned.

Anyone have any other suggestions before I pull out all my hair? < quasi grin >

TGH