Re: Running a query to check data entry
From: Treebeard (flyers999_at_hotmail.com)
Date: 04/12/04
- Next message: Sid: "Re: Execute SQL SELECT statement with VBA code"
- Previous message: Mel_P: "Re: Does an Access 2000 have a datagrid facility ?"
- In reply to: Todd: "Re: Running a query to check data entry"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 12 Apr 2004 16:04:42 -0400
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
"Todd" <anonymous@discussions.microsoft.com> wrote in message
news:1ab0d01c41e32$4cfa80e0$a101280a@phx.gbl...
> 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
> >
> >
> >"Todd" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:1a3c501c41dab$57b06e50$a501280a@phx.gbl...
> >> 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.
> >
> >
> >.
> >
- Next message: Sid: "Re: Execute SQL SELECT statement with VBA code"
- Previous message: Mel_P: "Re: Does an Access 2000 have a datagrid facility ?"
- In reply to: Todd: "Re: Running a query to check data entry"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
