undo on an unbound form

[icemonster]

i found this code on the internet, i think it was roger's library. basically he made an undo code on an unbound form, it does work on this thing. but the key difference with his and mine is that i use a sql string to fill my record while he using a variable to open it. can anyone help me integrate his code with mine?

his code for form onload:

Private Sub Form_Load()
    On Error Resume Next
    Dim ct As Access.Control
    
    Set colCt = New Collection
    Set colCtVal = New Collection
    Set colFieldVal = New Collection
    
    ' Populate collection colCt with control objects
    ' representing unbound controls used for entering
    ' or editing data.
    ' Imp: All controls are named same as corresponding fields.
    For Each ct In Me.Controls
        If ct.ControlType = acTextBox Or _
                    ct.ControlType = acComboBox Then
            ' Exclude Pk control and excluded controls
            If ct.name <> PkFieldName And _
                            InStr(ExcludedFieldNames, _
                            "/" & ct.name & "/") = 0 Then   ' (B)
                colCt.Add ct, ct.name
            End If
        End If
    Next
    
    Set db = DBEngine(0)(0)
    Set rst = db.OpenRecordset(TableName, dbOpenDynaset)
    
    IsDirty = False
    DataMode = "Add"
    P_HighLight -2147483633
    P_DisableCmdBtns
    
    Set ct = Nothing
    
    ' Note - In statement (B), "/" separator is used at either end
    '            of the field name so as to prevent partial match.
    '            Alse see remarks at (A) in declarations section.
    On Error GoTo 0
End Sub

my function for setting the forms recordset:

Function startprogramdetails()

        strStartSql1 = "SELECT qry_program.id_program, qry_program.program_name, qry_program.program_abbr, qry_program.program_id_program_type, " _
                     & "qry_program.program_type_desc, qry_program.program_description, qry_program.program_createdon, " _
                     & "qry_program.program_createdby_id, qry_program.program_createdby_user, qry_program.program_createdat_ip, " _
                     & "qry_program.program_createdat_pc FROM qry_program "
                     
        strWhereSql1 = "WHERE qry_program.id_program = " & Forms![frm_main_menu]![lstprograms] & " "
        
        strSQL1 = strStartSql1 & strWhereSql1
               
        With Form_frm_details_program
            .RecordSource = strSQL1
            
         IDProgram = !id_program
         Me.program_name = !program_name
         Me.program_id_program_type = !program_id_program_type <-this also causes the field to be null, is it because they have the same name?
         Me.program_abbr = !program_abbr
         Me.program_description = !program_description
         Me.txtprogramid = 
        
        End With

End Function

 

[Pat Hartman]

Access is intended to work with bound forms. You are missing out on all Access has to offer by using unbound forms. You may as well be working in VB.

With unbound forms, you need to do all the work yourself. You wrote code to populate each control, you'll need to write code to undo everything also.

My suggestion is to bind your form. You'll save a huge amount of effort in the long run.

 

[icemonster]

well if i was only dealing with 1 recordset sure, but i am dealing with more than 1 table and using odbc connection for update and inserts. is there any way to do this with an unbound form?

 

[Pat Hartman]

Yes, you can do anything you want with an unbound form. You simply have to code it yourself. I don't have any code I can post because I would never use an unbound form in an Access application. There is simply no reason to. I've been using Access almost exclusively since version 2.0 in the early 90's. Very few of my applications actually use Jet/ACE databases. The majority use SQL Server but many have used Oracle, DB2, Sybase, and names you would not recognize. The point of Access is "access". As long as there is an available ODBC driver, you can use any RDBMS. In fact, several of my applications can swap back end databases on the fly and all my code still works. I do that because it makes it easier for me to do development off site. I can work at home in Jet/ACE or SQL Server and I can work at my client site in Oracle, DB2, etc.

It is quite easy to create querydefs or even SQL strings (if you insist) and use them to modify the RecordSource in the form's Open event.