Running a query to check data entry

[Todd]

I have a form where users update equipment status.
Although Drop down boxes are used to enter most of the
status categories, there are still opportunities for data
entry error.

What I would like to do is a check before they update the
record, to ensure that the data entry is correct. I know
how to do basic checks based on the data that they've
entered.

For Example (inserted in BeforeUpdate event)
If (Me.Combo18 = "Returned" Or Me.Combo18 = "Frozen" Or
Me.Combo18 = "Shipped" Or Me.Combo18 = "Filled" Or
Me.Combo18 = "Thawed") And IsNull(Me.Combo23) Then
ReadyToClose = False
userreply = MsgBox("You must enter a LOT Number for the
Status category that you chose", 0, "Error")
If userreply = vbOK Then
Me.Combo23.SetFocus
End If
End If

This works fine.

However, what I don't know how to do is to check there
entry against previous entries to ensure that it is
correct.

So what I'd like to be able to do, is figure out how to
write the code to do this (again I plan to insert this
into the BeforeUpdate event code):

Run a query based on the vessel number that the user has
input to find what the current lot number is in the
vessel, and then compare that to the lot number that they
have just tried to enter. So, how do you run a query from
within the code function?

I'm thinking that the general format is going to look like
this,***'s are where I have a problem:

If (Me.Combo18 = "Returned" Or Me.Combo18 = "Shipped" Or
Me.Combo18 = "Filled" Or Me.Combo18 = "Thawed") And
***QUERY RESULTS FOR CURRENT LOT IN VESSEL ENTERED ON
FORM*** <> Me.Combo.23 Then
ReadyToClose = False
userreply = MsgBox("You must enter a Bin Number for the
Status category that you chose", 0, "Error")
If userreply = vbOK Then
Me.Combo23.SetFocus
End If
End If

Thanks for any help.

 

[Treebeard]

You could do a query using ADO or DAO but it would be simpler using the
DLookup function. It would look something like this:


If (DLookup ("[LotNumber]", "YourTableName", "[VesselNum] = " &
Me.VesselNumber) <> Me.Combo.23) Then
..
..
..

Jack

 

[Todd]

Thanks for the info on DLookup, I think that will help me
for some of what I need to do. I'm applying this to
several things. In one case there will be multiple
entries in the table for a single Lot number, so I need to
run a query to find the Latest one.

What would some example code be for running a query using
ADO or DAO?
Again, I'd like to be able to pass a value from the form
(Vessel number) as a parameter to a query which would then
return the Current LotNumber for comparison in an
If..Then.

-----Original Message-----
You could do a query using ADO or DAO but it would be simpler using the
DLookup function. It would look something like this:


If (DLookup

("[LotNumber]", "YourTableName", "[VesselNum] = " &

 

[Treebeard]

Todd,


Instead of

If (DLookup ("[LotNumber]", "YourTableName", "[VesselNum] = " &
Me.VesselNumber) <> Me.Combo.23) Then

you would use :

If GetLotNumber(Me.VesselNumber) = Me.Combo.23 then
..
..
..

Put the following code in one of your modules. I've included both ADO and
DAO. The default version for Access 2000 or less is DAO. For ADO, make
sure that your "Tools/References " has Microsoft ActiveX Data Objects
Library 2.5 (or later) checked. I would recommend ADO because it is the more
modern version of DAO, i believe. If one of your machines is running Windows
98, you'll have to download from microsoft the appropriate ADO library and
install it on the machine.

' returns the lot number for the specified vessel number
' if lot number not found, it returns 0
Public Function GetLotNumber(lngVesselNumber As Long) As Long
Dim lngLot As Long, strSQL As String
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
lngLot = 0

strSQL = "SELECT [LotNumber] from [YourTableName] WHERE [VesselNum] = " &
lngVesselNumber

Set dbs = CurrentProject.Connection
Set rst = New Recordset

rst.Open strSQL, dbs, adOpenStatic, adLockOptimistic
If rst.RecordCount > 0 Then
If IsNumeric(rst!LotNumber) Then lngLot = rst!LotNumber
End If

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing


GetLotNumber = lngLot

End Function



' same thing in DAO . make sure that your "Tools/References " has
' Microsoft DAO 3.6 Objects Library (or later) checked.

Public Function GetLotNumber(lngVesselNumber As Long) As Long

Dim lngLot As Long, strSQL As String
Dim rst As DAO.Recordset, dbs As Database

lngLot = 0

strSQL = "SELECT [LotNumber] from [YourTableName] WHERE [VesselNum] = "
& lngVesselNumber

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then
If IsNumeric(rst!LotNumber) Then lngLot = rst!LotNumber
End If

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing



GetLotNumber = lngLot

End Function

Thanks for the info on DLookup, I think that will help me
for some of what I need to do. I'm applying this to
several things. In one case there will be multiple
entries in the table for a single Lot number, so I need to
run a query to find the Latest one.

What would some example code be for running a query using
ADO or DAO?
Again, I'd like to be able to pass a value from the form
(Vessel number) as a parameter to a query which would then
return the Current LotNumber for comparison in an
If..Then.

-----Original Message-----
You could do a query using ADO or DAO but it would be simpler using the
DLookup function. It would look something like this:


If (DLookup

("[LotNumber]", "YourTableName", "[VesselNum] = " &

Me.VesselNumber) <> Me.Combo.23) Then
..
..
..

Jack





.