Change source of combo box dynamically (1 Viewer)

scubadiver007

Registered User.
Local time
Today, 04:15
Joined
Nov 30, 2010
Messages
317
For a combo box I am using "allocations_appraiserlist_MR" as the default source:

Code:
SELECT DISTINCT temptable_dctrecs.AppraiserGMC, [FName] & " " & [Surname] AS AppraiserName
FROM Tble_Appraiser INNER JOIN temptable_dctrecs ON Tble_Appraiser.GMC_ID = temptable_dctrecs.AppraiserGMC
WHERE (((temptable_dctrecs.DoctorGMC)=[forms]![Form_Appraisals]![GMC_ID]));

I need to check if this has returned zero records and I then need to use a separate source for each of the following conditions:

1) If no record is returned in the combo and a text box states "non-locum" I need to use the source "allocations_appraiserlist_SR":

Code:
SELECT tble_validpractices.GMC_ID, [tble_appraiser]![FName] & " " & [tble_appraiser]![Surname] AS AppraisalName, Tble_DoctorAppraisal.GMC_ID
FROM (Tble_DoctorAppraisal INNER JOIN Query1 ON Tble_DoctorAppraisal.GMC_ID = Query1.GMC_ID) INNER JOIN (Tble_Appraiser INNER JOIN (tble_validpractices INNER JOIN Tble_DocPrac ON tble_validpractices.KCode = Tble_DocPrac.KCode_ID) ON Tble_Appraiser.GMC_ID = tble_validpractices.GMC_ID) ON Tble_DoctorAppraisal.GMC_ID = Tble_DocPrac.GMC_ID
WHERE (((Tble_DoctorAppraisal.GMC_ID)=[forms]![Form_Appraisals]![GMC_ID]));

2) If no record is returned in the combo and a text box states "locum" then I need to use the source "allocations_appraiserlist_All":

Code:
SELECT Tble_Appraiser.GMC_ID, [FName] & " " & [Surname] AS AppraiserName
FROM Tble_Appraiser;

Would I have to use VBA?
 

mdlueck

Sr. Application Developer
Local time
Today, 07:15
Joined
Jun 23, 2011
Messages
2,631
I define my Combo Box SQL queries in VBA, have the VBA build the correct DAO.QueryDef object named what the actual Form Combo Box control is bound to.

Thus, update the DAO.QueryDef SQL in my case, and that updates the Combo Box list.

You will find sample code of how to create DAO.QueryDef objects here:

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

You may also update the Combo Box source property to be a bare SQL. This is a harder way to debug things as you do not have a GUI QueryDef which you can open interactively for debugging. Also it is said that Combo Boxes perform more efficiently bound to a DAO.QueryDef as opposed to bare SQL being stuffed into them.

An example thread discussing my most complicated ComboBox query in my current application:
Need to add static entry to a SQL populated Combo Box - UNION Query Example
http://www.access-programmers.co.uk/forums/showthread.php?t=219692&page=3#post1178814
 

mdlueck

Sr. Application Developer
Local time
Today, 07:15
Joined
Jun 23, 2011
Messages
2,631
I found a spot just now in code where I update the SQL that populates a Combo Box. The magic the change the SQL in the example I found involves a custom class object, so please do not let that detail confuse the bigger picture fact that a Combo Box's SQL is being updated due to the fact that the DAO QueryDef SQL is getting updated. Works great - go ahead and give it a try...

Code:
  Dim daoDB As DAO.Database
  Dim daoQDF As DAO.QueryDef

  'Attach to the FE DB
  Set daoDB = CurrentDb()

  'Select correct control labels, queries, etc...
  If Me.OpenArgs = "subform_metooling_gaging" Then

    'Update the FE DAO.QueryDef object to contain the correct SQL
    Set daoQDF = daoDB.QueryDefs(ObjMultipleItemsFormsInit.GetDAOQueryDefName("metoolingtooltype"))
    With daoQDF
      .SQL = "SELECT t.aid,t.authid,t.authusername,t.logtimestamp,t.id,t.sort,t.active,t.title,t.aqeflg FROM " & ObjMEToolingToolTypeTbl.FETempTableName() & " AS t WHERE t.active <> 0 AND t.aqeflg <> 0 ORDER BY t.sort,t.title;"
      .Close
    End With
  Else

    'Update the FE DAO.QueryDef object to contain the correct SQL
    Set daoQDF = daoDB.QueryDefs(ObjMultipleItemsFormsInit.GetDAOQueryDefName("metoolingtooltype"))
    With daoQDF
      .SQL = ObjMultipleItemsFormsInit.GetSQLNorm("metoolingtooltype")
      .Close
    End With
  End If

  'Clean up the connection to the database
  Set daoDB = Nothing
  Set daoQDF = Nothing
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:15
Joined
Jan 20, 2009
Messages
12,849
Also it is said that Combo Boxes perform more efficiently bound to a DAO.QueryDef as opposed to bare SQL being stuffed into them.

This is only with a static querydef that has been run before. An execution plan is saved the first time a query is run so that improves performance on subsequent runs.

However if you modify the querydef then the plan must be rebuilt so there is no difference with a dynamic querydef compared to using an SQL string as the RowSource.

Indeed if you were to save the querydef after editing then it would be slower while it is written to disk.
 

mdlueck

Sr. Application Developer
Local time
Today, 07:15
Joined
Jun 23, 2011
Messages
2,631
Indeed if you were to save the querydef after editing then it would be slower while it is written to disk.

Once a given Combo Box FE temp table is populated it does not get populated again, nor the DAO.QueryDef rebuilt.

Good to know that it is in the DAO.QueryDef that the optimizing get stored, and not merely the fact of putting the SQL into a DAO.QueryDef. Thanks.
 

scubadiver007

Registered User.
Local time
Today, 04:15
Joined
Nov 30, 2010
Messages
317
I am currently using this in the "current" event:

If DCount("*", "ALLOCATIONS_Appraiserlist_result") > 0 Then
Me.Appraiser_ID.RowSource = "ALLOCATIONS_Appraiserlist_result"
ElseIf DCount("*", "ALLOCATIONS_Appraiserlist_result") = 0 Then
Me.Appraiser_ID.RowSource = "ALLOCATIONS_appraiserlist_all"
End If

This could be more efficient but not sure how! :eek:
 

Users who are viewing this thread

Top Bottom