combo requery problem

[ElizCat]

I have a combo box used to select a StyleID, which happens to also be the
primary key for the Style table. If the style does not exist in the list,
the user can double-click to add a new style, launching a popup form in
dialog mode. I've cobbled together the code below from a couple of examples
(from similar questions in this forum), but when I close the popup form, my
combo box on the main form does not reflect the updated value. I don't want
to use NotInList, since the StyleID is a primary key, and I don't want the
users typing in those values. I'm using Access 2002.

Can any of you seasoned pros help me figure out what I'm doing wrong?
Thanks - ElizCat

'***************test code******************
Private Sub StyleID_DblClick(Cancel As Integer)
'double click to add new style to Style

Dim FormName As String
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

FormName = "StylePopUp"
strMsg = "Add new Style?"
mbrResponse = MsgBox(strMsg, _
vbYesNo + vbQuestion)
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm (FormName), _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
'Stop Here and wait until the form goes away

If fIsLoaded(FormName) Then
Response = acDataErrAdded
DoCmd.Save
Me!StyleID.Requery
DoCmd.Close acForm, (FormName)
Else
Response = acDataErrContinue
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub

 

[Allan Murphy]

On your StyleID combo add the Got Focus event
Then add
docmd.requery "StyleID"

THe above commands updates the combo box every time it is selected

 

[ElizCat]

Allan,
thanks for your suggestion. The combo box list appears to be populating
fine now, but new entry is not selected. I'd like the last Style entry to
appear selected in the combo box upon re-entry. Is there another simple
command I can use to make that happen?

thank you very much!
ElizCat

 

[Brian Bastl]

Hi ElizCat,

Your reasoning does not preclude using the Not In List event. You can still
force the user to input the new value via a pop-up form. Just set the
combo's Limit to List property to Yes. The following will do exactly what
you're trying to achieve (minus Error handling):

Private Sub StyleID_NotInList(NewData as String, Response As Integer)

Dim strMsg As String

strMsg = "Add new style?"

If MsgBox(strMsg, vbYesNo) = vbYes Then

'open form and pass NewData
DoCmd.OpenForm "StylePopUp", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded 'tell access to requery combo

Else
'cancel event and undo typing
Response = acDataErrContinue
Me.Undo

End If
End Sub


Then in the On Load event of StylePopUp, you can assign the passed value to
the appropriate text control:

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then

Me.MyStyleTextBox = Me.OpenArgs

End If

End Sub

HTH,
Brian

 

[Allan Murphy]

In response to your question no there is no command for this.

You could use the NotInList event without the need to use your popup.

The users enters a StyleID.
If the entry is not in the list a message box is displayed
The user selects YES and the StyleID is added to the table.
You may have to change tbl_Styles to the name of the table that hold the
StyleID.



Private Sub StyleID_NotInList(NewData As String, Response As Integer)

Dim strmsg As String
Dim rsy As DAO.Recordset
Dim db As DAO.Database

strmsg = "'" & UCase$(NewData) & "' is not in the list. "
strmsg = strmsg & "Would you like to add this Style ? "

If vbNo = MsgBox(strmsg, vbYesNo + vbQuestion, " New StyleID") Then
resposne = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_styles")

rst.AddNew
rst("StyleID") = UCase$(NewData)
rst.Update

Response = acDataErrAdded
End If

End Sub

 

[ElizCat]

Thank you both, Brian and Allan, for the suggestions, especially on how I
might use NotInList to achieve my goals. I hadn't thought to use NotInList
with the Limit to List property. I'll give your tips a try today and let you
know how I succeed!

ElizCat