Looping through two tables

  • Thread starter Thread starter laavista
  • Start date Start date
L

laavista

I'm new to VBA. I want to loop through two tables. This seems like an easy
task?? I can loop through the first db, but when I try to nest the 2nd one, I
get an error "object variable or with block variable not set". I've spent
hours looking through this forum, plus looking at Access "help"...

Your help would be GREATLY appreciated.

Dim dbHK As Database
Dim dbReserv As Database
Dim rstCurrecHK As DAO.Recordset
Dim rstCurrecReserv As DAO.Recordset
Dim longintHKNum As Long
Dim strConfirmNum As String
Dim longintTripsReservID As Long
Dim strSQL As String

private Sub ShuffleHKT()

Set rstCurrecHK = dbHK.OpenRecordset("SELECT t_Housekeeping.HKNum,
t_Housekeeping.ExpirationDate, t_Housekeeping.Obtained_Method,
t_Housekeeping.Used_On_Reservation " & vbCrLf & _
"FROM t_Housekeeping " & vbCrLf & _
"WHERE (((t_Housekeeping.ExpirationDate)>Date())) " & vbCrLf & _
"ORDER BY t_Housekeeping.ExpirationDate;", dbOpenDynaset)

rstCurrecHK.MoveFirst

Set rstCurrecReserv = dbReserv.OpenRecordset("SELECT
t_TripsReserv.TripsReservID, t_TripsReserv.ResortID, t_TripsReserv.[Start
date], t_TripsReserv.HKNum, t_TripsReserv.ConfirmNum, t_TripsReserv.Credits "
& vbCrLf & _
"FROM t_TripsReserv " & vbCrLf & _
"WHERE (((t_TripsReserv.[Start date])>=Date()) AND ((t_TripsReserv.Credits)
Is Not Null)) " & vbCrLf & _
"ORDER BY t_TripsReserv.[Start date];", dbOpenDynaset)

rstCurrecReserv.MoveFirst

Do Until rstCurrecHK.EOF
longintHKNum = rstCurrecHK!HKNum
MsgBox "HKNum " & longintHKNum

Do Until rstCurrecReserv.EOF
longintTripsReservID = rstCurrecReserv!TripsReservID
MsgBox "TripsReservID " & longintTripsReservID

rstCurrecReserv.MoveNext
Loop

rstCurrecHK.MoveNext
Loop

Exit Sub
 
I'm new to VBA.  I want to loop through two tables.  This seems like an easy
task?? I can loop through the first db, but when I try to nest the 2nd one, I
get an error "object variable or with block variable not set".   I've spent
hours looking through this forum, plus looking at Access "help"...

Your help would be GREATLY appreciated.  

Dim dbHK As Database
Dim dbReserv As Database
Dim rstCurrecHK As DAO.Recordset
Dim rstCurrecReserv As DAO.Recordset
Dim longintHKNum As Long
Dim strConfirmNum As String
Dim longintTripsReservID As Long
Dim strSQL As String

private Sub ShuffleHKT()
Click to expand...
===How does this next line work if you never set a reference to dbHK?
===You should have something like this:
set dbHK=CurrentDB or
set dbHK = wrkJet.OpenDatabase("Northwind.mdb", True)

Why are you trying to loop through tables? I'm only wondering because
looping through a table in code is VERY inefficient. If you're trying
to update something, you can run an update query... Only use this
method as an absolute last resort. Works okay if you have a couple
thousand records, but if you throw in a function or two, it'll bog
down quickly.
 
Thanks for your patience. Let me see if I can explain.

HK table has an autonumber as it's primary key (HKNum) and has fields
"reservid" and "credits expiration date" (there's more fields, but these are
the ones to work with)

Reserv has an autonumber as it's primary key (ReservID) and has fields
"hknum" and "reserv start date" (there's more fields...)

In reserv form, user can select the HK credits they wish to assign to that
record. When they select the credits to use, HKnum is put in reserv table
and reserv ID is put in HK table. Users will do this for each reserv record.

However, they may wish to have the application do a "re-assign" by assigning
the credits with the oldest expiration dates to the reserv with the oldest
start date.

I have two queries, sorting HK table by expiration date and sorting Reserv
by start date.

Want to loop through the two tables and re-assign old credits to oldest
start date for the 1st record; next oldest credits to the next oldest start
date, etc. There may be more records in the reserv table than in the HK
table.

Thanks for your help.
 
Thanks for your patience. Let me see if I can explain.

HK table has an autonumber as it's primary key (HKNum) and has fields
"reservid" and "credits expiration date" (there's more fields, but these are
the ones to work with)

Reserv has an autonumber as it's primary key (ReservID) and has fields
"hknum" and "reserv start date" (there's more fields...)

In reserv form, user can select the HK credits they wish to assign to that
record. When they select the credits to use, HKnum is put in reserv table
and reserv ID is put in HK table. Users will do this for each reserv record.

However, they may wish to have the application do a "re-assign" by assigning
the credits with the oldest expiration dates to the reserv with the oldest
start date.

I have two queries, sorting HK table by expiration date and sorting Reserv
by start date.

Want to loop through the two tables and re-assign old credits to oldest
start date for the 1st record; next oldest credits to the next oldest start
date, etc. There may be more records in the reserv table than in the HK
table.
Click to expand...

I'm still not sure I understand this. For Piet's and my benefit, could you
post a small example with just these fields, and four or five rows of data,
showing the "before" and "after"? It SOUNDS like there's a one to many
relationship from HK to Reserv with HKNum as the primary key in HK and the
foreign key in Reserv, but that you want to change the link by updating the
HKnum field in Reserv to... some other HKNum?

I would absolutely agree with Piet that stepping through recordsets is THE
WORST POSSIBLE way to do this; it can be done as a last resort but an update
query would always be preferable.
 
Back
Top