Data onto a Form 1

[carpetbelly]

I've got a form with a combo box on it and some text boxes.

The combo box is prepopulated with a list in the 'Row Source' property with the following SQL

SELECT [qryStaffEnt].[StaffName] FROM qryStaffEnt;

So basically just trying to populate it with a bunch of staffnames.

The 3 other text boxes I'm trying to populate from two other tables which data I can obviously just stick into a query.

I've been trying to do it by seperate SQL statements but that's been coming up as invalid and judging by the error messages it seems I need to use a recordset.

How would I go about matching the name selected in the combobox to records in a recordset then populate the other text boxes with the fields I need?

 

[SeeThru]

Can you not just use a query, referring to the other control

i.e.

First combo:
SELECT [qryStaffEnt].[StaffName] FROM qryStaffEnt;

Second combo
SELECT [PayRate] FROM qryStaffPay WHERE [StaffName]=forms!myform![firsttcombo]

Third combo
SELECT [Bonus] FROM qryBonuses WHERE [StaffName]=forms!myform![firsttcombo] AND PayRate=forms!myform![secondcombo]


and in the OnUpdate of the first two combos, requery the others

i.e.
firstCombnUpdate
SecondCombo.requery


SecondCombnUpdate
ThirdCombo.requery

Or have i mis-understood?

 

[carpetbelly]

well, they're text boxes but other than that I think you might be right and I could just be over complicating things which I wouldnt put past me.

 

[SeeThru]

Even easier then...

In the AfterUpdate of the combo, use dlookup to fill in the values in the other text boxes

combnUpdate

textbox1=dlookup("Pay","Staff","StaffName='" & me!combo & "'"

note that as you are looking up a string, you must build single quotes into the dlookup string

 

[carpetbelly]

awesome, worked first time. Thanks SeeThru and made my code a lot neater than the mess I was looking at