Unanswered: Look at Previous records using combo box and macro (combo box wizard)
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.
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
Last edited by Cobra800089; 06-27-11 at 12:28.
Reason: Forgot Access Version, sorry!
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,