Combo box requery

[Dimitris Nikolakakis]

I have a table Storage (Code, FactoryID)

I have created a form with:
-ComboBox to select the factory
-ComboBox to select the StorageCode (in the row source there is 'SELECT Code
FROM Storage WHERE
[Storage]![FactoryID]=[Forms]![RStorageItemStatement].[Combo21] ').

First the user selects the factory and then the StorageCode. Until then it
works OK. When the user changes the Factory then The combo box of Storage is
still based on the previous factory.

I tried in OnChange and in AfterUpdate of Factory (DoCmd.Requery(Combo33))
but nothing.

Is there anything else to do?

thanks

 

[Howard Brody]

If you need the factory to determine the storage code
list, leave the RowSource blank for the storage code
ComboBox and set it in the AfterUpdate.

Try this:

Private Sub cboFactory_AfterUpdate()

' if combo box is empty, stop here
If IsNull([cboFactory]) or [cboFactory] = "" Then
Exit Sub
End If

' otherwise...

' declare variables
Dim strFactory as String
Dim strSource as String

' build SQL string for combobox source
strFactory = [cboFactory]
strSource = "SELECT tblStorage.Code FROM tblStorage WHERE
tblStorage.FactoryID='" & strFactory & "'"

' set the row source for the combo box and requery
cboStorageCodes.RowSource = strSource
cboStorageCodes.Requery

End Sub

Hope this helps!

Howard Brody