12-07-12, 12:46 #1Registered User
- Join Date
- Dec 2012
Unanswered: Data entry validation rule in Form
I need help with a data entry validation rule in Access Forms.
I'm using Access 2010 on windows
What I have:
Table named: clients table
Form named: clients data form
What I'm trying to accomplish:
The form has the client information. The client sends me a document. I review the document. If the document has the correct info needed, then I have a field named "Incomplete" that is a Yes/No field in the clients table.
If the field is clicked, then I want the next date field to appear and then I can fill in the date that it was sent back to them.
If the field "incomplete" field is not checked, then I don't want that date field to appear nor do I want to fill it out.
I have searched many threads on the forum and I can't seem to figure it out still.
The best thread/most similar thread I found was this one:
After reading around, I think could will be the best option but I'm not that good with access and havne't been able to solve it.
I have attached a screen shot to give you an idea of what I mean.
Any help is greatly appreciated!
12-07-12, 18:12 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
This is simple enough to do, but you need to tell us what you want to happen later, when the information is completely in hand; do you want the date field emptied and the Control to disappear, or do you want it to remain, for historical/tracking reasons?
If you want the Control to remain, once the SentBack Field has a date entered, you can use something like this:
Private Sub Incomplete_AfterUpdate() If Me.Incomplete = -1 Then Me.SentBack.Visible = True Else Me.SentBack.Value = Null Me.SentBack.Visible = False End If End Sub
Private Sub Form_Current() If Nz(Me.SentBack.Value, "") = "" Then Me.SentBack.Visible = False Me.SentBack.Locked = False Incomplete.Locked = False Else Me.SentBack.Visible = True Me.SentBack.Locked = True Incomplete.Locked = True End If End Sub
If the user mistakenly ticks the Incomplete Checkbox, and enters a date, they can untick it, and all of that will be reversed, until the Record is Saved! Once the Checkbox is ticked, a date entered, and the Record is Saved, the date is there to stay and Incomplete cannot be unticked.
If Incomplete and SentBack are not the actual names of your Controls, replace them in the code above with the actual names.
And let us know if your requirement isn't met by this example.
Last edited by Missinglinq; 12-07-12 at 18:49.Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
12-09-12, 20:47 #3Registered User
- Join Date
- Dec 2012
Thanks a ton for the quick reply.
Also, sorry about the confusing in my description. I went back and reread it. You are correct.
If the incomplete box is checked, then I want the "sent back" section to appear. Logic: if it's incomplete or formatted incorrectly, then I'm going to send it back for them to fix and then they will send it back to me.
So if the "incomplete" or "wrong format" boxes are NOT checked, then I don't want the gray box below to appear.
The received back date field is when they send it back after they had corrected the format or sending it back after completing what wasn't complete.
So I tried the code, but I'm having some issues.
I opened the form in design view. Then "Database tools" ribbon. Then I opened "visual basic".
Couple of questions. What is the ".me"? I was confused on that when I saw it in multiple threads.
Additionally, do I type the code that provided in the same area? Or do I use either or?
The "attempted" and "wrong format" field are both set to "True/False" field properties in the actual table. That is the correct format, yes?
I got it to work. However, I have an issue.
When I open the form form, everything is good. The "sent back" is not visible.
Once I click "incomplete", the sent back field shows up.
However, if I try to un-check the incomplete (say it was clicked accidently), i can't un-click it and the sent backfield becomes "12:00:00AM" and I can't change it.
Any idea what to do here?
I also attempted to modify the code to if either or incomplete or wrong formatted is checked, then make the gray box content appear/disappear but I haven't gotten it to work.
I did this by copying by doubling the IF/ELSE statement. Ones for the Incomplete and ones for the wrong formats.
I'm assuming there's a way to use an "OR" instead of using more IF statements? Any help is greatly appreciated! Thanks again
Last edited by AccessN00b; 12-09-12 at 21:33.