Solved

Data Validation in Access 2007

Posted on 2011-02-28
12
231 Views
Last Modified: 2012-05-11
In an access database,user1 enters a value for a field (SF1) in one form and it is saved to tbl1.
In another form, user2 enters SF2 value in another form and it is saved to tbl2 table.
How do I validate, when user2 enters SF2 value in the 2nd form, if it is existing in the tbl1.If it is existing, 2 more fields will be filled in form2 and control goes to the next item, if not it shows a message and stays at the same place.I have not worked in access in years and need to refresh myself.Thanks !
0
Comment
Question by:MPI_IT
  • 6
  • 4
  • 2
12 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34998434
use dcount or dcount to do a lookup in table X for value table Y.

if the result of the dcount is > 0 then item exists, take relevant steps.

Or you could use a query to look for value x in table y, and use the queryas a rowsource for a combo box... then just requery the combo in your code... and check its listcount.

do you need more detail or an example?

your going to need to check x already exists, and use IF/then/else  to test the check, and take appropriate action

0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34998502
dlookup or dcount....sorry.
0
 

Author Comment

by:MPI_IT
ID: 34998579
Yes, please explain in detail. Thanks !
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35000163
Depending on your data, you can do this with relationships as well.

Aggregate functions can start to slow down performance as they search through larger and larger datasets...
But for relatively small databases, they are fine...

As the first Expert, I will allow Sudonim to explain the technique.

;-)

JeffCoachman
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35004826
OK,

code and db attached.
Private Sub SF2_AfterUpdate()
'MsgBox "afterupdate of SF2 - Test to see if value is in SF1"
Dim xStr As String
Dim varX As Variant
xStr = "[SF1] = " & Left("""", 1) & Me.SF2.Value & Left("""", 1)
varX = DLookup("[SF1]", "Tbl1", xStr)
'MsgBox varX
If varX = Me.SF2.Value Then
'found a match
Me.SFx1.Visible = True
Me.SFx2.Visible = True
End If
End Sub

Private Sub Form_Current()
Me.SFx1.Visible = False
Me.SFx2.Visible = False
End Sub

Open in new window

MPI-IT.mdb

JC - tell me more about your relationship hypothesis...how does it work, and how would you use it to trigger the display of the 2 fields controls
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35009204
<OT>
Sudonim, I was referring to the technique of using two key fields in the table (as in the Nothwind sample DB "Order Details" table.)
In that case you cannot have an order that has a duplicate product.
As I stated, "Depending on your data..."
...so I did not really know if it was applicable here, I was just mentioning it.

I just wanted to makes sure I gave you the respect of being able to explain your post.

;-)

Jeff
0
 

Author Comment

by:MPI_IT
ID: 35011992
Thanks Sudonim for your response.
I could not open the file. I get the following error :
Unrecognized database format <filename>. (Error 3343)
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 250 total points
ID: 35017560
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35017581
Thanks boag.  the fields in question are in two different tables.  didn;;t know of a way to do that with relationships... but theres a lot i dont know about access :)
0
 

Author Comment

by:MPI_IT
ID: 35030942
Sudonim,
Thanks for the file. I have to make some more changes to it.
If It there is no match, it should show the message and should not move the control to the next field.It should stay on that until user enters an existing value.
If it exists, field2 and field3 should be filled from the 1st table based on the SF1 code.
0
 

Author Closing Comment

by:MPI_IT
ID: 35138809
I want to close this.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35142993
ok to make your changes.  go into design mode of the form.  click on the sf2 control.  open properties ( right click on the control and select properties....

go to events, click on afterupdate = [event procedure] and then click on the button with the three dots [...]

it will open the vb editor.

If varX = Me.SF2.Value Then
'found a match
Me.SFx1.Visible = True
Me.SFx2.Visible = True
End If

thats the bit you need to change.

you need an  if then else

If varX = Me.SF2.Value Then
'found a match
Me.SFx1.Visible = True
Me.SFx2.Visible = True
' set the values as required from sf1....
else
msgbox "Show the message"
Me.SF2.setfocus
End If
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question