Copying a column from one table into a subform with a button-click

[Guest]

Hello,

I have a Site lookup table with one field (SiteID).

I also have a Projects table (ProjectID, Project Description) and an
AssignedProjects table(ProjectID, SiteID)

There is a form, which for each project, displays all the sites assigned to
it. Is it possible to have a button on the form, which will enter all the
sites in the Site lookup table into the subform, effectively assigning the
current project to all sites in the Sites table...

Any help and ideas would be appreciated.

Mike

 

[John W. Vinson]

Hello,

I have a Site lookup table with one field (SiteID).

I also have a Projects table (ProjectID, Project Description) and an
AssignedProjects table(ProjectID, SiteID)

There is a form, which for each project, displays all the sites assigned to
it. Is it possible to have a button on the form, which will enter all the
sites in the Site lookup table into the subform, effectively assigning the
current project to all sites in the Sites table...

Any help and ideas would be appreciated.

Mike

You'll need to run an Append query. Something like

Private Sub cmdAllSites_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
strSQL = "INSERT INTO [AssignedProjects] (SiteID, ProjectID)" _
& "SELECT SiteID, " & Me!ProjectID & " FROM Sites;"
Set db = CurrentDb
Set qd = db.CreateQuerydef("", strSQL) ' create an unnamed query
qd.Execute dbFailOnError
Set qd = Nothing
Set db = Nothing
End Sub


John W. Vinson [MVP]

 

[Guest]

Hello,

Thanks very much for your help - that works like a dream!

Mike

Hello,

I have a Site lookup table with one field (SiteID).

I also have a Projects table (ProjectID, Project Description) and an
AssignedProjects table(ProjectID, SiteID)

There is a form, which for each project, displays all the sites assigned to
it. Is it possible to have a button on the form, which will enter all the
sites in the Site lookup table into the subform, effectively assigning the
current project to all sites in the Sites table...

Any help and ideas would be appreciated.

Mike

You'll need to run an Append query. Something like

Private Sub cmdAllSites_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
strSQL = "INSERT INTO [AssignedProjects] (SiteID, ProjectID)" _
& "SELECT SiteID, " & Me!ProjectID & " FROM Sites;"
Set db = CurrentDb
Set qd = db.CreateQuerydef("", strSQL) ' create an unnamed query
qd.Execute dbFailOnError
Set qd = Nothing
Set db = Nothing
End Sub


John W. Vinson [MVP]