Am I thinking about RecordSets correctly?

mvanella

Registered User.
Local time
Yesterday, 21:50
Joined
Nov 15, 2012
Messages
29
I've been coding in VBA for over a year now but I haven't really dug into recordsets and handling databases as objects too much. From what I have read DAO seems to be the most appropriate for my use, but that's the most I've been able to grasp on the subject.

What I am trying to accomplish is to take a single record from a table (which has hours worked on an RMA), put the record data into a popup form that allows the user to edit the data and hours and either save the changes or cancel.

I am slightly confused as to how a recordset will act. I set a DAO.Database object then opened a recordset with a select/where statement. Now with the form acting on the single record in the recordset, if I commit changes made in the form to the recordset, can I commit those changes back into the table? How would I go about doing that? I don't need any specific code, just some ideas or tips and general information on how recordsets work and act. Links to articles are great also.

Here's what I have so far:
Code:
Private Sub Form_Load()
    'Check for OpenArgs
    If Not IsNull(OpenArgs) Then
        Dim intRecordID As Integer
        'Check to make sure that OpenArgs is numeric.
        If !IsNumeric(OpenArgs) Then
            MsgBox "Openargs is not a numeric value. Ending macro and closing form."
            DoCmd.Close
        End If
        intRecordID = OpenArgs
        MsgBox "RecordID read as: " & intRecordID
    'Close if no OpenArgs
    Else
        MsgBox "Openargs is null. No data loaded. Now closing form."
        DoCmd.Close
    End If
    
    
    'Open recordset from OpenArgs ID
    Dim strSQLGetID As String
    strSQLGetID = "SELECT * FROM tblRMALabor WHERE ID = " & intRecordID
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    rs = db.OpenRecordset(strSQLGetID, dbOpenDynaset) 'Not sure what other options to specify here, if any

    'Anything else here???
End Sub

Assuming that I use this recordset as the source, all the textboxes on the form would be bound to the fields from the recordset.

Obviously if the user hits the cancel button, the form closes and does not commit any changes to the record. But if the user clicks save, it would verify the data, and commit the changes. How should the changes be committed? Is that something done through a recordset or do I have to execute an update statement to set all the current data in the recordset to the entry in the tblRMALabor where the ID matches?
 
I guess one way to sum this all up is, do recordsets contain data by reference from a table, or by value?
 
What you are trying to do is quiet right, but might not be the best way. Recordsets are great if you need to create a Dynamic query or loop through the data. Something along those lines...

If you want is just to be able to edit the table entry via a Form create a bound form, however you have
What I am trying to accomplish is to take a single record from a table (which has hours worked on an RMA), put the record data into a popup form that allows the user to edit the data and hours and either save the changes or cancel.
This still is possible with a bound Form.. What you need to do is assign this query as the Record Source of this Form in the Form_Open event rather than the On_Load... This is mainly because OnOpen you can actually cancel the Form before it Loads, in other words if they fail the Checks you have you can just use Cancel = True instead of manually closing using DoCmd.Close to close the From even before it is actually loaded..

Code:
Private Sub Form_Open(Cancel As Integer)
    'do your checks
    Me.RecordSource = strSQLGetID
End Sub
This way there is no need for you to use UPDATE query.. You can handle the Cancel or Save using the Form_BeforeUpdate.
 
The simplest way of all is to use the where argument of the OpenForm method to control the record to be displayed on the popup form. If you want to be able to use the form to add records, you may need code to populate a foreign key. If that is the case, use the popup form's BeforeInsert event so you don't dirty the form before the user does.

Code:
Me.SomeFK = Forms!frmmain!SomPK
 

Users who are viewing this thread

Back
Top Bottom