MoveNext question

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

Guest

The code below is intended to append tbl_hotel for each night a person is
staying based on their flight information from tbl_Flights.
I am trying to determine why it doesn't move to the next record, but appends
the nights for the same person over and over. I'm under the gun and would
appreciate any help you can give!

---------------------------------------------------------------------------------
Private Sub cmd_AppendHotelFromFlights_Click()

Dim mySQL, i, LenStay As String
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tbl_Flights")

rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
LenStay = DateDiff("d", ArriveDate, DepartDate)
'Append the first night
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"SELECT AttendeeID AS Who, ArriveDate AS When"
DoCmd.RunSQL mySQL
'Append the remaining nights
For i = 1 To LenStay - 1
Me.txt_i = i 'Can't seem to find a way to use just i in the SQL;
this is a work-around
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"SELECT AttendeeID AS Who, " & _
"ArriveDate +
[Forms]![frm_AppendHotel_Sub].[txt_i] AS When"
DoCmd.RunSQL mySQL
Next i
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'Update shared information for FCEs
DoCmd.RunSQL "qry_UpdateFCERoomShare"

End Sub
 
The SQL for your Insert statements is wrong.

The correct syntax is:

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

In other words, you need to include a table name when you use INSERT
INTO...SELECT, and you need to use the keyword VALUES if you're trying to
pass the values from variables.

If AttendeeID and ArriveDate are variables in your code, be aware that you
need to pass the values, not the names of the variables. That means that the
variables need to be outside of the quotes when you're assigning MySQL:

mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & AttendeeID & ", " & ArriveDate & ")"

As well, text values need to be enclosed in quotes, and dates need to be
enclosed in # (and in mm/dd/yyyy format). For example, if AttendeeID is a
text field (and assuming that ArriveDate is a date), that should be:

mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & Chr$(34) & AttendeeID & Chr$(34) & _
", " & Format$(ArriveDate, "\#mm\/dd\/yyyy\#") & ")"
 
Doug, Thank you very much for your speedy reply! I need to further clarify
my problem. The SQL is working to append the correct nights. My problem is
that it is appending the correct nights for the same person over and over and
over. It's not moving to the next record.

In tbl_Flights is AttendeeID, ArriveDate and DepartDate (for example
{AttendeeID = 123, ArriveDate = 10/1/2005 and DepartDate = 10/3/2005},
{AttendeeID = 456, ArriveDate = 10/2/2005 and DepartDate = 10/3/2005}). I
want to append to tbl_Hotel
AttendeeID=123, Night=10/1/2005
AttendeeID=123, Night=10/2/2005
AttendeeID=123, Night=10/3/2005
AttendeeID=456, Night=10/2/2005
AttendeeID=456, Night=10/3/2005

Right now it's doing the first three of these multiple times. Am I being
obtuse and missing something? I have to admit that my grasp of this stuff is
spotty.

Douglas J. Steele said:
The SQL for your Insert statements is wrong.

The correct syntax is:

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

In other words, you need to include a table name when you use INSERT
INTO...SELECT, and you need to use the keyword VALUES if you're trying to
pass the values from variables.

If AttendeeID and ArriveDate are variables in your code, be aware that you
need to pass the values, not the names of the variables. That means that the
variables need to be outside of the quotes when you're assigning MySQL:

mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & AttendeeID & ", " & ArriveDate & ")"

As well, text values need to be enclosed in quotes, and dates need to be
enclosed in # (and in mm/dd/yyyy format). For example, if AttendeeID is a
text field (and assuming that ArriveDate is a date), that should be:

mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & Chr$(34) & AttendeeID & Chr$(34) & _
", " & Format$(ArriveDate, "\#mm\/dd\/yyyy\#") & ")"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Anne said:
The code below is intended to append tbl_hotel for each night a person is
staying based on their flight information from tbl_Flights.
I am trying to determine why it doesn't move to the next record, but
appends
the nights for the same person over and over. I'm under the gun and would
appreciate any help you can give!

---------------------------------------------------------------------------------
Private Sub cmd_AppendHotelFromFlights_Click()

Dim mySQL, i, LenStay As String
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tbl_Flights")

rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
LenStay = DateDiff("d", ArriveDate, DepartDate)
'Append the first night
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"SELECT AttendeeID AS Who, ArriveDate AS When"
DoCmd.RunSQL mySQL
'Append the remaining nights
For i = 1 To LenStay - 1
Me.txt_i = i 'Can't seem to find a way to use just i in the
SQL;
this is a work-around
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"SELECT AttendeeID AS Who, " & _
"ArriveDate +
[Forms]![frm_AppendHotel_Sub].[txt_i] AS When"
DoCmd.RunSQL mySQL
Next i
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'Update shared information for FCEs
DoCmd.RunSQL "qry_UpdateFCERoomShare"

End Sub
Click to expand...
Click to expand...
 
For i = 1 To LenStay - 1
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & AttendeeID & ", " & _
Format(DateAdd("d", i, ArriveDate), "\#mm\/dd\/yyyy\#") &
")"
DoCmd.RunSQL mySQL
Next i


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Anne said:
Doug, Thank you very much for your speedy reply! I need to further
clarify
my problem. The SQL is working to append the correct nights. My problem
is
that it is appending the correct nights for the same person over and over
and
over. It's not moving to the next record.

In tbl_Flights is AttendeeID, ArriveDate and DepartDate (for example
{AttendeeID = 123, ArriveDate = 10/1/2005 and DepartDate = 10/3/2005},
{AttendeeID = 456, ArriveDate = 10/2/2005 and DepartDate = 10/3/2005}). I
want to append to tbl_Hotel
AttendeeID=123, Night=10/1/2005
AttendeeID=123, Night=10/2/2005
AttendeeID=123, Night=10/3/2005
AttendeeID=456, Night=10/2/2005
AttendeeID=456, Night=10/3/2005

Right now it's doing the first three of these multiple times. Am I being
obtuse and missing something? I have to admit that my grasp of this stuff
is
spotty.

Douglas J. Steele said:
The SQL for your Insert statements is wrong.

The correct syntax is:

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

In other words, you need to include a table name when you use INSERT
INTO...SELECT, and you need to use the keyword VALUES if you're trying to
pass the values from variables.

If AttendeeID and ArriveDate are variables in your code, be aware that
you
need to pass the values, not the names of the variables. That means that
the
variables need to be outside of the quotes when you're assigning MySQL:

mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & AttendeeID & ", " & ArriveDate & ")"

As well, text values need to be enclosed in quotes, and dates need to be
enclosed in # (and in mm/dd/yyyy format). For example, if AttendeeID is a
text field (and assuming that ArriveDate is a date), that should be:

mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & Chr$(34) & AttendeeID & Chr$(34) & _
", " & Format$(ArriveDate, "\#mm\/dd\/yyyy\#") & ")"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Anne said:
The code below is intended to append tbl_hotel for each night a person
is
staying based on their flight information from tbl_Flights.
I am trying to determine why it doesn't move to the next record, but
appends
the nights for the same person over and over. I'm under the gun and
would
appreciate any help you can give!

---------------------------------------------------------------------------------
Private Sub cmd_AppendHotelFromFlights_Click()

Dim mySQL, i, LenStay As String
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tbl_Flights")

rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
LenStay = DateDiff("d", ArriveDate, DepartDate)
'Append the first night
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"SELECT AttendeeID AS Who, ArriveDate AS When"
DoCmd.RunSQL mySQL
'Append the remaining nights
For i = 1 To LenStay - 1
Me.txt_i = i 'Can't seem to find a way to use just i in the
SQL;
this is a work-around
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"SELECT AttendeeID AS Who, " & _
"ArriveDate +
[Forms]![frm_AppendHotel_Sub].[txt_i] AS When"
DoCmd.RunSQL mySQL
Next i
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'Update shared information for FCEs
DoCmd.RunSQL "qry_UpdateFCERoomShare"

End Sub
Click to expand...
Click to expand...
Click to expand...
 
I've EXTENSIVE Hotel/Resort Operations experience - much more than
probably should. If you need input on functionality or testing, feel
free to contact me directly.
 
Thanks again for your prompt response. I am truly grateful for this resource.

I updated my code with your information and it works like a charm to append
the date information. I will remember the DateAdd function. Now I don't
need to have a silly textbox with i in it!

The rest of the code is still kittywhumpus, though. It's appending the
information for the first record a number of times equal to the number of
records.

For example, if tbl_Flights has
AttendeeID ArriveDate DepartDate
208 10/1/2005 10/3/2005
209 10/2/2005 10/3/2005
210 10/1/2005 10/4/2005

It appends to tbl_Hotel
AttendeeID Night
208 01-Oct-05
208 02-Oct-05
208 01-Oct-05
208 02-Oct-05
208 01-Oct-05
208 02-Oct-05

Any further thoughts for me? Thanks in advance for your time.

Douglas J. Steele said:
For i = 1 To LenStay - 1
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & AttendeeID & ", " & _
Format(DateAdd("d", i, ArriveDate), "\#mm\/dd\/yyyy\#") &
")"
DoCmd.RunSQL mySQL
Next i


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Anne said:
Doug, Thank you very much for your speedy reply! I need to further
clarify
my problem. The SQL is working to append the correct nights. My problem
is
that it is appending the correct nights for the same person over and over
and
over. It's not moving to the next record.

In tbl_Flights is AttendeeID, ArriveDate and DepartDate (for example
{AttendeeID = 123, ArriveDate = 10/1/2005 and DepartDate = 10/3/2005},
{AttendeeID = 456, ArriveDate = 10/2/2005 and DepartDate = 10/3/2005}). I
want to append to tbl_Hotel
AttendeeID=123, Night=10/1/2005
AttendeeID=123, Night=10/2/2005
AttendeeID=123, Night=10/3/2005
AttendeeID=456, Night=10/2/2005
AttendeeID=456, Night=10/3/2005

Right now it's doing the first three of these multiple times. Am I being
obtuse and missing something? I have to admit that my grasp of this stuff
is
spotty.

Douglas J. Steele said:
The SQL for your Insert statements is wrong.

The correct syntax is:

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

In other words, you need to include a table name when you use INSERT
INTO...SELECT, and you need to use the keyword VALUES if you're trying to
pass the values from variables.

If AttendeeID and ArriveDate are variables in your code, be aware that
you
need to pass the values, not the names of the variables. That means that
the
variables need to be outside of the quotes when you're assigning MySQL:

mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & AttendeeID & ", " & ArriveDate & ")"

As well, text values need to be enclosed in quotes, and dates need to be
enclosed in # (and in mm/dd/yyyy format). For example, if AttendeeID is a
text field (and assuming that ArriveDate is a date), that should be:

mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & Chr$(34) & AttendeeID & Chr$(34) & _
", " & Format$(ArriveDate, "\#mm\/dd\/yyyy\#") & ")"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The code below is intended to append tbl_hotel for each night a person
is
staying based on their flight information from tbl_Flights.
I am trying to determine why it doesn't move to the next record, but
appends
the nights for the same person over and over. I'm under the gun and
would
appreciate any help you can give!

---------------------------------------------------------------------------------
Private Sub cmd_AppendHotelFromFlights_Click()

Dim mySQL, i, LenStay As String
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tbl_Flights")

rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
LenStay = DateDiff("d", ArriveDate, DepartDate)
'Append the first night
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"SELECT AttendeeID AS Who, ArriveDate AS When"
DoCmd.RunSQL mySQL
'Append the remaining nights
For i = 1 To LenStay - 1
Me.txt_i = i 'Can't seem to find a way to use just i in the
SQL;
this is a work-around
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"SELECT AttendeeID AS Who, " & _
"ArriveDate +
[Forms]![frm_AppendHotel_Sub].[txt_i] AS When"
DoCmd.RunSQL mySQL
Next i
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'Update shared information for FCEs
DoCmd.RunSQL "qry_UpdateFCERoomShare"

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Where do you get your values for AttendeeID, ArriveDate and DepartDate?
You're not setting them anywhere in your code.

I would expect you to be setting them between the following 2 statements:

Do While Not rs.EOF

and

LenStay = DateDiff("d", ArriveDate, DepartDate)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Anne said:
Thanks again for your prompt response. I am truly grateful for this
resource.

I updated my code with your information and it works like a charm to
append
the date information. I will remember the DateAdd function. Now I don't
need to have a silly textbox with i in it!

The rest of the code is still kittywhumpus, though. It's appending the
information for the first record a number of times equal to the number of
records.

For example, if tbl_Flights has
AttendeeID ArriveDate DepartDate
208 10/1/2005 10/3/2005
209 10/2/2005 10/3/2005
210 10/1/2005 10/4/2005

It appends to tbl_Hotel
AttendeeID Night
208 01-Oct-05
208 02-Oct-05
208 01-Oct-05
208 02-Oct-05
208 01-Oct-05
208 02-Oct-05

Any further thoughts for me? Thanks in advance for your time.

Douglas J. Steele said:
For i = 1 To LenStay - 1
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & AttendeeID & ", " & _
Format(DateAdd("d", i, ArriveDate),
"\#mm\/dd\/yyyy\#") &
")"
DoCmd.RunSQL mySQL
Next i


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Anne said:
Doug, Thank you very much for your speedy reply! I need to further
clarify
my problem. The SQL is working to append the correct nights. My
problem
is
that it is appending the correct nights for the same person over and
over
and
over. It's not moving to the next record.

In tbl_Flights is AttendeeID, ArriveDate and DepartDate (for example
{AttendeeID = 123, ArriveDate = 10/1/2005 and DepartDate = 10/3/2005},
{AttendeeID = 456, ArriveDate = 10/2/2005 and DepartDate = 10/3/2005}).
I
want to append to tbl_Hotel
AttendeeID=123, Night=10/1/2005
AttendeeID=123, Night=10/2/2005
AttendeeID=123, Night=10/3/2005
AttendeeID=456, Night=10/2/2005
AttendeeID=456, Night=10/3/2005

Right now it's doing the first three of these multiple times. Am I
being
obtuse and missing something? I have to admit that my grasp of this
stuff
is
spotty.

:

The SQL for your Insert statements is wrong.

The correct syntax is:

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

In other words, you need to include a table name when you use INSERT
INTO...SELECT, and you need to use the keyword VALUES if you're trying
to
pass the values from variables.

If AttendeeID and ArriveDate are variables in your code, be aware that
you
need to pass the values, not the names of the variables. That means
that
the
variables need to be outside of the quotes when you're assigning
MySQL:

mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & AttendeeID & ", " & ArriveDate & ")"

As well, text values need to be enclosed in quotes, and dates need to
be
enclosed in # (and in mm/dd/yyyy format). For example, if AttendeeID
is a
text field (and assuming that ArriveDate is a date), that should be:

mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"VALUES (" & Chr$(34) & AttendeeID & Chr$(34) & _
", " & Format$(ArriveDate, "\#mm\/dd\/yyyy\#") &
")"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The code below is intended to append tbl_hotel for each night a
person
is
staying based on their flight information from tbl_Flights.
I am trying to determine why it doesn't move to the next record, but
appends
the nights for the same person over and over. I'm under the gun and
would
appreciate any help you can give!

---------------------------------------------------------------------------------
Private Sub cmd_AppendHotelFromFlights_Click()

Dim mySQL, i, LenStay As String
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tbl_Flights")

rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
LenStay = DateDiff("d", ArriveDate, DepartDate)
'Append the first night
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " & _
"SELECT AttendeeID AS Who, ArriveDate AS When"
DoCmd.RunSQL mySQL
'Append the remaining nights
For i = 1 To LenStay - 1
Me.txt_i = i 'Can't seem to find a way to use just i in
the
SQL;
this is a work-around
mySQL = "INSERT INTO tbl_Hotel ( AttendeeID, Night ) " &
_
"SELECT AttendeeID AS Who, " & _
"ArriveDate +
[Forms]![frm_AppendHotel_Sub].[txt_i] AS When"
DoCmd.RunSQL mySQL
Next i
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'Update shared information for FCEs
DoCmd.RunSQL "qry_UpdateFCERoomShare"

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top