Determining if A record already exists in Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a data entry form and want to verify that the record does not exist in
the table. If the submitted record does exist I want a message box to
respond record x already exists. If not enter the data. I know that a MS
error will shoot back a key validation error message but I want a more
descriptive error.

All this would happened after the submit button is clicked on.

thanks.
 
From a user's point of view, I would be very frustrated to do all the work
of entering what I believed was a new record, only to be told by the
application that it already existed.

Another approach might be to give your users a way to find records that
already exist, and add a new one if they DON'T find an existing record. One
way to accomplish this is to use a combo box based on something they would
be likely to know/enter.

Regards

Jeff Boyce
<Office/Access MVP>
 
"I would be very frustrated to do all the work of entering what I believed
was a new record, only to be told by the application that it already existed."

Jeff that is actually why I want to check if the new record already exists
before the user continues to the next field. If the record id already exists
return "record exists in table aready" else allow the remaining data entry.
A combo box would be nice but it will not work for this task since the
record id is being entered at the time of entry (not using autonumber for
this key).
 
Ah, I missed that in your original post ... I didn't see mention of the ID,
just the "record".

So what is it about a combo box listing all existing record IDs that doesn't
work for you?

And what does a combo box have to do with an Autonumber field?

Jeff Boyce
<Office/Access MVP>
 
The system *should* advise the user if a record already exists, but like
Jeff says, it shouldn't wait till they hit the submit button after inputting
a truckload of data.

There are always a small number of data elements that collectively
constitute the criteria for which "pre-existence" can be determined. Once
these elements have been derived/input, the system should immediately check
to see if the record exists. In this way, the user isn't forced to waste
time inputting the remaining data elements if they can't eventually save the
record. I agree with Jeff; this would be *very* frustrating, and not a very
friendly thing for a piece of software to do.

There is the argument against system intervention, however in this case, I
think it lends itself more to data integrity, recoverability (which states
that user input should be validated as soon as possible) and task adequacy
(which implies that the system should minimise the ways in which a user can
make a mistake).

Identify those data elements that collectively constitute a record. Then in
the AfterUpdate event for each, call a private procedure to check if a
record already exists. You don't need to do that for every control on the
form; just those select few that constitute a record that can be looked for.

The code for something like that would be as follows:
Private Function RecordExists() As Boolean
Dim dbAs Database
Dim rs As DAO.Recordset
Dim sSQL As String

'Check that all the relevent controls meet the criteria
If Nz(Me.intControl1, 0) > 0 AND Len(Nz(Me.txtControl2, "")) > 0
Then
sSQL = "SELECT abc FROM MyTable WHERE NumField1 = " & _
Me.intControl1 & " AND TxtField2 = """ &
Me.txtControl2 & """"

'Check the database
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
MsgBox "Record exists."
RecordExists = True
End If
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Function

If you want, you can kill off the MsgBox from this function, and maybe give
the user the choice of subsequent actions if it returns True. This prevents
the user from making an error, and then passes control back to them.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
THANK you graham but I tried the code provided and get a "RUN-TIME ERROR
'91': OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET.

Private Function RecordExists() As Boolean
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

'Check that all the relevent controls meet the criteria
If Nz(Me.Text0, 0) > 0 And Len(Nz(Me.Text0, "")) > 0 Then
sSQL = "SELECT schedulenum FROM schedule WHERE schedulenum =
Me.text0 "

'Check the database
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
MsgBox "Record exists."
RecordExists = True
End If
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Function
 
Graham appears to have accidentally left out a line of code.

Before the line of code

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)

you need

Set db = CurrentDb
 
Doug - Thanks...I added that line of code and now I'm getting a "Run-time
error '3061': Too few parameters. Expected 1."

here is the code I am using...please note that I am just validating against
one field. THANK YOU AGAIN!!

Private Function RecordExists() As Boolean
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

'Check that all the relevent controls meet the criteria
If Nz(Me.Text0, 0) > 0 And Len(Nz(Me.Text0, "")) > 0 Then
sSQL = "SELECT ScheduleNum FROM schedule WHERE ScheduleNum =
ME.text0"

'Check the database
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > 0 Then
MsgBox "Record exists."
RecordExists = True
End If
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Function
 
ftrujill0 said:
Doug - Thanks...I added that line of code and now I'm getting a "Run-time
error '3061': Too few parameters. Expected 1."

here is the code I am using...please note that I am just validating against
one field. THANK YOU AGAIN!!

Private Function RecordExists() As Boolean
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

'Check that all the relevent controls meet the criteria
If Nz(Me.Text0, 0) > 0 And Len(Nz(Me.Text0, "")) > 0 Then
sSQL = "SELECT ScheduleNum FROM schedule WHERE ScheduleNum =
ME.text0"
Click to expand...


You need to use the value of the text box, not its name:

.. . . WHERE ScheduleNum = " & ME.text0
 
Bugger! How'd I manage to leave out a little detail like that?

Thanks for the catch Doug. :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
Don't feel bad. We both missed that it should have been

"... WHERE schedulenum = " & Me.text0
 
Back
Top