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

[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

 

[Graham Mandeno]

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
...

 

[Rick Brandt]

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.

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>.

 

[acs68]

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

 

[Rick Brandt]

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"

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.