Access goto next record for two tables

[laavista]

I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want to
go to the first record in HK_active and the first record in Reserv_active and
update each table from a value in the other table.
goto 1st record in t_HK
goto 1st record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
goto the next record in t_HK
goto the next record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
etc.

Right now, I'm just trying to move to the next record in each form. When
the procedure starts, I am on record 1 for both tables. Now I need to move
to the 2nd record for both tables.

DoCmd.OpenForm "f_HK"
DoCmd.OpenForm "f_Reserv"


Forms!f_HK!HKNum.SetFocus
longintHKNum = Forms.f_HK.HKNum
' at record 1 for t_HK

Forms!f_Reserv!TripsReservID.SetFocus
longintTripsReservID = Forms.f_Reserv.TripsReservID
' at record 1 for t_Reserv


Forms!f_HK!HKNum.SetFocus
DoCmd.GoToRecord , , acNext
longintHKNum = Forms.f_HK.HKNum
' stayed on record 1

Forms!f_Reserv!TripsReservID.SetFocus
DoCmd.GoToRecord , , acNext
longintTripsReservID = Forms.f_Reserv.TripsReservID
' Now at record 3 ???

Your help would be GREATLY appreciated.

 

[Jack Cannon]

It appears that there is some form of a problem with the
SetFocus command. Perhaps the GoToRecord command is
executing before the focus actually gets set.

Have you tried modifying the GoToRecord commands as follows?
DoCmd.GoToRecord acDataForm, "f_HK", acNext
DoCmd.GoToRecord acDataForm, "f_Reserv", acNext

I don't think that you will need the SetFocus command
if you modify the GoToRecord commands as above.

I also noticed that you changed the "!" in the first statement
to a "." in the second statement. I don't know if that
matters but it could certainly be criticized as poor form.
You probably should use the "!" in both commands.

Forms!f_HK!HKNum.SetFocus
longintHKNum = Forms.f_HK.HKNum

Forms!f_Reserv!TripsReservID.SetFocus
longintTripsReservID = Forms.f_Reserv.TripsReservID

Jack Cannon

 

[Jack Cannon]

A better approach to this problem would be to use recordsets instead of
using forms to manipulate the data.

Jack Cannon

 

[John W. Vinson]

I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want to
go to the first record in HK_active and the first record in Reserv_active and
update each table from a value in the other table.

Ummm....?

A very simple update query joining the two tables. No code, no forms, no
looping needed.

OTOH if you're assuming that the records *IN THE TABLES* will maintain a
strict order, and that you can assume that the 317th record in HK_active has
something to do with the 317th record in Reserv_active, you're
misunderstanding how tables and relational databases *work*. That's
appropriate logic for spreadsheets, but not for databases! Tables should be
viewed as unordered "buckets" of data; if you want to associate a record in
one table with a corresponding record in another table, you must - no
options!!! - use the value of some common field between the two tables to do
so.

 

[laavista]

Thank you so much for the responses. This really helped. I should use
recordsets, but I'm new to visual basic and wasn't sure if I could figure out
how to do it.

I will try the solutions you presented.

My actual task is to re-assign credits (in t_HK) to records in t_Reserv. I
need to assign the oldest credits in t_HK to the oldest records in t_Reserv.
I created a query to sort both and thought I would just loop through each
table and do the re-assignment.

Thanks again for your help.

 

[John W. Vinson]

My actual task is to re-assign credits (in t_HK) to records in t_Reserv. I
need to assign the oldest credits in t_HK to the oldest records in t_Reserv.
I created a query to sort both and thought I would just loop through each
table and do the re-assignment.

If the two tables have the same number of records you can do so by looping
through Recordsets - based on Queries with a sort on the datefield, *not*
directly based on the Table. You simply can't count on the table records being
in any particular order; you must specify the sort order in setting up the
recordset. Other than that, the recordset solution may be the best of a bad
lot. Any chance that you could normalize your table structures so that you
don't store credits redundantly in two tables, and don't need to do this!?

 

[laavista]

The tables will not have the same number of records. I think that the
tables are normalized--I'm just putting a "reference" field in the one table
to show which credits are being used. For example: HK's auto number will be
placed in Reserv's record to show that those credits are being used for that
specific reserv record. I also am adding the Reserv's auto number in HK's
record to show which of those are being used. Make sense?

 

[John W. Vinson]

The tables will not have the same number of records. I think that the
tables are normalized--I'm just putting a "reference" field in the one table
to show which credits are being used. For example: HK's auto number will be
placed in Reserv's record to show that those credits are being used for that
specific reserv record. I also am adding the Reserv's auto number in HK's
record to show which of those are being used. Make sense?

No. It doesn't make sense to me, because I guess I'm not understanding.

You have 410 records in one table, and 513 records in the other table. You
need to put a value from one of those 410 records into one of the 513 records.
HOW can you tell *WHICH* record provides the value, and which receives it? Is
there any field in the two tables that lets you identify which record is
which?

 

[laavista]

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.

 

[John W. Vinson]

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...)

And there is no connection between the two tables?

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.

Into WHICH RECORD of the HK table? How can Access determine which ReservID
goes with which HKNum? How can *you* determine which goes with which?

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.

I'm sorry, but without a clearer understanding of your business model (what is
a "credit"? what does it mean to "assign" a credit? what is the significance
of the "start date"?) I simply don't understand your logic.