Unanswered: Double Criteria for a dlookup function
I have a form on which I intend to capture farmer details. The form is attached to table FarmerReg, which is set to allow duplicate FarmerNos because the same farmer can register for more than one season using the same number. On my form, I want MS Access to restrict the same FarmerNo from being entered twice in one season. Below is the code from which I'm able to block duplicate FarmerNos minus considering the season. My problem is I can't figure out how to ensure that only duplicate FarmerNo entries for a particular season are blocked. Would somebody edit this code for me?
[Dim icount As Long
'check to see if this is a duplicate value for a parituclar season before updating
icount = Nz(DLookup("FarmerN", "FarmerReg", "FarmerN=" & Me.FarmerN), 0)
If icount <> 0 Then
' this item has already been entered for this Season
MsgBox "Duplicate Farmer Numbers are not allowed!", vbExclamation
Cancel = True
Last edited by Papa G; 04-02-10 at 04:51.
Reason: corrected end of code tag to include End If
I have 4 tables storing more details for the data in my main table FarmerReg i.e. tbl_FieldOfficerDetails, tbl_AreaDetails, tbl_Products and tbl_Farmers. Below are the fields in my main table FarmerReg.
The ID is my Primary and is an Autonumber field. FOCode is code for Field Officers and it is the one that links this table to the rest of the tables mentioned above Hope this is what you needed?
We register farmers on a yearly basis. Our system entails that both new and old farmers register with us before we can give them inputs for the coming season. Upon first registration, I want the basic farmer details i.e. National ID (NRCNo) being the Primary Key, Farmer FullNames, and stuff to be entered in a separate table (tbl_Farmers). This will ensure these basic details are only entered once but can still be used over several different seasons.
I will then want to enter all the farmers who have registered for the current season i.e. 2010 - 2011 using their unique Farmer Numbers [FarmerN] and these unique numbers are used by the same farmer for as long as they want to grow with us regardless of the change in seasons. With this setup, I can't set [FarmerN] as Primary Key as it would block me from registering the current farmer next season.
I now need to set [FarmerReg] to allow duplicate Farmer numbers while blocking the duplicate values for a particular season only using the dlookup function. I need the dlookup function to check if farmer number has already been entered for, say, season 2010 - 2011.
Sorry, I had to make it long so that you can have a complete understanding of what I'm trying to achieve here.
Below is my revised code. I now get a type mismatch error. It may seem a bother but I never knew anything about dlookup functions upto this morning. Kindly tell me where I went wrong please... Users have to select the season in a combo box so I thought, just writing Me.Season in this expression would make the search for seasons based on the value in combo box, am I wrong?
I still have issues getting this code right. I copied it exactly the way Sinndho put it and even created a new table Seasons for my combo box (Courtesy of Teddy) but when I try to run it, it now tells me "You can't go to the specified record".
For arguments sake, I modified my Season Columns to 'Number datatype' and changed my code as shown below.
sorry, that was just a typing error in my post, I've counter-checked it in my database and it is correctly written without the (') for text fields. I've seen your code working in the demo you have attached, let me see how it goes in my database, I'm quite sure though, that this will resolve my problem. Thanks alot.