Solved

Data Validation in Access 2007

Posted on 2011-02-28
12
210 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now