Prevent APPEND when certain fields match in a query (1 Viewer)

[NBRJ]

I have a unbound form to add data into a table. I have an ADD button which then adds in the field data on the form into the relevent table (these form fields are lookups to other tables, so the data that will be appended will be all ID numbers).

I have a query called qRequestDuplicateCheck (which is a combination of two tables), that has just the fields in that I need to prevent duplicates of. I need to search the entries in that query and compare them with the content in the unbound fields in the data entry form.

There is an ADD RECORDS button, so I will put the check in there: IF my form fields don't match any entries in the query, ADD, if they do, exit sub (with a message).

How do I cycle through that query table and compare? Thanks

 

[NBRJ]

Arrgh, sorry, most important info, one of the form fields I need to use in that duplicate check is a listbox, lstLocation.

When the user clicks add, if the listbox is NOT selected it will add a record for each of the records in that list. Alternatively if the list IS selected it will add a record for each of the SELECTED records.

So it's not just a simple IF statement to check for matching records. It's a cycle through the list...

Now I'm typing this, I think I have the answer, will test it and come back and hopefully apologise for wasting everyone's time.

 

[Ranman256]

Make a query to test if all the fields exist, Q1
Click the add button,
Test if there are recs,

 if DCount("*","Q1")= 0 then
    Docmd.openquery "qaAddData"
End if

 

[NBRJ]

How does that work if I have a list?

Unbound Form Fields --------- QueryName.Field
cboRequestAYear -------------- qRequestDUPLICATECHECK.FIDAcademicYear
cboFaculty -------------------- qRequestDUPLICATECHECK.FIDFaculty
cboTitle ----------------------- qRequestDUPLICATECHECK.FIDTitle
cboVersion -------------------- qRequestDUPLICATECHECK.FIDVersion
lstLocation -------------------- qRequestDUPLICATECHECK.FIDLocation

Note with the list.

• If List is unselected: I need to check all entries.
• If List has selections, check just the selections.

There are other fields on the form, but not needed to check for duplicates, just on these ones.

Unbound field data to check on form.
Year: 1
Faculty: 3
Title: 10
Version: 50
If SELECTED Location might be: 1, 5, 6.
If NOTHING SELECTED Location will be all , i.e 1,2,3,4,5,6,7,8 ....n

Example for each location selected in list:
EG: 1,3,10,50,1 and then 1,3,10,50,5, and then 1,3,10,50, 6, ... all checked against qRequestDUPLICATECHECK

Example for each unselected location in list:
EG: 1,3,10,50,1 and then 1,3,10,50,2, and then 1,3,10,50, 3, etc.... all checked against qRequestDUPLICATECHECK

The add button does more than a straight add so I need this to just ultimately, check fields against the query, and return 0 = no matches.

I'm assuming that DCount is has not taken into account the list?

The form already ensures all fields (they're all using IDs) have 0 or a value in, so no check for nulls needs to be done.

 

[NBRJ]

Not near the DB at the moment... how does this look?

For unselected list (I already have code to check for that)

Dim intX As Integer
Dim countOfMatchingRecs As Integer
countOfMatchingRecs = 0

For intX = 0 To Me.lstLocation.ListCount - 1
    If DCount("*", "qRequestDUPLICATECHECK", "[FIDAcademicYear] = " & cboRequestAYear & " AND [FIDFaculty] = " & cboFaculty & " AND [FIDTitle] = " & cboTitle & " AND  [FIDVersion] = " & cboVersion & " AND  FIDLocation = " & lstLocation) then
        countOfMatchingRecs = countOfMatchingRecs +1
    End If
Next intX

If countOfMatchingRecs =>1 then
    Exit Sub
End If

For selected list (again, I already have code to check for that)

Dim intX As Integer
Dim countOfMatchingRecs As Integer
countOfMatchingRecs = 0

For intX = 0 To Me.lstLocation.ListCount - 1
    If Me.lstLocation.Selected(intX) = True Then
        If DCount("*", "qRequestDUPLICATECHECK", "[FIDAcademicYear] = " & cboRequestAYear & " AND [FIDFaculty] = " & cboFaculty & " AND [FIDTitle] = " & cboTitle & " AND  [FIDVersion] = " & cboVersion & " AND  FIDLocation = " & lstLocation)
            countOfMatchingRecs = countOfMatchingRecs +1
        End If
    End If
Next intX

If countOfMatchingRecs =>1 then
    Exit Sub
End If

Is that the correct structure for Dcount with multiple values?

 

[NBRJ]

Ok solved it. That worked, except for a tweak to the reference to lstLocation in the DCount.

I had just put lstLocation instead of :

If DCount("*", "qRequestDUPLICATECHECK", 
"[FIDAcademicYear] = " & (txtSelectedRequestAYear) & " 
AND [FIDFaculty] = " & (txtSelectedRequestFaculty) & " 
AND [FIDTitle] = " & (cboTitle) & " 
AND  [FIDVersion] = " & (cboVersion) & " 
AND  FIDLocation = " & [B]lstLocation.Column(0, intX)[/B]) Then

Thanks for the pointer to DCount, Ranman256, not used it so far. Useful.