Solved

Data Validation in Access 2007

Posted on 2011-02-28
12
239 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

789 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