How to populate a text box and field based on combo box value? (1 Viewer)

BadBoy House

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
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

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
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

AWF VIP
Local time
Today, 05:57
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
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

AWF VIP
Local time
Today, 05:57
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
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

AWF VIP
Local time
Today, 05:57
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
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

MVP / VIP
Local time
Today, 05:57
Joined
Jan 14, 2017
Messages
18,186
I thought this had already been explained earlier in the thread but perhaps I'm wrong ...:rolleyes:

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

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
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

MVP / VIP
Local time
Today, 05:57
Joined
Jan 14, 2017
Messages
18,186
Fair enough. In that case, use an update query or SQL statement
 

BadBoy House

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
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

AWF VIP
Local time
Today, 05:57
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
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

AWF VIP
Local time
Today, 05:57
Joined
Jul 26, 2013
Messages
10,355
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

MVP / VIP
Local time
Today, 05:57
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
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

AWF VIP
Local time
Today, 05:57
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
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

Registered User.
Local time
Today, 05:57
Joined
Oct 8, 2009
Messages
54
I've figured it out.

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


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


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


Thanks again for your help.
 

Users who are viewing this thread

Top Bottom