UtterAccess Discussion Forums > How Do I Refresh Query Driving Combo Box?

Full Version: How Do I Refresh Query Driving Combo Box?

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

Dubious

May 10 2007, 04:18 AM

I have a down list combo box for selecting entries on a form - this is driven by a query which looks at all the existing values in that field. I have set the control up so that options can be selected from the combo list OR a new value typed into the box.

What command do I use on the control event (probably ON Exit) in order to refresh the query so that new options are added to the list (at the moment I need to close and reopen the form for the newly typed options to appear in the combo list)?
Cheers,
Tim.

dannyseager

May 10 2007, 04:39 AM

See Ricky's code here

http://www.utteraccess.com/forums/showflat...;Number=1411759

You want the NotInList event

Dubious

May 10 2007, 05:08 AM

Thanks - but I think this makes things slightly more complex than I need - plus I do not want to give them an option of an entry not being in the list.

The list is specifically being driven by a query listing all existing field contents and so it is just the Event command for refreshing queries that I am after.
Cheers,
Tim.

dannyseager

May 10 2007, 05:35 AM

You still need to use the not in list event to add the record...

CODE

Private Sub YourCombo_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Dim strSQL As String

Set ctl = Me!YourCombo
Response = acDataErrAdded
strSQL = "INSERT INTO tblYourTable (YourField) SELECT '" & NewData & "'"
CurrentDb.Execute strSQL, dbFailOnError
End Sub

Here is a simplified version of the coding without the question and without the error trapping (which you should put back once you are happy with it)

dannyseager

May 10 2007, 05:37 AM

Infact you can also remove

Dim ctl As Control

and

Set ctl = Me!YourCombo

laxmanchip

May 10 2007, 07:20 AM

To refresh the values in a combo box that are derived from a query, you can call the following VBA through a sub procedure:

me.[Control].requery

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.