I'm surprised that works at all: I thought you'd run into problems with the
square brackets around the variable name Failure in your statement. I'm also
not sure what MsgBox [movement!ATD] is actually showing there:
[movement!ATD] doesn't make sense to me. If movement is a table, VBA doesn't
let you address table values that way. If it's a recordset, your brackets
are incorrect: it should be movement![ATD].
Let's see what SQL is actually being generated.
Change the two lines of code
Failure = "[Airport] & "" - "" & CARRCODE & FLIGHTOUT & "" "" &
Format([ETD], ""HH:mm"") & "" / "" & Format([ATD], ""HH:mm"")"
DoCmd.RunSQL "INSERT INTO tblFailures ( RecNumber, Station, [Date],
Carrcode, Flight, FailureType, failure ) SELECT Movement.RecNumber,
getUID_Station(), Movement.DATE, Movement.CARRCODE, Movement.Flightout, 1 AS
Expr1, " & [Failure] & " AS Expr2 FROM Movement WHERE
(((Movement.RecNumber)=" & Me![RecNumber] & "));"
to
Dim strSQL As String
Failure = "[Airport] & "" - "" & CARRCODE & FLIGHTOUT & "" "" &
Format([ETD], ""HH:mm"") & "" / "" & Format([ATD], ""HH:mm"")"
strSQL = "INSERT INTO tblFailures ( RecNumber, Station, [Date],
Carrcode, Flight, FailureType, failure ) SELECT Movement.RecNumber,
getUID_Station(), Movement.DATE, Movement.CARRCODE, Movement.Flightout, 1 AS
Expr1, " & [Failure] & " AS Expr2 FROM Movement WHERE
(((Movement.RecNumber)=" & Me![RecNumber] & "));"
Debug.Print strSQL
DoCmd.RunSQL strSQL
After the code runs, go to the Immediate window (Ctrl-G) and check what's
printed there. Copy and paste it into your reply.
Pardon me if I seem abrupt, but your VBA coding style leaves something to be
desired. It would appear that you have not set up the VBA editor to insist
on variable declarations, as I don't see any Dim statements in your code.
(To set the VBA editor up to do this, go the Modules tab under Tools |
Options and ensure that the "Require Variable Declaration" box is checked.
When it's checked, all new modules will have a line Option Explicit at the
top. You'll have to go back to existing modules and add that line manually)
The reason for using Option Explicit is that it'll catch a lot of typos: if
you refer to a variable as ETD in one place, and (mistakenly) as ETTD in
another, that will be flagged as an error. Also, looking at your code,
there's no reason for any of the GoTo statements you're included. You should
be using the Else option of the If statement to control what you want done
if the condition isn't true, rather than branching using a GoTo.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Jerry Mc Cauley said:
Here is the code:
Private Sub ATD_AfterUpdate()
If IsNull([ATD]) Then
DELAY = 0
GoTo EndS
End If
If DatePart("h", ETD) = 0 And DatePart("h", ATD) = 23 Then
ETDS = DateAdd("h", 24#, ETD)
Else
ETDS = ETD
End If
DELAY:
If (ATD > ETDS) Then
DelayButton.Caption = "Need Delay Info"
DelayButton.ForeColor = 255
DelayButton.Visible = True
Failure_Type = "Delay"
Mess = "Delay: " & [Airport] & " - " & CARRCODE & FLIGHTOUT & " " &
Format([ETD], "HH:mm") & " / " & Format([ATD], "HH:mm")
If DELAY = 0 Then
DELAY = -1
If IsNull(DLookup("[RecNumber]", "tblFailures", "[RecNumber]=" &
Me!RecNumber)) Then
DoCmd.SetWarnings False
If (ATA <= STA) Then
MsgBox GetFailSendLst, , "Send Email to"
DoCmd.SendObject acSendNoObject, , , GetFailSendLst, , , "Delay Alert",
[Mess], 0, False
Else
GoTo Delay2
End If
Delay2:
MsgBox [movement!ATD]
Failure = "[Airport] & "" - "" & CARRCODE & FLIGHTOUT & "" "" &
Format([ETD], ""HH:mm"") & "" / "" & Format([ATD], ""HH:mm"")"
DoCmd.RunSQL "INSERT INTO tblFailures ( RecNumber, Station, [Date],
Carrcode, Flight, FailureType, failure ) SELECT Movement.RecNumber,
getUID_Station(), Movement.DATE, Movement.CARRCODE, Movement.Flightout, 1
AS
Expr1, " & [Failure] & " AS Expr2 FROM Movement WHERE
(((Movement.RecNumber)=" & Me![RecNumber] & "));"
DoCmd.SetWarnings True
Else
cmdFlight.Visible = False
DelayButton.Visible = False
End If
End If
End If
EndS:
Me.Refresh
End Sub
You can see that just right after Delay2: I send the field ATD to a
msgbox
and it reports the information correctly --- but then in the next line it
is
blank.
Any ideas?
--
Jerry
Douglas J. Steele said:
What's the exact code you're using?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Yes - date and time)
I put in a mesbox before the line to build Failure and the correct ATD
time
in there ... then .... blank
--
Jerry
:
Is ATD a Date/Time field? You sure there's a value in it?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Douglas,
Thats for the info .. it all works except the last number ... the
ATD.
It
is always blank after the / .
Thanks again
--
Jerry
:
I can't imagine how that worked in any previous versions of Access,
as
it's
invalid SQL. You only use the Name: Expression syntax in the
graphical
query
builder. In SQL, you use Expression As Name.
As well, you need to ensure that the definition that's stored in
the
variable Failure is what gets into the SQL, not the name of the
variable
that holds it. Access queries don't know anything about VBA
variables.
What are [Airport], CARRCODE, FLIGHTOUT, [ETD] and [ATD]: are all 5
of
them
fields in your table, or are some of them variables in your code?
If they're all fields in your table, try:
Failure = "[Airport] & "" - "" & CARRCODE & FLIGHTOUT & "" "" &
Format([ETD], ""HH:mm"") & "" / "" & Format([ATD], ""HH:mm"")"
If CARRCODE and FLIGHTOUT are variables, try:
Failure = "[Airport] & "" - """ & CARRCODE & FLIGHTOUT & """ "" &
Format([ETD], ""HH:mm"") & "" / "" & Format([ATD], ""HH:mm"")"
Once you've made the appropriate change, use:
DoCmd.RunSQL "INSERT INTO tblFailures ( RecNumber, Station,
[Date],
Carrcode, Flight, FailureType, failure ) SELECT Movement.RecNumber,
getUID_Station(), Movement.DATE, Movement.CARRCODE,
Movement.Flightout, 1
AS
Expr1, " & [Failure] & " AS Expr2 FROM Movement WHERE
(((Movement.RecNumber)=" & Me![RecNumber] & "));"
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hello all,
I'm sure the answer is right here --- but I can't figure out what
I
am
doing
wrong. this statement worked in the older verision of Access,
but
now
we
are
using Access 2003.
Failure = "Delay: " & [Airport] & " - " & CARRCODE & FLIGHTOUT
&
" "
&
Format([ETD], "HH:mm") & " / " & Format([ATD], "HH:mm")
DoCmd.RunSQL "INSERT INTO tblFailures ( RecNumber, Station,
[Date],
Carrcode, Flight, FailureType, failure ) SELECT
Movement.RecNumber,
getUID_Station(), Movement.DATE, Movement.CARRCODE,
Movement.Flightout,
1
AS
Expr1, [Failure] AS Expr2 FROM Movement WHERE
(((Movement.RecNumber)="
&
Me![RecNumber] & "));"
When I run these statements now --- I get a message box asking me
for
the
Failure info. H E L P
Thanks and have a great day
