Update Query using parameters passed from a form

G

Guest

I am trying to run a simple update query from within a VBA procedure using
calculated values I put in a form. However, the table is not updated when I
run the query using the parameters but will run successfully if I put in
explicit values.

I have four fields to update: XStat, NewDues, NewFees, and NewMisc based on
AcctNo. In the "Update To" I have the following syntax in the query:
[Forms]![frmMessage]![intStatus]
[Forms]![frmMessage]![curNewFees]
[Forms]![frmMessage]![curNewDues]
[Forms]![frmMessage]![curNewMisc]
In the "Criteria" I have: [Forms]![frmMessage]![lngAcctNo]

Each of these form fields are update correctly just before I call the query
using the DoCmd.OpenQuery "qryBillingRptUpt" but, the table I am trying to
update does not get updated and there is no error reported.

If I run the query directly by double clicking it and manually enter values
for each of the fields it runs successfully.

I am baffled at what I could be missing here. Any help would be appreciated.

Thanks,
Carl
 
J

John Spencer (MVP)

We are baffled to.

Can you post the VBA procedure and the SQL (text) of the query? It might help
us to see what you are doing and possibly what steps can be taken to accomplish
your goal. (Also, it might help if you would specify the type of data for the fields.)
 
G

Guest

Here's the SQL text of the query:

UPDATE tblBillingReport SET tblBillingReport.XStatus =
[Forms]![frmMessage]![intStatus], tblBillingReport.NewFees =
[Forms]![frmMessage]![curNewFees], tblBillingReport.NewDues =
[Forms]![frmMessage]![curNewDues], tblBillingReport.NewMisc =
[Forms]![frmMessage]![curNewMisc]
WHERE (((tblBillingReport.AcctNo)=[Forms]![frmMessage]![lngAcctNo]));

The VBA procedure code (at least the critical portion - it's a long
procedure):

mNewFees = mOldFees - mPdFees + mAsFees
mNewDues = mOldDues - mPdDues + mAsDues
mNewMisc = mOldMisc - mPdMisc + mAsMisc
Forms!frmMessage!curNewFees = mNewFees
Forms!frmMessage!curNewDues = mNewDues
Forms!frmMessage!curNewMisc = mNewMisc

bytXStat = IIf(bytXStat < 4, bytXStat + 1, bytXStat)
Forms!frmMessage!intStatus = bytXStat

' Update tblBillingRpt records with updated Dues, Fees & Misc
totals
DoCmd.OpenQuery ("qryBillingRptUpd") ' Update query for current
AcctNo

The NEW fields are all currency both as defined in the table I'm updating
and in the form. The bytXStat is a single byte field and lngAcct is a long
integer.

Thanks,
Carl


John Spencer (MVP) said:
We are baffled to.

Can you post the VBA procedure and the SQL (text) of the query? It might help
us to see what you are doing and possibly what steps can be taken to accomplish
your goal. (Also, it might help if you would specify the type of data for the fields.)
I am trying to run a simple update query from within a VBA procedure using
calculated values I put in a form. However, the table is not updated when I
run the query using the parameters but will run successfully if I put in
explicit values.

I have four fields to update: XStat, NewDues, NewFees, and NewMisc based on
AcctNo. In the "Update To" I have the following syntax in the query:
[Forms]![frmMessage]![intStatus]
[Forms]![frmMessage]![curNewFees]
[Forms]![frmMessage]![curNewDues]
[Forms]![frmMessage]![curNewMisc]
In the "Criteria" I have: [Forms]![frmMessage]![lngAcctNo]

Each of these form fields are update correctly just before I call the query
using the DoCmd.OpenQuery "qryBillingRptUpt" but, the table I am trying to
update does not get updated and there is no error reported.

If I run the query directly by double clicking it and manually enter values
for each of the fields it runs successfully.

I am baffled at what I could be missing here. Any help would be appreciated.

Thanks,
Carl
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

IF your values can ever be null, then the query will fail when that happens. I
would expect you to get some error though.

You can get null values for your calculations if any of the values used in your
calculations return null.

Try a little test statement just before you try to execute the query.

If IsNull(meNewFees + mNewDues + mNewMisc + bytXStat) Or
IsNull(Forms!FrmMessage!LngAcctNo) then
MsgBox "Error. Null values detected"
STOP
End if

IF you hit the STOP then you will at least know where to start looking.

Another possibility would be that there is no matching record found based on
your where clause.

Beyond that I don't see anything wrong with your query or the snippet you posted.
Carl said:
Here's the SQL text of the query:

UPDATE tblBillingReport SET tblBillingReport.XStatus =
[Forms]![frmMessage]![intStatus], tblBillingReport.NewFees =
[Forms]![frmMessage]![curNewFees], tblBillingReport.NewDues =
[Forms]![frmMessage]![curNewDues], tblBillingReport.NewMisc =
[Forms]![frmMessage]![curNewMisc]
WHERE (((tblBillingReport.AcctNo)=[Forms]![frmMessage]![lngAcctNo]));

The VBA procedure code (at least the critical portion - it's a long
procedure):

mNewFees = mOldFees - mPdFees + mAsFees
mNewDues = mOldDues - mPdDues + mAsDues
mNewMisc = mOldMisc - mPdMisc + mAsMisc
Click to expand...
Forms!frmMessage!curNewFees = mNewFees
Forms!frmMessage!curNewDues = mNewDues
Forms!frmMessage!curNewMisc = mNewMisc

bytXStat = IIf(bytXStat < 4, bytXStat + 1, bytXStat)
Forms!frmMessage!intStatus = bytXStat

' Update tblBillingRpt records with updated Dues, Fees & Misc
totals
DoCmd.OpenQuery ("qryBillingRptUpd") ' Update query for current
AcctNo

The NEW fields are all currency both as defined in the table I'm updating
and in the form. The bytXStat is a single byte field and lngAcct is a long
integer.

Thanks,
Carl

John Spencer (MVP) said:
We are baffled to.

Can you post the VBA procedure and the SQL (text) of the query? It might help
us to see what you are doing and possibly what steps can be taken to accomplish
your goal. (Also, it might help if you would specify the type of data for the fields.)
I am trying to run a simple update query from within a VBA procedure using
calculated values I put in a form. However, the table is not updated when I
run the query using the parameters but will run successfully if I put in
explicit values.

I have four fields to update: XStat, NewDues, NewFees, and NewMisc based on
AcctNo. In the "Update To" I have the following syntax in the query:
[Forms]![frmMessage]![intStatus]
[Forms]![frmMessage]![curNewFees]
[Forms]![frmMessage]![curNewDues]
[Forms]![frmMessage]![curNewMisc]
In the "Criteria" I have: [Forms]![frmMessage]![lngAcctNo]

Each of these form fields are update correctly just before I call the query
using the DoCmd.OpenQuery "qryBillingRptUpt" but, the table I am trying to
update does not get updated and there is no error reported.

If I run the query directly by double clicking it and manually enter values
for each of the fields it runs successfully.

I am baffled at what I could be missing here. Any help would be appreciated.

Thanks,
Carl
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using a Form to supply parameters to a query 13
Parameters for query from form 5
Multiple Parameter Crosstab query filter by form 1
Update Query Help *urgent* 0
Update TableA with data from TableB using Update Query 3
Update Table Field Value with SQL Query 2
update query using like criteria 3
Sources for query parameters 10

Top