Adding same data to a field in many records

[Jack at Relaxed]

I have a table of contacts that are given 'types' via a look up table, e.g.
Speaker, Audience etc and a second table of events that these contacts attend
- a third table records which contacts attend which events (a many to many
relationship). I would like to be able to choose all my audience types for
example from a set county and in one operation assign them to one event,
rather than having to assign them individually! Is this possible? I have a
query that pulls out my contact types with the relevant attendance field
ready to receive the Event ID but thereafter I am stuck - all help gratefully
received. I am working with Access 2003.

 

[NetworkTrade]

your query is a Select Query. Ultimately you will want an Append Query to
the 3rd table...but stay in Select query mode until you get it set up right...

If you query now has the correct people...you need to add the 2nd table to
this query and get the single correct event ID showing with each attendee
record....

once you have the correct result as a Select Query...change it to be an
Append Query onto the 3rd table...

Work in a copy so you can do trial and error...but this is the general
idea.....

 

[Jeanette Cunningham]

Hi Jack,
something like this code that works from a button (Command8) on a form
called frmContacts.
there is a combo to pick the Contact type
and a subform to pick the Event type
replace table names, field names and form and control names to suit your
database

Private Sub Command8_Click()
Dim strSQL As String
Dim db As DAO.Database

Set db = DBEngine(0)(0)

strSQL = "INSERT INTO tblContactEvent ( ContactID ) " _
& "SELECT tblContacts.ContactID " _
& "FROM tblContacts " _
& "WHERE tblContacts.ContactTypeID = " & me.cboContactType & " "
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

strSQL = "UPDATE tblContactEvent" _
& "SET tblContactEvent.EventID = " & Me.subform1.Form.cboEventID & "
" _
& "WHERE tblContactEvent.EventID Is Null"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

Set db = Nothing

End Sub

Jeanette Cunningham