By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Making my SQL dynamic

P: 1
Err,

I’ve been working on this problem for weeks and now I’m lost.

I finally got my append query to work but now I need it to be dynamic.

I’m new to this so I suppose I should give some back grown.

This is a database for a Boy Scout merit badge fair my father is putting on. It has a main “students” table which has all the technical statistics for each student, a class table for each of the different merit badge classes a scout can take. I have a form in which each student can enroll in their classes. This is the code I’ve written to do this:

Option Compare Database
Dim ClassTable As ComboBox
Private Sub Block_1_Button_Click()
On Error GoTo Block_1_err
Dim db As DAO.Database
Set db = CurrentDb

Dim strSQL As String
strSQL = "INSERT INTO ClassID_01 ( StudentID, Name, TroopNumber, Block_1, Block_2, Block_3, Block_4 )" & _
"SELECT StudentClassAssignment.StudentID, [LastName] & ', ' & [FirstName] AS Name, Students.TroopNumber, StudentClassAssignment.Block_1, StudentClassAssignment.Block_2, StudentClassAssignment.Block_3, StudentClassAssignment.Block_4 " & _
"FROM [Students], [StudentClassAssignment]" & _
"WHERE (((StudentClassAssignment.StudentID)=[Forms]![Students]![StudentID]) AND ((Students.StudentID)=[StudentClassAssignment].[StudentID]));"


DoCmd.RunSQL strSQL

I finally got this to work, but I need it to work will all my classes not just “ClassID_01”

So I modified it to this:

Option Compare Database
Dim ClassTable As ComboBox
Private Sub Block_1_Button_Click()
On Error GoTo Block_1_err
Dim db As DAO.Database
Set db = CurrentDb
ClassTable = Block_1
Dim strSQL As String
strSQL = "INSERT INTO ClassTable ( StudentID, Name, TroopNumber, Block_1, Block_2, Block_3, Block_4 )" & _
"SELECT StudentClassAssignment.StudentID, [LastName] & ', ' & [FirstName] AS Name, Students.TroopNumber, StudentClassAssignment.Block_1, StudentClassAssignment.Block_2, StudentClassAssignment.Block_3, StudentClassAssignment.Block_4 " & _
"FROM [Students], [StudentClassAssignment]" & _
"WHERE (((StudentClassAssignment.StudentID)=[Forms]![Students]![StudentID]) AND ((Students.StudentID)=[StudentClassAssignment].[StudentID]));"


DoCmd.RunSQL strSQL
ClassTable = Nothing


If there is any help any once can offer I would greatly appreciate it.

Yut,
Marine Mike
Dec 7 '07 #1
Share this Question
Share on Google+
1 Reply


puppydogbuddy
Expert 100+
P: 1,923
Err,

I’ve been working on this problem for weeks and now I’m lost.

I finally got my append query to work but now I need it to be dynamic.

I’m new to this so I suppose I should give some back grown.

This is a database for a Boy Scout merit badge fair my father is putting on. It has a main “students” table which has all the technical statistics for each student, a class table for each of the different merit badge classes a scout can take. I have a form in which each student can enroll in their classes. This is the code I’ve written to do this:

Option Compare Database
Dim ClassTable As ComboBox
Private Sub Block_1_Button_Click()
On Error GoTo Block_1_err
Dim db As DAO.Database
Set db = CurrentDb

Dim strSQL As String
strSQL = "INSERT INTO ClassID_01 ( StudentID, Name, TroopNumber, Block_1, Block_2, Block_3, Block_4 )" & _
"SELECT StudentClassAssignment.StudentID, [LastName] & ', ' & [FirstName] AS Name, Students.TroopNumber, StudentClassAssignment.Block_1, StudentClassAssignment.Block_2, StudentClassAssignment.Block_3, StudentClassAssignment.Block_4 " & _
"FROM [Students], [StudentClassAssignment]" & _
"WHERE (((StudentClassAssignment.StudentID)=[Forms]![Students]![StudentID]) AND ((Students.StudentID)=[StudentClassAssignment].[StudentID]));"


DoCmd.RunSQL strSQL

I finally got this to work, but I need it to work will all my classes not just “ClassID_01”

So I modified it to this:

Option Compare Database
Dim ClassTable As ComboBox
Private Sub Block_1_Button_Click()
On Error GoTo Block_1_err
Dim db As DAO.Database
Set db = CurrentDb
ClassTable = Block_1
Dim strSQL As String
strSQL = "INSERT INTO ClassTable ( StudentID, Name, TroopNumber, Block_1, Block_2, Block_3, Block_4 )" & _
"SELECT StudentClassAssignment.StudentID, [LastName] & ', ' & [FirstName] AS Name, Students.TroopNumber, StudentClassAssignment.Block_1, StudentClassAssignment.Block_2, StudentClassAssignment.Block_3, StudentClassAssignment.Block_4 " & _
"FROM [Students], [StudentClassAssignment]" & _
"WHERE (((StudentClassAssignment.StudentID)=[Forms]![Students]![StudentID]) AND ((Students.StudentID)=[StudentClassAssignment].[StudentID]));"


DoCmd.RunSQL strSQL
ClassTable = Nothing


If there is any help any once can offer I would greatly appreciate it.

Yut,
Marine Mike
Mike,
From what I can tell, for your modified version you need to include ClassID as one of the fields you are inserting into the class table in order for your insertion to work.
Dec 8 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.