refresh Cbobox after update & close diff form

[aidy]

Hi all,
I have a very busy form with drop down combo boxes that link to lookup tables with foriegn keys. When a user adds a new entry to the list I use an event to open a form based on the related table so they can add the new data.
my problem is this: Once they close and return to the original form they have to select a known value in the combo box, then refresh data, and then reselect the 'new' entry that they added. Otherwise the cboBox does not see the new data in the table via the SQL row source. How can I reference the combo box they came from (there are 8 on different forms) so that I can either requery or refresh? I am trying to work code 'onClse' of form that adds to related table.
best rgds
alorimer@netzero.net

 

[930driver]

You need to us the notinlist event where you have available the newdata and response parameters. Pass response to your function that opens the form, and when all done set response to acDataErrAdded. Or if you are just opening the form in the notinlist event, set response to acDataErrAdded right there.

This will cause the combo box to be requeried. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[Guest_imported]

Thank you for your help, I need a pointer .
I used a macro from cboBox Notinlist property with action Openform 'addtbldata' . So I am not calling a function directly. I think I can swap to a function to do this.
Can you point me at an example of how to implement the response and acDataErrAdded .

if you would like anything from

http://www.animationartgallery.com

I will try to get you a decent discount for your help.
Many Thanks
Aidy

 

[930driver]

Drop the sub below into a database module and save it. When you paste it it watch for line wraps which might screw things up.

In the combo's notinlist event procedure (in the module, not on the property sheet) call the sub like this:

OnNotInList NewData, Response, "Thing", "addtbldata"

Where I put "Thing" put in whatever is being added, for instance "Customer", or "Address" or whatever, it will be displayed in the msgbox.



Public Sub OnNotInList(NewData As String, Response As _
Integer, Title As String, TheForm As String)
'/Purpose: open a form, add a record
'/Created: 6/17/1997 08:58 AM
'/Created By: nkling
On Error GoTo Err_OnNotInList

' Add a new record by typing a name in the combo box.
On Error GoTo Error

Dim intNewRecord As Integer
Dim strTitle As String
Dim intMsgDialog As Integer

' Display message box asking if user wants to add _
a new record.
strTitle = Title + " Not In List"
intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
intNewRecord = MsgBox("Do you want to add a new " & _
Title & "?", intMsgDialog, strTitle)

If intNewRecord = vbYes Then
' Remove new data from combo box so
' control can be requeried when user returns to form.
On Error Resume Next
DoCmd.RunCommand acCmdUndo
On Error GoTo Error

' Open Add form.
DoCmd.OpenForm TheForm, acNormal, , , _
acAdd,acDialog, NewData

' Continue without displaying default error message.
Response = acDataErrAdded
End If

leave:
Exit Sub

Error:
MsgBox Err.Description
Resume leave

End Sub "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"