By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Multiple Fields Validation in Access 2010 Form

P: 2

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.


Jan 5 '14 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,494
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.
Jan 5 '14 #2

P: 2

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSubscriptionStartDate_AfterUpdate()
  2.   If DCount("cust_id", "Transaction", "cust_id= " & Me.cust_id) > 0 And DCount("[Journal_name]", "Transaction", "[Journal_name]='" & Me![Journal_name] & "") > 0 Then
  3.     MsgBox "Name Is Already In Database!"
  4.     Cancel = True
  5.     Forms!TransactionDetails!txtSubscriptionCustomerName.SetFocus
  6. End If
  7. End Sub
Journal_name filed is a text filed which is linked to journal master table where all the journal name are there.
Jan 6 '14 #3

Expert Mod 5K+
P: 5,397
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSubscriptionStartDate_AfterUpdate()
  2. DIM strSQLCID as string
  3. DIM strSQLJN as string
  4. Dim lngCountCustID As Long
  5. Dim lngCountJournalName Long
  6. '
  7. strSQLCID = "cust_id= " & Me.cust_id) > 0
  8. lngCountCustID = DCount("cust_id", "Transaction", strSQLCID)
  9. '> debug.print strSQLCid
  10. '
  11. strSQLJN = "[Journal_name]='" & Me![Journal_name] & "'") > 0
  12. '> debug.print strSQLJN
  13. '
  14. lngCountJournalName = DCount("[Journal_name]", "Transaction",strSQLJN ) 
  15. '
  16. '  If lngCountCustID > 0 And lngCountJournalName > 0 Then
  17.     MsgBox "Name Is Already In Database!"
  18.     Cancel = True
  19.     Forms!TransactionDetails!txtSubscriptionCustomerName.SetFocus
  20.    End If
  21. 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.
Jan 6 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.