Re: MsgBox problem
From: Walter (anonymous_at_discussions.microsoft.com)
Date: 02/27/05
- Next message: Ken Snell [MVP]: "Re: MsgBox problem"
- Previous message: Marshall Barton: "Re: BuildCriteria - tag issue"
- In reply to: Ken Snell [MVP]: "Re: MsgBox problem"
- Next in thread: Walter: "Re: MsgBox problem"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 26 Feb 2005 17:27:31 -0800
>-----Original Message-----
>Sorry... typo in my post. This is what you want to try
in the Immediate
>Window:
>
>?[forms]![frmTrips]![TruckID]
I deleted the ) and got Run-time Error: 2186
This property isn't available in design view.
>
>
>Also, when you typed
> ??
>
>as what was the result of the DLookup in the Immediate
Window, I assume you
>mean that nothing was shown; it appeared to be an empty
line?
The ?? was the literal response.
>
>--
>
> Ken Snell
><MS ACCESS MVP>
>
>
>"Walter" <anonymous@discussions.microsoft.com> wrote in
message
>news:217601c51c5c$34d4afd0$a601280a@phx.gbl...
>> OK. After my last post I was able to display help(could
>> not display it before) and found out how to use
>> breakpoints.
>>>-----Original Message-----
>>>Put a breakpoint on the code step with the MsgBox line.
>> When the code breaks
>>>at that point, after you begin adding a new record, put
>> the cursor over the
>>>dtLastTrip variable. What is the value that is shown?
>>
>> In trying to add a new record with a valid date I still
>> get the "type Mismatch" error. The dtLastTrip variable
>> shows 12:00:00 AM
>>>
>>>Then, in the Immediate Window, type
>>>?DLookup("TripDate", "qryLastTripDate")
>>>
>>>What value is returned?
>> ??
>>>
>>>Also, in the Immediate window, type
>>>?)[forms]![frmTrips]![TruckID]
>>
>> In trying to print this ?)[forms]![frmTrips]![TruckID]
my
>> computer locked up and I had to download an update. I
>> have to restart so I thought I would send this much.
I'll
>> post the rest after restart.
>> Thanks,
>> Walter
>>>
>>>What value is returned?
>>>
>>>What value do you think is in the TruckID control on
the
>> form frmTrips?
>>>--
>>>
>>> Ken Snell
>>><MS ACCESS MVP>
>>>
>>>
>>>
>>>"Walter" <anonymous@discussions.microsoft.com> wrote in
>> message
>>>news:1c1201c51c4f$fdb04930$a401280a@phx.gbl...
>>>>
>>>>>-----Original Message-----
>>>>>The data type mismatch, when you declare dtLastTrip
as
>> a
>>>> Date type, is being
>>>>>caused when the DLookup function finds no records and
>> is
>>>> returning a Null
>>>>>value; a Date variable cannot accept a value of Null,
>> so
>>>> the code errors.
>>>>>When you comment out the "As Date", then dtLastTrip
is
>>>> declared as a Variant
>>>>>type, which can accept a Null value. Then, when its
>>>> value is Null, nothing
>>>>>displays in your message box because Null has no
value,
>>>> and there is nothing
>>>>>to display.
>>>>>
>>>>>The problem is that your DLookup is returning a value
>> of
>>>> Null in these
>>>>>cases. I assume that is because the query will
return a
>>>> record * only if *
>>>>>there is a child record in the tblTripDetails table
for
>>>> the tblTrips.TruckID
>>>>>value that is in the TruckID control that is on your
>>>> frmTrips form. If
>>>>>there is no child record, there is no record returned
>> by
>>>> the query, even if
>>>>>you have a record in tblTrips for that TruckID.
>>>>
>>>> All trips in the db have child records in
>>>> tblTripDetails.
>>>>>
>>>>>Perhaps you need to modify your query:
>>>>>
>>>>>SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
>>>>>MaxOfTripDate
>>>>>FROM tblTrips LEFT JOIN tblTripDetails ON
>>>>>tblTrips.TripID = tblTripDetails.TripID
>>>>>WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
>>>>>GROUP BY tblTrips.TruckID;
>>>>>
>>>>>The above query will return a record if there is a
>>>> record in tblTrips for
>>>>>that TruckID * even if * you don't have a child
record
>>>> in the tblTripDetails
>>>>>table. It also will run a bit faster because I've
>>>> changed the HAVING filter
>>>>>to a WHERE filter; WHERE filter is performed before
the
>>>> query groups the
>>>>>results (thus, nondesired records are filtered out
>>>> before grouping), whereas
>>>>>HAVING filters after the grouping is done. Grouping
>>>> takes a lot of
>>>>>resources, so it's better to filter beforer grouping
if
>>>> you can do it.
>>>>>--
>>>> I pasted your SQL in a blank query window, renamed
the
>>>> old query and saved the new one as qryLastTripDate.
I
>>>> then tried to add a new record using a valid date and
>> got
>>>> the "Invalid date error" MsgBox with only the first
>> line
>>>> of text displayed. I understand it not displaying a
>> null
>>>> value but it is also not displaying the second line
of
>>>> text; "Please enter a date greater than or equal to
>> this
>>>> date."
>>>> Walter
>>>>>
>>>>> Ken Snell
>>>>><MS ACCESS MVP>
>>>>>
>>>>>
>>>>>"Walter" <anonymous@discussions.microsoft.com> wrote
in
>>>> message
>>>>>news:213e01c51c4a$d5721bb0$a601280a@phx.gbl...
>>>>>>
>>>>>>>-----Original Message-----
>>>>>>>Your DLookup code step is not filtering the records
>> in
>>>>>> any way. Thus, the
>>>>>>>code step will return the "first" record that the
>>>>>> function finds in
>>>>>>>qryLastTripDate. And if the query returns no
records,
>>>>>> dtLastTrip will be a
>>>>>>>Null value (see my comment below about how
>>>>>> you're "dimming" the variable).
>>>>>>>And a Null will show as a blank entry in your
>>>>>> MsgBox.What is the SQL
>>>>>>>statement of qryLastTripDate?
>>>>>>
>>>>>> If I run the query by itself, providing a truck #,
it
>>>>>> returns the correct date. Here is the SQL:
>>>>>>
>>>>>> SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
>>>>>> MaxOfTripDate
>>>>>> FROM tblTrips INNER JOIN tblTripDetails ON
>>>>>> tblTrips.TripID = tblTripDetails.TripID
>>>>>> GROUP BY tblTrips.TruckID
>>>>>> HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
>>>>>> [TruckID]));
>>>>>>>
>>>>>>>I also note that this code step has commented out
>>>>>> the "As Date" portion of
>>>>>>>the step; that makes dtLastTrip a Variant data
type,
>>>> not
>>>>>> a Date data type.
>>>>>>>Is this what you wanted?
>>>>>>> Dim dtLastTrip 'As Date
>>>>>>
>>>>>> With the "As Date" in the code, I get a Run time
>>>>>> error '13': Type mismatch. I go to debug and the
>>>>>> "Dim dtLastTrip As Date" line is highlighted in
>> yellow.
>>>>>> If I hold the cursor over the "dtLastTrip" it
shows a
>>>>>> value of 12:00:00 AM.
>>>>>> Thanks,
>>>>>> Walter
>>>>>>>
>>>>>>>
>>>>>>>--
>>>>>>>
>>>>>>> Ken Snell
>>>>>>><MS ACCESS MVP>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>"Walter" <anonymous@discussions.microsoft.com>
wrote
>> in
>>>>>> message
>>>>>>>news:0a6b01c51c0b$583abe70$a501280a@phx.gbl...
>>>>>>>>I have the following code in a TruckID control to
>>>>>>>> validate the trip date for each truck. If I
enter
>> a
>>>>>> date
>>>>>>>> previous to the last date, the MsgBox appears
>>>> with "The
>>>>>>>> last trip entered for this truck was on " and the
>> OK
>>>>>>>> button. Can someone tell me why it is not
showing
>>>> the
>>>>>>>> date of the last trip? Also it is displaying the
>>>>>> MsgBox
>>>>>>>> even when a valid date is entered. Thanks to
Dirk
>>>>>>>> Goldgar for the code. I have commented the "As
>> Date"
>>>>>>>> because with it in place, I would get a "type
>>>> mismatch"
>>>>>>>> error and the debug would show "dtLastTrip =
>> 12:00:00
>>>>>> AM"
>>>>>>>>
>>>>>>>> Private Sub TruckID_BeforeUpdate(Cancel As
Integer)
>>>>>>>> Dim dtLastTrip 'As Date
>>>>>>>>
>>>>>>>> dtLastTrip = DLookup
>>>> ("TripDate", "qryLastTripDate")
>>>>>>>>
>>>>>>>> If dtLastTrip > Me.TripDate Then
>>>>>>>>
>>>>>>>> MsgBox _
>>>>>>>> "The last trip entered for this truck
>> was
>>>>>>>> on " & _
>>>>>>>> dtLastTrip & vbCrLf & _
>>>>>>>> "Please enter a date greater than
or
>>>>>>>> equal " & _
>>>>>>>> "to this date.", , _
>>>>>>>> "Invalid Date Entry"
>>>>>>>>
>>>>>>>> Cancel = True
>>>>>>>> End If
>>>>>>>>
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>> Thanks for your help,
>>>>>>>> Walter
>>>>>>>
>>>>>>>
>>>>>>>.
>>>>>>>
>>>>>
>>>>>
>>>>>.
>>>>>
>>>
>>>
>>>.
>>>
>
>
>.
>
- Next message: Ken Snell [MVP]: "Re: MsgBox problem"
- Previous message: Marshall Barton: "Re: BuildCriteria - tag issue"
- In reply to: Ken Snell [MVP]: "Re: MsgBox problem"
- Next in thread: Walter: "Re: MsgBox problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
