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

Posted on 2006-05-01
Medium Priority
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
  • 2
LVL 44

Expert Comment

ID: 16576514
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

ID: 16581981
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

Arthur_Wood earned 2000 total points
ID: 16598281
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



Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

579 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