Lookup Default Value VBA

[RSS705]

Hello everyone,

This is my third question with this community, and I just want to say how shocked and amazed I am by this everyone's willingness to help people learn. I am truly thankful.

But now onto question three! I have a Lookup field that possesses a ID Number and Company name, taken from another table (which the user can add to as required).

I would like to set my Lookup field to specific ID Number and Company name by default, as this will be used most frequently by users completing the form.

I have been trying study online how to refer specifically in VBA to specific table, column and ID number, but have not had much luck. Really appreciate the help.

Thanks all in advance,
Rob

 

[Mihail]

In almost all my databases (there are few but good enough ) I use:

1) a table
_Implicite (Defaults in English)
Form (Text) - store the form name
Parametru (Text) - store the parameter name
Valoare (Text) - store the parameter's value

2) a public function:

Public Function DefaultParametru(Formular As String, Parametru As String, Optional ValoareParametru)
'Read/ write from/into table "_Implicite"
Dim SQL As String, Criteria As String
        Criteria = "(Form = """ & Formular & """) AND (Parametru = """ & Parametru & """)"
    If IsMissing(ValoareParametru) Then 'Read
        DefaultParametru = DLookup("Valoare", "_Implicite", Criteria)
    Else 'Write
        If IsDate(ValoareParametru) Then
            ValoareParametru = EnglishDate(ValoareParametru)
        End If
        With CurrentDb
            SQL = "UPDATE _Implicite SET [_Implicite].Valoare = " & ValoareParametru & _
                    " WHERE ((([_Implicite].Form)= """ & Formular & """)" & _
                    " AND (([_Implicite].Parametru)= """ & Parametru & """));"
            .Execute SQL
            If .RecordsAffected = 0 Then
                SQL = "INSERT INTO _Implicite VALUES (""" & Formular & """,""" & Parametru & """,""" & ValoareParametru & """)"
                .Execute SQL
            End If
        End With
    End If
End Function

Here is how I call this function for "write":
(I apply this from the Double Click event of the appropriate controls)

Call DefaultParametru(Me.Name, "ID_Radiator", ID_Radiator)

or

Call DefaultParametru(Me.Name, "Scalat", Scalat)

And this is the function for "read":
(I apply this from the Open event of the form)

Private Function IncarcaImplicitele() 'LoadDefaults
Dim X
    X = DefaultParametru(Me.Name, "ID_Radiator")
    If Not IsNull(X) Then
        ID_Radiator.DefaultValue = X
    End If

    X = DefaultParametru(Me.Name, "Scalat")
    If Not IsNull(X) Then
        Scalat.DefaultValue = X
    End If
End Function

This function appear in DefaultParametru routine and handle the date data type:

Public Function EnglishDate(UzualData)
    EnglishDate = "#" & Year(UzualData) & "-" & Month(UzualData) & "-" & Day(UzualData) & "#"
End Function

 

[RSS705]

Yikes.. although I really appreciate your reply, it's a bit ahead of my VBA understanding at this point.

Does it really require this much code to tell a lookup to default to a specific row in a table?

 

[Mihail]

No. My code remember the default on the next session.
You can choose the value at table level by setting the default value for the field or at form level by setting the default value for the control.
Both options are in the property sheets.

 

[RSS705]

But in the default value, at the form level, what code would I insert then to tell the form to use a specific table's row?

Thanks again!
Rob

 

[RSS705]

Thank you both for your help!
Rob