User not alerted of duplicate 'key' records?

P

Pat Dools

Hello,

I have a database of clinical patient records that is generally working
well, but does not alert the data entry person if they have entered in a
record w/ a duplicate 'key'. I check to see (in Sub 'Next_Click' below) to
see if certain required fields are filled in, and, if they are, then the
'Next Form' Command Button operates Sub 'OpenNextForm'-- also below, which
opens the next form in the 'series'. The user gets a nice error message if
one of the required fields is not filled in, but if they hit the 'Next Form'
Command Button and have entered in a record with a duplicate 'key', the user
gets no error message and moves on to the next form. Access does not save
the record (as it shouldn't), but I need the data entry person to be alerted
that they have just attempted to enter a record w/ a duplicate 'key'. The
only time they get an error message ab/ the duplicate 'key' is when they
click on the 'x' in the upper-right corner of the window. What can I add to
the existing code below, so the data entry person is alerted if there is a
duplicate 'key'? Thank you!

Private Sub Next_Click()

On Error GoTo Err_Next_Click

If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
Call OpenNextForm(Me.Name)
End If

Exit_Next_Click:
Exit Sub

Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click

End Sub

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String

On Error GoTo OpenNextForm_Err

intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , "[id] = Forms.fScrEligCriteria.id"
'DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName

OpenNextForm_Err:
'MsgBox Err.Description
Resume Next

End Sub
 
J

John Nurick

Hi Pat,

The first thing to do is to set an index on the key so that it's not
possible to enter a duplicate.

Then, to trap the duplicate value before Access attempts to update the
table, it's usually enough just to use DCount(), something like this,
where TTT is the name of the table KKK the name of the key field, and
the key value to be tested is in lngKey

If DCount("KKK", "TTT", "KKK=" & lngKey) > 0 Then
MsgBox "Key value " & lngKey & " is already in the table"
End If

The last argument of DCount() will be a little different if the key is a
text field, or if it consists of more than one field.


Hello,

I have a database of clinical patient records that is generally working
well, but does not alert the data entry person if they have entered in a
record w/ a duplicate 'key'. I check to see (in Sub 'Next_Click' below) to
see if certain required fields are filled in, and, if they are, then the
'Next Form' Command Button operates Sub 'OpenNextForm'-- also below, which
opens the next form in the 'series'. The user gets a nice error message if
one of the required fields is not filled in, but if they hit the 'Next Form'
Command Button and have entered in a record with a duplicate 'key', the user
gets no error message and moves on to the next form. Access does not save
the record (as it shouldn't), but I need the data entry person to be alerted
that they have just attempted to enter a record w/ a duplicate 'key'. The
only time they get an error message ab/ the duplicate 'key' is when they
click on the 'x' in the upper-right corner of the window. What can I add to
the existing code below, so the data entry person is alerted if there is a
duplicate 'key'? Thank you!

Private Sub Next_Click()

On Error GoTo Err_Next_Click

If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
Call OpenNextForm(Me.Name)
End If

Exit_Next_Click:
Exit Sub

Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click

End Sub

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String

On Error GoTo OpenNextForm_Err

intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , "[id] = Forms.fScrEligCriteria.id"
'DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName

OpenNextForm_Err:
'MsgBox Err.Description
Resume Next

End Sub
Click to expand...
 
P

Pat Dools

Hi John,

Sorry, forgot to mention in my original post that the key is more than one
field (sometimes 2, sometimes 3 fields). I've tried this:

If DCount("id", "tScrMedSurgHx", "id=" & lngKey) > 0 And_
DCount("tScrMedSurgHx","medhx_number=" & IngKey2) > 0 Then_
MsgBox "Key value " & lngKey & "and" & IngKey2 & " is already in the
table"
End If

I just get the ol' 'Syntax error'.
How do I get the syntax correct for a multi-field key?

Thank you!
John Nurick said:
Hi Pat,

The first thing to do is to set an index on the key so that it's not
possible to enter a duplicate.

Then, to trap the duplicate value before Access attempts to update the
table, it's usually enough just to use DCount(), something like this,
where TTT is the name of the table KKK the name of the key field, and
the key value to be tested is in lngKey

If DCount("KKK", "TTT", "KKK=" & lngKey) > 0 Then
MsgBox "Key value " & lngKey & " is already in the table"
End If

The last argument of DCount() will be a little different if the key is a
text field, or if it consists of more than one field.


Hello,

I have a database of clinical patient records that is generally working
well, but does not alert the data entry person if they have entered in a
record w/ a duplicate 'key'. I check to see (in Sub 'Next_Click' below) to
see if certain required fields are filled in, and, if they are, then the
'Next Form' Command Button operates Sub 'OpenNextForm'-- also below, which
opens the next form in the 'series'. The user gets a nice error message if
one of the required fields is not filled in, but if they hit the 'Next Form'
Command Button and have entered in a record with a duplicate 'key', the user
gets no error message and moves on to the next form. Access does not save
the record (as it shouldn't), but I need the data entry person to be alerted
that they have just attempted to enter a record w/ a duplicate 'key'. The
only time they get an error message ab/ the duplicate 'key' is when they
click on the 'x' in the upper-right corner of the window. What can I add to
the existing code below, so the data entry person is alerted if there is a
duplicate 'key'? Thank you!

Private Sub Next_Click()

On Error GoTo Err_Next_Click

If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
Call OpenNextForm(Me.Name)
End If

Exit_Next_Click:
Exit Sub

Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click

End Sub
Where Sub 'OpenNextForm is:
Click to expand...

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String

On Error GoTo OpenNextForm_Err

intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , "[id] = Forms.fScrEligCriteria.id"
'DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName

OpenNextForm_Err:
'MsgBox Err.Description
Resume Next

End Sub
Click to expand...
Click to expand...
 
J

John Nurick

You need to use one DCount(), with both (or all) the key field values
tested in its Where argument for the same DCount(). Something like this:

If DCount("id", "tScrMedSurgHx", _
"(id = " & lngKey & ") AND "(medhx_number = " _
& Ingkey2 & ")") > 0 Then

in which the Where argument should evaluate to (say)

"(id = 99) AND (medhx_number = 234)"

Hi John,

Sorry, forgot to mention in my original post that the key is more than one
field (sometimes 2, sometimes 3 fields). I've tried this:

If DCount("id", "tScrMedSurgHx", "id=" & lngKey) > 0 And_
DCount("tScrMedSurgHx","medhx_number=" & IngKey2) > 0 Then_
MsgBox "Key value " & lngKey & "and" & IngKey2 & " is already in the
table"
End If

I just get the ol' 'Syntax error'.
How do I get the syntax correct for a multi-field key?

Thank you!
John Nurick said:
Hi Pat,

The first thing to do is to set an index on the key so that it's not
possible to enter a duplicate.

Then, to trap the duplicate value before Access attempts to update the
table, it's usually enough just to use DCount(), something like this,
where TTT is the name of the table KKK the name of the key field, and
the key value to be tested is in lngKey

If DCount("KKK", "TTT", "KKK=" & lngKey) > 0 Then
MsgBox "Key value " & lngKey & " is already in the table"
End If

The last argument of DCount() will be a little different if the key is a
text field, or if it consists of more than one field.


Hello,

I have a database of clinical patient records that is generally working
well, but does not alert the data entry person if they have entered in a
record w/ a duplicate 'key'. I check to see (in Sub 'Next_Click' below) to
see if certain required fields are filled in, and, if they are, then the
'Next Form' Command Button operates Sub 'OpenNextForm'-- also below, which
opens the next form in the 'series'. The user gets a nice error message if
one of the required fields is not filled in, but if they hit the 'Next Form'
Command Button and have entered in a record with a duplicate 'key', the user
gets no error message and moves on to the next form. Access does not save
the record (as it shouldn't), but I need the data entry person to be alerted
that they have just attempted to enter a record w/ a duplicate 'key'. The
only time they get an error message ab/ the duplicate 'key' is when they
click on the 'x' in the upper-right corner of the window. What can I add to
the existing code below, so the data entry person is alerted if there is a
duplicate 'key'? Thank you!

Private Sub Next_Click()

On Error GoTo Err_Next_Click

If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
Call OpenNextForm(Me.Name)
End If

Exit_Next_Click:
Exit Sub

Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click

End Sub

Where Sub 'OpenNextForm is:

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String

On Error GoTo OpenNextForm_Err

intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , "[id] = Forms.fScrEligCriteria.id"
'DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName

OpenNextForm_Err:
'MsgBox Err.Description
Resume Next

End Sub
Click to expand...
Click to expand...
Click to expand...
 
P

Pat Dools

HI John,

Hopefully, I'm getting closer on this one. A couple of questions:

1) Does this go in the 'BeforeUpdate' property of the Form itself, or in
each control on the form that is associated w/ a Key field?
2) When I use the continuation symbol (_) to wrap code to the next line, it
gives me an 'invalid symbol' error. I'd really like to get beyond this, as
sometimes code
gets long in the tooth
3) When I use this code:

If DCount("id", "tScrMedSurgHx","(id = " & lngKey & ") AND "(medhx_number =
" & Ingkey2 & ")")> 0 Then
MsgBox ""Key value " & lngKey & "and" & IngKey2 & " is already in the
table""
Click to expand...
End If

I get 'Compile error: Expected: List separator or )' right at the
'(medhx_number..' piece of the code. In fact, no matter how I tweak the
code, it always seems to 'break down' here. Also, just to see if I could get
it to work using just one of the Key fields didn't work either, if that is
also helpful.

Thanks again.

John Nurick said:
You need to use one DCount(), with both (or all) the key field values
tested in its Where argument for the same DCount(). Something like this:

If DCount("id", "tScrMedSurgHx", _
"(id = " & lngKey & ") AND "(medhx_number = " _
& Ingkey2 & ")") > 0 Then

in which the Where argument should evaluate to (say)

"(id = 99) AND (medhx_number = 234)"

Hi John,

Sorry, forgot to mention in my original post that the key is more than one
field (sometimes 2, sometimes 3 fields). I've tried this:

If DCount("id", "tScrMedSurgHx", "id=" & lngKey) > 0 And_
DCount("tScrMedSurgHx","medhx_number=" & IngKey2) > 0 Then_
MsgBox "Key value " & lngKey & "and" & IngKey2 & " is already in the
table"
End If

I just get the ol' 'Syntax error'.
How do I get the syntax correct for a multi-field key?

Thank you!
John Nurick said:
Hi Pat,

The first thing to do is to set an index on the key so that it's not
possible to enter a duplicate.

Then, to trap the duplicate value before Access attempts to update the
table, it's usually enough just to use DCount(), something like this,
where TTT is the name of the table KKK the name of the key field, and
the key value to be tested is in lngKey

If DCount("KKK", "TTT", "KKK=" & lngKey) > 0 Then
MsgBox "Key value " & lngKey & " is already in the table"
End If

The last argument of DCount() will be a little different if the key is a
text field, or if it consists of more than one field.


On Sat, 16 Apr 2005 05:45:02 -0700, Pat Dools

Hello,

I have a database of clinical patient records that is generally working
well, but does not alert the data entry person if they have entered in a
record w/ a duplicate 'key'. I check to see (in Sub 'Next_Click' below) to
see if certain required fields are filled in, and, if they are, then the
'Next Form' Command Button operates Sub 'OpenNextForm'-- also below, which
opens the next form in the 'series'. The user gets a nice error message if
one of the required fields is not filled in, but if they hit the 'Next Form'
Command Button and have entered in a record with a duplicate 'key', the user
gets no error message and moves on to the next form. Access does not save
the record (as it shouldn't), but I need the data entry person to be alerted
that they have just attempted to enter a record w/ a duplicate 'key'. The
only time they get an error message ab/ the duplicate 'key' is when they
click on the 'x' in the upper-right corner of the window. What can I add to
the existing code below, so the data entry person is alerted if there is a
duplicate 'key'? Thank you!

Private Sub Next_Click()

On Error GoTo Err_Next_Click

If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
Call OpenNextForm(Me.Name)
End If

Exit_Next_Click:
Exit Sub

Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click

End Sub

Where Sub 'OpenNextForm is:

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String

On Error GoTo OpenNextForm_Err

intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , "[id] = Forms.fScrEligCriteria.id"
'DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName

OpenNextForm_Err:
'MsgBox Err.Description
Resume Next

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

John Nurick

HI John,

Hopefully, I'm getting closer on this one. A couple of questions:

1) Does this go in the 'BeforeUpdate' property of the Form itself, or in
each control on the form that is associated w/ a Key field?
Click to expand...

You need to put it in the form's BeforeUpdate event procedure in any
case. This means that users don't get warned about duplicate values
until they try to move to the next form, so if you want to warn them
when they exit the controls associated with the key fields you must also
use the controls' BeforeUpdate event procedures.

If you do, you have to cover the situation where the user types a value
for one of the key fields but the other is still blank. So you'd need
logic something like this in each textbox's BeforeUpdate procedure:

If Not IsNull(Me.OtherTextbox) Then
If DCount(blah blah) > 0 Then
Cancel = True
MsgBox blah blah
End If
End If
2) When I use the continuation symbol (_) to wrap code to the next line, it
gives me an 'invalid symbol' error. I'd really like to get beyond this, as
sometimes code
gets long in the tooth
Click to expand...

The continuation symbol is " _", not "_". The space is essential.
3) When I use this code:

If DCount("id", "tScrMedSurgHx","(id = " & lngKey & ") AND "(medhx_number =
" & Ingkey2 & ")")> 0 Then
MsgBox ""Key value " & lngKey & "and" & IngKey2 & " is already in the
End If

I get 'Compile error: Expected: List separator or )' right at the
'(medhx_number..' piece of the code. In fact, no matter how I tweak the
code, it always seems to 'break down' here. Also, just to see if I could get
it to work using just one of the Key fields didn't work either, if that is
also helpful.
Click to expand...

It looks as if the '"' in
AND "(medhx_number
is superfluous.
Thanks again.

John Nurick said:
You need to use one DCount(), with both (or all) the key field values
tested in its Where argument for the same DCount(). Something like this:

If DCount("id", "tScrMedSurgHx", _
"(id = " & lngKey & ") AND "(medhx_number = " _
& Ingkey2 & ")") > 0 Then

in which the Where argument should evaluate to (say)

"(id = 99) AND (medhx_number = 234)"

Hi John,

Sorry, forgot to mention in my original post that the key is more than one
field (sometimes 2, sometimes 3 fields). I've tried this:

If DCount("id", "tScrMedSurgHx", "id=" & lngKey) > 0 And_
DCount("tScrMedSurgHx","medhx_number=" & IngKey2) > 0 Then_
MsgBox "Key value " & lngKey & "and" & IngKey2 & " is already in the
table"
End If

I just get the ol' 'Syntax error'.
How do I get the syntax correct for a multi-field key?

Thank you!
:

Hi Pat,

The first thing to do is to set an index on the key so that it's not
possible to enter a duplicate.

Then, to trap the duplicate value before Access attempts to update the
table, it's usually enough just to use DCount(), something like this,
where TTT is the name of the table KKK the name of the key field, and
the key value to be tested is in lngKey

If DCount("KKK", "TTT", "KKK=" & lngKey) > 0 Then
MsgBox "Key value " & lngKey & " is already in the table"
End If

The last argument of DCount() will be a little different if the key is a
text field, or if it consists of more than one field.


On Sat, 16 Apr 2005 05:45:02 -0700, Pat Dools

Hello,

I have a database of clinical patient records that is generally working
well, but does not alert the data entry person if they have entered in a
record w/ a duplicate 'key'. I check to see (in Sub 'Next_Click' below) to
see if certain required fields are filled in, and, if they are, then the
'Next Form' Command Button operates Sub 'OpenNextForm'-- also below, which
opens the next form in the 'series'. The user gets a nice error message if
one of the required fields is not filled in, but if they hit the 'Next Form'
Command Button and have entered in a record with a duplicate 'key', the user
gets no error message and moves on to the next form. Access does not save
the record (as it shouldn't), but I need the data entry person to be alerted
that they have just attempted to enter a record w/ a duplicate 'key'. The
only time they get an error message ab/ the duplicate 'key' is when they
click on the 'x' in the upper-right corner of the window. What can I add to
the existing code below, so the data entry person is alerted if there is a
duplicate 'key'? Thank you!

Private Sub Next_Click()

On Error GoTo Err_Next_Click

If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
Call OpenNextForm(Me.Name)
End If

Exit_Next_Click:
Exit Sub

Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click

End Sub

Where Sub 'OpenNextForm is:

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String

On Error GoTo OpenNextForm_Err

intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , "[id] = Forms.fScrEligCriteria.id"
'DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName

OpenNextForm_Err:
'MsgBox Err.Description
Resume Next

End Sub
Click to expand...
Click to expand...
Click to expand...
 
P

Pat Dools

Hi John,

When I have the following code, Iat least get the error message, 'You can't
go to the specified record', and the duplicate key record is NOT saved in the
underlying table. How do I need to adjust the MsgBox to give the errant
values in both key fields so that the error message is nice and clear? Thank
you so much for helping me get this far. I'm sure you've noticed I'm
curtting my teeth on code syntax still! Thx, Patrick

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("id", "tScrMedSurgHx", id = " & lngKey & " And medhx_number = " &
IngKey2 & ") > 0 Then
MsgBox "Key value " & lngKey & " AND " & IngKey2 & " is already in the Table "
End If

End Sub

John Nurick said:
HI John,

Hopefully, I'm getting closer on this one. A couple of questions:

1) Does this go in the 'BeforeUpdate' property of the Form itself, or in
each control on the form that is associated w/ a Key field?
Click to expand...

You need to put it in the form's BeforeUpdate event procedure in any
case. This means that users don't get warned about duplicate values
until they try to move to the next form, so if you want to warn them
when they exit the controls associated with the key fields you must also
use the controls' BeforeUpdate event procedures.

If you do, you have to cover the situation where the user types a value
for one of the key fields but the other is still blank. So you'd need
logic something like this in each textbox's BeforeUpdate procedure:

If Not IsNull(Me.OtherTextbox) Then
If DCount(blah blah) > 0 Then
Cancel = True
MsgBox blah blah
End If
End If
2) When I use the continuation symbol (_) to wrap code to the next line, it
gives me an 'invalid symbol' error. I'd really like to get beyond this, as
sometimes code
gets long in the tooth
Click to expand...

The continuation symbol is " _", not "_". The space is essential.
3) When I use this code:

If DCount("id", "tScrMedSurgHx","(id = " & lngKey & ") AND "(medhx_number =
" & Ingkey2 & ")")> 0 Then
MsgBox ""Key value " & lngKey & "and" & IngKey2 & " is already in the
End If

I get 'Compile error: Expected: List separator or )' right at the
'(medhx_number..' piece of the code. In fact, no matter how I tweak the
code, it always seems to 'break down' here. Also, just to see if I could get
it to work using just one of the Key fields didn't work either, if that is
also helpful.
Click to expand...

It looks as if the '"' in
AND "(medhx_number
is superfluous.
Thanks again.

John Nurick said:
You need to use one DCount(), with both (or all) the key field values
tested in its Where argument for the same DCount(). Something like this:

If DCount("id", "tScrMedSurgHx", _
"(id = " & lngKey & ") AND "(medhx_number = " _
& Ingkey2 & ")") > 0 Then

in which the Where argument should evaluate to (say)

"(id = 99) AND (medhx_number = 234)"

On Sat, 16 Apr 2005 18:56:01 -0700, Pat Dools

Hi John,

Sorry, forgot to mention in my original post that the key is more than one
field (sometimes 2, sometimes 3 fields). I've tried this:

If DCount("id", "tScrMedSurgHx", "id=" & lngKey) > 0 And_
DCount("tScrMedSurgHx","medhx_number=" & IngKey2) > 0 Then_
MsgBox "Key value " & lngKey & "and" & IngKey2 & " is already in the
table"
End If

I just get the ol' 'Syntax error'.
How do I get the syntax correct for a multi-field key?

Thank you!
:

Hi Pat,

The first thing to do is to set an index on the key so that it's not
possible to enter a duplicate.

Then, to trap the duplicate value before Access attempts to update the
table, it's usually enough just to use DCount(), something like this,
where TTT is the name of the table KKK the name of the key field, and
the key value to be tested is in lngKey

If DCount("KKK", "TTT", "KKK=" & lngKey) > 0 Then
MsgBox "Key value " & lngKey & " is already in the table"
End If

The last argument of DCount() will be a little different if the key is a
text field, or if it consists of more than one field.


On Sat, 16 Apr 2005 05:45:02 -0700, Pat Dools

Hello,

I have a database of clinical patient records that is generally working
well, but does not alert the data entry person if they have entered in a
record w/ a duplicate 'key'. I check to see (in Sub 'Next_Click' below) to
see if certain required fields are filled in, and, if they are, then the
'Next Form' Command Button operates Sub 'OpenNextForm'-- also below, which
opens the next form in the 'series'. The user gets a nice error message if
one of the required fields is not filled in, but if they hit the 'Next Form'
Command Button and have entered in a record with a duplicate 'key', the user
gets no error message and moves on to the next form. Access does not save
the record (as it shouldn't), but I need the data entry person to be alerted
that they have just attempted to enter a record w/ a duplicate 'key'. The
only time they get an error message ab/ the duplicate 'key' is when they
click on the 'x' in the upper-right corner of the window. What can I add to
the existing code below, so the data entry person is alerted if there is a
duplicate 'key'? Thank you!

Private Sub Next_Click()

On Error GoTo Err_Next_Click

If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
Call OpenNextForm(Me.Name)
End If

Exit_Next_Click:
Exit Sub

Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click

End Sub

Where Sub 'OpenNextForm is:

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String

On Error GoTo OpenNextForm_Err

intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , "[id] = Forms.fScrEligCriteria.id"
'DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName

OpenNextForm_Err:
'MsgBox Err.Description
Resume Next

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

John Nurick

Pat,

1) Is this statement
If DCount("id", "tScrMedSurgHx", id = " & lngKey & " And medhx_number = " &
IngKey2 & ") > 0 Then
Click to expand...

copied and pasted from your code, or did you type it into your message?
If it's copied and pasted, your code isn't working the way you expect,
in fact I'm surprised it compiles at all. If you re-typed it, please
copy and paste in future: it's all to easy to make a mistake that
changes the meaning of the code and makes it much harder to help you.

In a previous message I gave you what I think is the correct syntax:

If DCount("id", "tScrMedSurgHx", _
"(id = " & lngKey & ") AND "(medhx_number = " _
& Ingkey2 & ")") > 0 Then

If you compare them you'll see that in your version the quote mark that
starts off the last argument of the DCount() (before 'id =') is missing,
and that the '& "' at the end is also wrong.

2) Your statement above would not compile if you had used

Option Explicit

at the top of the code module? Make sure you include it in every module:
see Help for why. To ensure that it's included by default in future, go
to Tools|Options (in the VB editor) and check "Require Variable
Declaration".

3) When, as here, you're using BeforeUpdate() for data validation, you
should do
Cancel = True
if the validation fails (as per the example in my previous message).
Otherwise there's no point.

4) Another thing: I don't know what values you are putting into the
variables lngKey and IngKey2. In order for the DCount() to do its job,
they need to contain the key values that the user has entered into the
form. Make certain that that's the case.




Hi John,

When I have the following code, Iat least get the error message, 'You can't
go to the specified record', and the duplicate key record is NOT saved in the
underlying table. How do I need to adjust the MsgBox to give the errant
values in both key fields so that the error message is nice and clear? Thank
you so much for helping me get this far. I'm sure you've noticed I'm
curtting my teeth on code syntax still! Thx, Patrick

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("id", "tScrMedSurgHx", id = " & lngKey & " And medhx_number = " &
IngKey2 & ") > 0 Then
MsgBox "Key value " & lngKey & " AND " & IngKey2 & " is already in the Table "
End If

End Sub

John Nurick said:
HI John,

Hopefully, I'm getting closer on this one. A couple of questions:

1) Does this go in the 'BeforeUpdate' property of the Form itself, or in
each control on the form that is associated w/ a Key field?
Click to expand...

You need to put it in the form's BeforeUpdate event procedure in any
case. This means that users don't get warned about duplicate values
until they try to move to the next form, so if you want to warn them
when they exit the controls associated with the key fields you must also
use the controls' BeforeUpdate event procedures.

If you do, you have to cover the situation where the user types a value
for one of the key fields but the other is still blank. So you'd need
logic something like this in each textbox's BeforeUpdate procedure:

If Not IsNull(Me.OtherTextbox) Then
If DCount(blah blah) > 0 Then
Cancel = True
MsgBox blah blah
End If
End If
2) When I use the continuation symbol (_) to wrap code to the next line, it
gives me an 'invalid symbol' error. I'd really like to get beyond this, as
sometimes code
gets long in the tooth
Click to expand...

The continuation symbol is " _", not "_". The space is essential.
3) When I use this code:

If DCount("id", "tScrMedSurgHx","(id = " & lngKey & ") AND "(medhx_number =
" & Ingkey2 & ")")> 0 Then
MsgBox ""Key value " & lngKey & "and" & IngKey2 & " is already in the
table""
End If

I get 'Compile error: Expected: List separator or )' right at the
'(medhx_number..' piece of the code. In fact, no matter how I tweak the
code, it always seems to 'break down' here. Also, just to see if I could get
it to work using just one of the Key fields didn't work either, if that is
also helpful.
Click to expand...

It looks as if the '"' in
AND "(medhx_number
is superfluous.
Thanks again.

:

You need to use one DCount(), with both (or all) the key field values
tested in its Where argument for the same DCount(). Something like this:

If DCount("id", "tScrMedSurgHx", _
"(id = " & lngKey & ") AND "(medhx_number = " _
& Ingkey2 & ")") > 0 Then

in which the Where argument should evaluate to (say)

"(id = 99) AND (medhx_number = 234)"

On Sat, 16 Apr 2005 18:56:01 -0700, Pat Dools

Hi John,

Sorry, forgot to mention in my original post that the key is more than one
field (sometimes 2, sometimes 3 fields). I've tried this:

If DCount("id", "tScrMedSurgHx", "id=" & lngKey) > 0 And_
DCount("tScrMedSurgHx","medhx_number=" & IngKey2) > 0 Then_
MsgBox "Key value " & lngKey & "and" & IngKey2 & " is already in the
table"
End If

I just get the ol' 'Syntax error'.
How do I get the syntax correct for a multi-field key?

Thank you!
:

Hi Pat,

The first thing to do is to set an index on the key so that it's not
possible to enter a duplicate.

Then, to trap the duplicate value before Access attempts to update the
table, it's usually enough just to use DCount(), something like this,
where TTT is the name of the table KKK the name of the key field, and
the key value to be tested is in lngKey

If DCount("KKK", "TTT", "KKK=" & lngKey) > 0 Then
MsgBox "Key value " & lngKey & " is already in the table"
End If

The last argument of DCount() will be a little different if the key is a
text field, or if it consists of more than one field.


On Sat, 16 Apr 2005 05:45:02 -0700, Pat Dools

Hello,

I have a database of clinical patient records that is generally working
well, but does not alert the data entry person if they have entered in a
record w/ a duplicate 'key'. I check to see (in Sub 'Next_Click' below) to
see if certain required fields are filled in, and, if they are, then the
'Next Form' Command Button operates Sub 'OpenNextForm'-- also below, which
opens the next form in the 'series'. The user gets a nice error message if
one of the required fields is not filled in, but if they hit the 'Next Form'
Command Button and have entered in a record with a duplicate 'key', the user
gets no error message and moves on to the next form. Access does not save
the record (as it shouldn't), but I need the data entry person to be alerted
that they have just attempted to enter a record w/ a duplicate 'key'. The
only time they get an error message ab/ the duplicate 'key' is when they
click on the 'x' in the upper-right corner of the window. What can I add to
the existing code below, so the data entry person is alerted if there is a
duplicate 'key'? Thank you!

Private Sub Next_Click()

On Error GoTo Err_Next_Click

If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
Call OpenNextForm(Me.Name)
End If

Exit_Next_Click:
Exit Sub

Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click

End Sub

Where Sub 'OpenNextForm is:

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String

On Error GoTo OpenNextForm_Err

intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , "[id] = Forms.fScrEligCriteria.id"
'DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName

OpenNextForm_Err:
'MsgBox Err.Description
Resume Next

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

No error message when required field left blank? 2
Auto-populating fields on next form in series? 4
'Locking' records entered via a form 1
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Form Navigation 5
Populate fields in Form Header based on header fields in startup f 0
Duplicate record in Form and Subform 0
Problem with code to catch duplicate Primay Key 27

Top