Access Update many records at once

[Matt_T_hat]

I have posted a similar question to this before so it's possible that given time I will solve this myself. But the help would be real good all the same.

I am working on a database project where there is a table of "global" values. It has two fields PK and Value both text. PK is the Primary Key (Duh).

The form is designed from unbound controls (mainly textboxes and comboboxes) one for each record. I have set the tag value to hold the PK of the destination record and the control clearly will hold the value for the Value field.

The plan is to have a module that contains a function of sub that will cycle through the controls on the form and add the values to the correct record.

Not knowing what to do I have started to plan out said Sub with the result that I have pritty much of the code fairly well written. However I am fairly new to the whole dealing with objects thing and would apriciate some feed back on my code (untested) and some clues on how to do the update records.

Sub update(frm As Form)
    On Error Resume Next
    
    Dim Ctl As Control
    Dim Obj As Object
    Dim CtlType As String
    Dim MeRecord As String
    Dim MeValue As String
 
    'set the controls' values to the DB using the
    'Tag property to locate the PK
 
    For Each Ctl In frm.Controls
        
        If CtlType = "Label" Then
            'do nothing
        ElseIf CtlType = "TextBox" Then
            MeRecord = Ctl.Tag
            MeValue = Ctl.Text
        ElseIf CtlType = "ComboBox" Then
            MeRecord = Ctl.Tag
            MeValue = Ctl.Value
        Else
            'Wonder what the hell is going on... Buttons
        End If
        'Take to values and now add them to the data base
        'Um find record where PK=MeRecord Set Value = MeValue
    Next
End Sub

if you really want to know the same topic post in this forum was called: Access Module Code: rst.movenext I assume... but that aside I'm a bit stuck today... the answers in other thread might help a bit but it deals with Access to Word more'n anything else.

Thanks in advance

[Granty]

To update, either open a recordset using the PK and then edit the record, or create an UPDATE sql statement and put the PK in the 'where' clause to get the correct record.

Are all of the records in the same table? If not you are going to have problems making this generic.

[Matt_T_hat]

The table is made of two fields

PK : TEXT
Value : TEXT

That's it.

All records are represented on the form at once.

[Granty]

Well an update SQL statement would look like this:

Dim strSQL as String

strSQL = "UPDATE tblName SET Value = '" & MeValue & "' " & _
               "WHERE PK = '" & MeRecord & "' "

 Connection.Execute strSQL

[Matt_T_hat]

That would be ok I guess - the idea of the code is that I will not know what the PKs are (stored in the TAG value) and do not know what the controls will actually be other than limited to text box and combo at this stage. So I need to loop through and get the info out and write it into the table. I would imagine that this would work then??

Sub update(frm As Form)
    On Error Resume Next
    
    Dim Ctl As Control
    Dim Obj As Object
    Dim CtlType As String
    Dim MeRecord As String
    Dim MeValue As String
    Dim strSQL as String
 
 
    'set the controls' values to the DB using the
    'Tag property to locate the PK
 
    For Each Ctl In frm.Controls
        
        If CtlType = "Label" Then
            'do nothing
        ElseIf CtlType = "TextBox" Then
            MeRecord = Ctl.Tag
            MeValue = Ctl.Text
        ElseIf CtlType = "ComboBox" Then
            MeRecord = Ctl.Tag
            MeValue = Ctl.Value
        Else
            'do nothing with this one
        End If
        strSQL = "UPDATE tblName SET Value = '" & MeValue & "' " & _
               "WHERE PK = '" & MeRecord & "' "
    Next
End Sub

is the rest of the code good I wonder.... well time to find out!

EDIT: SPot the error!

[Matt_T_hat]

The big question is really the moveing through the collection of objects on a form and doing X for each Y in Z kinda thing.

[Granty]

Dim ctl as Control

For each ctl in Me.Controls

If TypeOf ctl is Textbox Then
  
   'do stuff
ElseIf TypeOf ctl is ComboBox

   'do more stuff
End If

etc etc

[Matt_T_hat]

I was close... thankyou if not for your timely post I would have remained only very close ...maybe forever.

[Matt_T_hat]

umm... ERROR: module is not a valid type??

any clues??

[Granty]

???

You are putting this in a form right?

Post the code that is erroring pls?