Access 2016 – using a Form combo box

Question

I am new to Access and so far I have created a database and now a Form for entering new records into my database. In my database I have a heading called Source. I am trying to use a combo box to enter the source of my new record on my form.

I understand in Design View I can go to the Property Sheet, click on Data, and choose one of three options, (Table/Query or Value List or Field List).

I know how to set up a Value List but not Table/Query or Field List (this may be the problem?).

However I want to set this combo box to list information already listed in the Source previously entered in the database records. I would prefer not to have it set up as a Value List as there are too many sources. How then can I get the Combo box to show me previously entered sources from previous records in my database. I am not even sure if this can be done without writing code which is beyond me at this stage. Apologies in advance if this question has already been asked. If it can't be done I will accept that at this stage.

Answer 1

I am new to Access and so far I have created a database and now a Form for entering new records into my database. 

Access is not like a lot of programs where you can just jump in and start using it. There is a learning curve for Access and you need to do your homework before you start. Part of that homework is understand relational database design and normalization. The foundation of a well run database is a properly normalized table structure.

Ken describes that in his response. The Source field (divisions in a table are called fields, not headings) sounds like it needs to pulled from a lookup table. Lookup tables in Access are the same as any table, but they are defined by the way they are used. A lookup table typically has the structure that Ken suggested. 

Sources

….SourceID  (autonumber primary key)

….Source (short text)

Now you can use the NotInList event as Ken described or you can simply add Sources to your sources table when the combobox won't let you type in a value not in the list.

Answer 2

That is not the way sources should be modelled in a relational database.  You should have a separate 'referenced' table to model the Sources entity type:

Sources

….SourceID  (autonumber primary key)

….Source (short text)

In your current table (the 'referencing' table) you should have a SourceID foreign key column of long integer data type (NB: not an autonumber).  In a form for entering data into the referencing table set up a combo box like this:

ControlSource:   SourceID

RowSource:     SELECT SourceID, Source FROM Sources ORDER BY Source;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

To fill the Sources table with values from your current table execute an 'append' query as follows:

INSERT INTO Sources(Source)

SELECT DISTINCT Source

FROM [YourCurrentTablenameGoesHere];

Then add the SourceID foreign key column to your current table and execute the following 'update' query:

UPDATE Sources INNER JOIN [YourCurrentTablenameGoesHere]

ON Sources.Source = [YourCurrentTablenameGoesHere].Source

SET [YourCurrentTablenameGoesHere].SourceID = Sources.SourceID;

To create the above queries open the query designer, but don't add any tables.  Swith the view to SQL View and paste in the SQL statements I've given you above, changing YourCurrentTablenameGoesHere to the real name of your current table.  You don't need to save the queries; merely run each after entering the SQL statement by clicking the Run icon on the Design ribbon.

Then create a relationship between your current table and sources on SourceID and enforce referential integrity.

Once you are happy that the SourceID column in your current table's SourceID has been populated correctly you can delete the original Source column from the table in design view..

In your form, when you want to enter a new source not currently represented in the database you can do so in the combo box's NotInList event procedure with the following code:

    Dim ctrl As Control

    Dim strSQL As String, strMessage As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    strSQL = "INSERT INTO Sources(Source) VALUES(""" & _

            NewData & """)"

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        CurrentDB.Execute strSQL, dbFailOnError

        Response = acDataErrAdded

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

When you need to add a new source you simply type the text value into the combo box and confirm the addition at the prompt.  If you are unfamiliar with entering code into a form's or control's event procedures, this is how it's done in form design view:

1.  Select the form or control as appropriate and open its properties sheet if it's not already open.

2.  Select the relevant event property and select the 'build' button (the one on the right with 3 dots).

3.  Select Code Builder in the dialogue and click OK.  This step won't be necessary if you've set up Access to use event procedures by default.

4.  The VBA editor window will open at the event procedure with the first and last lines already in place.  Enter or paste in the code as new lines between these.

It goes without saying that before undertaking changes like the above to tables it is imperative that you back-up the tables first.