Refreshing combo boxes - with a difference?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys, i've been reading the discussion boards this morning trying to find
out how to requery a combo box when the current window loses focus.

I've tried the Me.Requery statement on the Lostfocus event but this doesn't
seem to work so hopefully someone can offer an alternative solution.

I have two forms - Quotations and Customer. When the user is entering a
quotation in Quotation funnily enough, there will be a time when the customer
they are selecting from the combo box called customer_name isn't there and
thus they need to enter the customer in the Customer database. The user then
clicks a button which takes them to the customer form. The problem is that
when they enter the customer and then go back to the quotations database,
even though I've put the Me.Requery on the lostfocus event, the only way I
can seem to get to the new customer to appear in the combo box is to close
the form and reload it, which is far from ideal.

So any suggestions?
 
Check the helpfile for the NotInList event of the combo box. I use a generic
function in a Standard Module to pass to any combo box:

Public Sub AddToList(strFormName, strItem, NewData, Response)
'---------------------------------------------------------------------------
------------
' Procedure : AddToList
' Date : 10/31/2004 Revised
' Author : Arvin Meyer
' Purpose : Generic Not In List Code
' Usage : AddToList "YourFormName", "YourComboItem", NewData, Response
'---------------------------------------------------------------------------
------------
On Error GoTo Error_Handler

Dim intNewItem As Integer
Dim strMsgText As String
Dim intMsgArg As Integer
Dim strTitle As String

strMsgText = "This " & strItem & " is not in the list. Do you want to
add a new " & strItem & "?"
intMsgArg = vbYesNo + vbQuestion + vbDefaultButton1
strTitle = "Not In This List"
intNewItem = MsgBox(strMsgText, intMsgArg, strTitle)

If intNewItem = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm strFormName, acNormal, , , acFormAdd, acDialog,
NewData
Response = acDataErrAdded
End If

Exit_Here:
Exit Sub

Error_Handler:
Call ErrorLog("basUtilities", "AddToList")
Resume Exit_Here

End Sub


Public Function ErrorLog(objName As String, routineName As String)
Dim db As DAO.Database

Set db = CurrentDb

Open "C:\Error.log" For Append As #1

Print #1, Format(Now, "mm/dd/yyyy, hh:nn:ss") & ", " & db.Name & vbCrLf & _
"An error occured in: " & objName & ", Procedure: " & routineName &
vbCrLf & _
"User: " & CurrentUser() & ", Error#: " & Err.Number & ": " &
Err.Description

Close #1
End Function

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top