OK, I know you have a table named "SCAN".
What are the names of the *fields* for:
patient attendance
time of attendance
date of attendance
a number
I know you have a form with at least 2 controls. What are the names of the
*controls*?
Do you have a text box named "date"?
Again, "Date" is a very bad name for a field of control. It is not very
descriptive.... date of What?? So you could have a *field* named date, a
control named *date* and there is a built in function named *date*. How
confusing...
In your DLOOKUP, you have "
/snip/ "date= " & Me.Date & " /snip/
Is Me.date the name of a control on the form? Or are you trying to get
today's date?
I made a table (SCAN) and entered some test data. I changed the field names
to differentiate the control names from the field names (and so I wouldn't be
as confused).
I made a form with three controls, a text box for a date, a text box for
idsurname and a button.
I tweeked the functions and got them to run without errors.
Your function: (watch for line wrap)
'---------------------------------------
Public Function CheckDuplicates()
Dim varDoseSupplied As Variant
If IsNull(Me.tbIDsurname) Or IsNull(Me.[tbAttendDate]) Then
CheckDuplicates = False
Else
varDoseSupplied = DLookup("attendTime", "Scan", "[attenddate]= #" &
Me.[tbAttendDate] & "# And IDSurname = " & Me.tbIDsurname)
End If
If Not IsNull(varDoseSupplied) Then
MsgBox "This dose has already been dispensed today at " &
Format(varDoseSupplied, "hh,mm,ss"), vbOKOnly
CheckDuplicates = True
Else
CheckDuplicates = False
End If
End Function
'------------------------------------
Mine: (watch for line wrap)
'------------------------------------
Public Function CheckDuplicates()
Dim varDoseSupplied As Variant
' if date and IDsurname entered, lookup dose time
If Not IsNull(Me.tbIDsurname) Or Not IsNull(Me.[tbAttendDate]) Then
varDoseSupplied = DLookup("attendTime", "Scan", "[attenddate]= #" &
Me.[tbAttendDate] & "# And IDSurname = " & Me.tbIDsurname)
End If
CheckDuplicates = Not IsNull(varDoseSupplied)
' show message
If CheckDuplicates Then
MsgBox "This dose has already been dispensed today at " &
Format(varDoseSupplied, "hh,mm,ss"), vbOKOnly
End If
End Function
'------------------------------------
Another way would be to open a recordset based on the query for the subform
and check if the record count is GT 0. If the record count is GT 0 then
display a message that record(s) were found and the time of attendance.
The code would look something like:
(watch for line wrap)
'---------------------------
Public Sub CheckDuplicates()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim varDoseSupplied As Variant
strSQL = "Select attendTime FROM SCAN"
strSQL = strSQL & " WHERE attendDate = #" & Me.[tbAttendDate] & "#"
strSQL = strSQL & " And SCAN.idsurname = " & Me.[tbIDsurname]
strSQL = strSQL & " ORDER BY SCAN.attendTime DESC , SCAN.attendDate DESC;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.EOF And Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
varDoseSupplied = rs!attendTime
End If
If Not IsNull(varDoseSupplied) Then
MsgBox "This dose has already been dispensed today at " &
Format(varDoseSupplied, "hh,mm,ss"), vbOKOnly
End If
rs.Close
Set rs = Nothing
End Sub
'---------------------------
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Thanks steve
I've tried the changes but still generating errors. Maybe i need to come at
this from a different angle.
I run a query of the scan table selecting records where date = date().
The results are then displayed on a subform/report.
Do you think i can look up this and compare IDSurname only, thus avoiding
the date issues?
:
PMFJI,
The syntax for the first IF() statement is wrong. You can't mix the single
line version of the IF() statement with the block form.
And like Arvin said, "Date" and 'Time " are reserved words in Access. For a
list of reserved words, see:
http://allenbrowne.com/AppIssueBadWord.html
I modified your code (for the first IF() statement):
(watch for line wrap!!!)
'------- beg code -------------------
Public Function CheckDuplicates()
Dim varDoseSupplied As Variant
If IsNull(Me.IDSurname) Or IsNull(Me.[Date]) Then
CheckDuplicates = False
Else
varDoseSupplied = DLookup("Time", "Scan", "[date]= " & Me.[Date] & ""
And IDSurname = " & me.idsurname")
End If
If Not IsNull(varDoseSupplied) Then
MsgBox "This dose has already been dispensed today at " &
Format(varDoseSupplied, "hh,mm,ss"), vbOKOnly
CheckDuplicates = True
Else
CheckDuplicates = False
End If
End Function
'------- beg code -------------------
