How to populate a text box and field based on combo box value?

[BadBoy House]

Thanks Minty

I've just tried this on the new job form whereby you can select the client from the dropdown box but it actually populates the job table entry with the client ID which is perfect.

 

[BadBoy House]

I have the Job table and via a New Job form it's possible to create new job records.

Next up is tblBudget and tblBudgetStaff. These two tables are linked via tblBudget.BudgetID -> tblBudgetStaff.BudgetID.

I need to figure out how to create the New Budget form so that it not only creates a new record in tblBudget but also links to tblBudgetStaff and automatically creates new records and enters the BudgetID from tblBudget to the BudgetID field - it would need to enter the same BudgetID for each row of staff details added for that budget.

It's like saying that an order has many products associated with it.

Are there any pointers you can suggest?

 

[Minty]

This is normally handled by a form and sub form. The main form is for your "order header" and the sub form has the order lines in it.

By using the Master / Child properties of the sub form CONTAINER (that is the control that holds the sub form, NOT the sub form itself) you can have auto populate the foreign key field.

Make two simple forms for both tables. The header should probably be a single form , the multiple details form should be a continuous form. Once built save both, then drag the details form icon onto the main form and let the wizards do their thing. That should give you a start.

 

[BadBoy House]

I've done that and it seems to work great.

If I manually enter the corresponding JobID (I will need to change this to a lookup to make it easier for users) and then start adding StaffID's, BudgetRates etc. on as many rows as required, the BudgetID field from the master form automatically populates in the BudgetID field in the child form.

I will need to make the StaffID box a lookup and for the budget rate to automatically populate but after that it should hopefully just be a case of adding the calculation to multiply BudgetRate x BudgetHours - presumably it is possible to do this even though the sub-form is set to continuous?

Again thanks for your help on this

 

[Minty]

Yes that sounds about right.

As a rule I tend to build continuous sub forms that imitate a datasheet, rather than using that as the default, you'll find it gives you a lot more control.

You look to have grasped it all pretty quickly.

 

[BadBoy House]

Things are going pretty well. I've got the sub-form in place and linked to the parent form and everything seems to be doing what it should.

There's just one problem with the sub-form: the BudgetRate field on the form gets automatically populated with the hourly rate of the staff member, which it picks up from column 1 of the combo box.

However I need the value in the BudgetRate field on the form to populate the BudgetRate field on tblBudgetStaff.

The StaffID and BudgetHours fields already populate as they should do.

I can see why the BudgetRate field isn't populating the corresponding field on tblBudgetStaff and that's because there is nothing telling it to (whereas the StaffID box is populated as configured by the combo box wizard).

It's as if I need some sort of action to be in place to take the BudgetRate form field value and put this in to the corresponding field on tblBudgetStaff.

Things are certainly clicking in to place now but this one has me stumped.

Any ideas?

 

[Minty]

I can't quite fathom what's not being pulled through from your description. Can you post up the picture of your tables and relationships and highlight what needs pulling from which table?

It sounds like you simply need to include that value in your combo and use that as well, but I'm not convinced I've grasped the structure correctly.

 

[BadBoy House]

Ok here we go...

The StaffSelect combo box is linked to the StaffID field on tblBudgetStaff and this gets populated in the table as expected.

The BudgetRate field on the form gets populated with the staff member's hourly rate after they are selected from the StaffSelect dropdown. This is achieved by using the Column(1) value on the combo box.

I also need this BudgetRate value to be stored in tblBudgetStaff as well.

Hope this makes sense - I have included some screenshots below.

 

[isladogs]

I thought this had already been explained earlier in the thread but perhaps I'm wrong ...

You don't need to populate that field in tblBudgetStaff. In fact that field is redundant as you can just look up the value in the tblStaffChargeRate table and use that in your query/subform.

If you want the same info in tblBudgetStaff, it can be done with an update query but you are just duplicating data

 

[BadBoy House]

I did think of that, however from time to time staff hourly rates change and we need to record the rate that it was when the budget was set up.

 

[isladogs]

Fair enough. In that case, use an update query or SQL statement

 

[BadBoy House]

Would an update query or SQL statement be able to add the staff member's hourly rate to the appropriate field on tblBudgetStaff, given that the new row on tblBudgetStaff wouldn't exist at that point (as the record will not have been saved)?

Sorry this is my first go at creating an Access database so I'm learning as a I go.

 

[Minty]

You could include the rate as part of your combo data.
Just add that table to your combo rowsource query as a left join in case they don't have a rate set at that point.

 

[BadBoy House]

The hourly rate is already part of the combo data/rowsource - the BudgetRate field on the form recognises the value.


I just can't work out how to get the value to populate the corresponding BudgetRate field on tblBudgetStaff.


isladogs mentioned about using an update query or SQL statement but I'm struggling to get my head around how to do this.

 

[Minty]

I'm confused, but it is Monday. You said

The BudgetRate field on the form gets populated with the staff member's hourly rate after they are selected from the StaffSelect dropdown. This is achieved by using the Column(1) value on the combo box.

I also need this BudgetRate value to be stored in tblBudgetStaff as well.

From this it appears you are already storing the rate in tblBudgetStaff by using the combo ?

Or have I firmly grasped the incorrect end of the stick.

 

[isladogs]

Would an update query or SQL statement be able to add the staff member's hourly rate to the appropriate field on tblBudgetStaff, given that the new row on tblBudgetStaff wouldn't exist at that point (as the record will not have been saved)?

I'm also a little confused ....but if the record doesn't exist, you would instead use an append query to create the record including the hourly rate value (selected from the combo????)

 

[BadBoy House]

On the new budget subform, when you select a staff member from the StaffSelect combo, this automatically populates the StaffID field on tblBudgetStaff.

When I added the combo box to the form I configured it via the wizard to add the StaffID number to the StaffID field on tblBudgetStaff. This works fine.

The StaffSelect combo contains the staff ID, hourly rate and name.

The BudgetRate field on the form gets populated with the hourly rate based on this being column 1 of the All Staff query - it populates this on the form when you select a staff member.

So the BudgetRate text box/field on the *form* gets populated with the rate, but the BudgetRate field in the tblBudgetStaff *table* does not get populated with the rate - and I need it to.

I think this is because there needs to be some sort of link between the BudgetRate field on the form and the BudgetRate field on the table.


The fields on the form are as follows:

BudgetStaffID (tblBudgetStaff.BudgetStaffID)
BudgetID (tblBudgetStaff.BudgetID)
StaffID (tblBudgetStaff.StaffID)
StaffSelect (row source = All Staff query)
BudgetRate (text box with record source of =[StaffSelect].[Column](1))
BudgetHours (tblBudgetStaff.BudgetHours)
Notes (tblBudgetStaff.Notes)

 

[Minty]

Ah - Now I understand.
Change the control source to tblBudgetStaff.BudgetRate then in your combobox after update code use

Me.BudgetRate = Me.StaffSelect.Column(1)

 

[BadBoy House]

I get where you're coming from - so after selecting the staff member from the combo box the After Update property would tell Access to look up the rate from the appropriate column and then place it in the BudgetRate field on both the form and on tblBudgetStaff?

I changed the Control Source of the Budget Rate field on the form to tblBudgetStaff.BudgetRate and then set the After Update Expression property of the combo box to Me.BudgetRate = Me.StaffSelect.Column(1) but this gave an error which said "The expression After Update you entered as the event property setting produced the following error: The object doesn't contain the Automation object 'Me'".

I presumed that was because the name of the fields on the form don't start with "Me."?

Anyway I changed the After Update expression on the combo box to [BudgetRate] = [StaffSelect].[Column](1) but I then get an error which says "The expression After Update you entered as the event property setting produced the following error "Type mismatch".

I noticed afterwards that the combo box after update expression actually shows =[BudgetRate]=[StaffSelect].[Column](1) - I presume Access adds the "=" symbol at the beginning automatically?

There are a couple of screenshots below showing the Expression settings for the BudgetRate form field and the Combo Box After Update.

Definitely feel like I'm almost there with this problem.

Thanks again

 

[BadBoy House]

I've figured it out.

I was picking the Expression option for the After Update action on the combo box .


Went back in and used your code via the Code Builder option instead and this works a treat.


On to the next stage now - I'll no doubt have a few more queries!


Thanks again for your help.