HELP! Add record to table through VBA

[nonturbo]

I've got a button on a form that runs the following code block:

    Dim ty As Integer
    Dim tp As Integer
    Dim ny As Integer
    Dim np As Integer
    Dim rs As Recordset
    Set rs = Me.RecordsetClone
    rs.MoveLast
    ty = rs("Year")
    tp = rs("Period")
    Call GetNextPeriod(ty, tp, ny, np)
    rs.AddNew
    rs("Year") = ny
    rs("Period") = np
    rs.Update
    rs.Bookmark = rs.LastModified
    Me.Bookmark = rs.Bookmark
    rs.Close

It works fine. The form this code is from, is linked to a table, called BrokerData, as the form's record source..

THE PROBLEM: I'd like to move this code from the click event of a button on the form, to a module not associated with the form or directly associated with the BrokerData table. Understand? If so, please help! Thanks!

-Jeremy

 

[nonturbo]

I think what needs to be modified is the line,

Set rs = Me.RecordsetClone

...But I'm not sure how to do so. I don't think Set rs = BrokerData would work, seems to easy..

 

[JennyS]

You might try the following:

Public NewProcedure(frm as form)
Dim ty As Integer
Dim tp As Integer
Dim ny As Integer
Dim np As Integer
Dim rs As Recordset
Set rs = frm.RecordsetClone
rs.MoveLast
ty = rs("Year&quot
tp = rs("Period&quot
Call GetNextPeriod(ty, tp, ny, np)
rs.AddNew
rs("Year&quot = ny
rs("Period&quot = np
rs.Update
rs.Bookmark = rs.LastModified
frm.Bookmark = rs.Bookmark
rs.Close
end function

 

[nonturbo]

That solution doesn't seem as if it'll work for my problem. What the code I posted at the start of the thread is attempting to do is add a record to a table (called BrokerData). The original code came from the click event of a button on a form that was linked directly to the table BrokerData. Now I've found that instead of having the user click a button to add this record, it should automatically be done at the end of another procedure that adds other records to other tables...

(after a moment of head scratching).. I found a solution that works! <BG>

...In case anyone is interested...

    Dim ty As Integer
    Dim tp As Integer
    Dim ny As Integer
    Dim np As Integer

    Dim BrokerData As Recordset
    Set BrokerData = d.OpenRecordset("BrokerData")
    BrokerData.MoveLast
    ty = BrokerData("Year")
    tp = BrokerData("Period")
    Call GetNextPeriod(ty, tp, ny, np)
    BrokerData.AddNew
    BrokerData("Year") = ny
    BrokerData("Period") = np
    BrokerData.Update
    BrokerData.Bookmark = BrokerData.LastModified
    'Me.Bookmark = BrokerData.Bookmark
    BrokerData.Close

 

[young]

Make a append query and input criteria as you want and then view the SQL, you will find the answer.