Issue with recordsets? (1 Viewer)

[mafhobb]

So I have this database that I need to add functionality to. I am creating a new form that draws information from a hidden subform and from some tables. I have several labels on this new form that need to be updated based on the record that the subform is opened at.

Here is what I used initially to update two labels. The value for one comes directly from the subform while the value for the other one comes from a table, based on the first label. It works well.

'change the SKU label caption to show SKU#
lblSKU.Caption = Nz(Forms![contacts]![Call Listing Subform].Form![SKU], "No SKU Loaded")
'find SKU message if it exists
    Dim SKUMess As String
    SKUMess = lblSKU.Caption
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSKUMessage", dbOpenDynaset)
    rs.FindFirst "[SKU#]='" & SKUMess & "'"
        If rs.NoMatch = True Then
            Exit Sub
        End If
'Make the SKUmessage box visible and display the SKU message if it exists
    txtmessage.Value = rs!SKUMessage
    txtmessage.Visible = True
    BoxMess.Visible = True
'reset recordset
    Set db = Nothing
    Set rs = Nothing
'Done with SKU Message Box

I now need to add additonal info to the new form based on the value of a variable on the same subform. I have added this code to the original

'Figure out the Customer ID
'What is the CallID from subform "Call Listing subfom"
Dim CallIDvar As Long
    CallIDvar = Forms![contacts]![Call Listing Subform].Form![CallID]
MsgBox CallIDvar
'find contactID based on CallID using the
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Calls", dbOpenDynaset)
    rs.FindFirst "[CallID]='" & CallIDvar & "'"
        If rs.NoMatch = True Then
            Exit Sub
        End If
MsgBox ContactID
'Make the lblCustID the same as the ContactID in the calls table
    lblCustID.Value = rs!ContactID
'reset recordset
    Set db = Nothing
    Set rs = Nothing
'Done with ContactID labe

When I run it I get a "duplicate declaration in current scope" error.

If I comment out this:

'    Dim db As dao.Database
'    Dim rs As dao.Recordset
'    Set db = CurrentDb

I get no error, but the labels do not change and the messageboxes do not pop on the screen either.

suggestions?

Thanks

mafhobb

 

[gemma-the-husky]

rs.FindFirst "[CallID]='" & CallIDvar & "'"

should be just

rs.FindFirst "[CallID]=" & CallIDvar

no quotes are needed with numbers.

 

[mafhobb]

All right, this is the code that works.

'change the SKU label caption to show SKU#
lblSKU.Caption = Nz(Forms![contacts]![Call Listing Subform].Form![SKU], "No SKU Loaded")
'find SKU message if it exists
    Dim SKUMess As String
    SKUMess = lblSKU.Caption
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSKUMessage", dbOpenDynaset)
    rs.FindFirst "[SKU#]='" & SKUMess & "'"
        If rs.NoMatch = True Then
            txtmessage.Value = "No SKU Message"
        Else
            txtmessage.Value = rs!SKUMessage
            txtmessage.Visible = True
            BoxMess.Visible = True
        End If
'reset recordset
    Set db = Nothing
    Set rs = Nothing
'Done with SKU label and Message Box

'Figure out the Customer ID
'What is the CallID from subform "Call Listing subfom"
Dim CallIDvar As Long
Dim ContactIDvar As Long
    CallIDvar = Forms![contacts]![Call Listing Subform].Form![CallID]
'find contactID based on CallID using a recordset on the Calls table, matching it to CallID
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Calls", dbOpenDynaset)
    rs.FindFirst "[CallID]=" & CallIDvar
'Assign the value to the ContactIDvar variable
    ContactIDvar = rs!ContactID
'reset recordset
    Set db = Nothing
    Set rs = Nothing
'Done with ContactID label

''find Customer ID and Full Name based on ContactIDvar using a recordset on the Contacts table, matching it to ContactIDvar
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Contacts", dbOpenDynaset)
    rs.FindFirst "[ContactID]=" & ContactIDvar
'Assign the value to the ContactIDvar variable
    lblCustID.Caption = rs!CustomerID
    lblCustName.Caption = rs!FirstName & " " & rs!LastName
'reset recordset
    Set db = Nothing
    Set rs = Nothing
'Done with ContactID label

I just have to think that there is a "cleaner" way of doing this...

mafhobb

 

[KenHigg]

Not sure why you use recordsets, why not a domain function?

 

[mafhobb]

...and the answer is...What is a domain function?

mafhobb

 

[KenHigg]

That a peek at this:

Link