Duplicates multiple text boxes and an option

[PlasticMonster]

Hi,

I am trying to run a check that will make the user aware if the request they are adding via the form is likely to be a duplicate. If so I want the option to allow or reject the entry.

Alternatively I have seen solutions that just notify on the form is something might be a duplicate, by highlighting a textbox red for example.

There will be duplicates across these fields but there are many other options included on the form, as I said this is just an indicataion.

The table is called
"MainAc"

The form is called
"SDC Request Form"

The comparison fields are called:
"First_name" - Text
"Surname" - Text
"Change_Number" - Number
"Date_from" - Date
"Date_to" - Date

Ive been trying the Dcount() function, IIF() function but no joy.

I tried to putting the below in the before update event, but it didnt run at all, now Ive put it on the button click acion that duplicates records for me, however it just returns a non duplicate responce regardless.

(Limited to a few of the fields but I cant even get that to work properly).

Dim PreviousRecordID As Long

PreviousRecordID = 0
PreviousRecordID = DLookup("first_name", "MainAc", "first_name<>" & First_Name & _
" AND surname=" & Surname & " AND change_number=" & Change_Number)

If PreviousRecordID <> 0 Then
MsgBox "Duplicate: exists already", , vbCritical
Exit Sub
Else
MsgBox "This is not a duplicate", , vbCritical

End If

This was a copy and modify from an example database but I dont entirely understand what each part does.

Im not asking for anyone to do the work for me but a steer would be gratefully received. Microsoft tutorials seem to leave something to be desired!!

Thankyou if you read this far

 

[pr2-eugin]

PlasticMonster, there is some problem with your code.. You have declared PreviousRecordID as Long, but using DLookUp on a String Field.. All you needed was a DCount.. Also you need to properly concatenate String values in the Criteria.. Look up this link for how to use DLookUp multiple criteria.. I have modified the code as..

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim PreviousRecordID As Long
    PreviousRecordID = DCount("*", "MainAc", "first_name = '" & First_Name & "'" & _
                                " AND surname = '" & Surname & "' AND change_number = " & Change_Number)

    If PreviousRecordID <> 0 Then
        MsgBox "Duplicate: exists already", , vbCritical
        [COLOR=Blue][B]Cancel = True[/B][/COLOR]
    Else
        MsgBox "This is not a duplicate", , vbCritical
    End If
End Sub

If you wish to Warn the user and not allow any change, then you have to Cancel the Update..

 

[PlasticMonster]

Re: Duplicates multiple text boxes and an option: runtime 3075 error

Thanks paul, Ive been trying to get this modified code to work

I was wrong also when I stated that one of the fields was a number, change_number is actually listed as a text field due to the format (CHNG0000000000). I think that this has to do with the ' or " demarking from what I gleam from researching the function.


Private Sub dupcheck()

Dim PreviousRecordID As Long
PreviousRecordID = DCount("*", "MainAc", "first_name = '" & First_Name & "'" & _
" AND surname = '" & Surname & " AND change_number = '" & Change_Number)

If PreviousRecordID <> 0 Then
MsgBox "Duplicate: exists already", , vbCritical
'Cancel = True
Else
MsgBox "This is not a duplicate", , vbCritical
End If

End Sub


This is the current code ive got running from a button click event (for testing) but im getting an error:

Runtime error '3075':

Syntax error (missing operator) in query expression 'first_name='Stuart' AND surname = 'Smith AND change_number = 'CHNG0000000000'.

Im looking at the error and seeing that both stuart and change number have ' demarking them eg 'stuart' but surname doesnt?

 

[pr2-eugin]

The Error shows you that there is a Syntax Error.. Please do not think I am patronizing you, but learning to read error will help you go a long way..

The dialog box presented shows the error, by enclosing the location of the Error inside a Single quote..

So..

Runtime error '3075':

Syntax error (missing operator) in query expression 'first_name='Stuart' AND surname = 'Smith AND change_number = 'CHNG0000000000'.

The highlighted portion, is Generic.. The blue bits concern your error.. So as you can see, that the error shows that it is missing a single quote at the end of the word Smith and the Change_Number being a Text should also be enclosed in single quote.. So your DCount statement should look like..

[COLOR=Green]'I have added a single Quote after Surname
'and wrapped the whole of change_number inside single quotes.
'highlighted in blue.[/COLOR]
DCount("*", "MainAc", "first_name = '" & First_Name & "' AND surname = '" & Surname & "[COLOR=Blue][B]'[/B][/COLOR] AND change_number =[COLOR=Blue][B] '[/B][/COLOR]" & Change_Number & "[COLOR=Blue][B]'[/B][/COLOR]")

 

[PlasticMonster]

Thanks again Paul, Ill work on this now.

Im self taught, the fact that my database has worked this long has amazed me. Im pretty much building extras into it as a means of learning, am not feeling patronised!!

I was convinced it was to do with the demarkation of the quotes but I didnt fathom where to start. My next challenge will be to add in some more criteria, so understanding the proper use in this smaller example is crucial.

I really do appreciate your help on something to you is prob so simple.

 

[pr2-eugin]

I really do appreciate your help on something to you is prob so simple.

When I started using Access, I had ZERO knowledge of VBA, and a very little knowledge of DB.. If you look at the questions I have asked on this Forum, you will laugh your lungs out.. I was terrible..

I learned little by little.. One baby step at a time.. It was not simple when I started.. I had hard time understanding.. But thanks to this Forum and its amazing people, I actually have learnt a great deal..

Lookup this link, it shows how to use multiple criteria on a DLookUp statement, any domain function will use the similar structure.. So they are the same..

 

[PlasticMonster]

You wouldent believe how happy I was when my test button actually worked!! now to make it more complicated and put it in to run before update...baby steps...

I thought I was an excel whizz, then learned VBA existed...got functional on there and thought, of course I can make a database...


I enjoy the challenge but some things are just so specific, this particular problem is a case in point! But each little problem opens a whole set of avenues to research and hopefully I avoid them next time. error handling must move higher on my list, im sure some of my "constructions" are working more through luck than good management!!

Thankyou again, Ill update you when ive mastered the whole issue, without your help I was close to abandoning this feature, ive been struggling with it for weeks.