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

Querydef parameters issue

100+
P: 116
My problem is that my code doesn't seem to be passing the parameter values to the query. I'm not very profiencient in access so i'm not sure where its going wrong.

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo WriteTableInfoTo_SRCE_TBL_STRUCTUREErr
  2. Dim strErrorMsg As String
  3.  
  4. Dim db As DAO.Database, qd As DAO.QueryDef
  5. Dim varRelate As Variant
  6.     ' Check for related child rows
  7.     ' Get a pointer to this database
  8.     Set db = CurrentDb
  9.     '
  10.     ' Run the Delete query
  11.     Set qd = db.QueryDefs("qry2DeleteTargTblColsFromSTRUCTURE")
  12.  
  13.     ' Set the parameters
  14. qd![App Vsn] = [Forms]!frm1BuildDescrTabOuterTable![cmbAppVsn]
  15. qd![Cust ID] = [Forms]!frm1BuildDescrTabOuterTable![cmbCust_ID]
  16. qd![Target Table] = [Forms]!frm1BuildDescrTabOuterTable![cmbTargetTable]
  17.  
  18.     qd.Execute
  19.     qd.Close
  20.  
  21.     Set qd = Nothing
  22.     Set db = Nothing
  23.  
  24.  
  25.  
It errors out on this line and throw an error message that the Target Table needs to be selected.

Expand|Select|Wrap|Line Numbers
  1. qd![App Vsn] = [Forms]!frm1BuildDescrTabOuterTable![cmbAppVsn]
  2.  
Thanks,
Eric
May 18 '07 #1
Share this Question
Share on Google+
19 Replies


ADezii
Expert 5K+
P: 8,638
My problem is that my code doesn't seem to be passing the parameter values to the query. I'm not very profiencient in access so i'm not sure where its going wrong.

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo WriteTableInfoTo_SRCE_TBL_STRUCTUREErr
  2. Dim strErrorMsg As String
  3.  
  4. Dim db As DAO.Database, qd As DAO.QueryDef
  5. Dim varRelate As Variant
  6.     ' Check for related child rows
  7.     ' Get a pointer to this database
  8.     Set db = CurrentDb
  9.     '
  10.     ' Run the Delete query
  11.     Set qd = db.QueryDefs("qry2DeleteTargTblColsFromSTRUCTURE")
  12.  
  13.     ' Set the parameters
  14. qd![App Vsn] = [Forms]!frm1BuildDescrTabOuterTable![cmbAppVsn]
  15. qd![Cust ID] = [Forms]!frm1BuildDescrTabOuterTable![cmbCust_ID]
  16. qd![Target Table] = [Forms]!frm1BuildDescrTabOuterTable![cmbTargetTable]
  17.  
  18.     qd.Execute
  19.     qd.Close
  20.  
  21.     Set qd = Nothing
  22.     Set db = Nothing
  23.  
  24.  
  25.  
It errors out on this line and throw an error message that the Target Table needs to be selected.

Expand|Select|Wrap|Line Numbers
  1. qd![App Vsn] = [Forms]!frm1BuildDescrTabOuterTable![cmbAppVsn]
  2.  
Thanks,
Eric
I'm not sure about the Target Table being selected, but your syntax is all wrong for the Parameters:
Expand|Select|Wrap|Line Numbers
  1.  ' Set the parameters - You must Reference the Parameters Collection of the QueryDef Object
  2. qd.Parameters("App Vsn") = [Forms]!frm1BuildDescrTabOuterTable![cmbAppVsn]
  3. qd.Parameters("Cust ID") = [Forms]!frm1BuildDescrTabOuterTable![cmbCust_ID]
  4. qd.Parameters("Target Table") = [Forms]!frm1BuildDescrTabOuterTable![cmbTargetTable]
May 18 '07 #2

100+
P: 116
Thanks for the reply. I've tried switching the the lines you suggested but it didn't seem to solve anything. I'm thinking this is going to be a little to involved for help over the web. Do you have any suggestions on Helpful reading material that covers querydefs in detail so i can better understand how they work?


Eric
May 23 '07 #3

jamjar
P: 50
Do you get the exact same error messages after applying Adezii's suggestions?

Are you running code from frm1BuildDescrTabOuterTable? If so, have you tried replacing '[Forms]!frm1BuildDescrTabOuterTable' with 'Me'? (though I wouldn't think that would make a difference).
If not, is the form 'frm1BuildDescrTabOuterTable' open when your code runs?

James
May 24 '07 #4

ADezii
Expert 5K+
P: 8,638
Thanks for the reply. I've tried switching the the lines you suggested but it didn't seem to solve anything. I'm thinking this is going to be a little to involved for help over the web. Do you have any suggestions on Helpful reading material that covers querydefs in detail so i can better understand how they work?


Eric
Instead of Form References for your QueryDef Parameters, try Absolute Values or Literals, if it then works, the problem lies with the Control References:
Expand|Select|Wrap|Line Numbers
  1. qd.Parameters("App Vsn") = <some value>
  2. qd.Parameters("Cust ID") = <some value>
  3. qd.Parameters("Target Table") = <some value>
May 24 '07 #5

100+
P: 116
I've tried substutition variables and when i step through the VB code i can see that the correct variables are being looked up. i think that the error is coming when its trying to pass the actual parameters. This is the SQL for the delete query. Any ideas? Thanks

Eric


Expand|Select|Wrap|Line Numbers
  1. PARAMETERS 
  2. [Target Table] Text ( 255 ), 
  3. [App Vsn] Text ( 255 ), 
  4. [Cust ID] Text ( 255 );
  5. DELETE 
  6. SRCE_TBL_STRUCTURE.SRCE_APP_VERSION, SRCE_TBL_STRUCTURE.CUST_ID, 
  7. SRCE_TBL_STRUCTURE.TABLE_NAME
  8. FROM SRCE_TBL_STRUCTURE 
  9. INNER JOIN qryForfrmBuildDescrTabOuterTable 
  10. ON SRCE_TBL_STRUCTURE.TABLE_NAME=qryForfrmBuildDescrTabOuterTable.[TRGT TBLNAME];
  11.  
May 24 '07 #6

jamjar
P: 50
Does this query work if you run it manually and type in the parameters?
May 28 '07 #7

100+
P: 116
No it says "Specify the target table and records you want to Delete" in a msg box and then exits.
May 29 '07 #8

jamjar
P: 50
This is the SQL for the delete query. Any ideas? Thanks

Eric
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS 
  2. [Target Table] Text ( 255 ), 
  3. [App Vsn] Text ( 255 ), 
  4. [Cust ID] Text ( 255 );
  5. DELETE 
  6. SRCE_TBL_STRUCTURE.SRCE_APP_VERSION, SRCE_TBL_STRUCTURE.CUST_ID, 
  7. SRCE_TBL_STRUCTURE.TABLE_NAME
  8. FROM SRCE_TBL_STRUCTURE 
  9. INNER JOIN qryForfrmBuildDescrTabOuterTable 
  10. ON SRCE_TBL_STRUCTURE.TABLE_NAME=qryForfrmBuildDescrTabOuterTable.[TRGT TBLNAME];
  11.  
Does this query work if you run it manually and type in the parameters?
No it says "Specify the target table and records you want to Delete" in a msg box and then exits.
I may be treading on shaky ground here - I don't say this with great confidence - but the problem may be that you have created a join in the DELETE query. The few times I've tried to use DELETE queries, I have had to limit them to just one table, which makes flagging what you want to delete difficult!
To make them work, I have had to add a 'doDelete' field to the target table and set the appropriate records to Yes via an update query before running the delete query.

cheers-
James
May 31 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Actually you can use a join in the delete query but you have to delete the full record of the table or tables. You can't delete values in some columns only. This would be an update query.

Assuming you want to delete records from SRCE_TBL_STRUCTURE your syntax will be as follows:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS 
  2. [Target Table] Text ( 255 ), 
  3. [App Vsn] Text ( 255 ), 
  4. [Cust ID] Text ( 255 );
  5. DELETE SRCE_TBL_STRUCTURE.*
  6. FROM SRCE_TBL_STRUCTURE 
  7. INNER JOIN qryForfrmBuildDescrTabOuterTable 
  8. ON SRCE_TBL_STRUCTURE.TABLE_NAME = qryForfrmBuildDescrTabOuterTable.[TRGT TBLNAME];
  9.  
Jun 1 '07 #10

jamjar
P: 50
Actually you can use a join in the delete query but you have to delete the full record of the table or tables. You can't delete values in some columns only. This would be an update query.
I stand corrected! And I'm glad - I don't know why I've had trouble with this in the past but I can get a joined Delete query to work fine now (delete records from one table based on conditions in another).

thanks-
James
Jun 1 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
I stand corrected! And I'm glad - I don't know why I've had trouble with this in the past but I can get a joined Delete query to work fine now (delete records from one table based on conditions in another).

thanks-
James
Exactly James.

You will have problems if you try to delete anything except the full table record(s). If for instance, in this example, you tried to include any field from the joined query in the select statement it wouldn't work either.

Mary
Jun 1 '07 #12

100+
P: 116
Actually you can use a join in the delete query but you have to delete the full record of the table or tables. You can't delete values in some columns only. This would be an update query.

Assuming you want to delete records from SRCE_TBL_STRUCTURE your syntax will be as follows...
Just to clarify my understanding. There is not a way to delete only certain rows in a join delete. So I'm going to have to delete everything and then reload the table.
Jun 4 '07 #13

ADezii
Expert 5K+
P: 8,638
Actually you can use a join in the delete query but you have to delete the full record of the table or tables. You can't delete values in some columns only. This would be an update query.

Assuming you want to delete records from SRCE_TBL_STRUCTURE your syntax will be as follows:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS 
  2. [Target Table] Text ( 255 ), 
  3. [App Vsn] Text ( 255 ), 
  4. [Cust ID] Text ( 255 );
  5. DELETE SRCE_TBL_STRUCTURE.*
  6. FROM SRCE_TBL_STRUCTURE 
  7. INNER JOIN qryForfrmBuildDescrTabOuterTable 
  8. ON SRCE_TBL_STRUCTURE.TABLE_NAME = qryForfrmBuildDescrTabOuterTable.[TRGT TBLNAME];
  9.  
Thanks for bailing me out on this one, Mary. I was far off somewhere on some Parameter Tangent!
Jun 5 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Just to clarify my understanding. There is not a way to delete only certain rows in a join delete. So I'm going to have to delete everything and then reload the table.
No you can delete certain rows as long as you delete the full row and the full row only.

Example:

tblCustomer {custID, custName, custAddress}
tblOldCustomer {custID, custName, custAddress}

If you wanted to delete all rows from tblCustomer that had a corresponding row in tblOldCustomer then
Expand|Select|Wrap|Line Numbers
  1.  
  2. DELETE tblCustomer.* 
  3. FROM tblCustomer INNER JOIN tblOldCustomer
  4. ON tblCustomer.custID=tblOldCustomer.custID
  5.  
However this won't work because it doesn't include custID
Expand|Select|Wrap|Line Numbers
  1.  
  2. DELETE tblCustomer.custName, tblCustomer.custAddress 
  3. FROM tblCustomer INNER JOIN tblOldCustomer
  4. ON tblCustomer.custID=tblOldCustomer.custID
  5.  
and this won't work because it includes a field not in the tblCustomer table
Expand|Select|Wrap|Line Numbers
  1.  
  2. DELETE tblCustomer.* , tblOldCustomer.custID
  3. FROM tblCustomer INNER JOIN tblOldCustomer
  4. ON tblCustomer.custID=tblOldCustomer.custID
  5.  
Get the idea?
Jun 5 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for bailing me out on this one, Mary. I was far off somewhere on some Parameter Tangent!
No problem, you've bailed me out enough times.
Jun 5 '07 #16

100+
P: 116
Alright thank you for all your help. I think i know what i need to change now.


Eric
Jun 5 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
Alright thank you for all your help. I think i know what i need to change now.


Eric
No problem Eric.

Let us know if you need further help.
Jun 5 '07 #18

100+
P: 116
Alright one more question...The DELETE SRCE_TBL_STRUCTURE.* seems to of solve my original problem,(atleast its stepping further through before it errors out),but now its giving me an error saying i have to few parameters(expects 6). The problem is that the three parameters i listed are what identifies the rows i want to delete. Is there a way to pass a wildcard or ignore the rest of the parameters it's expecting?


Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Target Table] Text ( 255 ), [App Vsn] Text ( 255 ), [Cust ID] Text ( 255 );
  2. DELETE SRCE_TBL_STRUCTURE.*
  3. FROM SRCE_TBL_STRUCTURE INNER JOIN qryForfrmBuildDescrTabOuterTable ON SRCE_TBL_STRUCTURE.TABLE_NAME=qryForfrmBuildDescrTabOuterTable.TRGT_TBLNAME;
  4.  
Jun 5 '07 #19

100+
P: 116
Nevermind I solved the problem. Thanks for all the help

Eric
Jun 5 '07 #20

Post your reply

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