Command button to populate part of a subform based on combobox 1

[storl2]

Hey all,

I couldn't find this in another post, but if there is something similar out there, please point me to it.

The title pretty much says it all. I am working in Access 97. I have two tables, pc_inventory (lists basic employee information, pulled from another database) and tblProblems, which we'll just call a ticket logging table.

There are is a form, frmProblems, with a subform fsubProblems. FrmProblems has a combobox listing employees' first and last names and user ID, with a command button that needs to populate fsubProblems with a new ticket, based on the user ID. I need some basic employee information prepopulated in the subform when the new ticket is created.

I had it sort of working by making the recordsource for the subform what was in the combobox on click, but that didn't work quite right. I don't want the entire ticket populated, since there is different information that I will need to enter for each ticket, but I do want some basic employee information populated in a new ticket when I click the command button.

I am not quite sure how to go about this, so any suggestions would be greatly appreciated.

 

[kjv1611]

Have you tried dimming String variables to store the information from the recordset or from another form/table for putting the information in the Subform?

Is the information for the employees stored in a separate table from the table you are pulling from, or no?

I would think that using a recordset of the table you are pulling the data from should work, or either use the DLOOKUP function in VB code.. just go to the vb editor and pull up the help file, and enter DLOOKUP in the Answer Wizard, there you should find the correct format and all. You could use the DLOOKUP function to do something like this: for employee ID number 1234, pull name, department, and title...

So, for your situation, maybe the DLOOKUP function would be the best route?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[storl2]

Thanks, from looking at the help file for dlookup, it looks like that might work for what I'm trying to do. I'll give that a shot and post the results back.

 

[storl2]

That worked perfectly. I didn't even end up having to use a subform, since I could do it all on one form. The dlookup allowed me to create a variable with the values that I wanted, and then I just had to make the text boxes equal whatever the variables were. Thanks!

 

[kjv1611]

Great! Glad the info helped.


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV