Prefilling a form - Problem

[matthewnsarah07]

I have a staff holiday request form in use. Each time they have to fill in their name [Staff Name], Line Manager [Team Manager and their Id no. [Staff Number]

They select their name from a combobox but I then want them to click a button 'AutoFill' which sets the value of the Staff ID and Manager.

All the necessary info is in one table called tblStaffinfo

I set up a query which bases it self on the contents of the Staff Name combo

I then set a macro which opened the query, and then did a SetValue
Item as: [Forms]![Annual Leave Form].[Staff Number]
Expression as: [Queries]![qryPrefill].[Staff Number]

When I run the macro on the form after updating the staff name the query appears correct but then halts with the error
'The object doesn't contain the automation object Queries'

How do I get round this? Can you use queries in expressions

 

[jrmask]

Set your rowsource of the combo box to query all the information you want to update. Set Column Widths and Column Count appropriately. For the combo box you will only want to store the ID for the name and have the Name of the person in the second column to show so that when someone choose a name they will see their name, the Column widths may look like this 1;2;0;0;0;0; depending on how many columns of infor you query and column count would be 6 in this case.
In the AfterUpdate event set the other controls to equal the combo query such as Me.[Line Manager] = Me.[Staff Name].Column(2). Remember this is zero based so the first column is 0 and the third would be (2)

 

[matthewnsarah07]

Thanks for your reply - I've got my form prefilling now, thanks again your advice was spot on