Problem.



Q: I have created an input form which is linked to a

(e.g) customer name table. On the input form I have

used the combo box to pull down names of my customers,

but if they are not on the list I would like to add

them to the list which inturn updates the customer

name table.



Help

I like to use the NotInList event of the combobox in conjunction with a data 

entry PopUp form for the situation you?re describing.



Follow these steps:



1)   Create a new form and call it frmCustomerPopUp. This form will be 

opened exclusively by your combo boxes NotInList event.  The record source 

of frmCustomerPopUp is the same as the rowsouce of your combo box (a lookup 

table called tblCustomers, for example). Add a customer last name and first 

name field to the form and name them txtLastName and txtFirstName.  Add 2 

command buttons and name them cmdCancel and cmdOK.  Now paste the following 

code into the frmCustomerPopUp module. frmMain in the code below would be 

the name of the form that contains you?re combo box.





Private Sub cmdCancel_Click()

    DoCmd.RunCommand acCmdUndo

    DoCmd.Close

End Sub



Private Sub cmdOK_Click()

     Me.Visible = False

     'Flow returns to NotInList() in basUtility.

End Sub



Private Sub Form_Load()

    If Not IsNull(Me.OpenArgs) Then

        Me.txtLastName.Value = Me.OpenArgs

    End If

    'Form launches.

End Sub



Private Sub Form_Open(Cancel As Integer)



    'Call function IsLoaded in basUtility module.

    If Not IsLoaded("frmMain") _

        And IsNull(Me.OpenArgs) Then

        MsgBox "You Can Only Open this Form from the Main Form"

        Cancel = True

    End If

    'Flow moves to the form Load event.

End Sub





2) Now add the following code in the NotInList event of you combobox



Private Sub cboCustomer_NotInList(strNewData As String, intResponse As 

Integer)

    intResponse = NotInList("frmCustomerPopUp", strNewData)

End Sub



3) Create a new module and name it basUtility. Paste the following code into 

it.



Function NotInList(strFormName As String, strNewData As String) As Integer

    Dim strMsgBox As String



    strMsgBox = "Customer not found in list. Add?"



    If MsgBox(Prompt:=strMsgBox, Buttons:=vbYesNo + vbQuestion) = vbYes Then

        DoCmd.OpenForm FormName:=strFormName, _

            DataMode:=acFormAdd, _

            Windowmode:=acDialog, _

            OpenArgs:=strNewData

            ' Flow moves to Open Event of Popup form.



        'Flow returns from click Event of form's "OK" button and is sent to 

the IsLoaded function

        'in basUtility a second time.

        If IsLoaded(strFormName) Then

            NotInList = acDataErrAdded

            DoCmd.Close ObjectType:=acForm, _

                ObjectName:=strFormName

        Else

            NotInList = acDataErrContinue

        End If

    Else

        NotInList = acDataErrContinue

    End If

'Flow returns to NotInList Event.

End Function



'Called first by popup form's Open Event and a second time by the NotInList 

function.

Function IsLoaded(ByVal strFormName As String) As Integer

    IsLoaded = False



    Dim frm As Form



    For Each frm In Forms

        If frm.Name = strFormName Then

            IsLoaded = True

        End If

    Next frm

End Function



4)  The flow goes like this:



a) Open your main form and type in a customer last name that doesn?t exist 

in your combobox. Press enter or tab.

b) Control moves to the NotInListEvent of your combo box and the new 

customer name is stored in the strNewData variable.

c) Control moves to the NotInList function in basUtility which receives 

frmCustomerPopUp and the new customer name as arguments. A message box is 

opened which asks you if you want to add the new customer to the combobox 

list. Click yes when prompted. frmCustomerPopUp is opened with the new 

customer last name as it?s opening argument.

d) Control moves to the Open event of frmCustomerPopUp which calls the 

IsLoaded function in basUtility.

e) Control moves to the IsLoaded function in basUtility. You don?t want 

users to be able to open frmCustomerPopUp unless the main form is loaded.

f) After control returns to the Open event of frmCustomerPopUp, the 

Form_Load event is called. This event assigns the value of the new customer 

last name to txtLastName on frmCustomerPopUp.

g) frmCustomerPopUp appears with txtLastName already filled. Enter values 

for any other text boxes you added to the form (you can add any fields from 

the forms record source) and click OK.

f) Contol moves to the click event of cmdOK which sets frmCustomerPopUps 

visible property to false.

g) Control returns to the NotInList event of basUtility which calls the 

IsLoaded function a second time.

h) Your new customer has (hopefully) been added to your combobox?s row 

source table.



Anyway, read up on the NotInList event and PopUp forms for this sort of 

thing. Hope that helps a bit.







>From: Kenneth Mungwira <kennethmungwira@y...>

>Reply-To: "Access" <access@p...>

>To: "Access" <access@p...>

>Subject: [access] Combo Box Update-Input Form

>Date: Fri, 10 Aug 2001 09:53:00 -0700 (PDT)

>

>Problem.

>

>Q: I have created an input form which is linked to a

>(e.g) customer name table. On the input form I have

>used the combo box to pull down names of my customers,

>but if they are not on the list I would like to add

>them to the list which inturn updates the customer

>name table.

>

>Help

>

>

I feel you should create a query from the customer name table.  Then

recreate the combo on your form which will filter from the query.



Robert L. Rogers 

Application Developer, Rexel Branch Electric

(xxx)-xxx-xxxx





-----Original Message-----

From: Kenneth Mungwira [mailto:kennethmungwira@y...]

Sent: Friday, August 10, 2001 12:53 PM

To: Access

Subject: [access] Combo Box Update-Input Form





Problem.



Q: I have created an input form which is linked to a

(e.g) customer name table. On the input form I have

used the combo box to pull down names of my customers,

but if they are not on the list I would like to add

them to the list which inturn updates the customer

name table.



Help

I got the following code a long time ago.  I believe Helen Feddema 

(http://www.helenfeddema.com) provided it in one of her Woody's Access 

Watch (http://woodyswatch.com/access) articles.  I have used it and it 

works well.



ComboBox Add New Records On the Fly



 Now for another use of combo boxes - on-the-fly addition of

  new items to a lookup table used as a combo box's row

  source.  This type of combo box is often called an add-to

  combo box, and it is a very useful tool.  When do you need

  an add-to combo box?  Basically, if you want to limit the

  selections for a field to items in a combo box's drop-down

  list, but you also want to let users add items to the list,

  so they will be available as list selections in the future.

  (If you don't want to allow any additions, but just

  restrict selections to those already in the list, just set

  the combo box's Limit to List property to Yes.)



  You can use the NotInList event procedure to allow users to

  add a new record to a lookup table from code, say to add a

  new sales district to a lookup table of sales districts.

  You can also use a slightly different procedure for this

  event to allow addition of more complex information, such

  as opening a New Customer form to create a new Customer

  record on-the-fly, and then add the appropriate information

  from the new record to a combo box's list.  This column

  describes the simpler procedure, which just takes the new

  entry typed by the user and (after requesting a

  confirmation) adds it to the lookup table (I'll save the

  more complicated version for next week's column).



  Place a combo box on the form, and select the appropriate

  lookup table as the combo box's row source.  Make sure the

  combo box's Limit to List property is set to Yes, and

  create an event procedure for its NotInList property.  I

  like to add the appropriate prefixes to the procedure's

  arguments, in accordance with the LNC naming convention.

  My standard NotInList event procedure is listed below:



  Private Sub cboSelect_NotInList(strNewData As String,intResponse As 

Integer)



  'Set Limit to List property to Yes



     Dim i As Integer

     Dim strTitle As String

     Dim intMsgDialog As Integer

     Dim strMsg1 As String

     Dim strMsg2 As String

     Dim strMsg As String

     Dim ctl As Control

     Dim dbs As Database

     Dim rst As Recordset

     Dim strTable As String

     Dim strEntry As String

     Dim strFieldName As String



     strTable =3D "tlkpTable"

     strEntry =3D "Entry"

     strFieldName =3D "FieldName"

     Set ctl =3D Me![cboSelect]



     'Display a message box asking if the user wants to add

     'a new entry

     strTitle =3D strEntry & " Not in List"

     intMsgDialog =3D vbYesNo + vbExclamation + vbDefaultButton1

     strMsg1 =3D "Do you want to add "

     strMsg2 =3D " as a new " & strEntry & " entry?"

     strMsg =3D strMsg1 + strNewData + strMsg2

     i =3D MsgBox(strMsg, intMsgDialog, strTitle)



     If i =3D vbNo Then

        intResponse =3D acDataErrContinue

        ctl.Undo

        Exit Sub

     ElseIf i =3D vbYes Then

        'Add new record to lookup table

        Set dbs =3D CurrentDb

        Set rst =3D dbs.OpenRecordset(strTable)

        rst.AddNew

        rst(strFieldName) =3D strNewData

        rst.Update

        rst.Close



        'Continue without displaying default error message

        intResponse =3D acDataErrAdded



     End If



  End Sub



  You'll note that I use variables extensively in this

  procedure - more than is really necessary.  This is for

  convenience in reusing the standard code - all I have to do

  is replace the text in the four variable setting lines

  after the Dim statements, and the code is ready for use

  with a new combo box.



  After creating the event procedure and setting its

  variables to match your combo box name, lookup table name,

  entry name and field name, when the user enters a new item

  and tabs away from the combo box, a message will appear

  asking if the user wants to add a new entry (for example,

  "Do you want to enter Midwest as a new Sales District

  entry?"), picking up the new entry text and several

  variables in the code.  If the user selects the Yes button

  in the dialog, the code adds the new entry to the lookup

  table, and suppresses the display of the Not in List error

  message.  The new entry is saved to the combo box's bound

  field, and is also available in its drop-down list for

  future selection.



John Ruff - The Eternal Optimist =E2=98=BA



-----Original Message-----

From: Kenneth Mungwira [mailto:kennethmungwira@y...]

Sent: Friday, August 10, 2001 9:53 AM

To: Access

Subject: [access] Combo Box Update-Input Form



Problem.



Q: I have created an input form which is linked to a

(e.g) customer name table. On the input form I have

used the combo box to pull down names of my customers,

but if they are not on the list I would like to add

them to the list which inturn updates the customer

name table.



Help

You need to use the ComboBox's NotInList Event. Either by macro or VBA

code you need to take the item you entered in the combobox and add it to

the table you are bound to. If you are going to use code and would like

an example, write directly to me and I will send you one. I do not use

macros but the same could be done there.



Richard Lobel

Accessible Data

richard@a... <mailto:richard@a...> 

Cell:  (xxx) xxx-xxxx

Fax:  (xxx) xxx-xxxx

Kenneth--you're getting good advice to use the NotInList event.  Just a side

point, in case no one else has already mentioned it--you've got to set the

LimitToList property of the combo to Yes, or else the NotInList event will

not fire.



Cheers,



-Roy



-----Original Message-----

From: Bob Bedell [mailto:bdbedell@m...]

Sent: Friday, August 10, 2001 11:46 AM

To: Access

Subject: [access] Re: Combo Box Update-Input Form





I like to use the NotInList event of the combobox in conjunction with a 

data entry PopUp form for the situation you?re describing.



Follow these steps:



1)   Create a new form and call it frmCustomerPopUp. This form will be 

opened exclusively by your combo boxes NotInList event.  The record source 

of frmCustomerPopUp is the same as the rowsouce of your combo box (a 

lookup table called tblCustomers, for example). Add a customer last name 

and first name field to the form and name them txtLastName and 

txtFirstName.  Add 2 command buttons and name them cmdCancel and cmdOK.  

Now paste the following code into the frmCustomerPopUp module. frmMain in 

the code below would be the name of the form that contains you?re combo 

box.





Private Sub cmdCancel_Click()

    DoCmd.RunCommand acCmdUndo

    DoCmd.Close

End Sub



Private Sub cmdOK_Click()

     Me.Visible = False

     'Flow returns to NotInList() in basUtility.

End Sub



Private Sub Form_Load()

    If Not IsNull(Me.OpenArgs) Then

        Me.txtLastName.Value = Me.OpenArgs

    End If

    'Form launches.

End Sub



Private Sub Form_Open(Cancel As Integer)



    'Call function IsLoaded in basUtility module.

    If Not IsLoaded("frmMain") _

        And IsNull(Me.OpenArgs) Then

        MsgBox "You Can Only Open this Form from the Main Form"

        Cancel = True

    End If

    'Flow moves to the form Load event.

End Sub





2) Now add the following code in the NotInList event of you combobox



Private Sub cboCustomer_NotInList(strNewData As String, intResponse As 

Integer)

    intResponse = NotInList("frmCustomerPopUp", strNewData)

End Sub



3) Create a new module and name it basUtility. Paste the following code into



it.



Function NotInList(strFormName As String, strNewData As String) As Integer

    Dim strMsgBox As String



    strMsgBox = "Customer not found in list. Add?"



    If MsgBox(Prompt:=strMsgBox, Buttons:=vbYesNo + vbQuestion) = vbYes Then

        DoCmd.OpenForm FormName:=strFormName, _

            DataMode:=acFormAdd, _

            Windowmode:=acDialog, _

            OpenArgs:=strNewData

            ' Flow moves to Open Event of Popup form.



        'Flow returns from click Event of form's "OK" button and is sent to 

the IsLoaded function

        'in basUtility a second time.

        If IsLoaded(strFormName) Then

            NotInList = acDataErrAdded

            DoCmd.Close ObjectType:=acForm, _

                ObjectName:=strFormName

        Else

            NotInList = acDataErrContinue

        End If

    Else

        NotInList = acDataErrContinue

    End If

'Flow returns to NotInList Event.

End Function



'Called first by popup form's Open Event and a second time by the NotInList 

function.

Function IsLoaded(ByVal strFormName As String) As Integer

    IsLoaded = False



    Dim frm As Form



    For Each frm In Forms

        If frm.Name = strFormName Then

            IsLoaded = True

        End If

    Next frm

End Function



4)  The flow goes like this:



a) Open your main form and type in a customer last name that doesn?t exist 

in your combobox. Press enter or tab.

b) Control moves to the NotInListEvent of your combo box and the new 

customer name is stored in the strNewData variable.

c) Control moves to the NotInList function in basUtility which receives 

frmCustomerPopUp and the new customer name as arguments. A message box is 

opened which asks you if you want to add the new customer to the combobox 

list. Click yes when prompted. frmCustomerPopUp is opened with the new 

customer last name as it?s opening argument.

d) Control moves to the Open event of frmCustomerPopUp which calls the 

IsLoaded function in basUtility.

e) Control moves to the IsLoaded function in basUtility. You don?t want 

users to be able to open frmCustomerPopUp unless the main form is loaded.

f) After control returns to the Open event of frmCustomerPopUp, the 

Form_Load event is called. This event assigns the value of the new 

customer last name to txtLastName on frmCustomerPopUp.

g) frmCustomerPopUp appears with txtLastName already filled. Enter values 

for any other text boxes you added to the form (you can add any fields 

from the forms record source) and click OK.

f) Contol moves to the click event of cmdOK which sets frmCustomerPopUps 

visible property to false.

g) Control returns to the NotInList event of basUtility which calls the 

IsLoaded function a second time.

h) Your new customer has (hopefully) been added to your combobox?s row 

source table.



Anyway, read up on the NotInList event and PopUp forms for this sort of 

thing. Hope that helps a bit.







>From: Kenneth Mungwira <kennethmungwira@y...>

>Reply-To: "Access" <access@p...>

>To: "Access" <access@p...>

>Subject: [access] Combo Box Update-Input Form

>Date: Fri, 10 Aug 2001 09:53:00 -0700 (PDT)

>

>Problem.

>

>Q: I have created an input form which is linked to a

>(e.g) customer name table. On the input form I have

>used the combo box to pull down names of my customers,

>but if they are not on the list I would like to add

>them to the list which inturn updates the customer

>name table.

>

>Help

While using the VB coding for the combo box, I still

can not get the coding to start.



1) does there have to be a look up table

2) LimitToList is set to Yes



Please Help

Dear Roy

I continue to get a Can't Find Macro error

when engaging my VB Coding for updating an input file.

Hmmm... that's puzzling.  You are using VBA code here, right?  Does the

NotInList event property have the entry "[Event Procedure]" in it?  If you

click on the ellipsis button to the right of the property (e.g., the three

dots) are you taken to the VBA editor with your code?



-Roy



-----Original Message-----

From: Kenneth Mungwira [mailto:kennethmungwira@y...]

Sent: Monday, August 13, 2001 12:30 PM

To: Access

Subject: [access] Re: Combo Box Update-Input Form





Dear Roy

I continue to get a Can't Find Macro error

when engaging my VB Coding for updating an input file.

Hi Kenneth,



I think Roy might be right on the money with his comment below. You'll 

notice in the code I posted that the NotInList event procedure HEADER for 

your combo reads:



Private Sub cboCustomer_NotInList(strNewData As String, intResponse As

Integer)



But if you click the old ellipsis in your properties window, the default 

event procedure header reads:



Private Sub cboCustomer_NotInList(NewData As String, Response As

Integer)



Notice I modified the default header variables to read strNewData instead of 

NewData and intResponse instead of Response (just 'cause i'm a naming 

conventions freak). Make sure you made those changes or basUtility won't 

recieve your arguments. Plus, seems like we're dealing with a brand new VBA 

editor anomoly here. If you paste code into an event procedure with modified 

parameter list variable names, the editor doesn't seem to pick up your 

routine. So instead of pasting the code, click your ellipsis and change the 

names of the header variables manually: just add the "str" and "int" prefix.



Also, I tried out the code I posted by cutting and pasting it, and low and 

behold, commented text doesn't survive translation very well on this 

listserv. The apostrophes tend to get dropped. So be weary of any commented 

text that may have inadvertantly been incoporated into a routine.



Everything else seems to check out.



Bob



>From: "Pardee, Roy E" <roy.e.pardee@l...>

>Reply-To: "Access" <access@p...>

>To: "Access" <access@p...>

>Subject: [access] Re: Combo Box Update-Input Form

>Date: Mon, 13 Aug 2001 12:42:37 -0700

>

>Hmmm... that's puzzling.  You are using VBA code here, right?  Does the

>NotInList event property have the entry "[Event Procedure]" in it?  If you

>click on the ellipsis button to the right of the property (e.g., the three

>dots) are you taken to the VBA editor with your code?

>

>-Roy

>

>-----Original Message-----

>From: Kenneth Mungwira [mailto:kennethmungwira@y...]

>Sent: Monday, August 13, 2001 12:30 PM

>To: Access

>Subject: [access] Re: Combo Box Update-Input Form

>

>

>Dear Roy

>I continue to get a Can't Find Macro error

>when engaging my VB Coding for updating an input file.

>

>

Dear Roy,



Yes the I am using VB coding, still not engaging.



Kenneth

That's awfully strange.  I think I'd try this out on a new form, with a new

combo & start from scratch.



-Roy



-----Original Message-----

From: Kenneth Mungwira [mailto:kennethmungwira@y...]

Sent: Tuesday, August 14, 2001 6:34 AM

To: Access

Subject: [access] Re: Combo Box Update-Input Form





Dear Roy,



Yes the I am using VB coding, still not engaging.



Kenneth