Checking duplicating at record level

[wasim_sono]

Dear All

I posted in other forum but still unanswered so I'm trying here. Sorry for any inconvevience.


I want to know that is it possible in VBA to apply check at record level? I explain as that I have an input form which has fields named Consumer, Rec_Date, Amount etc. For avoiding duplicate entry I apply Dlookup function on Consumer field but the requirement is that Rec_Date and amount should also be check for duplicate entry. Means The same Consumer with same Rec_Date and amount should not be entered. Have any one idea about solving the problem? If yes the please write the complete code in VBA for checking.

Thanks a lot in advance.

Wasim

[OBP]

This is fairly easy to achieve, if you construct a query which uses your form's fields named Consumer, Rec_Date, Amount as the Criteria for the Query.
You can test this yourself without VBA first to ensure that it works.
Then it will only take a little VBA to run the query and check it's record count is greater than zero, i.e. it has found a duplicate.
So if you create and checkout the Query I can create you some code.

[wasim_sono]

Thanks OBP

I created query and uses the following criteria for the fileds.

[me].[form].[cona]
[me].[form].[recdate]
[me].[form].[amnt]

Is it right? if yes then please tell me the code and tell on which event it should be apply.

Thanks.

[OBP]

wasim, unfortunately that is not the correct Criteria for the Query Criteria Row.
If you open your Form and enter a cona, recdata and amnt that you know is a duplicate and leaving the form open, switch to the Query and run it, you will get an error where the Query asks for the 3 fields to be entered.
The correct Syntax for the Query Criteria Row is
[forms]![form name]![cona]
[forms]![form name]![recdate]
[forms]![form name]![amnt]
where form name is the name of your open form.
Please test at described above to ensure that it does find your duplicate record and then post on here the name of the Query.

[wasim_sono]

I have created the query namely "duplicate Record" and the SQL view of the query is as follow.

SELECT MASJV.CONA, MASJV.DATE, MASJV.AMNT, MASJV.SER
FROM MASJV
WHERE (((MASJV.CONA)=forms.masjvdataentry.cona) And ((MASJV.DATE)=forms.masjvdataentry.date) And ((MASJV.AMNT)=forms.masjvdataentry.amnt) And ((MASJV.SER)=formss.masjvdataentry.ser));

Have I made it as control source of the form or not?

[OBP]

wasim, when you test the query with a known duplicate in the Input Form does it find it?

[wasim_sono]

No

I got empty record. Then what I'm wrong in query?

Wasim

[OBP]

wasim, can you post or email me a zipped copy of your database?
Alternatively have a look at this post on the Tech Guys forum -
http://forums.techguy.org/business-applications/535976-ms-access-queries-i-want.html

In post #7 I have attached a database which uses a search Query to filter data in a similar way to how you want to do it.
The Query is called "QryDeviceSearch1"