Insert two values via dropdown box (on Not in list)

Posted on 2006-05-01
Last Modified: 2012-06-21
I have a MS access database and one of the fields on the form (suburb_name) is a dropdown box that looks at the SUBURB table and returns the list of sall active suburbs (other colums in the same table are: id, postcode, state and status).

What I would like to achieve is to alllow users to pick suburb from the dropdown list and if the value desn't exist to be able to add it tot the source table. I have two problems:
1) How to prompt user to enter subub name and postcode one after another and to inseret them in to the SUBURB table.
2) Second problem is to populate onother text field (post_code) on the same form with the postcode value from the SUBURB table where the value from the above mantioned dropdown box on the form is the same as in the SUBURB table.
Example: dropdown box for SUburb ihas values of  A, B, C, D. If I pick A I want another field to show value of 1)
Thanks vilkop
Question by:VilkoP
    LVL 44

    Expert Comment

    In order to trigger the NotInList event, the user has already entered the Name of the suburb that is 'not in the list', and that entered value is passed as an argument to the NotInList event handsler, so there is no need to ask the user to enter that value again.  But in order to get the PostCode as well, you can preset anerth user-defined firm, to have the user enter the postcode, and there you would validate the entry, to make sure it is ion the proper form.  When the user closes this PostCode form, then you add the new record to the SUBURB table (suburb_name, postcode, status and status) and set the return code (so the combo-box will be re-queried) and ent the NotInList event handler.  At the same time, you can show the post-code that was entered in the text box.


    Author Comment

    Hi Arthur,

    Thanks for the information, I am however unsure about few things you said:
    "you can preset anerth user-defined firm, to have the user enter the postcode"
    (when not in list tit adds it to the table (works fine) how to trigger the other value to be entered ?)
    I have created a separate form for postcode, but its source is a table too, should it be free from that link,
    how do I write to the same row as initially entered suburb name..something like vhere value = form.filed.value ?
    How would you do the return code ?
    Thanks in advance. vilkop
    LVL 44

    Accepted Solution

    when you want the user to enter additional data, you should NOT bind that form to another table.  YOu might want to validate the user's entry against another table, but do NOT base the form on that other table.

    I would create the new form, and have a code like this in the form's code ( I would provide both a Done button - to indicate that the entry is to be accepted, and a Cancel button - to indicate that the user want's to cancel the entry of a new postcode)

    Private m_PostCode as String
    Private m_Cancel as Boolean
    Private m_Done as Boolean ' flag to indicate that the user is finished with the form, one way or the other

    Public Property Get PostCode() as String
        PostCode = m_PostCode
    End Property

    Public Property Done() as Boolean
        Done = m_Done
    End Property

    Private Sub cmdCancel_Click()
        m_Cancel = True
        m_Done = True
        Me.Visible = False
    End Sub

    Private Sub cmdDone_Click()
       m_Cancel = False
       m_Done = True
       m_PostCode = txtPostCode.Text
       Me.Visible = False
    End Sub

    then, in the form wher the new Suburb is being added via the NotInList event Handler:

    Private Sub cboSuburb_NotInList(...)

    Dim AddCode as frmPortCode
    set AddCode = new frmPostCode
    AddCode.SHow vbModal
    Do while Not AddCode.Done
    If Not AddCode.Cancel then
        'add the newly added post code to the information to be saved in the DB for this suburb
    End If
    Set AddCode = Nothing



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now