setvalue question

[Guest]

I have a database with a field (pull down list) of stores. I have another
field for miles. I'm trying to use the setvalue macro to make the miles field
read the store field and display the correct miles for the store the user
selects. Would the setvalue be the best option for this or is there a better
way? If so how? I'm having trouble getting the setvalue option to work.

NOTE: I'm using subforms also.

Here is my code:

On enter

Condition - [Half Hitch Tackle Co Inc.]="18"
Action - SetValue

I don't know what to put in the Item field or the Expression field. Can
anyone help?

Thanks,
Eydie

 

[Steve Schapel]

Eydie,

No, a SetValue macro is not the best way to do this. It would be
possible, but it would mean you would need a separate SetValue action in
your macro for each possible store. A much preferable approach would be
to set up a Stores table that has the Store and the Miles for each
store. You might already have such a table in your database? Which you
are using as the Row Source of the combobox on your form? Am I right?
It is not clear what the purpose of the form is where you are entering
this information, and what is the table that the data on the form is
going into. But it is almost certain that it is not correct design to
have a Miles field in this table. The Miles data is associated with the
Store, and as such should be in the Stores table. All you really need
is a way to *display* the Store's associated Miles data on the form, but
not to relicate the actual Miles data itself into the table that the
form is based on. Hope that makes sense. Have a look at
http://accesstips.datamanagementsolutions.biz/lookup.mht and see if you
can apply that to your situation.

 

[Guest]

Thank you Steve for your replay. Yes I do have a Stores table with the miles
listed. And yes I am using it as my Row Source of my combobox on my form.
What you say kind-of makes sense. My purpose is to have the miles information
automatically display in the miles field after the user selects the store.
This way I won't need to add this information in for every record.

I did check out your site and I tried your examples. I found out that I had
selected the wrong table in my query's form. It wasn't getting the
information from the correct source so it wasn't displaying it correctly
either. But I played with it and now it works great.

Thank you very much for your help.

Eydie,

No, a SetValue macro is not the best way to do this. It would be
possible, but it would mean you would need a separate SetValue action in
your macro for each possible store. A much preferable approach would be
to set up a Stores table that has the Store and the Miles for each
store. You might already have such a table in your database? Which you
are using as the Row Source of the combobox on your form? Am I right?
It is not clear what the purpose of the form is where you are entering
this information, and what is the table that the data on the form is
going into. But it is almost certain that it is not correct design to
have a Miles field in this table. The Miles data is associated with the
Store, and as such should be in the Stores table. All you really need
is a way to *display* the Store's associated Miles data on the form, but
not to relicate the actual Miles data itself into the table that the
form is based on. Hope that makes sense. Have a look at
http://accesstips.datamanagementsolutions.biz/lookup.mht and see if you
can apply that to your situation.

--
Steve Schapel, Microsoft Access MVP

I have a database with a field (pull down list) of stores. I have another
field for miles. I'm trying to use the setvalue macro to make the miles field
read the store field and display the correct miles for the store the user
selects. Would the setvalue be the best option for this or is there a better
way? If so how? I'm having trouble getting the setvalue option to work.

NOTE: I'm using subforms also.

Here is my code:

On enter

Condition - [Half Hitch Tackle Co Inc.]="18"
Action - SetValue

I don't know what to put in the Item field or the Expression field. Can
anyone help?

Thanks,
Eydie