Multiple tables - help required

[gavin]

As long as I only have one table to work with I can normally find my way
around the basics of Access fairly well - the Help menu being my best friend


However, I am working on a database with more than one table and I can't get
my head round it - could someone please offer some advice. I know it's not a
difficult scenario but I can't work it out.

The database is to record information about visits to various venues which
need to be inspected. What I have done so far is to create two tables - one
for the venues (including fields for name of venue, street city, post code,
telephone, email address etc) and one for the visits (fields for who made
the visit, date of visit, reason for visit, action taken etc). I created a
form and subform using the Wizard with the venue table as the main form and
the visits table as the subform - but I'm not sure I've got that the right
way round?

So far there are only 9 venues (this will grow to about 15, 20 max). What I
would like to be able to do is to select the name of the venue from a drop
down box and have the fields associated with that venue populate the
relevant fields in the form. I can't figure this out.

I would be very grateful if someone could give me some pointers here - be
brutally honest, I know I'm screwing it up!



Gavin

 

[Guest]

Hurah for you! you are definitely on the right track. You table structures
are correct. One thing I don't know is how you relate the two. The correct
way would be to have an Autonumber Key for the venus table as the primary
key. Then there sould be a field in the visits table that would be to link
all the visits to the venue using that key. Then, your subform should be
linked to the main form using that field.

The form/subform relation is also correct.
Now, here is some sample code I use on a main form to find the record I want
and make it the current record. If your form and subform are correctly
linked, the visits for the venue will be presented when the venue is selected
in the main form

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
Me.txtDescription.SetFocus
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

End Sub