Using a combo box to select multiple records on a form

[reverze]

Well, this seems like a pretty simple question, but for some reason I just can't get this to work.

I know it is probably really simple, and I have googled for answers and also looked in a Microsoft Access 2003 book ( I am using 2007 ) ... but still it isn't working.

So far, I have a very simple example database setup.

I have one table which is called tblEmp and a form which is called frmEmp



In the table, lets say I have three fields:

FirstName, LastName, and Salary

So on the form, all three of these exist as text boxes, and I have a combo box there called cboLookup

All I want to do is drop down the combo box which displays an employees last name, and by doing that it changes all of the text boxes on the form to the correct record that was chosen in the combo box.

This seems very simple, and I know it has to do with the AfterUpdate function on the combo box, but for some reason I just can't get the right VBA code or macro for this to work.

Does anyone have a simple answer for me?

[Rawb]

First off, I'd add some sort of ID field to your tblEmp Table. This helps deal with situations where, for example, employees have the same name (or at least the same last name). As your database grows, it also allows you to save space and decrease the complexity of your database by allowing you to reference the Employee by just this ID field instead of their full name.

However, to answer your question, you're right on the money. That's done from the Combo Box's After Update Event.

Assuming you've named your Text boxes tboFirstName, tboLastName, and tboSalary, just use the code below.

Code:

  Dim rstTblEmp as Recordset

  Dim strCriteria as String

  ' Snatch the contents of the tblEmp Table into a Recordset variable
  Set rstTblEmp = CurrentDb().OpenRecordset("tblEmp", dbObenDynaset)

  ' Filter down our recordsetvariable to just include the employee in the Combo Box
  strCriteria = "[LastName]='" & Me!cboLookup & "'"
  rstTblEmp.FindFirst strCriteria

  ' Set our Form variables to the value of the first Record in our newly filtered Recordset. Note that if you have more than one employee with the same last name, it will ALWAYS show the first one that was entered into the tblEmp Table!
  Me!tboFirstName = rstTblEmp("FirstName") ' First Name
  Me!tboLastName = rstTblEmp("LastName") ' Last Name
  Me!tboSalary = rstTblEmp("Salary") ' Salary

[reverze]

Great, thank you so much!

Going to grab some lunch and then I will give this a shot real quick and check back..

Thank you again, I really appreciate it!

[reverze]

Okay, I put the code in a recieved it and I am getting an error when trying to select from the box.

Run-time error 3001

Invalid Argument


The line that is in yellow is the following:

Set rstTblEmp = CurrentDb().OpenRecordset("tblEmp", dbObenDynaset)


All of my names match up..

[Rawb]

DOH! >.<

Try changing the dbObenDynaset to dbOpenDynaset. . .

[reverze]

haha wow.. I apologize too that I missed that error .. :P

The code has executed, but when I change through the combo box nothing is changing...hmm

[Rawb]

Umm, yeah, I must be off my game today. . .

Try moving it from After Update to On Change. After Update Events only fire once you leave the field after changing it's contents. On Change should fire as soon as you change the name.

[reverze]

hmm.. no luck still

[Rawb]

OK Then, let's try this. Add the following line to the very end of the function. If it's working, it'll pop up a box with a number followed by the person's name. The number is how many people we're finding in the tblEmp Table after it's Filtered

  MsgBox  rstTblEmp.RecordCount & " " & rstTblEmp("LastName") & ", " & rstTblEmp("FirstName")

[reverze]

It pulls the same record for all three choices

"3 Johnson, Brad" is what is displayed in the msgbox

[Rawb]

So it always shows the same name, no matter which one you select in the Combo Box?

Just a shot in the dark, but are all three names the same?

Seriously though, that's really weird. Can you save the database in Access 2000 format and attach it to the thread so I can take a look? I'm sure I'm missing something simple, but I can't for the life of me, think of what it may be.

[reverze]

No problem.. I appreciate the help.

It's probably something dumb that I am missing to be honest!..

[Rawb]

Ah HAH!

Apparently it's because the Combo Box is in the Form's Header while the other fields are in the Detail section.

If you move the Combo Box into the Detail section everything works fine.

What I'd recommend based on these findings is the following:

1. Move everything out of the Form Header and into Detail.
2. Make a Rectangle that covers the entire portion of the form what used to be in the Header and give it the same background color as the Header's.
3. Move it behind the rest of the Fields in the Form.

And there you go, you have a Form which looks just like it did, but works.

[reverze]

Hmmm, are you positive that you didn't change anything else?

I tried moving it to the detail section and still had no luck..

I deleted the form and went to "Form Design" and I created a brand new blank form with just the three text boxes and a combo box.

Copied over the code and everything but it still isn't pulling out the records... weird

[Rawb]

OK, two things:

First off, an apology. You came to this board for help. Not helpful attempts that end up not helping. I'm WAAAAAY off my game because I didn't realize that it still wasn't working after I made that change. It's just that a different record shows up for me (I get Emily) when I change the Combo Box.

Second, I think I know what the ACTUAL problem is, but because I haven't confirmed it yet, I'll refrain from mentioning any specifics. How about I just fix it and then send you a working copy before I say anything else