Solved

Data Validation in Access 2007

Posted on 2011-02-28
12
247 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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