Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Unanswered: Look at Previous records using combo box and macro (combo box wizard)

    Hi everyone,

    Sorry if this was posted before, I searched through the forum and couldn't find what I needed.

    I have a form with a combo box made with the combo box wizard. Basically all I want the combo box to do is look up previous records (I don't want to have my boss, whom I'm making it for, have to click through hundreds of records to find the one she wants, if she wants to edit a previous record), right now the combo box displays 3 fields: Semester, LastName, and FirstName.

    The wizard handled mostly everything for me except that because of the structure of my database I have a double primary key between ID and Semester. The wizard of course just sorts by one field (in this case the Semester field) and because of my double primary key, it finds the first record with that semester (the same semester may be in many records) so of course if there's 2 records for 2 different people and they each share a common semester, even though I select a later record in the combo box it pulls up the first record it finds with that semester, and obviously that won't work.

    I tried to edit the SQL in the macro the wizard made to make to search for Last name or ID as well as Semester and no dice. I'm just really not any good with SQL and that's probably my main problem.

    Here's the where condition of the macro:

    ="[Semester] = " & "'" & [Screen].[ActiveControl] & "'"

    I just need to add either ID or LastName to it so it will pull up the correct record. (ID would probably be better since there is a very slim chance that 2 students with the same last name will show up)

    All the fields being dealt with here are text fields.

    I'm pretty good at coding so VBA would be a fine alternative.

    I'm sure there's an easy fix here I'm missing.

    Thanks for any help in advance!

    Also, I'm running Access 2007

    Cheers,

    Jordan
    Last edited by Cobra800089; 06-27-11 at 12:28. Reason: Forgot Access Version, sorry!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Using VBA you can retrieve the values of several columns from a Combo or a List box using the syntax:
    Code:
    Me.Combo_1.Column(n)
    with n = 0 for the first (leftmost) column, etc.

    Once you have the values you need to perform the search you can easily assemble the criteria, say:
    Code:
    "[Semester] = " & "'" & Me.Combo_X.Column(x) & "' AND [Id] = " & Me.Combo_X.Column(y)
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Posts
    3
    Hi Sinndho Thanks for your response ,

    I'm not quite sure what're you're suggesting. I understand that:

    Code:
    Me.Combo_1.Column(n)
    this is VBA code that is quite helpful thank you, but I'm not sure whether you're suggesting I use this:

    Code:
    "[Semester] = " & "'" & Me.Combo_X.Column(x) & "' AND [Id] = " & Me.Combo_X.Column(y)
    as VBA or as the where clause of the Macro. I'm guessing VBA and that I would do:

    Code:
    "[Semester] = " & "'" & Me.Combo57.Column(0) & "' AND [ID] = " & Me.Combo57.Column(3)
    Since my previous record combo box is called Combo57 and the first column (Column 0) is Semester and the fourth Column (Column 3) is ID.

    My only issue is that once I have this, I'm not sure how to make it go to the proper record.

    I'm not sure how to use the GoToRecord function since it requires you to know which record number you want to navigate to.
    Code:
    DoCmd.GoToRecord acDataForm, "Employees", acGoTo, 7
    Can you look up the record number using DLookup()? Or am I missing the point entirely?

    Thanks Again,

    Jordan

  4. #4
    Join Date
    Jun 2011
    Posts
    3
    Actually I solved this is a much easier way. I kinda cheated the system.

    Instead of using VBA or changing the macro, I added a autonumber field to the table so that way I would have 1 field unique to all records (I guess I could have used an autonumber field instead of a double primary key [I just thought it was better DB structure to do so]) Then using the combo box wizard I put the first value (The one the wizard uses to sort the combo box) as the auto number field.
    Obviously I didn't want to have an arbitrary number displayed all the time, so when the column width page in the wizard showed up I dragged the auto number field all the way down so it wasn't visible and voila, I had a record updating combo box that actually returns the correct result.

    I appreciate your help a lot though Sinndho, taking time out of your day to help people you've never met before is a noble cause!

    Thanks everyone and I hope this helps someone in the future,

    Jordan

Posting Permissions

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