Default Value for Comboboxes using a lookup

[jackyaz]

Hi again,

Im obviously more of a novice at access than i thought because i cant wrap my head round what sounds like a simple concept, so I'm hoping the amazing people here can help.

I have a table called tblRates which stores Rate ID (Number, Primary Key), Rate Alias (Text) and Rate Amount (Currency). My form (Event Log), has fields for CLient NAme, Location, etc, as i described before.

The Rate Aliases are Rate 1, Rate 2, Special, Other and Credit Report. Other obviously implies the user is to enter the amount.

There is a dropdown for Charge Rate (which uses Charge Alias as its source) which applies to the particular event id.

I'd like this to default to the rate alias "Rate 1". However, my employer has asked that if a special rate applies to a client, that it defaults to "Special" as opposed to Rate 1. I have a table called tblSpecial which lists the 4 clients this applies to, and the special amount they are to be charged.

This means that a separate field underneath the "Charge rate" dropdown on the form for "Rate Amount" needs to be displayed, so the rate is shown, and allows for a custom amount to be input for Other. However, I dont know how to make it lookup the amount and fill in the textbox based on the dropdowns value. I had a go with DLookup but it didnt seem to work, so I must be doing something wrong.

Can you help? I apologise if I havent explained it in the best way.

 

[wiklendt]

you probably need to be looking into cascading combos. this is the instance where a combo box has a query as its source, and that query has a parameter as one of its criteria - this parameter is fed form the form.

so, basically, when you go to a particular record, the query will pick up the value in one of your form controls (which tells you a special rate is required) and return the rate you need. when you move to another record, the combo box requeries.

is this what you mean?

 

[jackyaz]

this will help pick up which clients use a special rate. however wont this only bring up the special rate and not the others? im not sure if its possible, but i need the combo to always bring up all 5 options that i mentioned before, default to rate 1 for all clients, apart from those which use a special rate by default. the combo still needs to show all options but default to the values i just mentioned.

 

[John Big Booty]

What you need to do is have a field in your clients table that records the default rate value for each client.

In you clients Combo Box, you will need to select the ClinetID, The Clinet Name and The DefaultRateID. This third column you can hide (ie zero width). In the AfterUpdate event of your clients Combo put the following code;

Me.RateComboName.Value = Me.ClientComboName.Column(2)

This will set the rate combo to the default rate that you have set for the selected client, but still allow you to choose from any of the other available rates.

Have a look at the attached for an example.

Rember the columns in a combo are numbered from zero up. So the third column is actually column number 2.

 

[jackyaz]

this works great! so thats that problem solved. now, how do i make a text box named "Rate Amount" lookup the rate amount and display it, based upon the dropdowns selection? For some reason I cant make DLookup work, if this is even the right approach?

 

[John Big Booty]

If I'm understanding you correctly a similar approach will work just as well for a text box.

This time in the Text Box Control Source put the following;

=Me.ComboName.Column([B]X[/B])

Were X is the number of the column in the combo you wish to display in that text box.

 

[jackyaz]

What you need to do is have a field in your clients table that records the default rate value for each client.

In you clients Combo Box, you will need to select the ClinetID, The Clinet Name and The DefaultRateID. This third column you can hide (ie zero width). In the AfterUpdate event of your clients Combo put the following code;

Me.RateComboName.Value = Me.ClientComboName.Column(2)

This will set the rate combo to the default rate that you have set for the selected client, but still allow you to choose from any of the other available rates.

Have a look at the attached for an example.

Rember the columns in a combo are numbered from zero up. So the third column is actually column number 2.

For some reason its just making the combo box blank, not sure why this is?

 

[John Big Booty]

1. Have you had a looked at the sample?
2. Have you changed the names in the code to match your Combo Names?
3. Are your Combos set up in the same what that I have them set up in the sample? If not the code may need adjusting.

 

[jackyaz]

Yes, I looked at the sample, and understood how that worked. When I went to apply it to mine it doesnt work. Im currently checking the lookups in mine to make sure they're correct

 

[John Big Booty]

.......... Im currently checking the lookups in mine to make sure they're correct

If you are using table level lookups, congratulations you have just discovered why the use of such things is discouraged. As you are now discovering table level lookups complicate the current task at hand, get rid of them ASAP.

 

[jackyaz]

By lookups I simply mean a table for things like Location, which stores Location ID, CLient ID and Location Name. It just serves as a row source for a cmobo box. Is this what you mean?

 

[jackyaz]

ah i got it to work, one of them wasnt lookign for an ID, once i changed it, it works perfectly! thanks again guys!

 

[John Big Booty]

By lookups I simply mean a table for things like Location, which stores Location ID, CLient ID and Location Name. It just serves as a row source for a cmobo box. Is this what you mean?

My bad I had assumed that you were using table level Lookups, my apologies

Glad you got it working

 

[jackyaz]

No worries, Im not even sure what a table level lookup is, im essentially a novice at databases, but I can put together impressive-looking ones, although they are fairly simple.

 

[John Big Booty]

No worries, Im not even sure what a table level lookup is, ...........

Best keep it that way then