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

[BadBoy House]

Hi all. I was wondering if anyone could help me with this...

I have a form based on a table called 'Budget'.

The form contains combo boxes which look up a list of staff names via a query.

The same query also contains hourly rates for each staff member.

What I am trying to achieve is when a staff member is selected from the combo box, for their corresponding hourly rate to then appear in a text box next to their name on the form. I also need their hourly rate to then be automatically entered into a specific field on the Budget table.

Many thanks in advance

 

[theDBguy]

Hi. Typically, you wouldn't want to store the same information in multiple tables. To display the hourly rate from the combobox, you can use the Column property. For example, assuming the hourly rate is in the second column of the combobox, you can add an unbound textbox on the form with the following control source.


=[ComboboxName].[Column](1)


However, if you must store the data, then you can assign the value to another bound textbox. For example, in the AfterUpdate event of the combobox, you could try something like:


Me.TextboxName = Me.ComboboxName.Column(1)


Hope it helps...

 

[Minty]

Pull those values into the combo box row source as hidden columns.

You can then refer to them in the after update code of the combo box to set other control values on your form, something like

Me.YourTextBox = Me.cmbEmployees.Column(2)

Column numbering starts at 0 so the above would give you the 3rd hidden column.

 

[BadBoy House]

I got it wrong when I mentioned that the hourly rate would need to be entered into a corresponding field in the Budget table.

- Select the staff member from the dropdown
- Their specific hourly rate should then appear in a text box next to their name
- A third text box next to their houry rate would be used to enter the number of hours.
- A fourth text box would multiple the hourly rate by the number of hours to give a total.
- The total of the fourth text box would be automatically entered into a specific field on the Budget table.

So the form would look like this:

Staff Name - Hourly Rate - Number of Hours - Total
John Smith - £25.00 - 2 - £50.00
Pete Jones - £100 - 3 - £300.00
etc.

So far I have a list of combo boxes underneath each other and these use lookups to then enter the staff name in to the appropriate field on the budget table. This works fine.

There are basically two things I'm struggling with:

1 - How to display the hourly rate for the selected staff member in a text box next to their name.

2 - How to make the text box for each person that calculates the hourly rate x number of hours total to enter that total in to a specific field on the Budget table.

Hope this all makes sense!

Thanks again

 

[isladogs]

As already stated use the Column property of your combo which should have 3 columns : StaffID (bound); StaffName; HourlyRate.
Make the column widths 0cm;2.5cm;0cm or similar so the StaffName is displayed

Then set the control source of your txtHourlyRate as Me.comboname.Column(2) as it's the third column

As for your second question, set the txtTotalHours control source to Me.Comboname.Column(2)*me.txtHours

 

[Uncle Gizmo]

If you have access to YouTube, you may find this video in my MS Access blog informative...

http://www.niftyaccess.com/display-data-from-a-combo-box-in-a-text-box/

Sent from my SM-G925F using Tapatalk

 

[BadBoy House]

Thanks very much - that's worked great.

I can now pick a staff member from the dropdown box and their hourly rate is displayed in the Rate box next to their name.

I have also got the total text box in place which multiplies their rate by the number of hours.

The staff member name and hours gets saved to the respective fields in the Budget table as expected.

I now need the value in the respective Rate text box to go in to the corresponding Rate field in the Budget table.

So for instance, if on the first row of the form I select a staff member whose rate is £59.00, I need 59.00 to go in to the Staff1Rate field on the Budget table.

Is there some sort of update function I need to use in order to do this?

 

[Minty]

Looking at your screen shot I have a suspicion that your aren't storing your data correctly, and have committed "Spreadsheet-itis" in your table design...

This will lead to horrible issues later I can guarantee. Can you post up picture of your table in either the relationship window or design view??

 

[BadBoy House]

Lol . Couple of screenshots below.

 

[isladogs]

Minty guessed correctly.
You should never have lots of similarly named fields.
Your design gives few records, lots of fields and lots of problems.
If you ever need a Staff8 the table needs redesigning.

The correct structure is
BudgetID, JobID, StaffID, StaffRate, StaffHours, StaffNumber where the final field is a value from 1-7.
This will give you more records, fewer fields and make life much simpler.
However you need a primary key field. Does BudgetID have any physical meaning? If not use that

 

[Minty]

"Well Mr Holmes, looks like you were right again" said Mr Watson...

Just to demonstrate why this is a poor idea, in Access try adding up the total staff hours.
Then add staff member 8 to the mix.

You need to think about your tables as holding information "vertically". Spreadsheets make you think in a Horizontal fashion.

Each field should store information about an entity, the field itself should not be the entity.

You would want a table for your staff per budget with some slighlty different fields, very simply without knowing the full picture of your project, something like ;

tblBudgetStaff
BudStaffID
BudgetID_FK
StaffID
BudgetRate
BudgetHours

Do you see how this allows for 2 staff or a hundred staff per budget without any additional work?

 

[BadBoy House]

Many thanks Minty. I think I can see where you are coming from.

It's supposed to be a very basic database. Its purpose is to record a client job when it comes in and to record the set number of hours each staff member will have on the particular job.

It's currently set up with two main tables - tblJob and tblBudget. It also has a couple of tables linked to our main SQL client database to pull things like client names and staff names etc.

As you will expect one job would have one budget and I have set the Budget table up so that there are three fields for each staff member's details to be recorded, up to seven staff members - so 21 fields just for this information.

I see what you are saying about adding 8, 9, 10 or more staff members in the future being a problem.

My experience is with Excel - hence the apparent horizontal fashion of the Budget table. This is my first access database project.

So what you are saying is stick with the Budget table but use this primarily to store individual key details for the budget at most, and then introduce a new table - tblBudgetStaff to record, on a row by row basis, details of each staff member assigned to that particular budget.

So for a particular job, there would be one entry in the Job table, one entry in the Budget table and multiple entries in the BudgetStaff table. The Job table would link to the Budget table and the Budget table would link to the BudgetStaff table.

Hope I've understood you correctly.

Chris

 

[Minty]

That sounds like you have grasped the basic design idea pretty well.

Essentially top level data for Staff, Jobs And Budgets. Then related data tables where those are brought together.

Generally design your tables first, based around the outputs you need. The outputs will determine the data you need to store (Inputs).

So slightly in reverse of what you might think, often the reports you need should drive the table design. Remember that if you don't store it you can't report on it.

 

[Uncle Gizmo]

As others have said, this is a misconception people moving from Excel have. The similarity between the two products causes a major design flaw.

I have blogged about it here:-

http://www.niftyaccess.com/excel-in-access/

I also provide a free tool for converting your table into a structure more suitable for MS Access.

You can get the tool for free by subscribing to my newsletter.

Sent from my SM-G925F using Tapatalk

 

[jdraw]

Good guessing Minty!

Chris,
Here's a link to lots of info on Database Planning and Design. By working through 1 or 2 of the tutorials from RogersAccessLibrary, you will experience a process that will rid you of the "spreadsheet approach" identified by Sherlock Minty.

 

[Uncle Gizmo]

Once you have got the data correct, the next problem is getting an interface that looks like something like the one you've already posted.

I describe how to create a checklist here:-

http://www.niftyaccess.com/add-a-check-list-to-your-ms-access-database/

the basic idea here can be used to construct an interface similar to the one you show.

I'd be happy to help you through the process if you need some assistance.

Sent from my SM-G925F using Tapatalk

 

[BadBoy House]

"Well Mr Holmes, looks like you were right again" said Mr Watson...

Just to demonstrate why this is a poor idea, in Access try adding up the total staff hours.
Then add staff member 8 to the mix.

You need to think about your tables as holding information "vertically". Spreadsheets make you think in a Horizontal fashion.

Each field should store information about an entity, the field itself should not be the entity.

You would want a table for your staff per budget with some slighlty different fields, very simply without knowing the full picture of your project, something like ;

tblBudgetStaff
BudStaffID
BudgetID_FK
StaffID
BudgetRate
BudgetHours

Do you see how this allows for 2 staff or a hundred staff per budget without any additional work?

Minty

Could I ask, on the BudgetStaff table, would it be best practice to have a StaffID field (which would be a foreign key linking back to the Staff table) or to have a StaffReference field, which would instead link back to the StaffReference field in the Staff table - this would contain the initials of the staff member instead? The StaffID field is simply the number generated by AutoNumber field on the Staff table.

Again hope I'm making sense.

 

[Minty]

The StaffID is guaranteed to be unique and would be the field I would link on. Initial letters may well be duplicated, or reused if staff change.

You can bring in the Names and or Initials on a combo box to make the forms user friendly,

 

[BadBoy House]

Thanks Minty

So it would be possible to populate the StaffID field based on the name or initials selected from the combo box?

 

[Minty]

Absolutely that is how you do it. Make the combo box record source a query with your staff table in it. Select the Staff ID as the first column, Name / initials as columns 2 and 3. Sort by Name ascending to make it alphabetical.

In the combo box properties make the bound column 1 . In the Column widths property set it to 0;3;1

You should now see just the names in the combo but be storing the ID.