Querydef parameters issue

[phytorion]

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.

[code=vb]
On Error GoTo WriteTableInfoT o_SRCE_TBL_STRU CTUREErr
Dim strErrorMsg As String

Dim db As DAO.Database, qd As DAO.QueryDef
Dim varRelate As Variant
' Check for related child rows
' Get a pointer to this database
Set db = CurrentDb
'
' Run the Delete query
Set qd = db.QueryDefs("q ry2DeleteTargTb lColsFromSTRUCT URE")

' Set the parameters
qd![App Vsn] = [Forms]!frm1BuildDescr TabOuterTable![cmbAppVsn]
qd![Cust ID] = [Forms]!frm1BuildDescr TabOuterTable![cmbCust_ID]
qd![Target Table] = [Forms]!frm1BuildDescr TabOuterTable![cmbTargetTable]

qd.Execute
qd.Close

Set qd = Nothing
Set db = Nothing


[/code]

It errors out on this line and throw an error message that the Target Table needs to be selected.

qd![App Vsn] = [Forms]!frm1BuildDescrTabOuterTable![cmbAppVsn]

Thanks,
Eric

[ADezii]

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.

[code=vb]
On Error GoTo WriteTableInfoT o_SRCE_TBL_STRU CTUREErr
Dim strErrorMsg As String

Dim db As DAO.Database, qd As DAO.QueryDef
Dim varRelate As Variant
' Check for related child rows
' Get a pointer to this database
Set db = CurrentDb
'
' Run the Delete query
Set qd = db.QueryDefs("q ry2DeleteTargTb lColsFromSTRUCT URE")

' Set the parameters
qd![App Vsn] = [Forms]!frm1BuildDescr TabOuterTable![cmbAppVsn]
qd![Cust ID] = [Forms]!frm1BuildDescr TabOuterTable![cmbCust_ID]
qd![Target Table] = [Forms]!frm1BuildDescr TabOuterTable![cmbTargetTable]

qd.Execute
qd.Close

Set qd = Nothing
Set db = Nothing


[/code]

It errors out on this line and throw an error message that the Target Table needs to be selected.

qd![App Vsn] = [Forms]!frm1BuildDescrTabOuterTable![cmbAppVsn]

Thanks,
Eric

I'm not sure about the Target Table being selected, but your syntax is all wrong for the Parameters:
[CODE=vb] ' Set the parameters - You must Reference the Parameters Collection of the QueryDef Object
qd.Parameters(" App Vsn") = [Forms]!frm1BuildDescr TabOuterTable![cmbAppVsn]
qd.Parameters(" Cust ID") = [Forms]!frm1BuildDescr TabOuterTable![cmbCust_ID]
qd.Parameters(" Target Table") = [Forms]!frm1BuildDescr TabOuterTable![cmbTargetTable][/CODE]

[phytorion]

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

[jamjar]

Do you get the exact same error messages after applying Adezii's suggestions?

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

James

[ADezii]

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:
[CODE=sql]qd.Parameters(" App Vsn") = <some value>
qd.Parameters(" Cust ID") = <some value>
qd.Parameters(" Target Table") = <some value>[/CODE]

[phytorion]

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


[code=sql]
PARAMETERS
[Target Table] Text ( 255 ),
[App Vsn] Text ( 255 ),
[Cust ID] Text ( 255 );
DELETE
SRCE_TBL_STRUCT URE.SRCE_APP_VE RSION, SRCE_TBL_STRUCT URE.CUST_ID,
SRCE_TBL_STRUCT URE.TABLE_NAME
FROM SRCE_TBL_STRUCT URE
INNER JOIN qryForfrmBuildD escrTabOuterTab le
ON SRCE_TBL_STRUCT URE.TABLE_NAME= qryForfrmBuildD escrTabOuterTab le.[TRGT TBLNAME];
[/code]

[jamjar]

Does this query work if you run it manually and type in the parameters?

[phytorion]

No it says "Specify the target table and records you want to Delete" in a msg box and then exits.

[jamjar]

This is the SQL for the delete query. Any ideas? Thanks

Eric
[code=sql]
PARAMETERS
[Target Table] Text ( 255 ),
[App Vsn] Text ( 255 ),
[Cust ID] Text ( 255 );
DELETE
SRCE_TBL_STRUCT URE.SRCE_APP_VE RSION, SRCE_TBL_STRUCT URE.CUST_ID,
SRCE_TBL_STRUCT URE.TABLE_NAME
FROM SRCE_TBL_STRUCT URE
INNER JOIN qryForfrmBuildD escrTabOuterTab le
ON SRCE_TBL_STRUCT URE.TABLE_NAME= qryForfrmBuildD escrTabOuterTab le.[TRGT TBLNAME];
[/code]

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

[MMcCarthy]

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_STRUCT URE your syntax will be as follows:

[code=sql]
PARAMETERS
[Target Table] Text ( 255 ),
[App Vsn] Text ( 255 ),
[Cust ID] Text ( 255 );
DELETE SRCE_TBL_STRUCT URE.*
FROM SRCE_TBL_STRUCT URE
INNER JOIN qryForfrmBuildD escrTabOuterTab le
ON SRCE_TBL_STRUCT URE.TABLE_NAME = qryForfrmBuildD escrTabOuterTab le.[TRGT TBLNAME];
[/code]

[jamjar]

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

[MMcCarthy]

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

[phytorion]

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_STRUCT URE 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.

[ADezii]

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_STRUCT URE your syntax will be as follows:

[code=sql]
PARAMETERS
[Target Table] Text ( 255 ),
[App Vsn] Text ( 255 ),
[Cust ID] Text ( 255 );
DELETE SRCE_TBL_STRUCT URE.*
FROM SRCE_TBL_STRUCT URE
INNER JOIN qryForfrmBuildD escrTabOuterTab le
ON SRCE_TBL_STRUCT URE.TABLE_NAME = qryForfrmBuildD escrTabOuterTab le.[TRGT TBLNAME];
[/code]

Thanks for bailing me out on this one, Mary. I was far off somewhere on some Parameter Tangent!

[MMcCarthy]

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
[CODE=sql]
DELETE tblCustomer.*
FROM tblCustomer INNER JOIN tblOldCustomer
ON tblCustomer.cus tID=tblOldCusto mer.custID
[/CODE]
However this won't work because it doesn't include custID
[CODE=sql]
DELETE tblCustomer.cus tName, tblCustomer.cus tAddress
FROM tblCustomer INNER JOIN tblOldCustomer
ON tblCustomer.cus tID=tblOldCusto mer.custID
[/CODE]
and this won't work because it includes a field not in the tblCustomer table
[CODE=sql]
DELETE tblCustomer.* , tblOldCustomer. custID
FROM tblCustomer INNER JOIN tblOldCustomer
ON tblCustomer.cus tID=tblOldCusto mer.custID
[/CODE]

Get the idea?