Prevent Duplicates from Multiple Drop Downs in Access 2013

[CynthiaDel]

Hello all. This is my first time reaching out on forums. I typically find my answers on here but this time for this specific question I can't seem to find a similar post so I'm hoping for some feedback.

I'm in Access 2013 and I built a form with multiple drop downs. Here is what I have in the same order of which the user must choose from......

Document "Field Observation", "Monthly Inspection", "Safety Roster" (The user must first choose the document from this drop down he is recording)

Supervisor "First & Last Name" (The user then chooses the first & last name of the specific Supervisor name he is recording the document for from this drop down)

Manager - This field automatically populates based on Supervisor.
AOR - This field automatically populates based on Supervisor.
Org Unit - This field automatically populates based on Supervisor.

Month "January", "February", etc. (The user then chooses the Month of which the document will need to be applied to)

I would like the form to trigger an error with "This document is already recorded for this month" when the user chooses "Monthly Inspection" OR "Safety Roster" for a specific "Supervisor" for a specific "Month" that is already recorded in "TBL_DataTracker". The TBL_DataTracker is where all of my records are stored.

Thank you in advance for your help!

 

[bob fitz]

I would suggest putting some code in the forms Before Update event which would use the DCount() function with the required criteria to return the number of existing records. If it returns a value greater than 0 then there must already be a record and then you could cancel the update and give a message to the user.

 

[CynthiaDel]

Thank you. I'm not too familar with coding. I know very little. Can you post an example and I can replace the field names and table names with my own references?

 

[bob fitz]

To help with the specific coding required, can you show us the the contents of the Row Source property for each of the 3 combo boxes.

 

[bob fitz]

Can you also tell us the names of the 3 combo box controls on your form.

 

[CynthiaDel]

1st Combo Box = "Document"
Row Source = "TBL_Documents"

2nd Combo Box = "Supervisor"
Row Source = "SELECT TBL_CrewMapping.Supervisor, TBL_CrewMapping.Manager, TBL_CrewMapping.AOR, TBL_CrewMapping.[Org Unit] FROM TBL_CrewMapping ORDER BY TBL_CrewMapping.Supervisor;"

3rd Combo Box = "Month"
Row Source = "TBL_Months"

 

[RCostolo2]

I'll admit up front that i'm not as good as many others on these forums, but if I were you this is how I would tackle the problem. (If anyone has better advice, by all means let me know!)
First I would create a query based off your table that gets just Document, Supervisor, and Month from TBL_DataTracker... only because you'll doing this validation will require you to search through all your data - which *COULD* take a long time depending on how much data you have. This query I've named "qSEL_Recorded_Documents" for the sake of being obvious.

I've also changed the names of the combo boxes within my code to cboDocument/cboSupervisor/cboMonth as well.

Select your cboMonth combo box and under properties go to the "Event" tab, select the [...] button on "After Update", and then select "Code Builder"

In here copy and paste:

Private Sub cboMonth_Change()

If ValidateDocument(Me.cboDocument.Value, Me.cboSupervisor.Value, Me.cboMonth.Value) = False Then
    MsgBox "This document is already recorded for this month.", vbOKOnly
    Exit Sub
End If

End Sub

The above code will pass your combo box selections to a function that will filter your query down to everything matching cboSupervisor and cboDocument, and then check to see if that month has been logged for said Supervisor and Document. If the document has been recorded the function will = false and you will get your msgbox, and if it's true it'll just allow you to continue using your form as you already do.

This next code is the Function doing the validating. You can copy and paste this code right underneath the previous code.

Private Function ValidateDocument(Document, Supervisor, Month As String) As Boolean
Dim db As DAO.Database
Dim SourceQuery As DAO.Recordset
Dim myFilter As String
Set db = CurrentDb()
Set SourceQuery = db.OpenRecordset("qSEL_Recorded_Documents")
        
Filter = "([Supervisor] = " & Supervisor & ") AND ([Document] = " & Document & ")"
        
        DoCmd.SetFilter , myFilter, SourceQuery
        
Do Until SourceQuery.EOF
    If Month = SourceQuery("Month") Then
        ValidateDocument = False
        Exit Function
    End If
SourceQuery.MoveNext
Loop
ValidateDocument = True
End Function

I hope I explained this well enough, and like I said... that would be MY solution to this problem right off the top of my head. I'd be interested to see what some others would try. If you do try this code and it doesn't work let me know what errors you are getting.

 

[CynthiaDel]

WOW thank you for going through all this work. Much appreciated, unfortunately this code didn't work for me. I did exactly what you told me and I went back to the form to see if the error would generate by entering a duplicate and it just took the entry like it was a new one.

 

[bob fitz]

1st Combo Box = "Document"
Row Source = "TBL_Documents"

2nd Combo Box = "Supervisor"
Row Source = "SELECT TBL_CrewMapping.Supervisor, TBL_CrewMapping.Manager, TBL_CrewMapping.AOR, TBL_CrewMapping.[Org Unit] FROM TBL_CrewMapping ORDER BY TBL_CrewMapping.Supervisor;"

3rd Combo Box = "Month"
Row Source = "TBL_Months"

What are names of the Primary Fields for each of the three tables.

 

[CynthiaDel]

What are names of the Primary Fields for each of the three tables.

TBL_Documents
Field Name: "Document"

TBL_CrewMapping
Field Name: "Supervisor"

TBL_Months
Field Name: "Month"

 

[RCostolo2]

No problem, I'm sorry it didn't work for you. I was sort of pondering this issue today because I really only use VBA to solve my problems, so I was trying to think of a non-vba way to get what you need.

What if you solved this in such a way that instead of throwing an error when a user creates a duplicate.... you eliminate their ability to select a duplicate month from the drop down?

For instance what if you created a query from TBL_DataTracker with Supervisor, Document, and Month so that you had a list of all the data that you do not want duplicated. (qSEL_Recorded_Documents)

You would also need a table that had a list of all the months of the year. I assumed this is your TBL_Months - so that's what I went with.

Then you create a second query that would generate a list of every month NOT recorded for the Supervisor and Document specified in your combo boxes. The SQL would look something like:

SELECT [TBL_Months].Month
FROM qSEL_Recorded_Documents, [TBL_Months]
WHERE ((([TBL_Months].Month)<>[qSEL_Recorded_Documents].[Month]) AND (([qSEL_Recorded_Documents].[Supervisor])=[Forms]![frm_Test]![cboSupervisor]) AND (([qSEL_Recorded_Documents].[Document])=[Forms]![frm_Test]![cboDocument]));

This might not solve your EXACT problem - but it might get us on track to solving it without VBA.

For sake of ease - here is the breakdown of what I "used" to come up with this solution.

TBL_Months = Holds Jan-Dec
qSEL_Recorded_Documents = Query to get all records already logged.
frm_Test = the form you have your combo boxes on
cboSupervisor = Supervisor combo box
cboDocument = Document Combo box

The SQL code you should be able to copy and paste right into the Record Source of your cboMonths combo box.

All in all - what this SHOULD do is list only the months in which no record was recorded for the selected Supervisor and Document. That way the user will never even have a chance to get an error in the first place.

Let me know how it goes. I'm sorry if I got confusing... i'm trying to break it down as much as possible to avoid any errors haha.

Good luck!

 

[CynthiaDel]

No problem, I'm sorry it didn't work for you. I was sort of pondering this issue today because I really only use VBA to solve my problems, so I was trying to think of a non-vba way to get what you need.

What if you solved this in such a way that instead of throwing an error when a user creates a duplicate.... you eliminate their ability to select a duplicate month from the drop down?

For instance what if you created a query from TBL_DataTracker with Supervisor, Document, and Month so that you had a list of all the data that you do not want duplicated. (qSEL_Recorded_Documents)

You would also need a table that had a list of all the months of the year. I assumed this is your TBL_Months - so that's what I went with.

Then you create a second query that would generate a list of every month NOT recorded for the Supervisor and Document specified in your combo boxes. The SQL would look something like:

SELECT [TBL_Months].Month
FROM qSEL_Recorded_Documents, [TBL_Months]
WHERE ((([TBL_Months].Month)<>[qSEL_Recorded_Documents].[Month]) AND (([qSEL_Recorded_Documents].[Supervisor])=[Forms]![frm_Test]![cboSupervisor]) AND (([qSEL_Recorded_Documents].[Document])=[Forms]![frm_Test]![cboDocument]));

This might not solve your EXACT problem - but it might get us on track to solving it without VBA.

For sake of ease - here is the breakdown of what I "used" to come up with this solution.

TBL_Months = Holds Jan-Dec
qSEL_Recorded_Documents = Query to get all records already logged.
frm_Test = the form you have your combo boxes on
cboSupervisor = Supervisor combo box
cboDocument = Document Combo box

The SQL code you should be able to copy and paste right into the Record Source of your cboMonths combo box.

All in all - what this SHOULD do is list only the months in which no record was recorded for the selected Supervisor and Document. That way the user will never even have a chance to get an error in the first place.

Let me know how it goes. I'm sorry if I got confusing... i'm trying to break it down as much as possible to avoid any errors haha.

Good luck!

The only problem I see with this is that I dont want duplicates for just 2 specific documents. I have a total of 3 documents and 2 out of the 3 are the ones I don't need duplicates for. The other document, the Supervisor conducts meetings with as many employees so duplicates would suffice.

 

[bob fitz]

Perhaps something like this in the forms Before Update event:

If DCount("IDfieldOfTableOrQuery","NameOfTableOrQuery","Document ='NameOfComboBox'" AND "Supervisor = 'NameOfComboBox'" AND "Month = 'NameOfComboBox'" >0 Then
   Cancel = True
   Msgbox "Duplicate"
End If

This is untested "air" code. The syntax may not be spot on and you would need to substitute IDfieldOfTableOrQuery and NameOfTableOrQuery with the appropriate names of your objects.

 

[CynthiaDel]

Perhaps something like this in the forms Before Update event:

If DCount("IDfieldOfTableOrQuery","NameOfTableOrQuery","Document ='NameOfComboBox'" AND "Supervisor = 'NameOfComboBox'" AND "Month = 'NameOfComboBox'" >0 Then
   Cancel = True
   Msgbox "Duplicate"
End If

This is untested "air" code. The syntax may not be spot on and you would need to substitute IDfieldOfTableOrQuery and NameOfTableOrQuery with the appropriate names of your objects.

Okay I can try this. Does this still work if I only want to identify duplicates for 2 specific documents? I only want the form to trigger when Monthly Inspection OR Safety Roster is chosen for X Supervisor and the X Month. If there is already a line item for "Safety Roster" for "Tom Jones" for "July" then the form will say Duplicate or whatever message. Essentially the form will be looking up "TBL_DataTracker" to find these duplicates.

 

[CynthiaDel]

Bob Fitz.....Thanks for the code! I found a similar code to yours below. This one works with the exception of I didn't put the Supervisor field but it still gave me a Duplicate message when I attempted to enter a new entry after the duplicate entry so this works but as I had mentioned above, I ONLY need it to give me a message of Duplicate if I choose "Monthly Inspection" or "Safety Roster" from the Document field combobox....can you work from that?
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "TBL_DataTracker", "Document = '" & Me.Document & "' And Month = '" & Me.Month & "'") > 0 Then
MsgBox "Duplicate"
Cancel = True
End If
End Sub

 

[bob fitz]

Just off to bed but will take a look at it tomorrow evening if I'm home early enough. If not I'll take a look asp.

 

[bob fitz]

Not sure I understand your requirement correctly but please try this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Document = "Monthly Inspection" OR Me.Document = "Safety Roster" Then
  If DCount("*", "TBL_DataTracker", "Document = '" & Me.Document & "' And Month = '" & Me.Month & "'") > 0 Then
    MsgBox "Duplicate"
    Cancel = True
  End If
End If
End Sub

Edit
PS
Sorry for the delay in replying

 

[CynthiaDel]

not sure i understand your requirement correctly but please try this:

private sub form_beforeupdate(cancel as integer)
if me.document = "monthly inspection" or me.document = "safety roster" then
  if dcount("*", "tbl_datatracker", "document = '" & me.document & "' and month = '" & me.month & "'") > 0 then
    msgbox "duplicate"
    cancel = true
  end if
end if
end sub

edit
ps
sorry for the delay in replying

you are the best ever!!!!!! It works!!!!!!! I'm jumping up and down excited!! Thank you so very much!! You are the man of access!! You are saving me a tremendous amount of manual work and you have helped me optimize my workload!!

 

[bob fitz]

you are the best ever!!!!!! It works!!!!!!! I'm jumping up and down excited!! Thank you so very much!! You are the man of access!! You are saving me a tremendous amount of manual work and you have helped me optimize my workload!!

Thank you for your kind words. Always glad to help if we can .

PS
Perhaps you could mark this thread as SOLVED

 

[spikepl]

Month is a reserved word. You can get away with most reserved words by using [] around them in SQL but it is not recommended. Google access reserved words.