Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Combo Box problem (Access 2K)

    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

  2. #2
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Combo Box problem (Access 2K)

    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 [img]/forums/images/smilies/smile.gif[/img] 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

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combo Box problem (Access 2K)

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

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Combo Box problem (Access 2K)

    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.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •