Add Button auto fills form based on listbox

[valgore]

Hello, i have a form EmployeeAssignment. on it is two list boxes. the first is a list of the first and last names of all employees and the second filters all assignment records with that employees ID. what i want is when i click on an employee in the first listbox and click a button, i want my AddAssignment form to pop up with all of the employees information auto filled in. i'm having trouble telling the AddAssignment form which employee was selected from the listbox on the EmployeeAssignment form. i hope this makes sense!

any help is appreciated.

Josh

 

[llkhoutx]

You have to cycle through all the names in the first listbox to see which has been selected, then requery the second based upon than value and popup a form with that record as a filter.

 

[valgore]

thats not entirely what i want so let me explain a little bit more. right now i have 4 tables. one is Employees which holds their basic information, the second is Departments table that holds all of our departments. the third table is Projects, which holds all of our projects. The last table is where it gets a little confusing to explain. this database is being used to track what each employee is working on every month. so this table (AssignmentData) shows by month, what every employee's project is. (hope that makes sense) so there are multiple records per employee, but they are all different months and year. what the form does is it has the first list box containing all our employees, and when the user clicks on the employee, the second list box filters all of that employees AssignmentData entries. this works as intended.

what i want to do is when a user clicks on an employee name in the first listbox and clicks add button, i want my AddAssignment form to popup with the Employees name, project, salary and department from the Employees table already filled in. but the difficult thing is, all the text boxes and combo boxes are pointing to AssignmentData table because that's where the data is being stored.

hope that makes a little more sense

 

[Adam Caramon]

One way to do this would be to base your AddAssignment form on a query, and in the query one of the fields would be EmployeeID (or whatever your employee PK is). In the criteria of that field, you point to the value of the listbox.

See this link for more details: http://msdn.microsoft.com/en-us/library/ff834793.aspx

 

[valgore]

So, i did something similar where i pulled the EmployeeID from the listbox and it successfully inputs the data into the AddAssignment ONLY if on the DoCmd.OpenForm i set acFormEdit. if i change it to acFormAdd, nothing is populated. and i need to add a new record on this form. any thoughts?

Josh

 

[John Big Booty]

You can use the OpenArgs portion of the OpenForm method to pass information between the forms. You can then interrogate the OpenArgs in the OnLoad event of the form being called and use that to populate the required fields.

 

[John Big Booty]

The sample posted here has an example of passing complex data between two forms.

Check the Lost Focus event of Combo's 4 & 12 and the On Load event of form FRM_ExchAdd

 

[valgore]

ok thanks, ill check it out and ill post back if i have any questions

 

[valgore]

ok, so in my listbox, it only has EmployeeID, first, and last name. but in the AddAssignment i want to bring over EmployeeID, project, department, salary. i currently have the OpenArgs bringing over the EmployeeID so is it possible to open the EmployeeID's whole record and pull stuff from it?

Edit: Can i use an ADO recordset and just filter the record based on the OpenArgs and then just set each field to the recordset?

Edit number 2 : I got it working by using the OpenArg with the ^ as a separator. thank you!

Josh