Solved

Data Validation in Access 2007

Posted on 2011-02-28
12
249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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