Re: [MS_AccessPros] Update another field from NotInList event

 

Khalid-

If you put both fields in one table, you need one combo box that displays both fields (city, country), and instead of simply inserting the data, you need to open a form in data entry and dialog mode (to halt the calling code) that lets the user enter both fields.  You can pass what the user entered in the Openargs parameter.  In the Load event of the data entry form, parse the two parts (look for the comma with InStr) and place the values in the two controls.  You'll also need code after the form closes (probably via a Save and Close button on the dialog form) to check to see that the data got entered.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On May 4, 2017, at 11:01 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John,

I have a Form "frmAddressSubform".

It has one Control "cboResidenceCity" , on its NotInList event following is the code:

    'On Error GoTo cboResidenceCity_NotInList_Err

    Dim intAnswer As Integer

    Dim strSQL As String

    intAnswer = MsgBox("City Name '" & Chr(34) & NewData & _

        Chr(34) & "' is not currently listed." & vbCrLf & _

        "Would you like to add it to the list now?" _

        , vbQuestion + vbYesNo + vbDefaultButton2, "SAC Pakistan - Guide")

    If intAnswer = vbYes Then

         strSQL = "INSERT INTO MainCity ([MainCityName])" & _

                 "VALUES ('" & NewData & "');"

        DoCmd.SetWarnings False

        DoCmd.RunSQL strSQL

        DoCmd.SetWarnings True

        MsgBox "The new 'City Name' " & Chr(34) & NewData & Chr(34) & " has been added to the list." _

            , vbInformation, "SAC Pakistan - Guide"

        Response = acDataErrAdded

    Else

        MsgBox "Please choose a 'City Name' from the list." _

            , vbInformation, "SAC Pakistan - Guide"

        Response = acDataErrContinue

    End If

cboResidenceCity_NotInList_Exit:

    Exit Sub

'------------------

Next control is "cboCountry" with similar NotInList event.

I am thinking to add a field "Country" in tbl "MainCity" and get its value as

=[cboResidenceCity].Column(1) and make control as text field instead of combobox.

My main question is if i do so, when "cboResidenceCity" NotInList event triggers how 

will the field "Country" be filled when it is a new "MainCity" ?

Please help.

Regards,

Khalid

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___