refresh combo box data

[GirlBlonde]

hi i have a continued question from my previous post but as it's a bit different i thought i'd start a different thread.

i basically want to requery the cbo box each time someone clicks on the down arrow so they get the latest data.

the cbo box is made up of 2 queries below that are run when they exit the form for adding the new data.

me.refresh and me.requery sets them all to null doesnt keep the data that is already in clears it all out.

am i making sense?

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE TblTempOnly_PoolContractDrivers.* FROM TblTempOnly_PoolContractDrivers;"
DoCmd.OpenQuery "Qry_AppTemp_PoolDrivers"
DoCmd.OpenQuery "Qry_AppTempTbl_ContractDrivers"
DoCmd.SetWarnings True

Energy & persistance conquer all things!

 

[boblarson]

Why are you doing up a temp table for this? You should be able to just use a query (and if you are needing info from two tables, you can use a UNION query).


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[boblarson]

Forgot to add - if you use the UNION query you can still refresh by using

Me.YourComboBoxNameHere.Requery

in the click event, if you want the most recent selections to be available. It would probably move faster if you weren't rebuilding a temp table each time.


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[GirlBlonde]

HI

thanks for your reply greatly appriciate it.

i have to make up a temp table as the data is coming from 2 different tables and i want all the names from both tables. users add new records to either the main table (all reg drivers) or a sub table (pool cars) the tables are not the same, pool cars table only needs 3 field basically just thier name whereas the main table requires allot more details. i have to combine these two so the drop down box includes all of the data names for them to choose from, including what they have just added. hence the refresh need.

does this make sense?

Energy & persistance conquer all things!

 

[boblarson]

Actually, you don't need to make a table for this at all. You can just use the make table queries as your combo's rowsource (just change it from a make table query to two select queries and create a union query from both.

post back if you need more info on how to do a Union query.


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[TheAceMan1]

How are ya GirlBlonde . . .

For what you've posted, this is the best I can come up with ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim hldText As String, Cbx As ComboBox
   
   Set Cbx = Me![purple][b]ComboboxName[/b][/purple]
   
   [b]hldText = Cbx.Text[/b]
   DoCmd.SetWarnings False
   DoCmd.RunSQL "DELETE * " & _
                "FROM TblTempOnly_PoolContractDrivers;"
   DoCmd.OpenQuery "Qry_AppTemp_PoolDrivers"
   DoCmd.OpenQuery "Qry_AppTempTbl_ContractDrivers"
   DoCmd.SetWarnings True
   [b]Cbx.Requery
   Cbx.SetFocus
   Cbx.Text = hldText[/b]
   
   Set Cbx = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886