Input Data Validation vai VBA

[scuba1392]

I have 2 tables - one is the customer master which contains customer # (the
unique key) and the customer' name and address - the second table collects
customer input data. Via an input form, users will input the customer
number (that is bar coded and the input comes from a scaner) and I want to
ensure it is a valid number (it exists on the customer master). What coding
should I use in the "after update" event on the data entry form? I have
tried linking the 2 tables as a 1 to many with referential integrity and if a
customer number is input that is NOT on the master I get ErrorCode 3201 which
I have trapped in the FORM_ERROR event; however, I can't make the MS Access
pop-up window stop appearing after my pop-up. Any suggestions ??? Should
this be posted in "Programming" or here as I'm trying to use VBA to handle
the task ?

 

[Software-Matters via AccessMonster.com]

You need to check the master table so could do something like this:

Private Sub DataField_AfterUpdate()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(CustomerMasterTable)
rs.FindFirst "CustomerNumber = " & Me!DataField
If rs.NoMatch Then
msgbox("No Match") ...or whatever code you need
Else
MsgBox ("Match")...or whatever code you need
End If
rs.Close

End Sub

Regards
JD

I have 2 tables - one is the customer master which contains customer # (the
unique key) and the customer' name and address - the second table collects
customer input data. Via an input form, users will input the customer
number (that is bar coded and the input comes from a scaner) and I want to
ensure it is a valid number (it exists on the customer master). What coding
should I use in the "after update" event on the data entry form? I have
tried linking the 2 tables as a 1 to many with referential integrity and if a
customer number is input that is NOT on the master I get ErrorCode 3201 which
I have trapped in the FORM_ERROR event; however, I can't make the MS Access
pop-up window stop appearing after my pop-up. Any suggestions ??? Should
this be posted in "Programming" or here as I'm trying to use VBA to handle
the task ?

--
<a href="
http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>

 

[scuba1392]

JD, thanks for your response. I entered the code as you described -
changing items as necessary to fit my tables; however, it would not execute.
I am using Access 2002 (10.4302.4219)SP-2. Could your code be for a
different version and maybe thats the problem ? What mods might I need to
make for my version of Access?
Thanks Ray

 

[Software-Matters via AccessMonster.com]

When you say it does not execute - do you get an error? What is the error and
which line of code does it error on?

Can you post the code with the error line highlighted and error text.

Regards
JD

--
<a href="
http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>

 

[Software-Matters via AccessMonster.com]

When you say it does not execute - do you get an error? What is the error and
which line of code does it error on?

Can you post the code with the error line highlighted and error text.

Regards
JD

--
<a href="
http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>

 

[scuba1392]

The error is a pop-up window with a title "Microsoft Visual Basic" the
contents of the window say "Compile error: Method or data member not found
- OK or HELP buttons" The line of code that is identified is the one which
says rs.FindFirst ......
Does this help you ??

 

[scuba1392]

JD - a 2nd test using just the code listed below produced a run-time error
3078 (I know the table name is correct)
Here is the code
Private Sub SKU_AfterUpdate()

Debug.Print "Entered SKU AFTER_UPDATE event"
Debug.Print "Me.SKU value = " & Me!SKU

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(tblPartMaster)
'rs.FindFirst "SKU = " & Me!SKU
'If rs.NoMatch Then
' MsgBox "No Match"
'Else
' MsgBox "SKUs Matched"
'End If

End Sub
The yellow arrow and highlight are on the [SET rs......] line of code
Any thoughts?

 

[Software-Matters via AccessMonster.com]

Sorry, the table should be in quotes ...OpenRecordset("tblPartMaster")

JD - a 2nd test using just the code listed below produced a run-time error
3078 (I know the table name is correct)
Here is the code
Private Sub SKU_AfterUpdate()

Debug.Print "Entered SKU AFTER_UPDATE event"
Debug.Print "Me.SKU value = " & Me!SKU

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(tblPartMaster)
'rs.FindFirst "SKU = " & Me!SKU
'If rs.NoMatch Then
' MsgBox "No Match"
'Else
' MsgBox "SKUs Matched"
'End If

End Sub
The yellow arrow and highlight are on the [SET rs......] line of code
Any thoughts?

When you say it does not execute - do you get an error? What is the error and
which line of code does it error on?

[quoted text clipped - 3 lines]

Regards
JD

--
<a href="
http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>

 

[scuba1392]

So I made the change and put the table name in quotes and ran the procedure
again - this time I received a "Run Time Error '13': Type Mismatch and
the yellow highlight is still on the (Set rs.........) line.

Sorry, the table should be in quotes ...OpenRecordset("tblPartMaster")

JD - a 2nd test using just the code listed below produced a run-time error
3078 (I know the table name is correct)
Here is the code
Private Sub SKU_AfterUpdate()

Debug.Print "Entered SKU AFTER_UPDATE event"
Debug.Print "Me.SKU value = " & Me!SKU

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(tblPartMaster)
'rs.FindFirst "SKU = " & Me!SKU
'If rs.NoMatch Then
' MsgBox "No Match"
'Else
' MsgBox "SKUs Matched"
'End If

End Sub
The yellow arrow and highlight are on the [SET rs......] line of code
Any thoughts?

When you say it does not execute - do you get an error? What is the error and
which line of code does it error on?

[quoted text clipped - 3 lines]

Regards
JD

--
<a href="
http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>

 

[Software-Matters via AccessMonster.com]

Is me!SKU a text or number value? Try wrapping it in quotes as follows:

rs.FindFirst "SKU = '" & Me!SKU & "'"

the yellow highlight is still on the (Set rs.........) line.

--
<a href="
http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>