Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America asked on

Update Table from Unbound Form - Problem with For Loop sequence

I am using a function to update a table based on an unbound form's fields.  Problem is that it is also passing the label controls. Determine control types and bypass all but the actual fields that contain data

Also I am not sure my loop is working correctly,  This is my attempt to prevent the labels as be passed as a control.  Not work right though.  See '>>>>>>>>>>

Thanks,

K
Function UpdateTables(strMain As String, strTable As String)
    On Error GoTo UpdateTables_Error
    Dim rs As DAO.Recordset
    Dim F As Form
    Dim c As Control
    Dim i As Long
    Dim strSQL As String
    Dim strFormField As String
    'Identify the form/subform you're working with
    Set F = Forms(strMain).Form
    ' Open the table to add new records
    strSQL = strTable
    Set rs = Currentdb.OpenRecordset(strSQL)
        rs.AddNew
        For i = 0 To rs.Fields.Count - 1
            For Each c In F
                strFormField = c.Name
                Debug.Print strFormField
   '>>>>>     If rs.Fields(i).Type <> dbText Or rs.Fields(i).Type <> dbMemo Or rs.Fields(i).Type <> dbInteger Or rs.Fields(i).Type <> dbDate Then
                    GoTo ResumeNext
                ElseIf rs.Fields(i).Name = strFormField Then
                    rs.Fields(i).Value = Nz(F(strFormField).Value)
                End If
ResumeNext:
            Next c
        Next i
        rs.Update
    rs.Close
    Set rs = Nothing
    'Now clean up the form so the user can add a new record if he wants
    ClearFormData strMain
    
 
UpdateTables_Error:
    Select Case Err
        Case 0
            Exit Function
        Case Else
            MsgBox "Subroutine Functions" & ".UpdateTables." & vbCrLf & vbCrLf & "You have error number " & Err & ".  " & Err.Description
    End Select
End Function

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Karen Schaefer

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Karen Schaefer

Jeff,

Thanks for the assist - I am still having difficulty pass the value of the unbound forms fields' data.

I am currently getting can't find aggregate in the funciton on the "Tech Person" field name.

Please take a look at my latest attempt at the code and let me know what I am missing.
Not sure my loops are correct.
Thanks,

Karen
Private Sub cmdApproval_Click()
    Dim rs As DAO.Recordset
    Dim F As Form
    Dim c As Control
    Dim i As Long
    Dim strSQL As String
    Dim strFormField As String
    'Identify the form/subform you're working with
 
   On Error GoTo cmdApproval_Click_Error
    Set F = Forms!frmEmergentWork.Form
   
    For Each ctl In Me.Controls
        If ctl.Tag = "C" Then
            If IsNull(ctl) Then
                Call MsgBox("There is Data Missing.", vbExclamation Or vbDefaultButton1, "Warning")
                ctl.SetFocus
                Exit Sub
            Else
                strSQL = "SELECT Tech_Grp_Person, Task_Description, Requested_by, Need_Date, Work_Estimate," & _
                        " Planned_Start, Planned_End, ECD, In_Support_of, Work_Authority, Status" & _
                        " FROM [Emergent Work]"
                Set rs = Currentdb.OpenRecordset(strSQL)
                rs.AddNew
                For i = 0 To rs.Fields.Count - 1
                    For Each c In F
                        strFormField = c.Name
                        Debug.Print strFormField
                         If c.ControlType <> acLabel Then
                            If rs.Fields(i).Name = strFormField Then
                                rs.Fields(i).Value = Nz(F(strFormField).Value)
                            Else
                                GoTo ResumeNext
                            End If
                        End If
                    Next c
ResumeNext:     Next i
                    rs.Update
                rs.Close
                Set rs = Nothing
            End If
        End If
    Next ctl
        Set rs = Currentdb.OpenRecordset("Select Max(Seq_NO) as ID, Tech_Grp_Person from [Emergent Work]")
        Do Until rs.EOF
            gWR = rs.Fields("ID").Value
            Debug.Print rs.Fields("Tech_Grp_Person")
            gTP = rs.Fields("Tech_Grp_Person").Value
        Loop
    EmailMgmtApprReq
   
   On Error GoTo 0
   Exit Sub
 
cmdApproval_Click_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdApproval_Click of VBA Document Form_frmEmergentWork"
End Sub

Open in new window

ASKER
Karen Schaefer

My current code is adding 1/2 dozen records with just the Tech_Grp_Person - all the other fields are still empty.  I know someting wrong with my loops.

Thanks,

K
ASKER
Karen Schaefer

I need to chk for nulls first and if null stop code then chk for field types and tags.
then
Still having an issue with my loops.  I am getting 1/2 doz.  records where I should be getting 1 record. and only 1 field is updated - just the 1st field.  can you help me with the correct order of my loops.  I have been playing around with them all afternoon..

Thanks,

Private Sub cmdApproval_Click()
    Dim rs As DAO.Recordset
    Dim F As Form
    Dim c, c1, ctl, ctl1 As Control
    Dim i As Long
    Dim strSQL As String
    Dim strFormField As String
    'Identify the form/subform you're working with
 
   On Error GoTo cmdApproval_Click_Error
    Set F = Forms!frmEmergentWork.Form
   
    For Each ctl In Me.Controls
          If ctl.Tag = "C" And ctl.ControlType <> acLabel And IsNull(ctl) Then
            Call MsgBox("There is Data Missing.", vbExclamation Or vbDefaultButton1, "Warning")
            ctl.SetFocus
            Exit Sub
        End If
    Next ctl
           strSQL = "SELECT Tech_Grp_Person, Task_Description, Requested_by, Need_Date, Work_Estimate," & _
                            " Planned_Start, Planned_End, ECD, In_Support_of, Work_Authority, Status" & _
                            " FROM [Emergent Work]"
    For Each ctl1 In Me.Controls
        If ctl1.Tag = "C" And ctl1.ControlType <> acLabel And Not IsNull(ctl1) Then
            Set rs = Currentdb.OpenRecordset(strSQL)
            rs.AddNew
                'For i = 0 To rs.Fields.Count - 1
            For Each c In F
                strFormField = c.Name
                If rs.Fields(i).Name = strFormField Then
                    rs.Fields(i).Value = Nz(F(strFormField).Value)
                End If
            Next c
            rs.Update
          '      Next i
        End If
    Next ctl1
            rs.Close
            Set rs = Nothing
        Set rs = Currentdb.OpenRecordset("Select Max(Seq_NO) as ID, Tech_Grp_Person from [Emergent Work]")
        Do Until rs.EOF
            gWR = rs.Fields("ID").Value
            Debug.Print rs.Fields("Tech_Grp_Person")
            gTP = rs.Fields("Tech_Grp_Person").Value
        Loop
    EmailMgmtApprReq
   
   On Error GoTo 0
   Exit Sub
 
cmdApproval_Click_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdApproval_Click of VBA Document Form_frmEmergentWork"
End Sub

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
Jeffrey Coachman

Questions:
-What is this code supposed to be doing?
-<I am using a function to update a table based on an unbound form's fields.  Problem is that it is also passing the label controls. >
This is not clear to me...
Passing label "controls" or passing Label control Captions?
Passing them where?

If "Passing Labels" is the only issue here, then make a copy of the form, delete the labels and then run the code.
Does it work?

Let me know

JeffCoachman

JeffCoachman



ASKER
Karen Schaefer

No I do not want to pass the label controls.

What I am trying to accomplish is the following.

1.  Verify certain fields contain data.  If missing the goto to that field and exit sub
2.  Based on Unbound field I want to take the inputted data and update a table with new single record.
3.  Determine the Max(seq_NO) for the current user, then retrieve the Seq_No and the assigned to name to pass to the next step which will email info to a specified person.

My current code is either creating multiple records with just the assigned to inserted into the table or nothing gets insert.  I know it has to to with my placement of the the loops.

Thanks again,

K
.
Jeffrey Coachman

kfschaefer1,

Here is a tip.
Always build you code one "functionality" at a time.
Get the insert working on its own
Get the Validation working on its own
Get theMax sequence working on its own
Get the Email stuff working on its own
*BEFORE* you try combining them.
:-O

Can you post a sample of this db?

JeffCoachman
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Karen Schaefer

sorry can't post db.  and I decided to dump the for loops for the update recordset code. and it is working pretty well.  thanks for your interest.

K
    Set F = Forms!frmEmergentWork.Form
   
    For Each ctl In Me.Controls
          If ctl.Tag = "C" And ctl.ControlType <> acLabel And IsNull(ctl) Then
            Call MsgBox("There is Data Missing.", vbExclamation Or vbDefaultButton1, "Warning")
            ctl.SetFocus
            Exit Sub
        End If
    Next ctl
    strSQL = "SELECT Tech_Grp_Person, Task_Description, Requested_by, Need_Date, Work_Estimate," & _
            " Planned_Start, Planned_End, ECD, In_Support_of, Work_Authority, Status" & _
            " FROM [Emergent Work]"
            Set rs = Currentdb.OpenRecordset(strSQL)
            rs.AddNew
                rs.Fields("Tech_Grp_Person").Value = Me.Tech_Grp_Person.Value
                rs.Fields("Task_Description").Value = Me.Task_Description.Value
                rs.Fields("Requested_by").Value = Me.Requested_by.Value
                rs.Fields("Need_Date").Value = Me.Need_Date.Value
                rs.Fields("Work_Estimate").Value = Me.Work_Estimate.Value
                rs.Fields("Planned_Start").Value = Me.Planned_Start.Value
                rs.Fields("Planned_End").Value = Me.Planned_End.Value
                rs.Fields("In_Support_of").Value = Me.In_Support_of.Value
                rs.Fields("Work_Authority").Value = Me.Work_Authority.Value
                rs.Fields("Status").Value = Me.Status.Value
            rs.Update
        Set rs = Currentdb.OpenRecordset(" SELECT Max([Emergent Work].Seq_No) AS ID, [Emergent Work].Tech_Grp_Person" & _
                            " FROM [Emergent Work]" & _
                            " WHERE ((([Emergent Work].UserName) = Environ$('UserName')))" & _
                            " GROUP BY [Emergent Work].Tech_Grp_Person")
            
     '   Do Until rs.EOF
            gWR = rs.Fields("ID").Value
            gTP = rs.Fields("Tech_Grp_Person").Value
      '  Loop
        rs.Close
        Set rs = Nothing

Open in new window