CODEWITHSUNDEEP
vbams-accessif-statementms-access-2010
user1687929
user1687929

Reputation: 143

If Then Else on 2 separate recordsets

I need to test 2 different conditions on 2 separate recordsets. I am not good with VBA, I have a very basic code, I just need help with If Then syntax. Here is my Code:

Private Sub SaveRecord_Click()

    '****  add a new record  ****
    Dim db As Database, rs1 As Recordset, rs2 As Recordset

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("ExpAsset", DB_OPEN_DYNASET)
    Set rs2 = db.OpenRecordset("LogTest", DB_OPEN_DYNASET)

'****  Following code is updating the tables in the ExpAsset table with new information     from the form****
If rs1.NoMatch Then
        rs1.AddNew
        rs1("User") = Me!Location
        rs1("Type") = Me!Type
        rs1("Model") = Me!MODEL
        rs1("Asset_ID") = Me!Asset_ID
        rs1("Serial_Number") = Me!Serial
        rs1.Update
Else
        MsgBox "Serial Number: " & Me!Serial & " already exists.", 48, "ERROR!"
        Me!Serial.SetFocus

End If
'****  Following code is creating a log in Logtest table with information provided in the form****
If rs2.NoMatch Then
        rs2.AddNew
        rs2("Asset_Type") = Me!Type
        rs2("Transfer_Type") = "New purchase"
        rs2("Description") = Me!DESCRIPTION
        rs2("DELIVERED_TO") = Me!Location
        rs2("DELIVERED_BY") = Me!DeliveredBy
        rs2("RECEIVED_BY") = Me!Receiver
        rs2("RECEIVED_DATE") = Me!Date
        rs2.Update

        MsgBox "Part information has been updated in the database!"

        'clear the controls to add more customers
        Call ClearControls
Else
        MsgBox "Asset ID: " & Me!Asset_ID & " already exists.", 48, "ERROR!"
        Me!Asset_ID.SetFocus
End If

    rs1.Close
    rs2.Close
    db.Close

End Sub

I know The If Then Else syntax is incorrect, I need to check both conditions, serial no. and asset ID.

Upvotes: 1

Views: 1068

Answers (2)

user1687929
user1687929

Reputation: 143

You are right HansUp, my code was silly, I realised later after I had posted that there was no criteria to test against. Following is the right code, I tested it and it works :)

Private Sub SaveRecord_Click()


    '****  add a new record  ****
    Dim db As Database, rs1 As Recordset, rs2 As Recordset, Criteria As String, Criteria2 As String

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("ExpAsset", DB_OPEN_DYNASET)
    Set rs2 = db.OpenRecordset("LogTest", DB_OPEN_DYNASET)

    Criteria = "[serial_number]='" & Me!Serial & "'"
    Criteria2 = "[Asset_ID]='" & Me!Asset_ID & "'"
'****  Following code is updating the tables in the ExpAsset table with new information from the form****
rs1.FindFirst Criteria
If rs1.NoMatch Then
    rs1.FindFirst Criteria2
    If rs1.NoMatch Then
        rs1.AddNew
        rs1("User") = Me!Location
        rs1("Type") = Me!Type
        rs1("Model") = Me!MODEL
        rs1("Asset_ID") = Me!Asset_ID
        rs1("Serial_Number") = Me!Serial
        rs1.Update

'****  Following code is creating a log in Logtest table with information provided in the form****
     rs2.AddNew
        rs2("Asset_Type") = Me!Type
        rs2("Transfer_Type") = "New purchase"
        rs2("Description") = Me!DESCRIPTION
        rs2("DELIVERED_TO") = Me!Location
        rs2("DELIVERED_BY") = Me!DeliveredBy
        rs2("RECEIVED_BY") = Me!Receiver
        rs2("RECEIVED_DATE") = Me!Date
        rs2.Update

        MsgBox "Part information has been updated in the database!"
    'clear the controls to add more customers
        Call ClearControls

    Else
        MsgBox "Asset_ID: " & Me!Asset_ID & " already exists.", 48, "ERROR!"
        Me!Asset_ID.SetFocus
    End If
Else
        MsgBox "Serial Number: " & Me!Serial & " already exists.", 48, "ERROR!"
        Me!Serial.SetFocus

End If

    rs1.Close
    rs2.Close
    db.Close


End Sub

Upvotes: 1

HansUp
HansUp

Reputation: 97131

Check the Access online help topic for Recordset.NoMatch Property:

Indicates whether a particular record was found by using the Seek method or one of the Find methods (Microsoft Access workspaces only).

However in your code, you're opening a recordset but not using either seek or find. In that situation, you haven't asked to match anything, so .NoMatch will be False every time. The logic is similar to this ...

If rs1.NoMatch Then
    ' this code will never run
Else
    ' this code runs every time
End If

You can use DCount to determine whether ExpAsset contains a given Asset_ID value.

DCount("*", "ExpAsset", "Asset_ID = " & Me!Asset_ID) ' if Asset_ID is numeric
DCount("*", "ExpAsset", "Asset_ID = '" & Me!Asset_ID & "'") ' if Asset_ID is text

Once you have a working DCount expression, you can use logic like this ...

If DCount("*", "ExpAsset", "Asset_ID = " & Me!Asset_ID) = 0 Then
    ' Asset_ID not present in table -> add it
Else
    ' inform user Asset_ID already present in table
End If

Upvotes: 1

Related Questions