query not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is related to a previous post on 7/01/05. I have a query which works in
one control but is picking up the control's value. As suggested, I created a
new unbound control and then copied the code from the first control into the
new control. In this control the variable shows null. I tried creating a new
field and setting the control source to this field but that made no
difference. The code is the same in both controls and in the BeforeUpdate
event in both. Can someone tell me why it's not working in the new control?
 
Walter -

I looked at your previous post. I understand that this code is not properly
validating your entries:

Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("Odometer", "qryLastOdometer")

If Me.Odometer < varPrevOdometer Then
Cancel = True
MsgBox _
"The last odometer entered for this truck was " & _
varPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If
Me.LegMiles = Me.Odometer - varPrevOdometer

End Sub


And the SQL statement for "qryLastOdometer" is

SELECT tblTrips.TruckID, Max(tblTripDetails.Odometer) AS MaxOfOdometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]));


Note that the above query does not have a output field named Odometer, which
is what your DLookup function is trying to lookup.

Change your DLookup statement to this:

varPrevOdometer = DLookup("MaxOfOdometer", "qryLastOdometer")


Does this work for you?
 
Many many thanks Ken. That worked. And it works in the original Odometer
control also.
On an invalid entry, OK in the message box leaves you in the control but at
the end. How do I get the incorrect entry highlighted to be replaced with
the new entry?

--
Thanks for your help,
Walter


Ken Snell said:
Walter -

I looked at your previous post. I understand that this code is not properly
validating your entries:

Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("Odometer", "qryLastOdometer")

If Me.Odometer < varPrevOdometer Then
Cancel = True
MsgBox _
"The last odometer entered for this truck was " & _
varPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If
Me.LegMiles = Me.Odometer - varPrevOdometer

End Sub


And the SQL statement for "qryLastOdometer" is

SELECT tblTrips.TruckID, Max(tblTripDetails.Odometer) AS MaxOfOdometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]));


Note that the above query does not have a output field named Odometer, which
is what your DLookup function is trying to lookup.

Change your DLookup statement to this:

varPrevOdometer = DLookup("MaxOfOdometer", "qryLastOdometer")


Does this work for you?
--

Ken Snell
<MS ACCESS MVP>
Click to expand...
 
Try this for the BeforeUpdate code ( I added two steps using SelStart and
SelLength properties):


Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("Odometer", "qryLastOdometer")

If Me.Odometer < varPrevOdometer Then
Cancel = True
Me.Odometer.SelStart = 0
Me.Odometer.SelLength = Len(Me.Odometer.Value)
MsgBox _
"The last odometer entered for this truck was " & _
varPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If
Me.LegMiles = Me.Odometer - varPrevOdometer

End Sub

--

Ken Snell
<MS ACCESS MVP>


Walter said:
Many many thanks Ken. That worked. And it works in the original Odometer
control also.
On an invalid entry, OK in the message box leaves you in the control but
at
the end. How do I get the incorrect entry highlighted to be replaced with
the new entry?

--
Thanks for your help,
Walter


Ken Snell said:
Walter -

I looked at your previous post. I understand that this code is not
properly
validating your entries:

Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("Odometer", "qryLastOdometer")

If Me.Odometer < varPrevOdometer Then
Cancel = True
MsgBox _
"The last odometer entered for this truck was " & _
varPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If
Me.LegMiles = Me.Odometer - varPrevOdometer

End Sub


And the SQL statement for "qryLastOdometer" is

SELECT tblTrips.TruckID, Max(tblTripDetails.Odometer) AS MaxOfOdometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]));


Note that the above query does not have a output field named Odometer,
which
is what your DLookup function is trying to lookup.

Change your DLookup statement to this:

varPrevOdometer = DLookup("MaxOfOdometer", "qryLastOdometer")


Does this work for you?
--

Ken Snell
<MS ACCESS MVP>




Walter said:
This is related to a previous post on 7/01/05. I have a query which
works
in
one control but is picking up the control's value. As suggested, I
created a
new unbound control and then copied the code from the first control
into
the
new control. In this control the variable shows null. I tried creating
a
new
field and setting the control source to this field but that made no
difference. The code is the same in both controls and in the
BeforeUpdate
event in both. Can someone tell me why it's not working in the new
control?
Click to expand...
Click to expand...
Click to expand...
 
Thanks Ken! Works great!

--
Thanks for your help,
Walter


Ken Snell said:
Try this for the BeforeUpdate code ( I added two steps using SelStart and
SelLength properties):


Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("Odometer", "qryLastOdometer")

If Me.Odometer < varPrevOdometer Then
Cancel = True
Me.Odometer.SelStart = 0
Me.Odometer.SelLength = Len(Me.Odometer.Value)
MsgBox _
"The last odometer entered for this truck was " & _
varPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If
Me.LegMiles = Me.Odometer - varPrevOdometer

End Sub

--

Ken Snell
<MS ACCESS MVP>


Walter said:
Many many thanks Ken. That worked. And it works in the original Odometer
control also.
On an invalid entry, OK in the message box leaves you in the control but
at
the end. How do I get the incorrect entry highlighted to be replaced with
the new entry?

--
Thanks for your help,
Walter


Ken Snell said:
Walter -

I looked at your previous post. I understand that this code is not
properly
validating your entries:

Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("Odometer", "qryLastOdometer")

If Me.Odometer < varPrevOdometer Then
Cancel = True
MsgBox _
"The last odometer entered for this truck was " & _
varPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If
Me.LegMiles = Me.Odometer - varPrevOdometer

End Sub


And the SQL statement for "qryLastOdometer" is

SELECT tblTrips.TruckID, Max(tblTripDetails.Odometer) AS MaxOfOdometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]));


Note that the above query does not have a output field named Odometer,
which
is what your DLookup function is trying to lookup.

Change your DLookup statement to this:

varPrevOdometer = DLookup("MaxOfOdometer", "qryLastOdometer")


Does this work for you?
--

Ken Snell
<MS ACCESS MVP>




This is related to a previous post on 7/01/05. I have a query which
works
in
one control but is picking up the control's value. As suggested, I
created a
new unbound control and then copied the code from the first control
into
the
new control. In this control the variable shows null. I tried creating
a
new
field and setting the control source to this field but that made no
difference. The code is the same in both controls and in the
BeforeUpdate
event in both. Can someone tell me why it's not working in the new
control?
Click to expand...
Click to expand...
Click to expand...
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Walter said:
Thanks Ken! Works great!

--
Thanks for your help,
Walter


Ken Snell said:
Try this for the BeforeUpdate code ( I added two steps using SelStart and
SelLength properties):


Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("Odometer", "qryLastOdometer")

If Me.Odometer < varPrevOdometer Then
Cancel = True
Me.Odometer.SelStart = 0
Me.Odometer.SelLength = Len(Me.Odometer.Value)
MsgBox _
"The last odometer entered for this truck was " & _
varPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If
Me.LegMiles = Me.Odometer - varPrevOdometer

End Sub

--

Ken Snell
<MS ACCESS MVP>


Walter said:
Many many thanks Ken. That worked. And it works in the original
Odometer
control also.
On an invalid entry, OK in the message box leaves you in the control
but
at
the end. How do I get the incorrect entry highlighted to be replaced
with
the new entry?

--
Thanks for your help,
Walter


:

Walter -

I looked at your previous post. I understand that this code is not
properly
validating your entries:

Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("Odometer", "qryLastOdometer")

If Me.Odometer < varPrevOdometer Then
Cancel = True
MsgBox _
"The last odometer entered for this truck was " & _
varPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal "
& _
"to this.", , _
"Invalid Odometer Entry"
End If
Me.LegMiles = Me.Odometer - varPrevOdometer

End Sub


And the SQL statement for "qryLastOdometer" is

SELECT tblTrips.TruckID, Max(tblTripDetails.Odometer) AS MaxOfOdometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]));


Note that the above query does not have a output field named Odometer,
which
is what your DLookup function is trying to lookup.

Change your DLookup statement to this:

varPrevOdometer = DLookup("MaxOfOdometer", "qryLastOdometer")


Does this work for you?
--

Ken Snell
<MS ACCESS MVP>




This is related to a previous post on 7/01/05. I have a query which
works
in
one control but is picking up the control's value. As suggested, I
created a
new unbound control and then copied the code from the first control
into
the
new control. In this control the variable shows null. I tried
creating
a
new
field and setting the control source to this field but that made no
difference. The code is the same in both controls and in the
BeforeUpdate
event in both. Can someone tell me why it's not working in the new
control?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top