Combo Box problem (Access 2K)

Topic

New Reply

Background:
Database holds lot traceability records. Batch Nos are in form of “04108A1” where 04 is year, 108 is a sequential number recorded in a factory register, the 6th character is a machine type, and the 7th Digit is a Machine Id. (BTW, I hate ‘meaningful’ codes!).

I have a continuous form which allows for the review of batch nos, showing Raw Material Batch Nos, Dates, Shifts etc.

I need to be able to select from a combo box, the Left 5 characters, of batch nos, and list all records that start with those characters. I have tried using a calculated field in the underlying query for the form, which is calculated as BatchNo: Left([BulkBatch],5). This is the source of the Combo box. I then tried to set as criteria for the BulkBatch field, the value of the ComboBox. I suspect that I may have the equivalent of the Excel Circular Reference, because I get an error which when debugged, highlights the ‘Bookmark’ line of the code below.

Private Sub Combo15_AfterUpdate()
‘ Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst “[BatchNo] = ‘” & Me![Combo15] & “‘”
Me.Bookmark = rs.Bookmark
End Sub

I am probably close to the solution, but just can’t see the wood for the trees.

TIA

Reply | Quote

Replies

Hey Dude,
I’m not quite sure I understand your form — is the Combo Box in the header and the rest of the data in the detail section? I’m assuming you’re not trying to use a subform, right? If you can you be more specific, I’m sure I have the answer since I deal extensively with 17 character VIN’s off vehicles. They are, as you say, “meaningful” codes BTW, don’t knock them too much, just break them up into different fields upon entry and then forget having to decode on the fly.
Kathi

Reply | Quote

Yes Kathi, combo box is in header. Detail section nis continuous form

Reply | Quote

Yes Kathi, combo box is in header. Detail section nis continuous form

Reply | Quote

Hey Dude,
I’m not quite sure I understand your form — is the Combo Box in the header and the rest of the data in the detail section? I’m assuming you’re not trying to use a subform, right? If you can you be more specific, I’m sure I have the answer since I deal extensively with 17 character VIN’s off vehicles. They are, as you say, “meaningful” codes BTW, don’t knock them too much, just break them up into different fields upon entry and then forget having to decode on the fly.
Kathi

Reply | Quote

In this situation you might be better off to apply a filter to your form based on the 5-characters selected in the combo box. That way you limit the display of records to the subset, and you can even use a LIKE experession in the filter so you don’t need to build a separate expression field in the query. Thus instead of doing the Recordset.Clone, you set the filter property of the form and then turn the filter on. If you need further details, post back. Good luck.

Reply | Quote

In this situation you might be better off to apply a filter to your form based on the 5-characters selected in the combo box. That way you limit the display of records to the subset, and you can even use a LIKE experession in the filter so you don’t need to build a separate expression field in the query. Thus instead of doing the Recordset.Clone, you set the filter property of the form and then turn the filter on. If you need further details, post back. Good luck.

Reply | Quote