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

Please help: Error with simple SQL statement

P: 31
Hi. I am building a process that retrieves a value from a combo box, and based on the value, one of two SQL processes occur. If the value from the box is "-ALL-" then all records from a table are selcted for output. If a specific value is entered that is NOT "-ALL-", a separate process occurs (records from the table that match the value are stored as DIVISION).

This is the piece of code i have in place, and I kep getting the error message
"Run Time error 3141 - The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

And, yes - the variable strSQL is declared...

Can anyone look at this code and tell me what I am doing wrong with Syntax or use? Thanks in advance.

Expand|Select|Wrap|Line Numbers
  1. If [Forms]![manuallyadjustdata]![Combo10] = "-ALL-" Then
  2. strSQL = "SELECT * As DIVISION FROM [tbl_Div].[Div_Name]"
  3. DoCmd.RunSQL strSQL
  4. Else
  5. strSQL = "SELECT [tbl_Div].[Div_Name] As DIVISION From [tbl_Div] WHERE [tbl_Div].[Div_Name] = " & strDiv & ""
  6. DoCmd.RunSQL strSQL
  7. End If
May 1 '07 #1
Share this Question
Share on Google+
20 Replies


Rabbit
Expert Mod 10K+
P: 12,382
Hi. I am building a process that retrieves a value from a combo box, and based on the value, one of two SQL processes occur. If the value from the box is "-ALL-" then all records from a table are selcted for output. If a specific value is entered that is NOT "-ALL-", a separate process occurs (records from the table that match the value are stored as DIVISION).

This is the piece of code i have in place, and I kep getting the error message
"Run Time error 3141 - The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

And, yes - the variable strSQL is declared...

Can anyone look at this code and tell me what I am doing wrong with Syntax or use? Thanks in advance.

Expand|Select|Wrap|Line Numbers
  1. If [Forms]![manuallyadjustdata]![Combo10] = "-ALL-" Then
  2. strSQL = "SELECT * As DIVISION FROM [tbl_Div].[Div_Name]"
  3. DoCmd.RunSQL strSQL
  4. Else
  5. strSQL = "SELECT [tbl_Div].[Div_Name] As DIVISION From [tbl_Div] WHERE [tbl_Div].[Div_Name] = " & strDiv & ""
  6. DoCmd.RunSQL strSQL
  7. End If
Please use Code tags next time.

There are 2 problems.

1) You can't assign an alias to *. What it would mean to do so is to name every field the same name.

2) Div_Name seems to be a text value in which case you'll have to surround the strDiv with single quotes. i.e.
Expand|Select|Wrap|Line Numbers
  1. "'" & strDiv & "'"
May 1 '07 #2

P: 31
Please use Code tags next time.

There are 2 problems.

1) You can't assign an alias to *. What it would mean to do so is to name every field the same name.

2) Div_Name seems to be a text value in which case you'll have to surround the strDiv with single quotes. i.e.
Expand|Select|Wrap|Line Numbers
  1. "'" & strDiv & "'"

Thanks.

I made a few modifications. I am still getting a bunch of error messages. My code is a big mess. I am not sure i am going about this in the most efficient manner. Is there a tutorial, or any threads you could point me to that may help me build this out better?

What I am doing:

I have a Form with 4 combo boxes (call them A, B, C, D) and a command button.
The top combo box (A) is a date value.
The bottom 3 combo boxes (B,C,D) have a list of values, and also include an option to select "ALL"
The user will make a selection in each box.
After making selections, user clicks command button.
Command button retrieves values from combo boxes, and:
For each associated table (each combo box points to a diff table), retrieves matching records.

So, the code I am trying to build will:
Find records that match A AND B AND C AND D.

I want the system to take the retrieved records and create a new table.

Any help? My code is a big mess.
May 1 '07 #3

Rabbit
Expert Mod 10K+
P: 12,382
Post your entire code.
May 1 '07 #4

P: 31
Post your entire code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command16_Click()
  2.  
  3. Dim strDate As Date
  4. Dim strDiv As String
  5. Dim strLoc As String
  6. Dim strVP As String
  7. Dim strSQL As String
  8.  
  9. If IsNull(Combo0) Then
  10. strDate = " "
  11. Else
  12. strDate = [Forms]![manuallyadjustdata]![Combo0]
  13. End If
  14.  
  15. If IsNull(Combo10) Then
  16. strDiv = " "
  17. Else
  18. strDiv = [Forms]![manuallyadjustdata]![Combo10]
  19. End If
  20.  
  21. If IsNull(Combo12) Then
  22. strLoc = " "
  23. Else
  24. strLoc = [Forms]![manuallyadjustdata]![Combo12]
  25. End If
  26.  
  27. If IsNull(Combo14) Then
  28. strVP = " "
  29. Else
  30. strVP = [Forms]![manuallyadjustdata]![Combo14]
  31. End If
  32.  
  33. strDiv = Me!comboX
  34.  
  35. If [Forms]![manuallyadjustdata]![Combo10] = "-ALL-" Then
  36. strSQL = "SELECT * FROM [tbl_Div].[Div_Name]"
  37. DoCmd.RunSQL strSQL
  38. Else
  39. strSQL = "SELECT [tbl_Div].[Div_Name] As DIVISION From [tbl_Div] WHERE [tbl_Div].[Div_Name] = " '" & strDiv & "'"
  40. DoCmd.RunSQL strSQL
  41. End If
  42.  
  43. 'DoCmd.RunSQL "SELECT SponProd.[Prod_Date] As PRODDATE FROM SponProd WHERE SponProd.[Prod_Date] = #" & strDate & "#"
  44.  
  45. 'If strDiv = "-ALL-" Then
  46. 'strDiv = strDivAll
  47. 'strDivAll = "SELECT * As DIVISION FROM Div.Div_Name"
  48. 'DoCmd.RunSQL strDivAll
  49. 'Else
  50. 'If strDiv <> "-ALL-" Then
  51. 'strDiv = strDivRandom
  52. 'strDivRandom = "SELECT Div.Div_Name As DIVISION FROM Div WHERE Div.Div_Name =" & strDiv & ""
  53. 'DoCmd.RunSQL strDivRandom
  54. 'End If
  55. 'End If
  56.  
  57. 'If strLoc = "-ALL-" Then
  58. 'DoCmd.RunSQL "SELECT * AS LOCATION FROM Loc.[Loc_Name]"
  59. 'Else
  60. 'DoCmd.RunSQL "SELECT Loc.[Loc_Name] AS LOCATION FROM Loc WHERE Loc.[Loc_Name] = " & strLoc & ""
  61. 'End If
  62.  
  63. 'If strVP = "-ALL-" Then
  64. 'DoCmd.RunSQL "SELECT * AS VPCODE FROM VPList.[VP_Code]"
  65. 'Else
  66. 'DoCmd.RunSQL "SELECT VPList.[VP_Code] AS VPCODE FROM VPList WHERE VPList.[VP_Code] = " & strDiv & ""
  67. 'End If
  68.  
  69. DoCmd.RunSQL "DELETE * FROM RESULTS"
  70.  
  71. DoCmd.RunSQL "INSERT INTO RESULTS (PRODDATE, DIV, LOCATION, VPCODE)"
  72.  
  73. Dim stDocName As String
  74.  
  75.     stDocName = "Macro6"
  76.     DoCmd.RunMacro stDocName
  77.  
  78. End Sub
  79.  
May 1 '07 #5

P: 31
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command16_Click()
  2.  
  3. Dim strDate As Date
  4. Dim strDiv As String
  5. Dim strLoc As String
  6. Dim strVP As String
  7. Dim strSQL As String
  8.  
  9. If IsNull(Combo0) Then
  10. strDate = " "
  11. Else
  12. strDate = [Forms]![manuallyadjustdata]![Combo0]
  13. End If
  14.  
  15. If IsNull(Combo10) Then
  16. strDiv = " "
  17. Else
  18. strDiv = [Forms]![manuallyadjustdata]![Combo10]
  19. End If
  20.  
  21. If IsNull(Combo12) Then
  22. strLoc = " "
  23. Else
  24. strLoc = [Forms]![manuallyadjustdata]![Combo12]
  25. End If
  26.  
  27. If IsNull(Combo14) Then
  28. strVP = " "
  29. Else
  30. strVP = [Forms]![manuallyadjustdata]![Combo14]
  31. End If
  32.  
  33. strDiv = Me!comboX
  34.  
  35. If [Forms]![manuallyadjustdata]![Combo10] = "-ALL-" Then
  36. strSQL = "SELECT * FROM [tbl_Div].[Div_Name]"
  37. DoCmd.RunSQL strSQL
  38. Else
  39. strSQL = "SELECT [tbl_Div].[Div_Name] As DIVISION From [tbl_Div] WHERE [tbl_Div].[Div_Name] = " '" & strDiv & "'"
  40. DoCmd.RunSQL strSQL
  41. End If
  42.  
  43. 'DoCmd.RunSQL "SELECT SponProd.[Prod_Date] As PRODDATE FROM SponProd WHERE SponProd.[Prod_Date] = #" & strDate & "#"
  44.  
  45. 'If strDiv = "-ALL-" Then
  46. 'strDiv = strDivAll
  47. 'strDivAll = "SELECT * As DIVISION FROM Div.Div_Name"
  48. 'DoCmd.RunSQL strDivAll
  49. 'Else
  50. 'If strDiv <> "-ALL-" Then
  51. 'strDiv = strDivRandom
  52. 'strDivRandom = "SELECT Div.Div_Name As DIVISION FROM Div WHERE Div.Div_Name =" & strDiv & ""
  53. 'DoCmd.RunSQL strDivRandom
  54. 'End If
  55. 'End If
  56.  
  57. 'If strLoc = "-ALL-" Then
  58. 'DoCmd.RunSQL "SELECT * AS LOCATION FROM Loc.[Loc_Name]"
  59. 'Else
  60. 'DoCmd.RunSQL "SELECT Loc.[Loc_Name] AS LOCATION FROM Loc WHERE Loc.[Loc_Name] = " & strLoc & ""
  61. 'End If
  62.  
  63. 'If strVP = "-ALL-" Then
  64. 'DoCmd.RunSQL "SELECT * AS VPCODE FROM VPList.[VP_Code]"
  65. 'Else
  66. 'DoCmd.RunSQL "SELECT VPList.[VP_Code] AS VPCODE FROM VPList WHERE VPList.[VP_Code] = " & strDiv & ""
  67. 'End If
  68.  
  69. DoCmd.RunSQL "DELETE * FROM RESULTS"
  70.  
  71. DoCmd.RunSQL "INSERT INTO RESULTS (PRODDATE, DIV, LOCATION, VPCODE)"
  72.  
  73. Dim stDocName As String
  74.  
  75.     stDocName = "Macro6"
  76.     DoCmd.RunMacro stDocName
  77.  
  78. End Sub
  79.  
May just be better for me to "begin anew" ??
May 1 '07 #6

P: 31
May just be better for me to "begin anew" ??
Sorry - by the way - all the Macro does is open the RESULTS table...
May 1 '07 #7

Rabbit
Expert Mod 10K+
P: 12,382
A couple of things to note.

1) A DoCmd.RunSQL "SELECT ... " doesn't do anything. In fact, it throws an error because it doesn't do anything. A DoCmd.RunSQL does not bring up the results of a query.

2) An append query is written as:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TableName (Column List) VALUES (Value List)
Your INSERT INTO statement is written incorrectly.
May 1 '07 #8

P: 31
A couple of things to note.

1) A DoCmd.RunSQL "SELECT ... " doesn't do anything. In fact, it throws an error because it doesn't do anything. A DoCmd.RunSQL does not bring up the results of a query.

2) An append query is written as:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TableName (Column List) VALUES (Value List)
Your INSERT INTO statement is written incorrectly.
OK. Where to begin?

As opposed to using DoCmd.RunSQl "SELECT..."

How about:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM [tbl_Div].[Div_Name]"
  2. DoCmd.RunSQL strSQL
  3.  
Will this work?
May 1 '07 #9

Rabbit
Expert Mod 10K+
P: 12,382
OK. Where to begin?

As opposed to using DoCmd.RunSQl "SELECT..."

How about:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM [tbl_Div].[Div_Name]"
  2. DoCmd.RunSQL strSQL
  3.  
Will this work?
That's the same thing. A SELECT query doesn't work with RunSQL no matter how you phrase it. You want them to see the results of the SQL much like if you ran it as a query correct? The only way to do so is to use a query or a seperate form. In which case you'll have to change query defs or the record source of a form.
May 1 '07 #10

P: 31
That's the same thing. A SELECT query doesn't work with RunSQL no matter how you phrase it. You want them to see the results of the SQL much like if you ran it as a query correct? The only way to do so is to use a query or a seperate form. In which case you'll have to change query defs or the record source of a form.
I'm sorry, Rabbit. I don;t understand this:

The only way to do so is to use a query or a seperate form. In which case you'll have to change query defs or the record source of a form

I am trying to build out an IF...THEN Block

If response in comboA = X Then Run some SQL Statement(s)
If response in comboB = Y Then Run some other SQL Statement(s)

Am I going about this wrong?

i just want to collect responses from Combo Boxes, and use values to populate table with like records

Can you help me build a shell for this, that I can then work with to modify?

Thanks. J.
May 1 '07 #11

Rabbit
Expert Mod 10K+
P: 12,382
I'm sorry, Rabbit. I don;t understand this:

The only way to do so is to use a query or a seperate form. In which case you'll have to change query defs or the record source of a form

I am trying to build out an IF...THEN Block

If response in comboA = X Then Run some SQL Statement(s)
If response in comboB = Y Then Run some other SQL Statement(s)

Am I going about this wrong?

i just want to collect responses from Combo Boxes, and use values to populate table with like records

Can you help me build a shell for this, that I can then work with to modify?

Thanks. J.
It's not your reasoning that's incorrect. It's the implementation. You can't use a non-action query like a SELECT sql statement with DoCmd.RunSQL.

It sounds like you want to append records to a table in which case you should be using an append SQL statement and not a SELECT statement.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TableName SELECT * FROM OtherTable.
A SELECT statement by itself throws an error because it doesn't do anything.
May 1 '07 #12

P: 31
It's not your reasoning that's incorrect. It's the implementation. You can't use a non-action query like a SELECT sql statement with DoCmd.RunSQL.

It sounds like you want to append records to a table in which case you should be using an append SQL statement and not a SELECT statement.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TableName SELECT * FROM OtherTable.
A SELECT statement by itself throws an error because it doesn't do anything.
Rabbit,

I have tried reworking this. I am still getting an error: Incomplete Query Clause

My re-worked code:

Expand|Select|Wrap|Line Numbers
  1. If Me!Combo0 = " " Then
  2. MsgBox "Please make a valid selection from the list"
  3. Else
  4. strSQL = "INSERT INTO RESULTS SELECT * FROM [tbl_SponProd].{Prod_Date]"
  5. DoCmd.RunSQL strSQL
  6. End If
  7.  

Am I missing something..?
May 1 '07 #13

Rabbit
Expert Mod 10K+
P: 12,382
Rabbit,

I have tried reworking this. I am still getting an error: Incomplete Query Clause

My re-worked code:

Expand|Select|Wrap|Line Numbers
  1. If Me!Combo0 = " " Then
  2. MsgBox "Please make a valid selection from the list"
  3. Else
  4. strSQL = "INSERT INTO RESULTS SELECT * FROM [tbl_SponProd].{Prod_Date]"
  5. DoCmd.RunSQL strSQL
  6. End If
  7.  

Am I missing something..?
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO RESULTS SELECT * FROM [tbl_SponProd].{Prod_Date]
First off, the FROM clause must be a table, not a field.
Secondly, if you want to return just one field that has to be in the SELECT clause.

Right now you're inserting the results of the SELECT statement into the table RESULTS. The SELECT statement is selecting all fields (*) from the field Prod_Date in table tbl_SponProd. Which doesn't make sense.
May 1 '07 #14

P: 31
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO RESULTS SELECT * FROM [tbl_SponProd].{Prod_Date]
First off, the FROM clause must be a table, not a field.
Secondly, if you want to return just one field that has to be in the SELECT clause.

Right now you're inserting the results of the SELECT statement into the table RESULTS. The SELECT statement is selecting all fields (*) from the field Prod_Date in table tbl_SponProd. Which doesn't make sense.

Rabbit,

I have tried re-working the code several ways, all of which produce error messages.

For example, this source code gives me an error that the field does not exist:
Expand|Select|Wrap|Line Numbers
  1. If Me!Combo0 = " " Then
  2. MsgBox "Please make a valid selection from the list"
  3. Else
  4. strSQL = "INSERT INTO RESULTS SELECT [tbl_SponProd].[Prod_Date] FROM [tbl_SponProd] WHERE [Prod_Date] = " & Me!Combo0
  5. DoCmd.RunSQL strSQL
  6. End If
May 1 '07 #15

P: 31
Rabbit,

I have tried re-working the code several ways, all of which produce error messages.

For example, this source code gives me an error that the field does not exist:
Expand|Select|Wrap|Line Numbers
  1. If Me!Combo0 = " " Then
  2. MsgBox "Please make a valid selection from the list"
  3. Else
  4. strSQL = "INSERT INTO RESULTS SELECT [tbl_SponProd].[Prod_Date] FROM [tbl_SponProd] WHERE [Prod_Date] = " & Me!Combo0
  5. DoCmd.RunSQL strSQL
  6. End If

Yet, the field does exist.

The table is named: tbl_SponProd
The field is named: Prod_Date

So, because both contain special characters, I have included them in brackets [ ]

J.
May 1 '07 #16

Rabbit
Expert Mod 10K+
P: 12,382
Yet, the field does exist.

The table is named: tbl_SponProd
The field is named: Prod_Date

So, because both contain special characters, I have included them in brackets [ ]

J.
Yes but does the field exist in the table RESULTS?
May 1 '07 #17

P: 31
Yes but does the field exist in the table RESULTS?
OK. Thank you. I went in to the RESULTS table, and made sure there were comparable field names for those I am pulling from the other table. That helped.

Now, as I initiate the process, any time I choose an option OTHER THAN "-ALL-", the system prompts me with an Input Box, asks for a parameter value for whichever option I select from the drop-down.

So, this code works fine (if user selects "-ALL-" from drop-down):

Expand|Select|Wrap|Line Numbers
  1. If Me!Combo12 = "-ALL-" Then
  2. strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc]"
  3. DoCmd.RunSQL strSQL
  4. Else
  5.  
But, if I choose any other option from the list, it takes that value and asks (with an Input Box) for the parameter value. Can you see a problem with the code here? I am guessing it is related to the way I have the control reference at the end of the line?

Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc] WHERE [Loc_Name]= " & Me!Combo12 & ""
  2. DoCmd.RunSQL strSQL
  3. End If
  4.  
  5.  

I am attempting here to tell the system to push out records that match the value selected in the drop-down...Help?

Thanks,

J.
May 1 '07 #18

Rabbit
Expert Mod 10K+
P: 12,382
OK. Thank you. I went in to the RESULTS table, and made sure there were comparable field names for those I am pulling from the other table. That helped.

Now, as I initiate the process, any time I choose an option OTHER THAN "-ALL-", the system prompts me with an Input Box, asks for a parameter value for whichever option I select from the drop-down.

So, this code works fine (if user selects "-ALL-" from drop-down):

Expand|Select|Wrap|Line Numbers
  1. If Me!Combo12 = "-ALL-" Then
  2. strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc]"
  3. DoCmd.RunSQL strSQL
  4. Else
  5.  
But, if I choose any other option from the list, it takes that value and asks (with an Input Box) for the parameter value. Can you see a problem with the code here? I am guessing it is related to the way I have the control reference at the end of the line?

Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc] WHERE [Loc_Name]= " & Me!Combo12 & ""
  2. DoCmd.RunSQL strSQL
  3. End If
  4.  
  5.  

I am attempting here to tell the system to push out records that match the value selected in the drop-down...Help?

Thanks,

J.
I see nothing wrong with it as long as Combo12 is the name of the control on the main form that calls this event. And that Loc_Name is a number although from the looks of it, it's a text value in which case you have to surround it with quotes.
Expand|Select|Wrap|Line Numbers
  1. [Loc_Name] = '" & Me!Combo12 & "'"
What is the input box asking you to define?
May 1 '07 #19

P: 31
I see nothing wrong with it as long as Combo12 is the name of the control on the main form that calls this event. And that Loc_Name is a number although from the looks of it, it's a text value in which case you have to surround it with quotes.
Expand|Select|Wrap|Line Numbers
  1. [Loc_Name] = '" & Me!Combo12 & "'"
What is the input box asking you to define?
Rabbit,

I took your suggestion and added the single quotes. That corrected the problem. i tested it, worked fine, except it kept hanging up right at the end. I was about to ask your advice, but I noticed that I had not set echo back to Yes in the macro. I fixed that, and I now have a seemingly functional process.

Here is my final source code for this piece of the puzzle:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command16_Click()
  2.  
  3. Dim strDate As Date
  4. Dim strDiv As String
  5. Dim strLoc As String
  6. Dim strVP As String
  7. Dim strSQL As String
  8.  
  9. If IsNull(Combo0) Then
  10. strDate = " "
  11. Else
  12. strDate = [Forms]![manuallyadjustdata]![Combo0]
  13. End If
  14.  
  15. If IsNull(Combo10) Then
  16. strDiv = " "
  17. Else
  18. strDiv = [Forms]![manuallyadjustdata]![Combo10]
  19. End If
  20.  
  21. If IsNull(Combo12) Then
  22. strLoc = " "
  23. Else
  24. strLoc = [Forms]![manuallyadjustdata]![Combo12]
  25. End If
  26.  
  27. If IsNull(Combo14) Then
  28. strVP = " "
  29. Else
  30. strVP = [Forms]![manuallyadjustdata]![Combo14]
  31. End If
  32.  
  33. SetWarnings = False
  34.  
  35. DoCmd.RunSQL "DELETE * FROM RESULTS"
  36.  
  37. If Me!Combo0 = " " Then
  38. MsgBox "Please make a valid selection from the list"
  39. Else
  40. strSQL = "INSERT INTO RESULTS SELECT [tbl_SponProd].[Prod_Date] FROM [tbl_SponProd] WHERE [Prod_Date] = #" & Me!Combo0 & "#"
  41. DoCmd.RunSQL strSQL
  42. End If
  43.  
  44. If Me!Combo10 = "-ALL-" Then
  45. strSQL = "INSERT INTO RESULTS SELECT [tbl_Div].[Div_Name] FROM [tbl_Div]"
  46. DoCmd.RunSQL strSQL
  47. Else
  48. strSQL = "INSERT INTO RESULTS SELECT [tbl_Div].[Div_Name] FROM [tbl_Div] WHERE [Div_Name] = '" & Me!Combo10 & "'"
  49. DoCmd.RunSQL strSQL
  50. End If
  51.  
  52. If Me!Combo12 = "-ALL-" Then
  53. strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc]"
  54. DoCmd.RunSQL strSQL
  55. Else
  56. strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc] WHERE [Loc_Name] = '" & Me!Combo12 & "'"
  57. DoCmd.RunSQL strSQL
  58. End If
  59.  
  60. If Me!Combo14 = "-ALL-" Then
  61. strSQL = "INSERT INTO RESULTS SELECT [tbl_VPList].[VP_Code] FROM [tbl_VPList]"
  62. DoCmd.RunSQL strSQL
  63. Else
  64. strSQL = "INSERT INTO RESULTS SELECT [tbl_VPList].[VP_Code] FROM [tbl_VPList] WHERE [VP_Code] = '" & Me!Combo14 & "'"
  65. DoCmd.RunSQL strSQL
  66. End If
  67.  
  68. SetWarnings = True
  69.  
  70. Dim stDocName As String
  71.  
  72.     stDocName = "Macro6"
  73.     DoCmd.RunMacro stDocName
  74.  
  75. End Sub
Thanks again for your help. Tomorrow, i have to begin developing the next piece - which will involve programmatically locking certain records on/in the datasheet (RESULTS table), and linking certain records in that datasheet back to the original tables (so any modifications to the datasheet will drill-back to the primary data).

I'll start a new thread tomorrow, see how much of that I can accomplish.

I'm guessing I'll again need your assistance!

You're the best, rabbit.

Many Thanks!

J.
May 1 '07 #20

Rabbit
Expert Mod 10K+
P: 12,382
Good Luck.
May 1 '07 #21

Post your reply

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