Resolving parameters

G

Guest

I have seen posts about resolving parameters, but I must not be doing it right.

The following code is exerpted from the sub cmdContinue_Click in the class
module Form_fdlgProcessingSelector:

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set qdf = CurrentDb.QueryDefs!qryInquiry_new
With qdf
.Parameters!prm_dtmProcess = gdtmProcess
.Parameters!prm_dtmLastRun = gdtmLastRun
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
End With
DoCmd.OpenForm "frmInquiryNotBalanced"

gdtmProcess and gdtmLastRun are public variables declared in the sub
Form_Open in the same class module.

The SQL statement for qryInquiry_new is:

PARAMETERS prm_dtmProcess DateTime, prm_dtmLastRun DateTime;
SELECT tblProspect_import.pkID, tblProspect_import.strPOISE_Representative
AS fk_strPOISE_Representative, tblAdmissionCode.ysnHighSchool,
IIf(IsDate([strInquiryDate]),CDate([strInquiryDate]),0) AS dtmInquiry
FROM tblAdmissionCode INNER JOIN tblProspect_import ON
tblAdmissionCode.pk_strAdmissionCode=tblProspect_import.fk_strMigrationStatus
WHERE (((IIf(IsDate([strInquiryDate]),CDate([strInquiryDate]),0)) Between
prm_dtmProcess-1 And prm_dtmLastRun));

The code stops on the looped statement with the error "Microsoft Office
Access can't find the name 'prm_dtmProcess' you entered in the expression"; I
chose debug. I mouse-over "prm.name" and the tip responds
"prm.name='prm_dtmProcess'"; I mouse-over "prm.value" and the tip responds
"prm.value=7/11/2006".

Where have I gone wrong?
 
R

RoyVidar

I have seen posts about resolving parameters, but I must not be doing
it right.

The following code is exerpted from the sub cmdContinue_Click in the
class module Form_fdlgProcessingSelector:

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set qdf = CurrentDb.QueryDefs!qryInquiry_new
With qdf
.Parameters!prm_dtmProcess = gdtmProcess
.Parameters!prm_dtmLastRun = gdtmLastRun
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
End With
DoCmd.OpenForm "frmInquiryNotBalanced"

gdtmProcess and gdtmLastRun are public variables declared in the sub
Form_Open in the same class module.

The SQL statement for qryInquiry_new is:

PARAMETERS prm_dtmProcess DateTime, prm_dtmLastRun DateTime;
SELECT tblProspect_import.pkID,
tblProspect_import.strPOISE_Representative AS
fk_strPOISE_Representative, tblAdmissionCode.ysnHighSchool,
IIf(IsDate([strInquiryDate]),CDate([strInquiryDate]),0) AS dtmInquiry
FROM tblAdmissionCode INNER JOIN tblProspect_import ON
tblAdmissionCode.pk_strAdmissionCode=tblProspect_import.fk_strMigrationStatus
WHERE (((IIf(IsDate([strInquiryDate]),CDate([strInquiryDate]),0))
Between prm_dtmProcess-1 And prm_dtmLastRun));

The code stops on the looped statement with the error "Microsoft
Office Access can't find the name 'prm_dtmProcess' you entered in
the expression"; I chose debug. I mouse-over "prm.name" and the tip
responds "prm.name='prm_dtmProcess'"; I mouse-over "prm.value" and
the tip responds "prm.value=7/11/2006".

Where have I gone wrong?
Click to expand...

You can skip the looping.

The looping is what you use to resolve parameters from forms dynamicly,
and since you're not using any of them, you should be able to remove
this looping.

I'm not very familiar with DAO, but I don't see you using this query,
so
why are you resolving these paremeters?
 
G

Guest

RoyVidar said:
I have seen posts about resolving parameters, but I must not be doing
it right.

The following code is exerpted from the sub cmdContinue_Click in the
class module Form_fdlgProcessingSelector:

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set qdf = CurrentDb.QueryDefs!qryInquiry_new
With qdf
.Parameters!prm_dtmProcess = gdtmProcess
.Parameters!prm_dtmLastRun = gdtmLastRun
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
End With
DoCmd.OpenForm "frmInquiryNotBalanced"

gdtmProcess and gdtmLastRun are public variables declared in the sub
Form_Open in the same class module.

The SQL statement for qryInquiry_new is:

PARAMETERS prm_dtmProcess DateTime, prm_dtmLastRun DateTime;
SELECT tblProspect_import.pkID,
tblProspect_import.strPOISE_Representative AS
fk_strPOISE_Representative, tblAdmissionCode.ysnHighSchool,
IIf(IsDate([strInquiryDate]),CDate([strInquiryDate]),0) AS dtmInquiry
FROM tblAdmissionCode INNER JOIN tblProspect_import ON
tblAdmissionCode.pk_strAdmissionCode=tblProspect_import.fk_strMigrationStatus
WHERE (((IIf(IsDate([strInquiryDate]),CDate([strInquiryDate]),0))
Between prm_dtmProcess-1 And prm_dtmLastRun));

The code stops on the looped statement with the error "Microsoft
Office Access can't find the name 'prm_dtmProcess' you entered in
the expression"; I chose debug. I mouse-over "prm.name" and the tip
responds "prm.name='prm_dtmProcess'"; I mouse-over "prm.value" and
the tip responds "prm.value=7/11/2006".

Where have I gone wrong?
Click to expand...

You can skip the looping.

The looping is what you use to resolve parameters from forms dynamicly,
and since you're not using any of them, you should be able to remove
this looping.

I'm not very familiar with DAO, but I don't see you using this query,
so
why are you resolving these paremeters?
Click to expand...
Your thoughts led me to a review of my code to make it more explicit (and I
found a couple of errors in the process :)

Set qdf = .QueryDefs!qryInquiry_new
With qdf
.Parameters!prm_dtmProcess = gdtmProcess
.Parameters!prm_dtmLastRun = gdtmLastRun
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
End With
Set qdf = .QueryDefs!qryAdjustment_current
With qdf
.Parameters!prm_dtmProcess = gdtmProcess
.Parameters!prm_dtmLastRun = gdtmLastRun
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
End With
sbrDeleteAllRecords ("tblInquiry_x_Representative_temp")
.QueryDefs!qappInquiry_x_Representative_temp.Execute

The SQL statement for qappInquiry_x_Representative_temp is:

INSERT INTO tblInquiry_x_Representative_temp ( pk_fk_strRepCode,
intInquiry_new, intInquiryHighSchool, intApplication, intInquiryWithdrawn )
SELECT tblRepresentative.pk_strRepCode,
qxtbInquiry_x_Representative_new.intInquiry_total +
tblMainActivity.intMainInquiry_count,
qxtbInquiry_x_Representative_new.[True],
tblMainActivity.intApplication_count, qryAdjustment_current.intAdjustment
FROM ((tblRepresentative INNER JOIN qxtbInquiry_x_Representative_new ON
tblRepresentative.strPOISE_Code =
qxtbInquiry_x_Representative_new.pk_fk_strPOISE_Representative) INNER JOIN
tblMainActivity ON tblRepresentative.strPOISE_Code =
tblMainActivity.pk_fk_strPOISE_Representative) INNER JOIN
qryAdjustment_current ON tblRepresentative.strPOISE_Code =
qryAdjustment_current.pk_fk_strPOISE_Representative;

The form in the original code is opened later.
 
Top