on click see if a record exists in a table and exit without running the code

  • Thread starter Thread starter acs68
  • Start date Start date
A

acs68

Hi all,

have a table called tblJobs with the following fields.

JobNumber
JobDescription

If a JobNumber exists, then I would like a msgbox to pop up and say "Record
Exists" and then Exit, so that the user can enter another number.

If it doesn't exist then simply move on to some other code I'll have
attached to the button.

Any help here would be greatly appreciated.

cheers,

Adam
 
Hi Adam

You can check if a record exists using DLookup:

If Not IsNull(DLookup("JobNumber", "tblJobs", _
"JobNumber=" & txtJobNumberEntered )) Then
MsgBox "Job already exists"
Else
...
 
acs68 said:
Hi all,

have a table called tblJobs with the following fields.

JobNumber
JobDescription

If a JobNumber exists, then I would like a msgbox to pop up and say "Record
Exists" and then Exit, so that the user can enter another number.

If it doesn't exist then simply move on to some other code I'll have
attached to the button.

Any help here would be greatly appreciated.
Click to expand...

Best place to do this is in the BeforeUpdate event of the JobNumber TextBox...

If DCount("*", "YourTableName", "JobNumber = " & Me.JobNumber & "") > 0 Then
MsgBox "Record Exists"
Cancel = True
End If

The nice thing about BeforeUpdate is the option to use "Cancel = True" as in the
above example. That literally cancels the update and leave focus in the
control. Just as a validation rule the user will be forced to change the entry
or cancel altogether with <Escape>.
 
Thanks Rick,

could you have a look at this code and see what I might be doing wrong. I
suspect it's because the field AppNo is "Text"

Private Sub txtAppNo_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblConditions_Build", "AppNo = " & Me.txtAppNo & "") > 0
Then
MsgBox "Record Exists"
Cancel = True
End If

Any suggestions ?

cheers,

Adam
 
acs68 said:
Thanks Rick,

could you have a look at this code and see what I might be doing wrong. I
suspect it's because the field AppNo is "Text"
Click to expand...

Correct. If it's text it need to be delimited with quotes. Note that
below I added single quotes in front of and behind the form reference.
 
Back
Top