Forum Discussion
Which data entry approach to take?
Hi I have a sub form, within a form, which contains combo boxes and text boxes. I want to use it to enter data, creating new records whilst also ideally potentially being able to edit existing records which have been created in the same sitting. I'm not sure which approach to take.
At the moment, I'm using continuous form view, but my code (see below) is playing havoc with the existing records, e.g. CategoryCB\_AfterUpdate causes an update of the CategoryCB combo box to requery the Subcategory combo box (SubcategoryCB) resulting in the existing data being cleared.
I'm pretty new to Access and I'd really appreciate guidance on what approach to take please.
Private Sub Form\_Load()
' Initially show all subcategories, including CategoryID
Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName, CategoryID FROM ExpenseSubcategoryT ORDER BY SubcategoryName"
Me.SubcategoryCB.ColumnCount = 3 ' Set the column count to 3
Me.SubcategoryCB.Requery
End Sub
Private Sub Form\_Current()
' Set the initial state of the CategoryManuallySelected flag
CategoryManuallySelected = False
If Me.NewRecord Then
' Disable the relevant text boxes on form load
Me.MilesTravelledTB.Enabled = False
Me.MonthsUsedTB.Enabled = False
Me.AmountTB.Enabled = False
Me.InvoiceNoTB.Enabled = False
Me.DescriptionTB.Enabled = False
End If
End Sub
Private Sub CategoryCB\_AfterUpdate()
' Clear the SubcategoryCB value and filter based on the selected Category
Me.SubcategoryCB.Value = Null
Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName, CategoryID FROM ExpenseSubcategoryT WHERE CategoryID = " & Me.CategoryCB.Value & " ORDER BY SubcategoryName"
Me.SubcategoryCB.Requery
' Clear relevant fields
ClearRelevantFields
' Set the flag to indicate manual selection
CategoryManuallySelected = True
End Sub
Private Sub SubcategoryCB\_AfterUpdate()
If Not CategoryManuallySelected Then
' Access the CategoryID directly from the combo box
Dim CategoryID As Integer
CategoryID = Me.SubcategoryCB.Column(2)
' Update the CategoryCB with the corresponding category
Me.CategoryCB.Value = CategoryID
End If
' Enable relevant fields
Me.InvoiceNoTB.Enabled = True
Me.DescriptionTB.Enabled = True
' Update column visibility and clear relevant fields
ClearRelevantFields
UpdateColumnVisibility
End Sub
' ClearRelevantFields subroutine definition
Private Sub ClearRelevantFields()
Me.MilesTravelledTB.Value = ""
Me.MonthsUsedTB.Value = ""
Me.AmountTB.Value = ""
Me.InvoiceNoTB.Value = ""
Me.DescriptionTB.Value = ""
End Sub
Private Sub UpdateColumnVisibility()
Select Case Me.SubcategoryCB.Value
Case 16 ' Subcategory for Miles Travelled
Me.MilesTravelledTB.Enabled = True
Me.MonthsUsedTB.Enabled = False
Me.AmountTB.Locked = True
Me.AmountTB.Value = ""
Case 47 ' Subcategory for Months Used
Me.MonthsUsedTB.Enabled = True
Me.MilesTravelledTB.Enabled = False
Me.AmountTB.Locked = True
Me.AmountTB.Value = ""
Case Else
Me.MilesTravelledTB.Enabled = False
Me.MonthsUsedTB.Enabled = False
Me.AmountTB.Locked = False
Me.AmountTB.Enabled = True
Me.AmountTB.Value = ""
End Select
End Sub
Private Sub MilesTravelledTB\_AfterUpdate()
If IsNull(Me.MilesTravelledTB.Value) Or Me.MilesTravelledTB.Value = "" Then
Me.AmountTB.Value = ""
Else
Dim miles As Integer
miles = Me.MilesTravelledTB.Value
If miles <= 10000 Then
Me.AmountTB.Value = miles \* 0.45
Else
Me.AmountTB.Value = (10000 \* 0.45) + ((miles - 10000) \* 0.25)
End If
End If
End Sub
Private Sub MonthsUsedTB\_AfterUpdate()
If IsNull(Me.MonthsUsedTB.Value) Or Me.MonthsUsedTB.Value = "" Then
Me.AmountTB.Value = ""
Else
Dim months As Integer
months = Me.MonthsUsedTB.Value
Me.AmountTB.Value = months \* 26
End If
End Sub
Mks_1973 is correct when he said that there is only one combobox for both category and subcategory.
changing the recordsource of the combo can affect the display of your combo (previous record).
what you can do is add a "dummy Textbox" to show the SubCategoryName.
see this demo and open SalesForm on design view.
see that there is a "dummyTextbox" in front of subcategory combobox.
also note that the recordsource of the form is a Query.
Mks_1973 is correct when he said that there is only one combobox for both category and subcategory.
changing the recordsource of the combo can affect the display of your combo (previous record).
what you can do is add a "dummy Textbox" to show the SubCategoryName.
see this demo and open SalesForm on design view.
see that there is a "dummyTextbox" in front of subcategory combobox.
also note that the recordsource of the form is a Query.
Thank you so much, this is working beautifully and your example was really helpful. I still have a few things to iron out around 1) restoring the functionality allowing the SubcategoryCB combo box to set the value of the CategoryCB combo box until a category has been selected using the CategoryCB, 2) preventing inappropriate editing of the MilesTravelled and MonthsUsed text boxes if they are re-enabled, 3) I haven't worked out how to get the dummy text box to sit on top of the SubcategoryCB combo box yet, 4) numerous other things I haven't spotted yet I'm sure! But I'm well on my way thanks to your response.
to get the dummytextbox to sit on top of the combobox, on design view you clicked on the subcategory combobox and Copy it's Top property, then clicked on the dummy textbox and paste the Top property you just copied. you do the same with it's Left property. now right-clicked on the dummytextbox and choose, Position->Bring to front.
Key issues identified:
When you requery the SubcategoryCB in the CategoryCB_AfterUpdate event, it resets the SubcategoryCB value for all records.
Continuous forms share control properties like RowSource among all instances, leading to unexpected behavior.
Event-driven updates may unintentionally alter data in existing records due to shared state.
Instead of directly modifying the RowSource for SubcategoryCB, use a TempVar or a temporary unbound combo box for filtering.Private Sub CategoryCB_AfterUpdate()
' Use a TempVar to store the selected category
TempVars("SelectedCategoryID") = Me.CategoryCB.Value' Filter SubcategoryCB only for the current record
Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName " & _
"FROM ExpenseSubcategoryT " & _
"WHERE CategoryID = " & TempVars("SelectedCategoryID") & " " & _
"ORDER BY SubcategoryName"
Me.SubcategoryCB.Requery' Clear SubcategoryCB value only for the current record
Me.SubcategoryCB.Value = Null' Clear relevant fields
ClearRelevantFields
End Sub
Add logic to differentiate between new and existing records to prevent overwriting existing data.
Private Sub Form_Current()
If Me.NewRecord Then
' New record: Disable fields until category/subcategory is selected
Me.MilesTravelledTB.Enabled = False
Me.MonthsUsedTB.Enabled = False
Me.AmountTB.Enabled = False
Me.InvoiceNoTB.Enabled = False
Me.DescriptionTB.Enabled = False
Else
' Existing record: Enable fields for editing
Me.MilesTravelledTB.Enabled = True
Me.MonthsUsedTB.Enabled = True
Me.AmountTB.Enabled = True
Me.InvoiceNoTB.Enabled = True
Me.DescriptionTB.Enabled = True
End If
End Sub
Modify the ClearRelevantFields procedure to clear fields only for new records or when explicitly required.
Private Sub ClearRelevantFields()
If Me.NewRecord Then
Me.MilesTravelledTB.Value = Null
Me.MonthsUsedTB.Value = Null
Me.AmountTB.Value = Null
Me.InvoiceNoTB.Value = Null
Me.DescriptionTB.Value = Null
End If
End Sub
Add logic in SubcategoryCB_AfterUpdate to avoid updating CategoryCB if it’s already setPrivate Sub SubcategoryCB_AfterUpdate()
If Not IsNull(Me.SubcategoryCB.Value) And Not Me.NewRecord Then
' Access the CategoryID from SubcategoryCB
Dim CategoryID As Integer
CategoryID = Me.SubcategoryCB.Column(2)
' Update the CategoryCB only if it differs
If Me.CategoryCB.Value <> CategoryID Then
Me.CategoryCB.Value = CategoryID
End If
End If' Enable fields for data entry
Me.InvoiceNoTB.Enabled = True
Me.DescriptionTB.Enabled = True
UpdateColumnVisibility
End Sub
======================================Create a few dummy records and test adding/editing to ensure the functionality works as expected.
Error Handling:Add error-handling blocks to gracefully handle unexpected errors, especially in database queries or updates.
On Error GoTo ErrorHandler
' Your code here
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Description, vbExclamation
End Sub
Alternative, use Single Form View
If your use case allows, consider using a single form view for better control over individual records. Continuous forms can be challenging for dynamic controls.Thank you for your response. This is the sort of response I've had from various AIs. Unfortunately I'm not knowledgeable enough about AI or Access to be able to make use of it. I appreciate your responding though.
