Difficulty working with recordsets (A97-SR2)

Topic

New Reply

I am missing the total concept of recordsets and how to manipulate them. Have read numerous books and have not found an explanation of how to extract one record from a recordset and relocate it to an empty table. If anyone knows of a book that puts the techniques into understandable language, please advise. What I am trying to achieve is to have a form that someone can enter many different workorder numbers into – then they press the command button and it will go through each number, one at a time, processing all the data and printing a batch or workorders. The following code is as far as I have gotten – I am stuck!! I have also attached a one form, one table database to demonstrate.

Private Sub Command1_Click()

Dim dbs As Database, rstWorkorderNumber As Recordset
Dim strWonum As String

Set dbs = CurrentDb
Set rstWorkorderNumber = dbs.OpenRecordset(“WorkorderNumber”)

DoCmd.OpenForm “frmWkorderNum”
DoCmd.Maximize

Do
rstWorkorderNumber.MoveFirst
strWonum = rstWorkorderNumber!Wonum

‘=================================================================
‘ What I wish to do is to eliminate the following message box and
‘ instead move the recordset, (strWonum) via code to a temporary
‘ table. Then I will try to create all the code so I can use
‘ that record to generate the reports for that workorder number.
‘ There will be many tables, many queries and many separate reports
‘ utilized for each workorder number.
‘==================================================================

MsgBox “The workorder number being processed is:” _
& strWonum, vbOKOnly, “Rons Message Box!”

rstWorkorderNumber.Delete
DoCmd.Requery

If rstWorkorderNumber.RecordCount = 0 Then
MsgBox “There are no more workorders to process”

Exit Do

End If

Loop While rstWorkorderNumber.RecordCount > 0

DoCmd.Close acForm, “frmWkorderNum”, acSaveNo
rstWorkorderNumber.Close
Set dbs = Nothing

End Sub

Reply | Quote

Replies

I’m not quite sure what you’re trying to do here. In later versions of Access, you could do this with a disconnected recordset and not use a table at all, but using DAO code in 97, you can’t do exactly what you’re trying to do … at least, not the way you’re trying to do it. Your recordset and your form appear to be two unrelated objects, so I don’t see what the form is for.

Let’s simplify. A recordset is something like a table or query but it resides in memory. You can change data in it and even add and delete records to the underlying data structure, but once you close the recordset, it no longer exists. Any changes you made to the underlying data structure remain, but the recordset itself is gone. If you want to add records to a table, you open *another* recordset and step through your first one getting values from it and inserting them into new records appended to the second one. Does that clarify it for you?

The problem I see here is that you haven’t provided a way for your user to enter any data into the form, or at least not in what you posted. Nor have you included any code that gets data *from* the form. You seem to be trying to step through a table and add every single record to another table, which doesn’t make a lot of sense.

Reply | Quote

I don’t think I would do it like that, However if you want to you could try:

Create the target table (once only outside the VBA.)

before you start run a delete query to empty the target table

then use an Insert SQL query to add the new record to the table

populate its fields

and do update

Sorry this is only a partial answer but it might set you on the right track (if you MUST do it this way)

Reply | Quote

Sometimes I think that people try to make things a lot more difficult than they need to be. I stepped back and looked at what I was trying to achieve with my limited programming abilities. I tried to do it with relatively simple queries. Sure enough, I got just what I needed with one append and two delete queries. Worked the SQLs into the Do/Loop and If/Then statements and got just what I needed. Thanks for politely reminding me to refocus.

Reply | Quote