Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Difficulty working with recordsets (A97-SR2)

    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. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Difficulty working with recordsets (A97-SR2)

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

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Difficulty working with recordsets (A97-SR2)

    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)
    David Grugeon
    Brisbane Australia

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difficulty working with recordsets (A97-SR2)

    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.

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •