Refresh Combo Boxes But On Tabbed Sub Forms?

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

Guest

Hi guys, I know 'how do I refresh a combo box' has been asked and solved a
million times now but the solution does not seem to apply to my particular
problem.

I have a tabbed menu system, on which one page is a page showing the details
for any particular company, the idea is that you type the first few letters
of either the company name or the contact within a company and then their
details are loaded below. This works fine, however when you use the same for
to add a new contact, the combo boxes do no requery without closing and
reloading the whole form.

I have tried adding Me.ComboBox.Requery in the after update event of a field
on the form but this does not work.

So how I do requery two combo boxes that are in a sub form of a tabbed menu?
Thanks.
 
You are looking for the NotInList event. Here is a generic one that you can
use almost everywhere in your application. Just put it in a standard module:

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:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub

In the combo's NotInList event, you'd use:

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

AddToList "frmCustomers", "Customer", NewData, Response

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
To refer to a control on a subform and requery it from the main form, the
syntax is

Me!NameOfSubformControl.Form!NameOfControlOnSubform.Requery

The NameOfSubformControl is the name of the container control on the main
form that holds the subform. This may or may not be the same as the name of
the subform itself. To get this name, open the main form in design view,
open the Properties sheet, and click on the subform ONE time. The Properties
sheet should show the name of the subform control. If you click more than
once, you'll be in the subform and the Properties sheet will show
information for the subform, not the control holding it.
 
Back
Top