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].[Bi n], [StockLocation_Bins_B].[In ventoryLoc ationDescr iption] 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("Stock Location_B ins_B")
SQL01 = "SELECT dbo_FS_InventoryLocation.B in, dbo_FS_InventoryLocation.I nventoryLo cationDesc ription "
SQL02 = "FROM dbo_FS_InventoryLocation "
SQL03 = "WHERE (((dbo_FS_InventoryLocatio n.Stockroo m)='" & cmbStock & "')) "
SQL04 = "ORDER BY dbo_FS_InventoryLocation.B in;"
QDFDynamic.SQL = SQL01 & SQL02 & SQL03 & SQL04
QDFDynamic.Close
cmbBin.Requery
cmbStock - SELECT [Stock Locations].[Stockroom] FROM [Stock Locations] ORDER BY [Stockroom];
cmbBin - SELECT [StockLocation_Bins_B].[Bi
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("Stock
SQL01 = "SELECT dbo_FS_InventoryLocation.B
SQL02 = "FROM dbo_FS_InventoryLocation "
SQL03 = "WHERE (((dbo_FS_InventoryLocatio
SQL04 = "ORDER BY dbo_FS_InventoryLocation.B
QDFDynamic.SQL = SQL01 & SQL02 & SQL03 & SQL04
QDFDynamic.Close
cmbBin.Requery
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
glad I could help.
Unfortunately, that same technique does not work for datasheets.
Unfortunately, that same technique does not work for datasheets.
ASKER