Evaluating a table grid to find a value (1 Viewer)

thudson · Nov 20, 2018

I have a grid of postal prices in a spreadsheet grid for which I want Access to evaluate the correct value for the 2 criteria that I enter.
The columns are zones which I have called: STD_Z1, STD_Z, STD_EU and ECO.
For each of these columns I have created 12 records which represent the weights of the item. I have called them: LL100, LL250, LL500, LL750, SP100, SP250, SP500, SP750, SP1000, SP1250, SP1500, SP1750, SP2000.
Attached is spreadsheet that contains the data.
On the form I will have 2 textboxes that will have value lists, one for the column field names and the other for the record names.
Depending on the values in the 2 textboxes, I want to evaluate the value from the grid.
I thought this might be easy, but my little brain can't work out the best method.
I had a look at Lookup fields and even tried a query, but with no success,as I floundered when trying to work out the method.
Can anyone point me in the right direction, with an example if possible.
I'm sure I am getting to old for this, my brains turning to mush!:banghead:

pbaldy · Nov 20, 2018

Did you see my suggestion on your earlier thread? Particularly the last bit?

"I'd have hose values in a table, so they're more easily maintained and instead of a complex formula you have a simple lookup. 4 fields: zone, weight min, weight max, and cost."

I won't say it can't be done the way you have it, but it will be more complicated.

JHB · Nov 21, 2018

Is that what you're looking for, (see attached database), run/open the form in it.

thudson · Nov 21, 2018

HI
Thanks thats just what I was looking for!

How is the code run as I could not see anything in the control source.
I havn't done much code level programming so I'm not sure how it runs.
I'm guessing that that it is attached some how to the form, so do you open the form and then add the code?

pbaldy · Nov 21, 2018

Looks like JHB is off duty. The private sub FindPrice() is called from the after update event of the two dropdowns. The code behind the dropdowns would have been added like this:

http://www.baldyweb.com/FirstVBA.htm

The sub was typed directly into the code window, as it is not directly attached to any event. Similar to this:

http://www.baldyweb.com/Function.htm

mike60smart · Nov 21, 2018

Hi

The example is of an Unbound Form.

You would need to apply this method to your Bound Form to store the values selected.

thudson · Nov 21, 2018

I have a problem!
When I click on the Zones combo, instead of the value list, it lists all of the fields in the current form. I have checked the properties and it is definately set to the value list.
I created the code as pbaldy explained, but I was confused about the FindPrice sub routine. I guessed that this was called by the other subroutines. Looking at Mikes comment, how does he mean that I need to apply this method to my bound form?
To add it to the form that I already had, I just added 3 unbound text boxes to the form.
Can anyone suggest why the problem I am getting happens, because it does not make sense to me!

JHB · Nov 22, 2018

thudson said:
..
When I click on the Zones combo, instead of the value list, it lists all of the fields in the current form.

You should see that.

If not, then change ";" in the below code line to ",":

Code:

      ComboValues = ComboValues & fld.Name & "[B][COLOR=Red];[/COLOR][/B]"

Code:

      ComboValues = ComboValues & fld.Name & "[B][COLOR=red],[/COLOR][/B]"

But the problem you're facing here to get then correct value, is because the way you're storing the value, it should not be in a matrix, but vertical which is the correct way in a database.
Then you don't need any VBA-code, and you can use functions like DLookup.
I've attached another sample database, open the form "frmPriceVertical".

thudson · Nov 25, 2018

Hi JHB
Did you forget to attach frmPriceVertical?
Can't see it in the post!

mike60smart · Nov 25, 2018

Hi

The Form is contained within the database attached by JHB

thudson · Nov 25, 2018

Sorry I thought that was the one I attached!

thudson · Nov 25, 2018

Di not work when I tried to integrate it into my existing form, so decided to make it a sub-form.

Evaluating a table grid to find a value (1 Viewer)

thudson

Old Programmer

Attachments

pbaldy

Wino Moderator

JHB

Have been here a while

Attachments

thudson

Old Programmer

pbaldy

Wino Moderator

mike60smart

Registered User.

thudson

Old Programmer

JHB

Have been here a while

Attachments

thudson

Old Programmer

mike60smart

Registered User.

thudson

Old Programmer

thudson

Old Programmer

Similar threads

Users who are viewing this thread