Hi,
I'm having trouble interacting SQL with VBA. I have a
report which displays a sub-report. This sub-report's
recordsource is a table. The data in this table depends
upon the chioce of the database user. Basically, I have
taken queries and saved them as tables. (using append-
queries). I did this because I need certain data to
remain static. The names of these tables are all all
housed in a seperate table. I am using a combo-box to
display the names of all the saved tables. The user then
selects the name of the table he wants to use as the
recordsource of the sub-report (which is located inside
the report). So, I am temp. transferring the data in the
saved-query-table into the table which is used as the sub-
reports recordsource. The problem I'm having is with the
SQL code. How do I put the object-referece I used (Dim
Source....which represents the name of the table the user
has chosen- see below) into the 'DoCmd.RunSQL' code? I've
pretty much tried everything I could think of, so if you
have any suggestions I would really appreaciate it!!
Here is my code so far:
Private Sub Label2_DblClick(Cancel As Integer)
Dim Source
DoCmd.RunSQL ("DELETE FROM tblProfileReport"
Source = Combo0.Column(x)
DoCmd.RunSQL ("INSERT INTO tblProfileReport
(Cus_Organization_Name) SELECT Cus_Organization_Name FROM
Source"
DoCmd.OpenReport "Company Saved Profile", acViewPreview
End Sub
When running this code (above) I recieve an error message
something like : table 'Source' can't be found; make sure
it exists.
Thanks for your help,
Nick
I'm having trouble interacting SQL with VBA. I have a
report which displays a sub-report. This sub-report's
recordsource is a table. The data in this table depends
upon the chioce of the database user. Basically, I have
taken queries and saved them as tables. (using append-
queries). I did this because I need certain data to
remain static. The names of these tables are all all
housed in a seperate table. I am using a combo-box to
display the names of all the saved tables. The user then
selects the name of the table he wants to use as the
recordsource of the sub-report (which is located inside
the report). So, I am temp. transferring the data in the
saved-query-table into the table which is used as the sub-
reports recordsource. The problem I'm having is with the
SQL code. How do I put the object-referece I used (Dim
Source....which represents the name of the table the user
has chosen- see below) into the 'DoCmd.RunSQL' code? I've
pretty much tried everything I could think of, so if you
have any suggestions I would really appreaciate it!!
Here is my code so far:
Private Sub Label2_DblClick(Cancel As Integer)
Dim Source
DoCmd.RunSQL ("DELETE FROM tblProfileReport"
Source = Combo0.Column(x)
DoCmd.RunSQL ("INSERT INTO tblProfileReport
(Cus_Organization_Name) SELECT Cus_Organization_Name FROM
Source"
DoCmd.OpenReport "Company Saved Profile", acViewPreview
End Sub
When running this code (above) I recieve an error message
something like : table 'Source' can't be found; make sure
it exists.
Thanks for your help,
Nick