Check if record exists in another table
I have the following code attached to the close button to see if a record exists in the invoice table. Shirley, there is quicker way to determine that other than the folllowing code. Thanks.
Private Sub cmdClose_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim sSQL As String
Set db = CurrentDb
sSQL = "SELECT Invoices.ContractNumber" _
& " FROM Invoices" _
& " WHERE Invoices.ContractNumber=" _
& Chr(34) & Me.ContractNumber & Chr(34)
Set rs = db.OpenRecordset(sSQL)
If rs.RecordCount = 0 Then
MsgBox ("Not in Invoices table")
Else
DoCmd.Close
End If
end sub
Private Sub cmdClose_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim sSQL As String
Set db = CurrentDb
sSQL = "SELECT Invoices.ContractNumber" _
& " FROM Invoices" _
& " WHERE Invoices.ContractNumber=" _
& Chr(34) & Me.ContractNumber & Chr(34)
Set rs = db.OpenRecordset(sSQL)
If rs.RecordCount = 0 Then
MsgBox ("Not in Invoices table")
Else
DoCmd.Close
End If
end sub
You could use the Dcount function to count the number of mathching records
If dcount("contractnumber","i
MsgBox ("Not in Invoices table")
Else
DoCmd.Close
End If
Also, if contractnumber is a numeric field and not text, the " ' " characters are not needed. the ' character is only used to compare text values, not numeric.
If it is numeric, you could change the last part of the Dcount to:
"contractnumber = " & me!contractnumber)
If dcount("contractnumber","i
MsgBox ("Not in Invoices table")
Else
DoCmd.Close
End If
Also, if contractnumber is a numeric field and not text, the " ' " characters are not needed. the ' character is only used to compare text values, not numeric.
If it is numeric, you could change the last part of the Dcount to:
"contractnumber = " & me!contractnumber)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sasha42
How about something like:
If IsNull(DLookup("[ContractN
MsgBox ("Not in Invoices table")
End if
Sugestion would be to NOT use Same Name for table field and form field.
I try to use frmContractNumber for the form name and or tblContractName for the Table field.
While it may be difficult to rename the fields if the application is older or large its a good practice to always have different names.
Don
How about something like:
If IsNull(DLookup("[ContractN
MsgBox ("Not in Invoices table")
End if
Sugestion would be to NOT use Same Name for table field and form field.
I try to use frmContractNumber for the form name and or tblContractName for the Table field.
While it may be difficult to rename the fields if the application is older or large its a good practice to always have different names.
Don
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
that is one of the quicker ways to accomplish your current task.
If you need to speed the operation up some, check to see that there is an index set on ContractNumber field in the table
regards
-j-