How to check in advance to see if a record already exists?

[Pat Dools]

Hello, I have forms set up in a series in my database, and in the first form,
I have the following code behind a Command Button to move you to the next
form:

Private Sub CommandBeginSeries_Click()
On Error GoTo Err_BeginSeries_Click
Dim strSQL As String, strName As String, rst As DAO.Recordset, dbs As Database

Set dbs = CurrentDb
strSQL = "SELECT * FROM [LU_Forms] WHERE [FormOrder] = 2"
Set rst = dbs.OpenRecordset(strSQL)

DoCmd.OpenForm rst![FormName], , , , acAdd

Exit_BeginSeries_Click:
Exit Sub

Err_BeginSeries_Click:
MsgBox Err.Description
Resume Exit_BeginSeries_Click
End Sub

As the next form is opened, it looks back to the four header fields and
auto-fills them on the next form in the series based on the values present in
the initial form (see code):

Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
End If

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!id = Forms!fScrEligCriteria!id
!ptin = Forms!fScrEligCriteria!ptin
!site = Forms!fScrEligCriteria!site
End With

SetAutoValues_err:
'MsgBox Err.Description
Resume Next

End Sub


Is there a way to check to see if a record already exists as this second
form in the series is opened, and if there is, then data entry person
receives a messages and is brought back to initial form in series? The way
it works now, a new record is brought up using the second form in series, and
data entry person is allowed to enter information, but none of it gets saved
(which is good). BUT the data entry person is never alerted they are trying
to enter info. they will not be allowed to save. This could save them some
time.

Thanks,

 

[Wayne Morgan]

Actually, you could probably make the check before attempting to open the
second form. In the button's Click event use DLookup or DCount to see if the
record exists, passing the parameter's you're looking for in the WHERE part
of the function call. If the record doesn't exist, open the form. If it does
exist, inform the user with a MsgBox.

 

[Pat Dools]

HI Wayne,

I adjusted the code to do the DLookup (see below), and as I step thru using
the debugger, the stepper gets to the 'uniqueRec' DLookup, and then goes to
the error routine saying 'Object required'. What I am trying to do is look
up to the table underlying the form I am going to, and if there is an 'id'
field that matches what is currently present in the 'id' field of the current
form ('fScrEligCriteria'), then they get a msgBox, otherwise, they get the
new form. What am I missing here? (See code below):

Private Sub CommandBeginSeries_Click()
On Error GoTo Err_BeginSeries_Click
Dim uniqueRec As Variant
Dim strSQL As String, strName As String, rst As DAO.Recordset, dbs As Database

uniqueRec = DLookup("[id]", "tScrDemographics", "[id] =" _
& Forms!fScrEligCriteria!id)
If uniqueRec Is Null Then
MsgBox ("A Demographics form has already been completed for this patient")
Else
Set dbs = CurrentDb
strSQL = "SELECT * FROM [LU_Forms] WHERE [FormOrder] = 2"
Set rst = dbs.OpenRecordset(strSQL)

DoCmd.OpenForm rst![FormName], , , , acAdd
End If

Exit_BeginSeries_Click:
Exit Sub

Err_BeginSeries_Click:
MsgBox Err.Description
Resume Exit_BeginSeries_Click
End Sub

Actually, you could probably make the check before attempting to open the
second form. In the button's Click event use DLookup or DCount to see if the
record exists, passing the parameter's you're looking for in the WHERE part
of the function call. If the record doesn't exist, open the form. If it does
exist, inform the user with a MsgBox.

--
Wayne Morgan
MS Access MVP

Hello, I have forms set up in a series in my database, and in the first
form,
I have the following code behind a Command Button to move you to the next
form:

Private Sub CommandBeginSeries_Click()
On Error GoTo Err_BeginSeries_Click
Dim strSQL As String, strName As String, rst As DAO.Recordset, dbs As
Database

Set dbs = CurrentDb
strSQL = "SELECT * FROM [LU_Forms] WHERE [FormOrder] = 2"
Set rst = dbs.OpenRecordset(strSQL)

DoCmd.OpenForm rst![FormName], , , , acAdd

Exit_BeginSeries_Click:
Exit Sub

Err_BeginSeries_Click:
MsgBox Err.Description
Resume Exit_BeginSeries_Click
End Sub

As the next form is opened, it looks back to the four header fields and
auto-fills them on the next form in the series based on the values present
in
the initial form (see code):

Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
End If

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!id = Forms!fScrEligCriteria!id
!ptin = Forms!fScrEligCriteria!ptin
!site = Forms!fScrEligCriteria!site
End With

SetAutoValues_err:
'MsgBox Err.Description
Resume Next

End Sub


Is there a way to check to see if a record already exists as this second
form in the series is opened, and if there is, then data entry person
receives a messages and is brought back to initial form in series? The
way
it works now, a new record is brought up using the second form in series,
and
data entry person is allowed to enter information, but none of it gets
saved
(which is good). BUT the data entry person is never alerted they are
trying
to enter info. they will not be allowed to save. This could save them
some
time.

Thanks,

 

[Wayne Morgan]

First, which line does it indicate the error is in?

Next, instead of "If uniqueRec Is Null Then" try "If IsNull(uniqueRec)
Then". Also, if the result is Null, then a match wasn't found. You appear to
have the Then...Else portions of the If statement reversed.

--
Wayne Morgan
MS Access MVP

HI Wayne,

I adjusted the code to do the DLookup (see below), and as I step thru
using
the debugger, the stepper gets to the 'uniqueRec' DLookup, and then goes
to
the error routine saying 'Object required'. What I am trying to do is
look
up to the table underlying the form I am going to, and if there is an 'id'
field that matches what is currently present in the 'id' field of the
current
form ('fScrEligCriteria'), then they get a msgBox, otherwise, they get the
new form. What am I missing here? (See code below):

Private Sub CommandBeginSeries_Click()
On Error GoTo Err_BeginSeries_Click
Dim uniqueRec As Variant
Dim strSQL As String, strName As String, rst As DAO.Recordset, dbs As
Database

uniqueRec = DLookup("[id]", "tScrDemographics", "[id] =" _
& Forms!fScrEligCriteria!id)
If uniqueRec Is Null Then
MsgBox ("A Demographics form has already been completed for this
patient")
Else
Set dbs = CurrentDb
strSQL = "SELECT * FROM [LU_Forms] WHERE [FormOrder] = 2"
Set rst = dbs.OpenRecordset(strSQL)

DoCmd.OpenForm rst![FormName], , , , acAdd
End If

Exit_BeginSeries_Click:
Exit Sub

Err_BeginSeries_Click:
MsgBox Err.Description
Resume Exit_BeginSeries_Click
End Sub

Actually, you could probably make the check before attempting to open the
second form. In the button's Click event use DLookup or DCount to see if
the
record exists, passing the parameter's you're looking for in the WHERE
part
of the function call. If the record doesn't exist, open the form. If it
does
exist, inform the user with a MsgBox.

--
Wayne Morgan
MS Access MVP

Hello, I have forms set up in a series in my database, and in the first
form,
I have the following code behind a Command Button to move you to the
next
form:

Private Sub CommandBeginSeries_Click()
On Error GoTo Err_BeginSeries_Click
Dim strSQL As String, strName As String, rst As DAO.Recordset, dbs As
Database

Set dbs = CurrentDb
strSQL = "SELECT * FROM [LU_Forms] WHERE [FormOrder] = 2"
Set rst = dbs.OpenRecordset(strSQL)

DoCmd.OpenForm rst![FormName], , , , acAdd

Exit_BeginSeries_Click:
Exit Sub

Err_BeginSeries_Click:
MsgBox Err.Description
Resume Exit_BeginSeries_Click
End Sub

As the next form is opened, it looks back to the four header fields and
auto-fills them on the next form in the series based on the values
present
in
the initial form (see code):

Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
End If

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!id = Forms!fScrEligCriteria!id
!ptin = Forms!fScrEligCriteria!ptin
!site = Forms!fScrEligCriteria!site
End With

SetAutoValues_err:
'MsgBox Err.Description
Resume Next

End Sub


Is there a way to check to see if a record already exists as this
second
form in the series is opened, and if there is, then data entry person
receives a messages and is brought back to initial form in series? The
way
it works now, a new record is brought up using the second form in
series,
and
data entry person is allowed to enter information, but none of it gets
saved
(which is good). BUT the data entry person is never alerted they are
trying
to enter info. they will not be allowed to save. This could save them
some
time.

Thanks,