Returning the appropriate value from an unrelated acces table.

[Ray Spackman]

First, I apologize if this has already been specifically covered, however, the further I search for the answer or example, the more confused I seem to get.

Second, I hope my explanation of the situation and requested goal is clear.

I have a form; frmbowlerspecs that contains a few user input fields; numTilt, txtRotationDescrip, txtSpeedDescrip, and txtRevsDescrip.

This form also has 2 fields; numInitialDARatio, and numInitialVARatio that will show the values I am trying to return form a separate table.

The table; tblDualAngleRatios is indexed with a primary key field; AxisTilt, and has 9 other columns; HRevsandLRotDA, HRevsandLRotVA, HRevsorLRotDA, HRevsorLRotVA, SRMatchedDA, SRMatchedVA,HSpeedorHRotDA, HSpeedorHRotVA, HSpeedandHRotDA, and HSpeedandHRotDA.

Attached is a picture of my table for clarification:

In VBA I am trying to find and return the appropriate value that corresponds to the correct axistilt, which matches the forms numTilt value, and the corresponding column which is designated by the comparison of two of the three other user input fields on the form ie: txtRotationDescrip and txtSpeedDescrip.

A part of the code to help clarify my goal:

Private Sub RatioCalc()
Select Case numTilt
Case 2.5
Select Case True
Case txtRotationDescrip = "High" And txtSpeedDescrip = "High"
numInitialDARatio = ??????? THE CODE TO GET THIS VALUE
numInitialVARatio = ??????? THE CODE TO GET THIS VALUE
End Select
End Select
End Sub

In this example tblDualAngleRatios.AxisTilt would match 2.5 while the comparison of txtRotationDescrip = "High" and txtSpeedDescrip = "High"
would correspond to tblDualAngleRatios.HSpeedandHRotDA or tblDualAngleRatios.HSpeedandHRotVA while numInitialDARatio would = the value in tblDualAngleRatios.HSpeedandHRotDA and numInitialVARatio would = the value in tblDualAngleRatios.HSpeedDescripandHRotVA, both in the in the tblDualAngleRatios.AxisTilt, which in this case is 2.5.

Was trying to accomplish this with some form of lookup but not having any luck and do not know the correct or best way to accomplish this or have I seen an example of such.

Thank you in advance.

 

[PNGBill]

Have you tried DLookup() ?

 

[Ray Spackman]

Yes I have tried dlookup() but because I do not know what value will be returned, I was having a problem with the criteria as the criteria would actually need to be the specific column in which to find the value. I understood the criteria as being a value already known, which we don't.

I should also say that the value results that should be returned for the example would be a numInitialDARatio value of 1 and numInitialVARatio value of 1. I mention this only as a debugging confirmation. But still looking for the code to acheive this.

 

[PNGBill]

There has to be some way to define what the data will be (which record it wil come from)

DLookup() can match a record from any table where some data on a Form or another table matches some other data.
ie You will get X from Record 123 where y = z. x and y are on record 123 and z can be just about anywhere.

Otherwise, maybe you need to create your own function to return the value you require.
Doing this, you can get very complicated but, there still must be only one record that is acceptable, even if this is the First, Last etc.

Otherwise, you are dealing with a random result ??

Creating a function is quite easy and there is a lot of assistance here to do this.

 

[spikepl]

I have not much clue what this is about but I note one thing: As far as I understand you wish to find some match, but you do not know in which column.

The implication of this is that your data structure is unsuitable- presumably influenced by the eternal Excel-view of the world, where data is stored in the same way as it is displayed. In a DB data storage and display are two entirely unrelated issues.

If you can find a value of interest in different columns, that means that the value is of the same type, and therefore belongs in one and the same column , with another column tagging it with its specificity. Searching for a value in column is no big deal - searching for a value across columns is a tell-tale sign of malformed data structure.

 

[Ray Spackman]

First to PNGBILL, Hi again. I do understand that with the dlookup() it is easy to find the matching record, but within that record, there is one possible value that can be returned for each column (10) in the table, leaving 10 possible values. I am not undrstanding how dlookup() can narrow this down to the correct value in the correct column. I do also understand that I may have to write my own function to accomplish this, but have no idea where to even start if that is the case.

Second to spikepl, Hi. Basically I have a table and need to retrieve one value from that table that matches the row and column. I do understand that my structure may be incorrect to accomplish this and if it is, I am looking for help as to what structure would be best. If I can use the existing access table, I am looking for a way to retrieve that one correct value.

To both PNGBILL and spikepl, I am toying around with the idea of creating an array to mirror the access table and then retrieve the value form the array. Any thought on this?

 

[PNGBill]

If you use descriptions Records and Fields you, and others, may be able to resolve this issue easier.

You are saying that you need to ascertain the record and then the correct field in that record ? out of 10 possible fields.

This is not normal and either means your database should be reviewed and or you do need a different aproach then DLookup()

There are ways to search through the fields in a record. You could do this by appending each field as anew record in a Temp Table and then you have 10 records to select from rather then 10 fields.

vba code can do this by checking each of the 10 fields one at time until it gets a matching result

But... how will the code know when this has been reached and what will it do with the result ?

How many records are there ? just one ? and 10 fields ? or many records each with 10 possible fields. ?