Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA help on if match exists (2000)

    Hi looking to have some help on the following.

    On the click of the button I have the following code.


    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim varsalesid As Variant
    Dim strlogon As String

    'Find logon information
    strlogon = Environ("Username")
    varsalesid = DLookup("[SALESID]", "tblsalesman", "Winlogon=" & Chr(34) & strlogon & Chr(34))

    Set cnn = CurrentProject.Connection
    rst.Open "tblOffer", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst!PROPERTYID = Me!PROPERTYID
    rst!PURCHID = Parent!PURCHID
    rst!SalesID = Me!SALESMANID
    rst.Update
    rst.Save
    rst.Close

    Set rst = Nothing
    Set cnn = Nothing

    DoCmd.OpenForm "frmOfferFin", acNormal, , "propertyid=" & Me!PROPERTYID & " and PurchID=" & Parent!PURCHID

    What I want it to do is scan through tblOffer to see if it can already find a match for the PURCHID and PropertyID being passed to it. If it can then do the last function by opening the form, if it cannot then it is to resume and add a record in the table.

    Any ideas?
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA help on if match exists (2000)

    Try this. Additions in bold

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim varsalesid As Variant
    Dim strlogon As String

    'Find logon information
    strlogon = Environ("Username")
    varsalesid = DLookup("[SALESID]", "tblsalesman", "Winlogon=" & Chr(34) & strlogon & Chr(34))

    If DCount("*", "tblOffer", "propertyid=" & Me!PROPERTYID & " and PurchID=" & Parent!PURCHID) = 0 Then
    Set cnn = CurrentProject.Connection
    rst.Open "tblOffer", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst!PROPERTYID = Me!PROPERTYID
    rst!PURCHID = Parent!PURCHID
    rst!SalesID = Me!SALESMANID
    rst.Update
    rst.Save
    rst.Close

    Set rst = Nothing
    Set cnn = Nothing
    End If

    DoCmd.OpenForm "frmOfferFin", acNormal, , "propertyid=" & Me!PROPERTYID & " and PurchID=" & Parent!PURCHID

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA help on if match exists (2000)

    Thank you, worked a treat! Will have to start paying you for royalties soon! [img]/forums/images/smilies/smile.gif[/img]
    Best Regards,

    Luke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •