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

[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.

 

[Minty]

Glad you sorted it - I was trying to figure out how to word my reply to steer you in the right direction.

Me. is used to refer to the current form object. When you use it in the VBA editor you get Intellisense assistance to pick up the right control names, hopefully !

If you can while you are still on the learning curve try and get into the habit of renaming your controls on forms so they are different to the defaults of the field name.
e.g. if your Field name is BudgetRate then call your control txtBudgetRate this makes sure you refer to the control rather than the field.

In fact a naming convention is a great thing to adopt full stop. http://access.mvps.org/access/general/gen0012.htm

 

[Gasman]

I am guilty of that I'm afraid, especially when I use the wizards to create a form based on table or query, so I thought 'why not write something to rename them'?

Created my own little program and then discovered this
:banghead:

 

[BadBoy House]

Hi all. Just wanted to come back and say thanks again for all the assistance with this project.

We went live with the database about six weeks ago and it turned out to be a big success. No problems whatsoever and the introduction of the database has made a big difference to the organisation of jobs within the office.

Thanks again.