Filtered TextBox Value

[connerlowen]

HI,

I have a form that has 3 combo boxes and a text box that I want to be a lookup. I have a little experience with DLookUp, but I do not think that will work here. The three combo boxes are linked to queries, and Tables. The first combo box is for choosing "Precious" or "Base" metals. The second combo box is filtered from the first and displays the "Metal Names". The third combo box is to choose a "Metal Market". There are currently four options for that. I want the text box to display the "Metal Market Price" based on the second and third combo box selections.

Here is a screenshot of the table with all of the metals and their market prices filled in with dummy values for now:




Here is a screenshot of the relevant Form:



Here is a screenshot of the Table that relates to the relevant form:



Here is a screenshot of the Metal Market table that the Query is based on:



Any help would be greatly appreciated.

Thank you,

Conner Owen

 

[vbaInet]

You can use a DLookup() function that is based on multiple criteria.

 

[connerlowen]

How will I address that I want the column from the metals list query that is equal to the selection in a combo box? The Prices are in 4 different fields in a table. what goes in the expression?

 

[vbaInet]

Give that some thought Conner... do a bit of research, show us what you came up with and we can work towards a solution

 

[connerlowen]

Fair enough. I will be back with something shortly. Thank you.

 

[connerlowen]

This is what I have so far. Control Source =
=DLookUp("[LME] & ',' [COMEX] & ',' [LMBA] & ' ' & [EnglehardFab]", [OptionMetalsListQ], "[Fields]=" & [cboMetalMarket])

The only place where I am unsure what to put is where I put [Fields].

 

[vbaInet]

It's nicely explained here.

http://access.mvps.org/access/general/gen0018.htm

Please take your time to digest it. You seem to rush with your replies

 

[connerlowen]

I think using the Column of the combobox Metals will be a better method. the combobox Metals has 7 columns. The last 4 of which are the 4 Markets and their prices. I want to make the text box = [cboMetals].Column(4 '&' 5 '&' 6 '&' 7) '&' Where ([cboMetals].Column = "[cboMetalMarket]")

 

[vbaInet]

You're jumping the gun, first try and get one price and you can build on that.

What field in the target table does MetalMarketID correspond to?

 

[connerlowen]

The MetalMarketID only corresponds to the ID field in my MetalMarketT table that I have a query based on for the combo box.

 

[connerlowen]

I can get a price with the column command based on the metal name. I cannot get it to use the Market that I want.

 

[vbaInet]

Can you list out, just the names, of the query/table the three combo boxes are using as their RowSource.

 

[connerlowen]

combo1: OptionPreciousOrBaseQ

combo2: OptionMetalsListQ

combo3: OptionMetalMarketQ

 

[vbaInet]

So none of your screenshots relate to those queries, can you upload screenshots of sample records from each of those queries.

 

[connerlowen]

 

[vbaInet]

Ok, you already have the values in the Column of Combo Box 2, so just concatenate the values. The Column property has this format:

Column(Column, Row)

So, using the data from the OptionMetalsListQ screenshot, row 1. If I want to get the LME value which is in the 5th column, it will be:

Column(0, 4)

It's zero based, so your first row is 0 and your first column is 0. If you have column headers turned on, your first row will be 1 because the column header becomes 0.

 

[connerlowen]

I have the code now that gets the price based on the metal from whatever column I have specified. I just need to get the column in the control source property to look at the market selected. using the column names from the combo box metals?

 

[vbaInet]

For the sake of simplicity:

* Create function that returns the concatenated prices
* Set the Control Source to this function:

=GetMetalPrices()

Or even simpler:
* Concatenate the values in the query itself - it can be hidden
* Refer to this column in your textbox.

 

[connerlowen]

I have the values Concatenated in another field in the query. I know which column this is. How do I get the value from the market that I want?

 

[vbaInet]

Looks like I got the wrong end of the stick this time. You'll still need the DLookup() with the criteria of MetalsID and PreciousOrBaseID included. The table/query will be the query with the concatenated values.