Parameter Query - Action Query

[VBA Coder]

I have a Delete Query named "Test_Delete", with one
parameter, "ID_In" on the "ID" field of the table. The
Delete Query will delete all records from the "Test" table
where the "ID" field equals the value the user entered in
the "ID_In" parameter dialog box.

Now, I would like to create a form where the user enters
the ID_In parameter into a text box, and presses the OK
button execute this query. The form would pass the value
entered in the ID_In text box to the parameter query and
do the deletes without having the user enter the value
into a parameter dialog box.

I am familiar with using the QueryDef object to pass the
parameter using Recordsets, but I have had any success
passing the parameter to an Action Query (ie: Delete,
Update, Append).

Does anyone have sample code that would accomplish passing
a parameter(s) to an Action Query?

 

[Rick]

-----Original Message-----
I have a Delete Query named "Test_Delete", with one
parameter, "ID_In" on the "ID" field of the table. The
Delete Query will delete all records from the "Test" table
where the "ID" field equals the value the user entered in
the "ID_In" parameter dialog box.

Now, I would like to create a form where the user enters
the ID_In parameter into a text box, and presses the OK
button execute this query. The form would pass the value
entered in the ID_In text box to the parameter query and
do the deletes without having the user enter the value
into a parameter dialog box.

I am familiar with using the QueryDef object to pass the
parameter using Recordsets, but I have had any success
passing the parameter to an Action Query (ie: Delete,
Update, Append).

Does anyone have sample code that would accomplish passing
a parameter(s) to an Action Query?

W = "DATE = #" & Q_DATE & "#"
W1 = IIf(IsNull(Q_COMPANY), "", " AND [COMPANY]
= '" & Q_COMPANY & "'")
W = W + W1
W1 = IIf(IsNull(Q_SHIFT), "", " AND [SHIFT] = " &
Q_SHIFT)
W = W & W1
W1 = IIf(IsNull(Q_JOB), "", " AND [JOB] LIKE '" &
Q_JOB & "'")
W = W & W1
If DCount("*", "MASTERDB", W) = 0 Then
CHECK = " "
MSG = "Could not find any DATA meeting your
criteria. Refine your "
MSG = MSG & "selection criteria and try again, or
press the RETURN "
MSG = MSG & "button to return to the main menu."
MsgBox MSG, , "DATA SELECTION"
End If
End If

If CHECK = "OK" Then

W1 = " DELETE DISTINCTROW MASTERDB.* FROM
MASTERDB WHERE "
W = W1 & W
W1 = ";"
W = W & W1
DoCmd.RunSQL W

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Parameters are used in action queries as in other queries. An example
of your DELETE query:

PARAMETERS Forms!frmCriteria!txtID_In Long;
DELETE *
FROM TableName
WHERE ID = Forms!frmCriteria!txtID_In

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDuACYechKqOuFEgEQLnpwCfWl5tr0mCeMukEyXacu0rjmMxl1gAoPx/
ayAdOVKzUkcCz07sRzvtBrqh
=SeJs
-----END PGP SIGNATURE-----

 

[VBA Coder]

I do not want to hard code the Form's field in the
parameter of the query or anywhere within the query.
Instead, I want my query to be independent of the form,
because this query could be called from many forms, which
is why I want to pass the parameter value to the query
from a form.

 

[VBA Coder]

Thank you for the reply, however, with your example, you
are building a SQL Delete query from code, which will
work, however, for my purpose, I am trying to avoid
building the SQL statement with code. Rather, I want to
execute an existing Access Query that I have created that
has parameter(s).

-----Original Message-----

-----Original Message-----
I have a Delete Query named "Test_Delete", with one
parameter, "ID_In" on the "ID" field of the table. The
Delete Query will delete all records from the "Test" table
where the "ID" field equals the value the user entered in
the "ID_In" parameter dialog box.

Now, I would like to create a form where the user enters
the ID_In parameter into a text box, and presses the OK
button execute this query. The form would pass the value
entered in the ID_In text box to the parameter query and
do the deletes without having the user enter the value
into a parameter dialog box.

I am familiar with using the QueryDef object to pass the
parameter using Recordsets, but I have had any success
passing the parameter to an Action Query (ie: Delete,
Update, Append).

Does anyone have sample code that would accomplish passing
a parameter(s) to an Action Query?

W = "DATE = #" & Q_DATE & "#"
W1 = IIf(IsNull(Q_COMPANY), "", " AND [COMPANY]
= '" & Q_COMPANY & "'")
W = W + W1
W1 = IIf(IsNull(Q_SHIFT), "", " AND [SHIFT] = " &
Q_SHIFT)
W = W & W1
W1 = IIf(IsNull(Q_JOB), "", " AND [JOB] LIKE '" &
Q_JOB & "'")
W = W & W1
If DCount("*", "MASTERDB", W) = 0 Then
CHECK = " "
MSG = "Could not find any DATA meeting your
criteria. Refine your "
MSG = MSG & "selection criteria and try again, or
press the RETURN "
MSG = MSG & "button to return to the main menu."
MsgBox MSG, , "DATA SELECTION"
End If
End If

If CHECK = "OK" Then

W1 = " DELETE DISTINCTROW MASTERDB.* FROM
MASTERDB WHERE "
W = W1 & W
W1 = ";"
W = W & W1
DoCmd.RunSQL W
.

 

[John Spencer (MVP)]

Perhaps you could use a global variable that is set by the after update of the
controls on the various forms. Then you would need a simple function to return
that value. You could call the function from within the SQL.

Public Function fGetCriteria()
fGetCriteria = gvarPublic
End Function

SQL statement:
DELETE *
FROM tableName
WHERE ID = fGetCriteria