Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ComboBox to find record (A2K)

    I found this tip (Author Allen Browne), which looks quite nifty, however it is falling over, can anyone help please. The idea is that an Unbound Combo Box can be used as a means of record navigation.

    AfterUpdate Event Procedure (the example show the combo named as cboMoveTo and the field Customer Name as the 1st part of the loockup)

    Private Sub cboMoveTo_AfterUpdate()
    Dim Rst As Recordset
    Set Rst = Me.RecordsetClone
    Rst.findfirst "[Customer Name] = ' " & Me![cboMoveTo] & " ' "
    If Not Rst.NoMatch Then Me.Bookmark = Rst.Bookmark
    End Sub

    However it returns a compile error on the part Me![cboMoveTo] which is method or data member not found.

    Any suggestions please

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ComboBox to find record (A2K)

    That's very standard code and I use that kind of thing all the time, but your error suggests that it isn't recognizing either the name of the control or the name of the field. Is the actual name of your combobox cboMoveTo and is there a CustomerName field in your underlying recordset? If not, you'll always get an error.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox to find record (A2K)

    Hi Charlotte,

    Thanks for the quick response. I think I was getting the error because I left the . out between rst and findfirst. However even though I am now not getting the error message nothing seems to happen, perhaps I am expecting the wrong thing.

    To try this out I made a form based on my Customer table and in the header info put the combo box and named it cboMoveTo and then put the code into the AfterUpdate Property.

    What I expected was that when I selected a name from the cboMoveTo the rest of the fields would be completed on the form.

    Is that right or am I missing the point of it.

    TIA

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ComboBox to find record (A2K)

    There was a period in the appropriate position in the code you posted here. If the other fields are bound, then the statement "Me.Bookmark = Rst.Bookmark" should cause the form to display the appropriate record ... unless it doesn't find a match for "[Customer Name] = ' " & Me![cboMoveTo] & " ' "

    Is the combobox populated with just the customer name, or is the first column the CustomerID? In that case, you'll never get a match using the Customer table in the Northwind sample unless you change your criteria to "[CustomerID]= '" & Me!cboMoveTo & "'"

    Oh, you also have an extra space between the combobox value and the closing single quote in your existing criteria, which could also cause you problems.
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox to find record (A2K)

    Hi Charlotte

    Thanks for spareing the time to help. I have the combobox set to lookup all of the records from my Customers table but only have it set so that when the combobox is clicked only the CompanyName shows up (o";1";0" etc.). I have then placed text boxes on the form, the first of which is called CompanyName. My expectation was that one I picked the CustomerName from the combo box all of these other fields would be populated.

    To try not to bother you I opened the N/W db but on all of the forms the only one with a combo is the 'orders' form, however the afterupdate event is nothing like what I was trying to do. I copied this piece of code though and then named boxes appropriately on a new form and even that did not work.

    If you could tell me which form in N/W this example is in so I can try to solve it.

    TIA

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ComboBox to find record (A2K)

    <hr>I have the combobox set to lookup all of the records from my Customers table <hr>
    Sorry, but I'm not sure what you mean by this. The normal way to set this up is with a regular bound form that returns all the records in the records. The combobox itself is unbound so that in the afterupdate event, the code looks for a matching CustomerID and then moves to that record. All you need in the combobox is CustomerID and CompanyName, the value you want to match and the value you want to select. Everything else is going to be ignored for these purposes.

    I haven't looked at the Northwind sample in years unless I wanted something specific from it, so I have no idea where such a button might be. Where did you get the code from? It's entirely possible that nothing like that exists and it's simply a sample that uses those table because they wind up installed automatically when you install Access.
    Charlotte

Posting Permissions

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