Combo Box Update Code

[Hayton]

Hi Guys,

I have a combo box that when I select an item in the list, I would like it to refresh two other combo boxes. A query runs that creates a table with new data the old data is deleted. The combo boxes then use this table data as a rowsource.

The the main combobox is cboCustomer and the two comboboxes I need refreshed are cboFleet and cboTyreSize.

Not being very good at coding I tried the following:

Private Sub cboCustomer_AfterUpdate()
On Error GoTo ErrorHandler
Dim stDocName As String

Me.cboFleet.RowSource = ""

Me.cboFleet.Value = ""

stDocName = "qmakVehicles"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Me.cboFleet.RowSource = "SELECT DISTINCTROW tblFleetDD.FltNbr FROM tblFleetDD ORDER BY tblFleetDD.FltNbr;"

Me.cboFleet.Requery


Dim stDocName As String

Me.cboTyreSize.RowSource = ""

Me.cboTyreSize.Value = ""

stDocName = "qmakTyreSize"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Me.cboTyreSize.RowSource = "SELECT tlkpCasingSize.CsngSz FROM tlkpCasingSize ORDER BY tlkpCasingSize.CsngSz;"

Me.cboTyreSize.Requery

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub

I also wish for the messages to be suppressed when rebuilding the data for the tables and for the tables to be updated without any questions being asked.

Any suggestions will be appreciated.


Hayton McGregor

 

[TheAceMan1]

How are ya Hayton . . .

Your main problem is the code keeps running after the queries [blue]qmakVehicles[/blue] & [blue]tlkpCasingSize[/blue] are open. You need the code to stop and wait for the queries to close.

The easiest way to do this is to make two forms based on those queries and open them in the [blue]acDialog[/blue] window mode. This will cause the code to halt while you do your editing and continue after you close. I don't know the names of the forms you'll use, but following example of your code uses the query names. You need to change them approriately to what you assign:

[blue]   [purple]DoCmd.OpenForm "qmakVehicles", , , , , [b]acDialog[/b][/purple]
   Me.cboFleet.Value = ""
   Me.cboFleet.RowSource = "SELECT DISTINCTROW FltNbr " & _
                           "FROM tblFleetDD " & _
                           "ORDER BY FltNbr;"
   
   [purple]DoCmd.OpenForm "qmakTyreSize", , , , , [b]acDialog[/b][/purple]
   Me.cboTyreSize.Value = ""
   Me.cboTyreSize.RowSource = "SELECT CsngSz " & _
                              "FROM tlkpCasingSize " & _
                              "ORDER BY CsngSz;"[/blue]

BTW: when you write to the [blue]rowsource[/blue] of a combobox it automatically requires!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Hayton]

Hi AceMan1

Thanks for the reply AceMan. I am trying to refresh two comboboxes (cboFleet & cboTyresize) on the same form using the after update event for a combobox(cboCustomer).

After I have selected a customer from cboCustomer, cboFleet and cboTyreSize need to be refreshed. All 3 comboboxes are on the same form and I don't need to edit content fo cboFleet & cboTyreSize.

I am not sure if you solution above is the correct one. If understand you correctly I would not be able to do the above on a single form.

Many thanks

Hayton McGregor

 

[TheAceMan1]

Hayton . . .

I believe I misquoted one of the queries. In any event what are you doing with queries [blue]qmakVehicles[/blue] & [blue]qmakTyreSize[/blue]? . . .

Post the SQL of the queries . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Hayton]

Hi Aceman

Both these queries are make table queries. The tables are use for the combobox rowsource query.

So what I am trying to acheive is the following:
1. I select a customer from cboCustomer.
2. In the after update event for cboCustomer place a code which will refresh data for cboFleet and cboTyreSize.
3. Both cboFleet and cboTyreSize need to show fleet and tyre details for the selected customer. The application should do this via rebuilding a table of data used for the combobox rowsource to query

This works via a button which I click after selecting the customer. So in essence I am trying to automate this process.


Hayton McGregor

 

[Hayton]

Hi AceMan I seem to have got it working.

Thanks for the help

Hayton McGregor