Unbound Form Write Conflict

[mjvoce]

I currently have an unbound form (parent form) for data entry. And a datasheet subform based on a query that pulls accounts the user needs to work.

My reasoning behind using an unbound form is because the user needs the option to update multiple records at once and I'm unaware of how to do this with a bound form.

I just got done writing the vba to pull individual records and update them but I keep getting two different pop ups. (pictures attached)

Here is the code to pull the record into the unbound form. Located in the datasheet subform.

Private Sub Form_Current()
    Dim par As Access.Form, ctl As Access.Control
    
    On Error GoTo ErrorHandler
    
    Set par = Me.Parent
    
    For Each ctl In par.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            If Len(ctl.ControlSource) = 0 And ctl.Tag <> "Search" And ctl.Tag <> "Header" Then
                ctl.Value = Me.Recordset.Fields(ctl.Name)
            End If
        End If
    Next
ErrorHandler:
    If Err.Number = 2113 Then Exit Sub
End Sub

And here is the code to save any changes the user makes. Located in the unbound form.

Private Sub cmdSubmit_Click()
    Dim rs As DAO.Recordset, ctl As Access.Control
    
    Set rs = Me!subDatasheet.Form.Recordset
    
    With rs
        .Edit
            For Each ctl In Me.Controls
                If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
                    If Len(ctl.ControlSource) = 0 And ctl.Tag <> "Search" And ctl.Tag <> "Header" Then
                        rs.Fields(ctl.Name) = ctl.Value
                    End If
                End If
            Next
        .Update
    End With
End Sub

Any ideas how to avoid these pop ups? And I'm very open to an alternative way to do this but would like to maintain the functionality of being able to update a single record or multiple records from the same form. I'm new to access and databases in general. I'm use to working within excel.

 

[Galaxiom]

My reasoning behind using an unbound form is because the user needs the option to update multiple records at once and I'm unaware of how to do this with a bound form.

What you need is a Transacted Bound Form.

Have look at this thread. A sample is provided in Post 9.

 

[mjvoce]

Interesting example but I'm unsure how I could leverage it to solve my issue. Perhaps there was a miscommunication on my part.

By update multiple records at once I mean the user enters data into three textboxes, hits update, and it updates three different fields for every record within a recordset with the data entered in the textboxes.

So, if there's 10 records the user only needs to enter the data 3 times instead of 30 times.

 

[Galaxiom]

By update multiple records at once I mean the user enters data into three textboxes, hits update, and it updates three different fields for every record within a recordset with the data entered in the textboxes.

If you are copying the same data into three fields on multiple records then perhaps this data should be a single record in a related table.

How much do you understand about Normalization?

 

[mjvoce]

I understand normalization but this data will be dates that need to be tied to individual accounts and may not always be the same for a given set of accounts.

 

[mjvoce]

So, I did attempt to change my tables around some. Instead of having multiple date fields in the master table I made a table to house all the dates with a field for datetype to identify what action within the process the date corresponds with. However now I'm completely lost on how to even use a form to add and edit dates. I've attached a picture of my current table & relationship structure.

 

[MarkK]

What do those dates represent? Are there a series of dated deadlines or thresholds or something that can be attached to a case? If so, I would expect to see a table called Deadline, or Threshold. A date simply describes a location in time, but without knowing what it is that is so located, what is the use of a date?

 

[arnelgp]

Private Sub cmdSubmit_Click()
Dim rs As DAO.Recordset, ctl As Access.Control

Set rs = Me!subDatasheet.Form.Recordset

With rs
.Edit
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
If Len(ctl.ControlSource) = 0 And ctl.Tag <> "Search" And ctl.Tag <> "Header" Then
.edit
.Fields(ctl.Name) = ctl.Value
.update
End If
End If
Next
.Update
End With
End Sub