CODEWITHSUNDEEP
vbams-access
zekeinvestor
zekeinvestor

Reputation: 21

VBA in MS Access lookup value in Temp Table

I am creating a sales configuration database. I am using forms to step salesman through the configuration process. Certain products will determine the path of configuration. As the salesmen click through the forms, I have a temporary table (tblConfigTemp) being populated with their choices. This table gets cleared at the end of the configuration or anytime the user presses the 'home' button.

I am having issues with VBA on how to lookup values on my temporary table. I want to be able to look at my temporary table, search for a ProductID and then insert an additional ProductID based on previous entries. Here is what have so far:

Private Sub btnRTKGO_Click()

    Dim locProductID As Integer

    locProductID = [Tables]![ConfigTemp]![ProductID]

    If locProductID = 1 Then
        sSQL = "INSERT INTO ConfigTemp( [ProductID] ) SELECT  '10' "
    ElseIf locProductID = 2 Then
        sSQL = "INSERT INTO ConfigTemp( [ProductID] ) SELECT  '11' "
    ElseIf locProductID = 3 Then
        sSQL = "INSERT INTO ConfigTemp( [ProductID] ) SELECT  '12' "
    ElseIf locProductID = 4 Then
        sSQL = "INSERT INTO ConfigTemp( [ProductID] ) SELECT  '13' "
    Else
        Call MsgBox("This is a pop-up message", 0, "A Message")
    End If

End Sub

Been awhile since I have done any programming, trying to get back into the saddle. Thanks in advance.

Upvotes: 1

Views: 1424

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

The Domain functions DLookUp(), DCount(), etc. are often the most straightforward ways of doing simple lookups in Access. For example, to see if [ProductID] number 1 exists you could do something like

Dim n As Integer
n = DCount("*", "ConfigTemp", "ProductID=1")
If n = 0 Then
    MsgBox "ProductID number 1 was not found."
Else
    MsgBox "ProductID number 1 was found."
End If

For additional information see the Microsoft KB article here.

To add another row to the table, you could either use something similar to the syntax in your sample code...

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "INSERT INTO ConfigTemp (ProductID) VALUES (10)", dbFailOnError

...or you could use a Recordset object

Dim cdb As DAO.Database, rst As DAO.Recordset
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("ConfigTemp", dbOpenTable)
rst.AddNew
rst!ProductID = 10
rst.Update
rst.Close

Upvotes: 1

Related Questions