Combo Box Update

[vanleurth]

Hi happy programmers,

I wonder how can I update a combo box that has a query attached that it is link to its on field.

For example,

I have a combo box that looks for entries in the same field that is attach to it.
The purpose is to allow the user to enter categories using the combo box and also use the autocomplete properties of a combo box.

The problem; I can't get the combo box to reuquery and include the latest entry. To make the combo box add up the latest entry so it will show up in the combo box, I have to close and open the form, not very elegant.

Any ideas,

thanks in advance,

V.

 

[kjv1611]

Hmm.. have you considered using a separate table for the values for the "autolookup" part? For instance I have some combo boxes that do that, and I have code put in the NotInList event of the combo box that says something like "this is not in the list, do you wish to add it" ok or cancel... then if the user hits "ok", then it adds the new value to the table, if hit cancel, then it does not. Now, for my application, it makes sense to have the LimitToList property set to Yes/True. To use this method, you would still need to do that, and thus if any new entry is used, then the user is prompted to save it to the list, and if there is some error, then they can just cancel and re-enter. That also helps cover for typos, and having accurate data for any queries you may want to run.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[vanleurth]

Thanks kjv1611

But I'm actually trying to reduce redundancy and tables in my database.

For example,
If I have certain information that only exists in a table and just one table, I use a combo box attach to a field with a query looking at the same field. In this way I have an autofill combo box that also allows the user to add more items to its list because they are obtained from its own field. Fancy ?? don't you think ?

Next case. If I have a form that has a combo box that also exist in another combo box, then I use a table and the notislist event to add a new field.

I think this is a better approach. Perhaps, this is the greatest/worst advantage of ACCESS, you can do the same thing in many different ways and it doesn't seems to lead you to the best way or more logical way of doing things.

Any ideas will be appretiated,

Thanks in advance,

V.

 

[kjv1611]

Hmmm, have you tried using a recordset updating the field? Possibly that would work, I would think. Here is an example I use (using the separate table, but would work for using the one table, as you mentioned, I would think). I got some of the code/idea from some tek-tips users. Give it a shot, and of course, edit as need be - different names, different methods/ideas, and such. This works great for my application.

Private Sub cmbDirector_NotInList(NewData As String, Response As Integer)
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim Msg As String, Title As String, CR As String
    
    If NewData = "" Then Exit Sub
    
    CR = Chr$(13)
    Title = "Not In List Error"
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    
    If MsgBox(Msg, vbQuestion + vbYesNo, Title) = vbYes Then
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblDirector", dbOpenDynaset)
        With rs
            .AddNew
            ![DirectorName] = cmbDirector.Text
            .Update
            .Close
        End With
        Response = acDataErrAdded
        Set rs = Nothing
        Set db = Nothing
    Else
        Me.cmbDirector.Undo
        Response = acDataErrContinue
    End If
End Sub

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[willir]

vanleurth

For the AfterUpdate event for the record (not the field), use...

'Refresh - optional, will force current record to be updated
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

'You can reset your recordsource, optional
Me.YourSubForm.RecordSource = YourComboSQLStatment

'Requery to repopulate the combo box
Me.YourSubForm.Requery

The Refresh and the RecordSource lines may not be required.

The ReQuery will force Access to repopulate the combo box which now include the new record.

Richard

 

[Zion7]

Actually vanleurth, I simply use Me.Refresh, in the AfterUpdate event, of the combobox. The new addition is immediate, the minute I change focus.(I'm using Access2003?)
AutoExpand is set to yes.
LimitToList is set to No.

...couldn't believe how easy it was, especially after trying, UpdateQueries, recordset updates, Requery methods etc...All worked, as stated above by KJV & Richard, but refreshing the form, on the AfterUpdate event of the cbobox, was instantaneous (relatively speaking).


Good Luck!