Read query, build another table based on content (2k)

[chrisseguin]

Want to read field1 in a raw data query (rst) and create another table/query (tsr) based on field1 content. I get the LINEID field written to the new table to start the record but the Update_prod: sub fails with a "NO CURRENT RECORD" message leaving the PROD1 field blank. Three fields in new table: LINEID, PROD1, SOFT1. Each time field1 ="LineID", create new record and fill in 3 fields.


'open query-cnf-raw, read field1, build query-cnf-scrub records/fields based on field1 content
Dim rst As DAO.Recordset
Dim tsr As DAO.Recordset
Dim dbs As DAO.database
Set dbs = CurrentDb
Set rst = dbs.openrecordset("query-cnf-raw", dbopendynaset)
Set tsr = dbs.openrecordset("query-cnf-scrub", dbopendynaset)
Do While Not rst.EOF
rst.Edit
id_update:
If Left(rst!field1, 4) = "Line" Then GoTo update_id
If Left(rst!field1, 7) = "prodnam" Then GoTo Update_prod
If Left(rst!field1, 7) = "softdld" Then GoTo Update_soft
'End If
Continue:
rst.MoveNext
Loop
Exit Sub
update_id:
tsr.AddNew
tsr!LineID = Mid(rst!field1, 10, 8)
tsr.Update
GoTo Continue
Update_prod:
tsr.Edit
tsr!prod1 = Mid(rst!field1, 11, 22)
tsr.Update
GoTo Continue
Update_soft:
tsr.Edit
tsr!soft1 = Mid(rst!field1, 14, 22)
tsr.Update
GoTo Continue
End Sub

[WendellB]

You appear to have a logic error, as you need to set all three fields before you actually do the tsr.Update. The way you code is structured, you save the record as soon as you process the LineID, and Prod1 and Soft1 will have Null values. Are you trying to create a three field table from a single field table where every three records represent one record in your new database? If so, you might try an If ElseIf construct and put the code all in one procedure. The only issue you need to worry about is if there are errors in the input table such that things are out of sequence.

[patt]

<P ID="edit" class=small>(Edited by patt on 14-Jan-03 07:17. Add comment about input file order.)</P>You have another problem, and that is that you have not read the tsr record before trying to update it in Update_prod: and Update_soft:, I believe this is the reason for the "NO CURRENT RECORD" error message.

Does each input record always have the 3 fields? If so, change your code to read 3 records at a time before doing the AddNew...Update.
Eg.
tsr.AddNew
rst.MoveNext
tsr!LineID = Mid(rst!field1, 10, 8)
rst.MoveNext
tsr!prod1 = Mid(rst!field1, 11, 22)
rst.MoveNext
tsr!soft1 = Mid(rst!field1, 14, 22)
tsr.Update

This presumes that the order of the input file is:
"Line"
"prodnam"
"softdld"

Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

[chrisseguin]

The problem was the tsr.update. I was unclear on it's function and thought it was needed after each assignment of
tsr! instead of just once per record. I fixed the logic and it works as needed. Thanks for the help.

[chrisseguin]

The problem was the tsr.update. I was unclear on it's function and thought it was needed after each assignment of
tsr! instead of just once per record. I fixed the logic and it works as needed. A typical raw table has multiple Prod and Soft per Line so the number of records to read is variable. I'm having to code in a counter to allocate data to multiple Prod fields (prod1, prod2,etc.) until the next record with LINE comes up and reset to zero. That appears to be working as needed. Thanks for the help.