Loop and MoveNext problem!

[Markvand]

hello again,

I'm in trouble again and need help, I have a query which has a Id of set and particular pieces for it. On the form that I've got user selects the Id of set and gets all the pieces which are in it. Each piece has its own report which is printed afterwards.

What I want to do, is to have a sort of running number which will be printed on the report (haven't done this piece yet) thus I want to save the Id_piece in a table with the autonumber filed (pk). As a result, will have a uniqe number on the report and can easily go back to particular piece.

Here is the code I came up with, I have a problem with loop and MoveNext

Any help would be appreciated


Private Sub RunNo_Click()

On Error GoTo Err_Save_Click

Dim dbs As Database
Dim qdf As QueryDef
Dim id As String
Dim i As Single
Dim Response, resp
Dim rstName As Recordset
Dim prm As Parameter

Set dbs = CurrentDb()

Set qdf = dbs.QueryDefs("INQ_BOM")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rstName = CurrentDb.OpenRecordset("TAB_MM_Autonumber", dbOpenDynaset)


Response = MsgBox("Do you want to pick that set?", vbYesNo, "Message")

If Response = 6 Then

DoCmd.OpenReport "Metric", acViewPreview

End If

resp = MsgBox("Was the document printed correctly?", vbYesNo, "Message")

If resp = 6 Then

Do While Not qdf.OpenRecordset.EOF

id = qdf.OpenRecordset.Fields("Id_piece")

rstName.AddNew
rstName.Fields("Id_seti") = id
rstName.Update

qdf.OpenRecordset.MoveNext

End If

Loop
rstName.Close

Exit_save_Click:
Exit Sub

Err_save_Click:
MsgBox Err.Description
Resume Exit_save_Click

End Sub

 

[FoFa]

Should that not be:
Do While Not rstName.EOF

id = rstName.Fields("Id_piece")

rstName.AddNew
rstName.Fields("Id_seti") = id
rstName.Update

rstName.MoveNext

End If

Loop

 

[Markvand]

hi there,

unfortunately not

this line: rstName.Fields("Id_seti") = id

should save this in a table, so Id_seti it's a field in a table

 

[ecniv]

Do While Not qdf.OpenRecordset.EOF

id = qdf.OpenRecordset.Fields("Id_piece")

rstName.AddNew
rstName.Fields("Id_seti") = id
rstName.Update

qdf.OpenRecordset.MoveNext

End If

Loop 
rstName.Close

A different way to open the query as a recordset?? Not heard of that done like that. Are you sure its returned a recordset?

Also, if its just filling another table, and there is not likely to be much data, perhaps an Update query could be more appropriate?

 

[Markvand]

hi Ecniv

you have to treat my piece of code as a bid of struggle anyway, yes I wanted to open a query as recordset and loop through the records, if u have any suggestion how to do this in civilised way, shoot me. You gave a good hint with the update query, though.

Cheers mate

Mark

 

[The_Doc_Man]

OpenRecordset isn't a property you can examine or subdivide. It is a formal METHOD of a recordset object.

The syntax - qdf.OpenRecordset.Fields("name") - is meaningless.

If you open a recordset as myRS = db.OpenRecordset(etc etc), then you can do a myRS.MoveNext or refer to myRS.Fields("Name") and it has meaning. But I'll admit I'm surprised your code didn't barf all over you when you tried this. Or perhaps it did and that's your problem?

 

[Markvand]

hello Doc_Man

yep it is my problem, hopefully one day it will be better ,

"If you open a recordset as myRS = db.OpenRecordset(etc etc), then you can do a myRS.MoveNext or refer to myRS.Fields("Name") and it has meaning."

that's quite clear, but how would you get around examine records in query and perphaps store them in a table (they come from a table I know, but it was my idea to get this running number thing going).

Regards

 

[Markvand]

I've just used append query and it does the job, the simplest ideas are the best.

Thanks again Ecniv,

Cheers Doc

Regards

Mark