Form field populted by 2 form droplist to cross reference Query

[Rabbit]

I solved for p1 and plugged that in to see if the numbers calculate correctly; It didn't.

That could mean one of 3 things: the numbers in the chart are incorrect, or the formulas in the picture aren't the ones they used for the chart, or I did my algebra wrong.

In any case, if we can't get our hands on the formula, then you'll have to use the DLookup function to find the correct intersection in your table.

[williamson1979]

Thanks Rabbit and Phil for all the help by the way. Yeah I tried as well Rabbit. The 0.80 matches the paper correction chart straight from the book.

Does DLookup take the input of Temp and SG to return the intersection of the 2 in a text field? If so how do I do that? I'm new but I did try Dlookup with no luck.

[Rabbit]

The syntax for DLookup is

DLookup("field name", "table name" , "filter criteria")

Any of those strings can be replaced with a dynamic value, say from a drop down.

[williamson1979]

So if I want to use Temp box, SG box to intersect values I’d use the above in the correction field? I tried before but all I got was a drop list in the query. Of course I wouldn’t know what value to use without the paper chart so little difference. I’m going to assume I did something wrong.

[PhilOfWalton]

Grovelling apologies, I sent the wrong database

Try this instead.

Phil

[williamson1979]

Thanks Phil,

Im looking at your code trying to understand how you made it work. Maybe I'll learn something to help me improve the rest Ive worked on. There are improvements Id like to make but don't know the language so I used all built in options which was aggravating this being my first attempt at this.

So you said earlier I needed to remove field calculations and work on relationships. From what I had uploaded could you give me some pointers on how I should have relationships and if I remove the field calculations must I use code as you did instead? If so will it write the values to the main table?

Thanks for the help always

[williamson1979]

Phil, I restarted the DB breaking the main table into smaller tables and using vba to do the calculations. I read to set tables the way you might file documents. As far as the relationships go if field1 is in the calculation with field 2 from another table I added a relationship, same thing for table fields pulling info from other tables/queries in my comboboxes.

I don't understand your SG code but I'll copy it over and maybe figure it out.

[PhilOfWalton]

I'll try to explain the code, but if you want further help on the tables & forms, I think you should start another thread, as this one if getting too long. See
https://bytes.com/newreply.php?do=postreply&t=972149 and any other articles you can find about Database Nomalisation.

To try to explain the code, the 2 update statements check here is a valid S.G. & Temperature.

Option Compare Database
Option Explicit

Function GetCorrection(Temp As Single) As Double

    Dim MyDb As Database
    Dim CorrectionSet As Recordset
    Dim StrSQL As String
    Dim TDF As TableDef
    Dim Inti As Integer
    Dim FieldName As String
    
    Set MyDb = CurrentDb
    Set TDF = MyDb.TableDefs("TblCorrections")
    
    For Inti = 2 To 7                       ' We don't want the CorrectionID or Temperature columns
    
        Debug.Print TDF.Fields(Inti).Name
        If TDF.Fields(Inti).Name = Me!CboSpecificGravity.Column(1) Then     ' Field name matches CboSpecific Gravity
            FieldName = TDF.Fields(Inti).Name                               ' Name of S.G. field
            Exit For
        End If
    Next Inti
 
    StrSQL = "SELECT DISTINCT TblCorrections." & FieldName & " FROM TblCorrections "
    StrSQL = StrSQL & "WHERE Temperature = " & Temp & ";"
    Set CorrectionSet = MyDb.OpenRecordset(StrSQL)
    
    With CorrectionSet
        Correction = .Fields(0).Value
        .Close
        Set CorrectionSet = Nothing
    End With
 
End Function

Line 4 defines the function and says we will the temperature as single number, and the output as a double precision number.

Line 14 creates a TableDef, the same as Table TblCorections, but we can find the field names from this (SG80, SG85, ... SG105). Note that the fields are numbered from zero. What a coincidence ... they also happen to be the same names as appear in the S.G. Combo Box as the second column (Column(1) as the first column ( set to zero width, so that you can't see it is named Column(0)

Lines 16 to 23 scan the field names till they find the name as has been selected from the S.G/ Combo box. So for example, if we select SG90, we get all the values in the 5th. column of the table, and there is only a single column named "FieldName" , but Access will equally be satisfied with Field(0).

So we build a simple query to find the correction corresponding to the temperature selected in the Temperature Combo Box.

This thread is far too long, so if you want talk about database structure ans table, I think you should start a new thread.

Phil

[williamson1979]

Surething Phil, 1 last question here.... On temperature entry I get a error on this line Set CorrectionSet = MyDb.OpenRecord set(StrSQL) "missing operator in query exoression"

[PhilOfWalton]

Are you using the form "FrmCorrections "? The database I sent you works perfectly. Does it for you?

If you have changed any Control Names, change them back to the names I gave you, because the StrSQL won't find those names.

If you still have problems, please send the your Db back to me.

Phil

[williamson1979]

Hey, I moved it to the updated DB but named the controls and tables to match your code. That said after it gives the error it works just fine. I had to remove the 2 options listed st the top because it wouldn’t run with them. I do have one option compare database at the beginning of my calculations codes though.

It isn’t using frmCorrections. I put it to the controls on my form. So I need to rename Corrections to the name of my form?

[williamson1979]

Phil,

Here is what Ive got done taking your above mentioned advice. Hopefully its better than before just Im getting the error with your code after I moved it to the report form to intergrade it.

The data is no longer easy to follow with multiple tables but I figure a query will fix that.

[PhilOfWalton]

Sorry, got the same error in both the updates. It should read

If Nz(cboSpecificGravity) = 0 Or IsNull(cboTemperature) Then

instead of
t

If IsNull(cboSpecificGravity) Or IsNull(cboTemperature) Then

It is essential that you have Option Compare Database and Option Explicit at the top of every module.

For example, Option Compare Database will treat the letter "A" and the letter "a" as identical. Useful for ordering, filtering etc. So "A" = "a"
Option Compare Binary (not often used) compares the ASCII value of the letter ("A" = 65, "a" = 97, obviously the order of results will be totally different.

Just as important is Option Explicit. This means that every function and variable including the names of fields on forms, and a whole lot more, must be declared before those variables can be used in your code.So when you deleted "Option Explicit", you said "Don't check for errors at compile time" ... and it didn't.

Phil

[williamson1979]

The error checking part makes sense. So the two above options just go once per page I put code, not above every code? I think I can get most of this correct with the info you have gave me. Ill need help on assigning Cbo options to fields for the agg part put I can start a new topic so we can get this closed since it has morphed from the topic.

Thanks