Help With Code for Paired listbox validation pior to Adding New Re

[Guest]

Below is the code I use to add a staff member to the Visit Staff member list.
I need code to be able to check to see if the staff member allready exists in
tblVisitStaff (for that particular visit) and if that pt exist in the list, I
need to have a MsBox that says, "This Staff Member is Already Listed"

This is a paired listbox application.

Private Sub lstStaff_DblClick(Cancel As Integer)
Dim sSQL As String

sSQL = "AddNew tblVisitStaff WHERE fldStaffID = " & Me.lstStaff.Column(0)


Me.Refresh

End Sub

Any help would be greatly appreciated. Thanks, Rob

 

[Guest]

You could try to count the records before adding a new record. I added this
to your code, using dummy names for your fields etc.

Private Sub lstStaff_DblClick(Cancel As Integer)

Dim sSQL As String

if dcount ("[staffID]", "[tblVisitStaff]", "[visitID]=" & visitID) > 0 then
' already exists
else
' does not exist
sSQL = "AddNew tblVisitStaff WHERE fldStaffID = " & Me.lstStaff.Column(0)
endif

Me.Refresh

End Sub

 

[Jeff Boyce]

Another approach would be to add an index (No duplicates) on the
tblVisitStaff on the combination of Visit and Staff. That way, if the user
attempts to add a second copy of a staff member to a Visit, Access refuses
to add it.

Another approach would be to list the "available" staff in the left listbox
(those that are not already assigned to the visit), and the "assigned" staff
in the right listbox (those that ARE already added to tblVisitStaff). You
can determine these with queries, and update after each assignment (and
after each "removal").

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/