Evaluating a table grid to find a value

[thudson]

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]

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]

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

 

[thudson]

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]

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]

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]

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]

..
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 ",":

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

      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]

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

 

[mike60smart]

Hi

The Form is contained within the database attached by JHB

 

[thudson]

Sorry I thought that was the one I attached!

 

[thudson]

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