Link to home
Start Free TrialLog in
Avatar of dsoderstrom
dsoderstrom

asked on

problem with combo boxes on subform

I have a subform with 7 columns bound to a table.  Two of the columns in the subform, named cmbStock and cmbBin, are combo boxes for selecting values.  The Row sources for each combo box are as follows:

cmbStock -  SELECT [Stock Locations].[Stockroom] FROM [Stock Locations] ORDER BY [Stockroom];
cmbBin -  SELECT [StockLocation_Bins_B].[Bin], [StockLocation_Bins_B].[InventoryLocationDescription] FROM StockLocation_Bins_B ORDER BY [Bin];

StockLocation_Bins_B is a select query.  

In the On Current Event for the subform I have the following code which dynamically changes query StockLocation_Bins_B based upon the value in cmbStock and then requerys cmbBin.  Although the query does get changed correctly the values in cmbBin do not change.  What am I doing wrong?

Dim QDFDynamic As QueryDef
    Dim SQL01 As String
    Dim SQL02 As String
    Dim SQL03 As String
    Dim SQL04 As String
   
   Set QDFDynamic = CurrentDb.QueryDefs("StockLocation_Bins_B")
   SQL01 = "SELECT dbo_FS_InventoryLocation.Bin, dbo_FS_InventoryLocation.InventoryLocationDescription "
    SQL02 = "FROM dbo_FS_InventoryLocation "
    SQL03 = "WHERE (((dbo_FS_InventoryLocation.Stockroom)='" & cmbStock & "')) "
    SQL04 = "ORDER BY dbo_FS_InventoryLocation.Bin;"
    QDFDynamic.SQL = SQL01 & SQL02 & SQL03 & SQL04
    QDFDynamic.Close
   
    cmbBin.Requery
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dsoderstrom
dsoderstrom

ASKER

thanks you Dale.  You solved my problem
glad I could help.

Unfortunately, that same technique does not work for datasheets.