Insert Field Contents Based on Previous Field 1

[darrelhawes]

I have a form that contains a combo box with employee data. The form is tied to a transaction table. I choose the employee's initials (small organization ). The combo box is tied to another small table with initials, employee name, and employee wage rate. All three appear on the combo box.

I then have a separate field for Rate. I created the control by dragging from the field list. How does I force the entry of the Rate based upon my Employee choice? The only way I could figure out how to do this was to put the appropriate code in the Control Source property for Rate, but then it won't update the underlying transaction table. The Employee Initials do get updated to the table with no problem.

Thanks in advance.

Darrel Hawes
Access Newbie, trying to get up to speed

 

[SeeJane]

Hi -

You can get the value of any column in a combo box like this. Note that indexes start at zero, so the 1st column will be 0, second 1, and so forth.

[Forms]![YourForm].[ComboBoxName].[Column](index number)

For the AfterUpdate on your combo box just enter

Me.[Ratetextbox].Value = [Forms]![YourForm].[ComboBoxName].[Column](index)

For the record, now (when it's small) would be a good time add a numeric ID to your intitials table and use that as the primary key (and link to your other table). Since you're using a combo box, your users need never actually see the unique numeric IDs, and this fix will save you tons of grief down the road.

Good luck, CJ

 

[darrelhawes]

Two questions:

1. Are you saying that I should put the following in the AfterUpdate property of my employee field:

Me.[Ratetextbox].Value = [Forms]![YourForm].[ComboBoxName].[Column](index)

I tried this and nothing happened. I did notice that the system added an equals sign at the beginning of the expression.

Should it have been entered in the AfterUpdate field of the target field? I tried that too but nothing happened.

2. Why is a numeric ID so important?

Thanks,

Darrel

 

[darrelhawes]

Following the example of the "Time and Billing" sample database that comes with Access 2000, I tried to achieve the same result by referring to the combo box (as stated above) in the Default Value property of my Rate text box. Still, nothing happens. If I copy the code to the control source field, I get the correct result. Of course, I then cannot feed the result back to the underlying table, which is what I need to do. Can anyone help me with this?

Thanks,

Darrel

 

[SeeJane]

Hi,

Sorry - I guesss that wasn't too helpful. Before you do anything, I suggest making a copy of your database as a backup. That way you can fool around without needing to worry about the safety of your data. This is the safest way to work on your database, I'd reccomend it as general practice.

It's pretty hard to teach yourself VB and Access all on your own. I do recommend experimenting some, and maybe checking out a few resources (O'reilly puts out a good book on access databases).

For your specific problem, here's how to do the code.
Once you've backed up your data, manuver your way into the code window.
1. Select "Code builder" from the after update event and click OK. This should put you in a code screen - the cursor will be between the phrases Private Sub whatever and End Sub
2. Where the cursor is, type the code

Me.[YourRateFieldName].Value = [Forms]![YourFormName].[YourComboBoxName].[Column](index)

Note that whenever I've said [Your....], substitute the object's name. For index, you'll need to list the column number where rate appears in the combo box, as an index (the 1st field will be 0, 2nd 1, etc..).


The reason to use numeric ID is that it is easy to make every new ID unique (which is the aim of any ID field). I've learned the hard way that even in a small org, you could, for example, get 2 people with the initials MS - this would then give you 2 identical ID. Your links will fail if the rate table doesn't know which MS you're referring to. there are lots of threads written by folks far more experienced and skilled than I if you want to know more about the basics of assigning IDs.

Does this help at all?

Good luck, CJ

 

[darrelhawes]

I tried your instructions...I received the error message:

"Run-time error '450':

Wrong number of arguments or invalid property assignment"

By the way, why do you suppose that my other attempt did not work, namely putting the reference to the combo box in the default value setting in my "Rate" text box? It seems to work perfectly in the Access sample database I mentioned.

Also, thanks for your explanations re: ID numbers. I see now what you were getting at.

Darrel

 

[SeeJane]

Let's try a slightly different format

Me.[YourRateBoxName].Value = Me.[YourComboName].Column(index)

Hopefully this should work
CJ

 

[darrelhawes]

It worked! Thanks so much!

Darrel