Alert to duplicate entry (with combination of 2 fields) using DLookup

[Hilary Ostrov]

I have a subform, sfrmCaregiverBillings on which user enters a
CaregiverAssignment, DateOfService and HoursOfService

I would like to be able to alert the user when the combination of
CaregiverAssignmentID and DateOfService already exists (duplicate may
or may not be a valid entry, so cannot set to AllowDuplicates=No.)

Seems to me that I should be able to do this using DLookup, but my
attempts thus far have not been successful.

I have tried various samples that I've found here, but they don't seem
to work for me - either I get no alert (when I would expect to), or I
get an error. I suspect the problem may derive from my date format
(which is dd-mmm-yy) - or maybe it's because this is a subform - but
I'm not sure.

If someone could point me in the direction of the appropriate code, I
would appreciate it.

Thanks,
Hilary
hro

 

[DStegon via AccessMonster.com]

Problem with DLookup is that unless you are sure of the exact fields and
WHERE and it is formatted correctly, then it errors without telling you "why".
Many times it is easier to open up a recordset of the table with the
conditions (WHERE clause of the SELECT statement). Then if the recordset is .
EOF then you know nothing matches. So, you would test

rst.open "SELECT * FROM tbl_Caregiver WHERE Assignemnt=" &
CaregiverAssignment & " AND DateOfService=" & Format("mmddyyyy",
DateOfService) & " and HoursOfService=" & HoursOfService, currentproject.
connection,.........

you will have to fill in the various values with the form control values and
make the format date value equal or make them both DATEVALUE(exp) thus
comparing the "date". Not sure what the HoursOfService means (number of
hours or the time of day) so you will have to handle that.

then test the recordeset to see if any records exist...

If NOT rst.EOF then
msgbox "Yo.... dude... this already exists!"
else
msgbox "You can do this... there is no matching sched"
end if

 

[Hilary Ostrov]

On Mon, 30 Mar 2009 23:15:17 GMT, in <93e0796f51d25@uwe>, "DStegon via

Thanks, but I'm having trouble translating your instructions into
something that will compile

Here's the SQL of a query (that works!) .. how could I best translate
this into something I could use in combination with your suggestion:

SELECT tblCaregiverBilling.fk_AssignmentID,
tblCaregiverBilling.DateOfService, tblCaregiverBilling.InvoiceNo
FROM tblCaregiverBilling
WHERE
(((tblCaregiverBilling.fk_AssignmentID)=[Forms]![frmBillings]![txtAssignmentID])
AND
((tblCaregiverBilling.DateOfService)=[Forms]![frmBillings]![sfrmCaregiverBilling]![txtDateOfService]));

Haven't done a lot with recordsets (actually, nothing in my coding
career so far ), so I'm wondering if I would also need some Dim
statements as well (and if so what should they be?)

Thanks again for your help.

Hilary

Problem with DLookup is that unless you are sure of the exact fields and
WHERE and it is formatted correctly, then it errors without telling you "why".
Many times it is easier to open up a recordset of the table with the
conditions (WHERE clause of the SELECT statement). Then if the recordset is .
EOF then you know nothing matches. So, you would test

rst.open "SELECT * FROM tbl_Caregiver WHERE Assignemnt=" &
CaregiverAssignment & " AND DateOfService=" & Format("mmddyyyy",
DateOfService) & " and HoursOfService=" & HoursOfService, currentproject.
connection,.........

you will have to fill in the various values with the form control values and
make the format date value equal or make them both DATEVALUE(exp) thus
comparing the "date". Not sure what the HoursOfService means (number of
hours or the time of day) so you will have to handle that.

then test the recordeset to see if any records exist...

If NOT rst.EOF then
msgbox "Yo.... dude... this already exists!"
else
msgbox "You can do this... there is no matching sched"
end if

[...]


hro

 

[DStegon via AccessMonster.com]

Sorry!! Yes, you will need to set object

Dim rst As New ADODB.Recordset

With rst
.Open "YOUR_QUERY_HERE", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
IF NOT .EOF Then

Else

End If

.Close
End With

Also... remember that if the code is part of the form that the controls are
on it is much easier to use "Me."

(if these are controls on your form, plus the VB helps with autofill... Pull
up the VB coding window (module for the form) and type Me. and you will get a
dropdown list of all the functions and controls on the form. SO much easier
than typing the "!" crap.

Example - Me.txtAssignmentID
or me.subformname.txtDateOfService

Remember that your SQL will have to have the values of those two fields and
that is done by

WHERE
(((tblCaregiverBilling.fk_AssignmentID)=" & Me.txtAssignmentID & ")
AND
((tblCaregiverBilling.DateOfService)=#" & Me.sfrmCaregiverBilling.txtDateOfService & "#));

The "#" is needed if you are comparing a DATE value.

Oh... and if you are tyring to access a control on another form it has always
seems much easier to me to use the "Form_NAME_OF_THE_FORM.control" instead of
Forms!NameOfForm!Control again because VB will give you Intellisense to help
you fill in the rest... makes sure the spelling is correct on the controls
and functions you are trying to access.

Hope this helps.

Thanks, but I'm having trouble translating your instructions into
something that will compile

Here's the SQL of a query (that works!) .. how could I best translate
this into something I could use in combination with your suggestion:

SELECT tblCaregiverBilling.fk_AssignmentID,
tblCaregiverBilling.DateOfService, tblCaregiverBilling.InvoiceNo
FROM tblCaregiverBilling
WHERE
(((tblCaregiverBilling.fk_AssignmentID)=[Forms]![frmBillings]![txtAssignmentID])
AND
((tblCaregiverBilling.DateOfService)=[Forms]![frmBillings]![sfrmCaregiverBilling]![txtDateOfService]));

Haven't done a lot with recordsets (actually, nothing in my coding
career so far ), so I'm wondering if I would also need some Dim
statements as well (and if so what should they be?)

Thanks again for your help.

Hilary

Problem with DLookup is that unless you are sure of the exact fields and
WHERE and it is formatted correctly, then it errors without telling you "why".

[quoted text clipped - 26 lines][...]

hro

 

[Hilary Ostrov]

On Tue, 31 Mar 2009 06:22:17 GMT, in <93e433c80c572@uwe>, "DStegon via

Thank you, so much! Took a little fiddling, but it now works like a
charm ... and I have another new concept to work with from now on!

Sorry!! Yes, you will need to set object

Dim rst As New ADODB.Recordset

With rst
.Open "YOUR_QUERY_HERE", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
IF NOT .EOF Then

Else

End If

.Close
End With

Also... remember that if the code is part of the form that the controls are
on it is much easier to use "Me."

(if these are controls on your form, plus the VB helps with autofill... Pull
up the VB coding window (module for the form) and type Me. and you will get a
dropdown list of all the functions and controls on the form. SO much easier
than typing the "!" crap.

Yes, intellisense is a marvellous invention! The SQL I had pasted in
my last post was from a regular query [which does require the "'!'
crap" in the query grid] I had created, (mistakenly) thinking that I
might be able to access it via code.

[...]

Thanks again for your help

hro

 

[DStegon via AccessMonster.com]

;o)

Thank you, so much! Took a little fiddling, but it now works like a
charm ... and I have another new concept to work with from now on!

Thanks again for your help

hro