Update Combobox

[kevygee]

I have a combo box in a form that uses a query to access values from a different table, and dispaly them as choices. This works great. The first problem I encountered was if the value that was needed to be entered was not in the list, then there should be an option to add it. I found a couple good examples by using the NotInList definition to achieve this. And it works, when a new value is added, a message box comes up asking to enter the info in the database and the needed form then pops up. But when I close the form, the value is not in the combobox. Now I also researched this for a while, and found out about the function ComboBox.Requery, but I don't know where to put it to work.

Here's a more code definition of what I'm trying to do/have:

Private Sub Building_NotInList(Newdata As String, Response As Integer)
    If MsgBox("""" & Newdata & """ is not in the customer list. Add it?", 33) <> 1 Then
        Response = DATA_ERRCONTINUE
        Exit Sub
    End If
    DoCmd.OpenForm "addbldgform", , , , 1 'Data Entry Mode
    Response = DATA_ERRCONTINUE
End Sub

I was thinking about putting the ComboBox.Requery inside the AfterUpdate of a field in the addbldgform, so that when a new entry was added it would update by writing this in the AfterUpdate:

Form!buildingPractice!BuildingID.Requery

but that gives me an error saying that the field buildingPractice can't be found, when it is really a form with the value BuildingID (the name of the combobox that needs to be updated)

Sorry about the long definition, I just wanted to be clear, hope someone can help out.

- kevygee

 

[RichB]

I personally wouldn't put the requery on the form used to update the record. I would put your requery at the OnActivate of your main form as:

buildingPractice.Requery

After you update the record with the new info, close the add building form, your main form will activate and then get the focus and you should be in business.

 

[kevygee]

Thank you very much for the response. I added this function:

Private Sub buildingPractice_OnActivate()
    buildingPractice.Requery
End Sub

to the buildingPractice form. And when I try it, I get no errors, but there is no update. I just close the second form and the combobox is the same as before. When I close and reopen it, the new entry is there, but I can't seem to make the form requery.

Perhaps I'm writing this function wrong, which could happen. I just started doing VBA. Thanks in advance for any ideas.

 

[Mile-O]

Here's a more code definition of what I'm trying to do/have:

Private Sub Building_NotInList(Newdata As String, Response As Integer)
    If MsgBox("""" & Newdata & """ is not in the customer list. Add it?", 33) <> 1 Then
        Response = DATA_ERRCONTINUE
        Exit Sub
    End If
    DoCmd.OpenForm "addbldgform", , , , 1 'Data Entry Mode
    Response = DATA_ERRCONTINUE
End Sub

Well, where's the DAO (or ADO) code adding it into the relevant table? Why do you have a constant called DATA_ERRCONTINUE when there's already a constant called acDataErrContinue? Also, when adding you need the acDataErrAdded constant for the response.

 

[kevygee]

Thanks for your input.

First of all, the main form and second form are both bound, so the information is automatically updated in the table. Secondly, when a word is typed in the combobox (triggering NotInList), it is NOT automatically added into the database as a new choice because there is more information that is needed in order for that piece of data to be kept in the database, thus the second form that pops up.

About the DataErr constant, I was trying to adapt someone's code and they were using that and I actually thought that was the constant.

 

[kevygee]

Thanks for all your help. Like always, someone has had the same problem and it has been solved. I found the following thread to be very useful in solving this problem

http://www.access-programmers.co.uk/forums/showthread.php?t=15931

which actually links to this page

http://support.microsoft.com/suppor...N-US&SD=gn&FR=0

and goes through a process on how to achieve NotInList Combobox Updating.