Multiple Fields Validation in Access 2010 Form

[pagi]

HI,

I have one table called transaction where in i am storing transaction.

There are different customer for different type of journals subscription.

We have journals subscription start date and end date in same transaction table.

What is need is while in entry as soon as users select subscription start date it should first check whether customer is already there in the table if not then let him enter data and if customer already exists then it should check with journal name if it no then let him enter data and if journal name exists it should check the subscription end date and compare it with forms subscription start date and start date is greater then the end date of find record then let him enter data or other wise popup message that record already exists.

I know i have to put vba code in subscription start date after update option but i dont know what to put in code to check all this.

Please help me out in this.

Thanks

Pagi

[NeoPa]

What if, when the Start Date has been entered, the other information that you want to check hasn't been entered yet?

What if any of the dependent information changes after the check has been done?

It doesn't seem that you've given your specification enough thought yet. It is not a good idea to look for an answer until at least the question is properly understood.

[pagi]

Hi,

I have put following code in subscription date which will check the previous filed entered but i am getting an error as follow.

run time error 3075 Syntax Error in string query expression ['Journal_name]='Candid'.

please find below my code

Private Sub txtSubscriptionStartDate_AfterUpdate()
  If DCount("cust_id", "Transaction", "cust_id= " & Me.cust_id) > 0 And DCount("[Journal_name]", "Transaction", "[Journal_name]='" & Me![Journal_name] & "") > 0 Then
    MsgBox "Name Is Already In Database!"
    Cancel = True
    Forms!TransactionDetails!txtSubscriptionCustomerName.SetFocus
End If
End Sub

Journal_name filed is a text filed which is linked to journal master table where all the journal name are there.

[zmbd]

The error is telling you that there is something wrong with the string that you are using in the dcount function.

You're missing the closing quote on the string... see my correction below.

You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.
Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.
So to use your code:

Private Sub txtSubscriptionStartDate_AfterUpdate()
DIM strSQLCID as string
DIM strSQLJN as string
Dim lngCountCustID As Long
Dim lngCountJournalName Long
'
strSQLCID = "cust_id= " & Me.cust_id) > 0
lngCountCustID = DCount("cust_id", "Transaction", strSQLCID)
'> debug.print strSQLCid
'
strSQLJN = "[Journal_name]='" & Me![Journal_name] & "'") > 0
'> debug.print strSQLJN
'
lngCountJournalName = DCount("[Journal_name]", "Transaction",strSQLJN ) 
'
'  If lngCountCustID > 0 And lngCountJournalName > 0 Then
    MsgBox "Name Is Already In Database!"
    Cancel = True
    Forms!TransactionDetails!txtSubscriptionCustomerName.SetFocus
   End If
End Sub

Now I used strSQLCID and strSQLJN here for clarity; however, I would normally just recycle a single string varible.
Note the '> Debug.Print.... lines. Uncomment and remove the ">" when the code is ran, you can press <Ctrl><G> and see how these strings are resolving.

I also pull the dcount() out of the IF..Then structure, once again, this is to make troubleshooting easier as now you can debug.print the value if needed to see what is happening within the count.

I know that many will complain about the extra code and processor times etc... we're no longe in the CBM-Vic20 days where 5K-RAM and a ultra-slow processor was the norm. These extra lines are hardly a blip in the processor and it is (IMHO) better to write code that is well commented and easy to troubleshoot that to compound things.
(^_^)