Check for existing record from 2 criteria

[djackson]

Hopefully, this will make sense.

I am trying to open a form to a specific record if it exists, or add a record if it does not. But the check needs to be based on two criteria (the QuoteID and where "HandBraze" is True).
Whenever I use the following code, it opens to a new record each time.

Private Sub HandBraze_AfterUpdate()
If HandBraze = True Then
    Dim Exists As Boolean
        If IsNull(Me.OrderQty) Then
            MsgBox "Please enter a Diameter, Cut Length and Order Qty"
            Me.Diameter.SetFocus
            Me.HandBraze = False
            
        Else
            If DLookup("quoteid", "tblweldquotes", "quoteid = forms!frmpipequotation!quoteid") > 0 And DLookup("handbraze", "tblweldquotes", "quoteid = forms!frmpipequotation!quoteid") = True Then
                Exists = True
            Else
                Exists = False
            End If
            
            If Exists = True Then
                DoCmd.OpenForm "frmweldquote", , , "quoteid = " & QuoteID & "And [handbraze] =" & HandBraze, acFormEdit, acDialog
            Else
                DoCmd.OpenForm "frmweldquote", , , , acFormAdd, acDialog
            End If
        End If
ElseIf HandBraze = False Then
    HandBrazeCost = ""
    
End If
       
End Sub

I'm sure it is probably something quite simple, and I suspect it is to do with my Dlookup trying to find the "True" value, but I can't figure out how to do it.

Any help would be much appreciated.

Many thanks in advance,

Dave

 

[jzwp22]

First, I would strongly recommend not using the word EXISTS as a variable. EXISTS is an SQL keyword.

If DLookup("quoteid", "tblweldquotes", "quoteid = forms!frmpipequotation!quoteid") > 0 And DLookup("handbraze", "tblweldquotes", "quoteid = forms!frmpipequotation!quoteid") = True Then

In the above statement, the quoteID you are pulling from the form is a variable and thus it cannot be enclosed in the double quotes but rather concatenated to the string. Also, the form frmpipequotation must be open.

The IF..THEN should look like this:

If DLookup("quoteid", "tblweldquotes", "quoteid =" & forms!frmpipequotation!quoteid) > 0 And DLookup("handbraze", "tblweldquotes", "quoteid = " & forms!frmpipequotation!quoteid) = True Then

 

[jdraw]

I totally agree that EXISTS should not be a variable name as jzwp22 says. You might also try using the BeforeUpdate event.

 

[djackson]

That worked perfectly jzwp22! Thank you very much.
I've never been able to get to grips with the use of variables and double quotes. I kind of just bumble along through it (going on a course soon though).

And I didn't realise EXISTS was a keyword. That is now changed

Again, thank you very much for your help

 

[jzwp22]

You are welcome.

 

[jdraw]

Here's a link with many reserved words
http://allenbrowne.com/AppIssueBadWord.html