Problem with Combo Box and (not) changing values

[granttwiss]

Good afternoon, Access Forums!

I am an IT Technician for multiple firms in Port Angeles, WA. I have a little experience with Access from building a Purchase Order database for a Housing Authority, but this build was over 7 years ago, and I am quite out of touch with the application these days.

My current situation involves a client's equipment repair database, which is functional for the most part. The only problem they have is with a form's combo box not updating records when they select a new value unless the form is closed and re-opened.

Specifically, the user can open the form and select an initial value, and all record data appears intact, and they can make changes and update the table successfully. However, if the user makes a subsequent selection from the combo-box, the records do not pull in the new info, the old info sits in the form with the only thing updated being the combo-box.

The work-around, at this point, is to make the first selection, update the records and then close the form and re-open it. Then a different selection can be made and the process repeated.

Interestingly, after the first selection is made, the user can select "remove filter" and the records will reset to default values; but when they make another selection, the form still shows whatever record was filtered by the combo-box originally, no matter the currently selected list item, until the form is closed...

Any help with this would be appreciated.

Thank you!

Grant

 

[missinglinq]

To be honest your explanation has left me more than a little confused as to exactly what it is you're doing, but when Records are only updated after Closing and then Reopening a Form, the answer is that the Form needs to be Requeried.

So after the new selection has been made, probably in the Combobox AfterUpdate event, a simple

Me.Requery

should do the trick!

Linq ;0)>

 

[Pat Hartman]

The best way to pull in lookup fields is to use a query that joins the main table to the lookup tables as the RecordSource for the form. When you do that, the form changes immediately and without any VBA code necessary as soon as you pick a new foreign key value. An alternative method uses VBA code that runs in the form's Current event and in either the Before or After update events of the combo to copy values from the columns of the combo to unbound form fields.

Open the form in design view and look at the form code, if you copy and paste it (use the Advanced reply dialog so you can use the code tag to keep the spacing) we'll look at it.

 

[granttwiss]

Thank you for your responses, Linq and Pat!

Here is the code for the Form_AllTabs:

Option Compare Database

Private Sub EquipmentGroupCombo_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_EquipmentGroupCombo_NotInList

' Allows user to add a new Equipment Group by typing the Equipment Group
' in the EquipmentGroupCombo box.

    Dim NewEquipmentGroup As Integer, Title As String, msgdialog As Integer
    Const MB_YESNO = 4
    Const MB_ICONQUESTION = 32
    Const MB_DEFBUTTON1 = 0, IDYES = 6


        ' Display message box asking if the user wants to add a
        ' new Equipment Group
        
        Title = "Equipment Group Not in List"
        msgdialog = MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON1
        NewEquipmentGroup = MsgBox("Do you want to add a new Equipment Group?", msgdialog, Title)

        If NewEquipmentGroup = IDYES Then

            ' Remove text user entered from the combo box and
            Me![EquipmentGroupCombo].Undo
            
        'if the record does not exist, we need to create a new one
        If IsNull(DLookup("[Equipment Group]", "EquipmentGroups", "[Equipment Group] = "" & NewData & """)) Then
            DoCmd.RunSQL "INSERT INTO EquipmentGroups VALUES (""" & NewData & """);"
            Me![EquipmentGroupCombo].Requery
            Me![EquipmentGroupCombo] = NewData
        End If

        
            ' Continue without displaying default error message.
            Response = DATA_ERRCONTINUE
            
        End If


Exit_EquipmentGroupCombo_NotInList:
    Exit Sub

Err_EquipmentGroupCombo_NotInList:
    MsgBox Error$
    Resume Exit_EquipmentGroupCombo_NotInList

End Sub

Private Sub FindCombo_AfterUpdate()

    DoCmd.ApplyFilter , "[EquipmentID] = Forms![AllTabs]![FindCombo]"
    Me![RepairFrom] = DMin("[Date]", "Repair History", "[EquipmentID] = '" & [EquipmentID] & "'")

End Sub

Private Sub FindCombo_Click()

    DoCmd.ApplyFilter , "[EquipmentID] = Forms![AllTabs]![FindCombo]"
    Me![RepairFrom] = DMin("[Date]", "Repair History", "[EquipmentID] = '" & [EquipmentID] & "'")

End Sub

Private Sub cmdPreviewSpecs_Click()
On Error GoTo Err_cmdPreviewSpecs_Click

    Dim stDocName As String

    stDocName = "Specifications Report"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewSpecs_Click:
    Exit Sub

Err_cmdPreviewSpecs_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewSpecs_Click
    
End Sub

Private Sub cmdPreviewFilters_Click()
On Error GoTo Err_cmdPreviewFilters_Click

    Dim stDocName As String

    stDocName = "Filters and Oil Report"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewFilters_Click:
    Exit Sub

Err_cmdPreviewFilters_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewFilters_Click
    
End Sub
Private Sub cmdPreviewService_Click()
On Error GoTo Err_cmdPreviewService_Click

    Dim stDocName As String

    stDocName = "Service Report"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewService_Click:
    Exit Sub

Err_cmdPreviewService_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewService_Click
    
End Sub
Private Sub cmdPreviewRepair_Click()
On Error GoTo Err_cmdPreviewRepair_Click

    Dim stDocName As String

    stDocName = "Repair History"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewRepair_Click:
    Exit Sub

Err_cmdPreviewRepair_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewRepair_Click
    
End Sub
Private Sub cmdPreviewRepairsToDo_Click()
On Error GoTo Err_cmdPreviewRepairsToDo_Click

    Dim stDocName As String

    stDocName = "Repairs To Do"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewRepairsToDo_Click:
    Exit Sub

Err_cmdPreviewRepairsToDo_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewRepairsToDo_Click
    
End Sub

Private Sub RepairFrom_DblClick(Cancel As Integer)

'    Me![RepairFrom].Requery
    Me![RepairFrom] = DMin("[Date]", "Repair History", "[EquipmentID] = '" & [EquipmentID] & "'")
    
End Sub
Private Sub List89_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[EquipmentID] = '" & Me![List89] & "'"
    Me.Bookmark = rs.Bookmark
End Sub

The combo-box in question is named FindCombo. It looks like it has a Click event defined and an AfterUpdate event defined, but the AfterUpdate does not correspond with Linq's advice.

Thanks again for looking at this!

 

[Pat Hartman]

Delete the FindCombo_Click event. It is redundant and may be causing a conflict. Is it the [Date] field that is not being updated when you think it should be?

If EquipmentID is defined as numeric, it should not be enclosed in quotes.

Me.RepairFrom = DMin("[Date]", "Repair History", "EquipmentID = " & Me.EquipmentID)

Notice the changes to the syntax also.

 

[granttwiss]

I made the changes you suggested:

1) Deleted the entirety of the "Click" Event.

2) Adjusted the syntax to what you suggested, (see updated code)

I am now receiving a "missing operator" error. Specifically,

"Run-Time error '3075': Syntax error (missing operator) in query expression 'EquipmentID = A23'.

The Equipment ID in most cases is alpha-numberic, meaning that the first character is a Letter and then there are two numbers following. As in A23, above.

Thank you again for your help!

Option Compare Database

Private Sub EquipmentGroupCombo_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_EquipmentGroupCombo_NotInList

' Allows user to add a new Equipment Group by typing the Equipment Group
' in the EquipmentGroupCombo box.

    Dim NewEquipmentGroup As Integer, Title As String, msgdialog As Integer
    Const MB_YESNO = 4
    Const MB_ICONQUESTION = 32
    Const MB_DEFBUTTON1 = 0, IDYES = 6


        ' Display message box asking if the user wants to add a
        ' new Equipment Group
        
        Title = "Equipment Group Not in List"
        msgdialog = MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON1
        NewEquipmentGroup = MsgBox("Do you want to add a new Equipment Group?", msgdialog, Title)

        If NewEquipmentGroup = IDYES Then

            ' Remove text user entered from the combo box and
            Me![EquipmentGroupCombo].Undo
            
        'if the record does not exist, we need to create a new one
        If IsNull(DLookup("[Equipment Group]", "EquipmentGroups", "[Equipment Group] = "" & NewData & """)) Then
            DoCmd.RunSQL "INSERT INTO EquipmentGroups VALUES (""" & NewData & """);"
            Me![EquipmentGroupCombo].Requery
            Me![EquipmentGroupCombo] = NewData
        End If

        
            ' Continue without displaying default error message.
            Response = DATA_ERRCONTINUE
            
        End If


Exit_EquipmentGroupCombo_NotInList:
    Exit Sub

Err_EquipmentGroupCombo_NotInList:
    MsgBox Error$
    Resume Exit_EquipmentGroupCombo_NotInList

End Sub

Private Sub FindCombo_AfterUpdate()

    DoCmd.ApplyFilter , "[EquipmentID] = Forms![AllTabs]![FindCombo]"
    Me.RepairFrom = DMin("[Date]", "Repair History", "[EquipmentID] = " & Me.EquipmentID)


End Sub

Private Sub cmdPreviewSpecs_Click()
On Error GoTo Err_cmdPreviewSpecs_Click

    Dim stDocName As String

    stDocName = "Specifications Report"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewSpecs_Click:
    Exit Sub

Err_cmdPreviewSpecs_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewSpecs_Click
    
End Sub

Private Sub cmdPreviewFilters_Click()
On Error GoTo Err_cmdPreviewFilters_Click

    Dim stDocName As String

    stDocName = "Filters and Oil Report"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewFilters_Click:
    Exit Sub

Err_cmdPreviewFilters_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewFilters_Click
    
End Sub
Private Sub cmdPreviewService_Click()
On Error GoTo Err_cmdPreviewService_Click

    Dim stDocName As String

    stDocName = "Service Report"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewService_Click:
    Exit Sub

Err_cmdPreviewService_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewService_Click
    
End Sub
Private Sub cmdPreviewRepair_Click()
On Error GoTo Err_cmdPreviewRepair_Click

    Dim stDocName As String

    stDocName = "Repair History"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewRepair_Click:
    Exit Sub

Err_cmdPreviewRepair_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewRepair_Click
    
End Sub
Private Sub cmdPreviewRepairsToDo_Click()
On Error GoTo Err_cmdPreviewRepairsToDo_Click

    Dim stDocName As String

    stDocName = "Repairs To Do"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewRepairsToDo_Click:
    Exit Sub

Err_cmdPreviewRepairsToDo_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewRepairsToDo_Click
    
End Sub

Private Sub RepairFrom_DblClick(Cancel As Integer)

'    Me![RepairFrom].Requery
    Me![RepairFrom] = DMin("[Date]", "Repair History", "[EquipmentID] = '" & [EquipmentID] & "'")
    
End Sub
Private Sub List89_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[EquipmentID] = '" & Me![List89] & "'"
    Me.Bookmark = rs.Bookmark
End Sub

 

[granttwiss]

Adding the ME.Requery to the AfterUpdate Event Procedure fixed my issue. Thank you missinglinq.

I would also like to thank Pat Hartman for the time spent on this problem. I know when it comes to this stuff, there is always more than one way to skin a cat. I'm going to test this thing out and if everything works out, I will let you guys know!

 

[DemonDNF]

...
So after the new selection has been made, probably in the Combobox AfterUpdate event, a simple

Me.Requery

should do the trick!

Linq ;0)>

I had a similar problem with a layout that looked like this

Main form
  Inventory form (source is Part table)
    Inventory Detail form (source is Inventory table)

A combobox at the detail level is bound to Inventory table; it is a query to a Spec table. The Spec values did not change when moving to another Part using the record selector.

I've been plagued with this problem for a few days now. The answer is a mix of information from several sources; I requery the combobox at the Part level:

Private Sub Form_Current()
  Form_InventoryDetail!SpecID1.Requery
End Sub

I'm a COBOL programmer by trade. I dabble in Access every 5-10 years so I never get familiar with all the peculiarities, proper syntax and possible solutions. That and object oriented is "special" then you come from decades of hierarchical programming.

Thank you to all that give so much of your time. This forum is by far the one that comes up the most in my google searches. It's the only one that was worth registering.

Robert