need help identifying table value as a variable in an sql command

[MM70]

I have no real experience programming, i have been teaching myself things as i go along.

Here is my issue.

I am trying to create a from where you can enter an id and a date range and hit the button on the form to activate code that does the following:

query1: creates a new table (table_data) and inserts id, field1 based on the data entered in the id textbox on the form from an external database(db1).

query2: creates another table with details fields ex. detail1, detail2, detail3 based on the data in field1 from the above table.

here is my problem. I have the loop command working but i don't know how to set the variable in a way that the sql insert in the loop will pick up the next record in the column i want and insert the detail data for that record into table1 that was created.

for example:


ID field 1
--- | ------
223 | 12345
111 | 23421
121 | 14521
122 | 33453

how do i reference the value of field one in my loop so it will start at the beginning all the way to the end of the column and run my insert sql into another table which houses the end result.

another thing i should add is that the loop which contains the sql insert into the final table is pulling data from another table (table1) some records may have multiple records corresponding to the value of field1 in table_data. for instance, record 12345 above may have 8 records that fit the criteria of the insert command and are inserted into table1 which is the end result table.

If i manually set the variable to a value i know is right, it will work like i want it to. The problem i face is how to declare the field value from the table in the loop code.

 

[Privateer]

MM70,
No programming experience but you are doing For Loops, not bad. This should rock your world. You create a recordset of table1 (which is like a copy in ram) and use the movenext command to progress through the table and the EOF (end of file) to know when to stop. Try this.

Dim dbs As DAO.Database
Dim rstNewTables As DAO.Recordset

Set dbs = CurrentDb

Set rstNewTables = dbs.OpenRecordset("table1", dbOpenSnapshot)

Do Until rstNewTables.EOF = True

'place your new table code here
'to reference the records as you move through the code
rstNewTables!TableID
rstNewTables!Field1
'take note, the exclaimation point for field names, a period for commands

rstNewTables.MoveNext

Loop

Good luck with it.

Privateer

 

[Privateer]

Just read over my post and realized I forgot one moderately necessary command. Before you start the do until loop, you want to make sure your "cursor" is on the first record of the recordset. So, add this line.
rstNewTables.MoveFirst
Again, make sure this is above/outside the loop otherwise infinite loop time.
Privateer