Keeping Relational Table Current-Access 2007

[Stumped-need help]

I am creating my first database in Access 2007 and found when completing
certain fields, my data is redundant. I thought a good solution to avoid
typing was to create a new single field table, linking it to my primary table
in my database with a many to one relationship, and then simply clicking the
appropriate value(s) from the drop down list in that field. This worked
great for the first 20 or so records. Then I needed new values for that
field. I updated the second table with additional records, but can't seem to
get them to show in my primary table when choosing to select that field.

How can I get the additional values to be reflected in that drop down list?

 

[Stumped-need help]

Never mind. This field has now been updated with the additional values. Not
sure how long for the delay, or what I can do to accelerate the
synchronization.

 

[John W. Vinson/MVP]

I am creating my first database in Access 2007 and found when completing
certain fields, my data is redundant. I thought a good solution to avoid
typing was to create a new single field table, linking it to my primary table
in my database with a many to one relationship, and then simply clicking the
appropriate value(s) from the drop down list in that field. This worked
great for the first 20 or so records. Then I needed new values for that
field. I updated the second table with additional records, but can't seem to
get them to show in my primary table when choosing to select that field.

How can I get the additional values to be reflected in that drop down list?

A one-field table would be quite unusual. I think the delay may be
because updating an underlying table will not automatically requery a
combo box based on that table (unless you close and open the form with
the combo box, or requery it by pressing the F9 key, or doing so
programmatically). Just creating a one-field table will not by itself
avoid redundancy though! What are the structures of your tables, what
is this one field, and how are you doing the update?

 

[Ken Sheridan]

You can use a combo box's NotInList event procedure to insert a new row into
the referenced table by typing the value directly into the combo box. The
combo box's LimitToList property should be True (Yes). This does mean that
the data must be entered via a form based on the table of course, but data
should really be entered via forms in any case, not in raw datasheet view of
the table.

As an example here's the code for the NotInList event procedure of a combo
box used for entering cities:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new city to list?"

strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

Set cmd = Nothing

End Sub

Ken Sheridan
Stafford, England

 

[Stumped-need help]

I am just playing to get practical use for Access. I am creating a database
of my recorded DVD collection. One of the fields I have created is for the
main actors in the movie. My intention is to be able to query my database to
determine what movies do I have with x actor, or what movies do I have rated
G or PG when the children come to visit, etc. The one field tables I created
are for the actors (so I can check the box for who is in the movie) and
Ratings.

 

[Stumped-need help]

I'm sorry, I do not understand your message. I am not a programmer, so I
have no idea where to key the instructions you have provided. How do I find
a "combo box's NotInList event procedure?" I guess I should move the "the
design a form for data entry" phase of my education on Access 2007. Thanks
for your response.

 

[Ken Sheridan]

Select the combo box control in form design view and open its properties
sheet if its not already open. Then select the NotInList event property in
the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the lines of code between these two existing lines.
The sample code I sent you includes the first and end lines so omit those if
you copy and paste in my code as they'll already be there. All you need to
do then is change the table and column (field) names form Cities and City
wherever they appear in my code to the real names of your one-column table
and its column.

I'm using an earlier version of Access, but I don't imagine it will have
changed much in Access 2007 from the above description.

You'll also find the LimitToList property in the combo box's properties
sheet. Make sure that is set to True (Yes).

Ken Sheridan
Stafford, England

 

[John W. Vinson/MVP]

I am just playing to get practical use for Access. I am creating a database
of my recorded DVD collection. One of the fields I have created is for the
main actors in the movie. My intention is to be able to query my database to
determine what movies do I have with x actor, or what movies do I have rated
G or PG when the children come to visit, etc. The one field tables I created
are for the actors (so I can check the box for who is in the movie) and
Ratings.

Since each movie will have multiple actors, and each actor may appear
in multiple movies, the proper structure would have three tables (none
of them single field!):

Movies
MovieID Primary Key
Title
Rating
DateIssued
<any other info about the movie as an entity in its own right>

Actors <or People, you might want to record directors, producers...>
ActorID Primary Key
LastName
FirstName
<other biographical info>

Credits
MovieID <link to Movies>
ActorID <link to Actors>
Role <character's name, or maybe "Producer">

 

[Ken Sheridan]

With the table structures John is suggesting (absolutely correctly) you'd
most probably use a Movies form, in single form view, with an Actors subform
in continuous form view embedded in it. The subform would be based on the
Credits table and have two controls, a combo box bound to ActorID and a text
box bound to Role.

The subform would be linked to the parent form on MovieID by setting this as
the LinkMasterFields and LinkChildFields properties of the subform control in
the parent form (that's the control which houses the subform). Its other
properties would be:

RowSource: SELECT ActorID, FirstName & " " & LastName FROM Actors ORDER
BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The code for its NotInList event procedure would be as follows:

Const conMESSAGE = "Add new actor to list?"
Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String

Set ctrl = Me.ActiveControl

' insert new row into Actors table, parsing name into first and last names
strSQL = "INSERT INTO Actors(FirstName, LastName) VALUES(" & _
"""" & Left(NewData,Instr(NewData," ")-1) & """,""" & _
Mid(NewData,Instr(NewData," ")+1) & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(conMESSAGE, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

Note that this does require the new name to be entered into the combo box as
first and last name with an intervening space, so does not allow for middle
names or initials, or actors with a single name, e.g. Lassie or Madonna. It
is possible to code it to allow for variations like this but its not trivial,
and even then Access will not know the difference between names like Richard
E Grant and Victoria de los Angeles. The above code would insert the names
into the table but interpret 'E Grant' and 'de los Angeles' as the last
names. So the first would appear in the list under 'E', the second under
'D'. Single word names would fail completely and raise a runtime error.
While these issues can be addressed its probably better at this stage in your
learning process if you work within the limitations of the
FirstName-space-LastName format.

Ken Sheridan
Stafford, England