VBA help please

[Ste4en]

I get by in Access without knowing any VBA, usually by copying macros I convert. I have the following simple code to open a report with the current record from a form. I would like to add some code to that procedure to run an update query. Below if the code to run the report and under that is a converted macro to run the update query. How can I join the two. Thanks
Steve

Private Sub Command22_Click()
On Error GoTo Err_Command22_Click

Dim strDocName As String
Dim strWhere As String
strDocName = "rptinvoice"
strWhere = "[new invoice No]='" & Me![new invoice no] & "'"
DoCmd.OpenReport strDocName, acPreview, , strWhere

Exit_Command22_Click:
Exit Sub

Err_Command22_Click:
MsgBox Err.Description
Resume Exit_Command22_Click

End Sub
_____________

Function macUpdateSubmit()
On Error GoTo macUpdateSubmit_Err

DoCmd.OpenQuery "qryUpdateSubmitted", acNormal, acEdit


macUpdateSubmit_Exit:
Exit Function

macUpdateSubmit_Err:
MsgBox Error$
Resume macUpdateSubmit_Exit

End Function

 

[Mile-O]

Firstly, let's tidy up the rubbish that the conversion creates:

Private Sub Command22_Click()
    On Error GoTo Err_Command22_Click

    DoCmd.OpenReport "rptinvoice", acPreview, , "[new invoice No] = """ & Me.[new invoice no] & """"

End Sub

It depends when you want the query to run: before or after the report being opened.

Private Sub Command22_Click()
    On Error GoTo Err_Command22_Click

    [color=red]DoCmd.OpenQuery "qryUpdateSubmitted"[/color]

    DoCmd.OpenReport "rptinvoice", acPreview, , "[new invoice No] = """ & Me.[new invoice no] & """"

    [color=red]DoCmd.OpenQuery "qryUpdateSubmitted"[/color]

End Sub

If it's before, remove the second red line; if it's after remove the first red line.

 

[Ste4en]

Thanks- thay was easy - but I think I need one more thing - the query is an update query and I want to update only the record where [new invoice No] = """ & Me.[new invoice no].

So my update query is filtered to only update that record, how can i pass the Me.[new invoice no] to the udate query.

thanks

 

[Ste4en]

Thanks, but I still can't get it working. Here is the update query I would like to run, how can I convery this to run in the procedure.


UPDATE Invoicing SET Invoicing.[Amount Submitted] = [labor amount]+[oh Amount], Invoicing.[Invoice Date] = Now()
WHERE (((Invoicing.[NEW Invoice No])=[strwhere]));

thanks

 

[Mile-O]

SInce it's just the current record:

Me.[Amount Submitted] = Me.[labor amount] + Me.[oh Amount]
Me.[Invoice Date] = Now()

 

[Ste4en]

Thanks again , I added the statement, but got the error
"You can't assign a value to this object"; here is the whole thing as it stands. The report part runs fine.

Dim strDocName As String
Dim strWhere As String
strDocName = "rptinvoice"
strWhere = "[new invoice No]='" & Me![NEW Invoice No] & "'"
DoCmd.OpenReport strDocName, acPreview, , strWhere
Me.[Amount Submitted] = Me.[labor amount] + Me.[oh Amount]
Me.[Invoice Date] = Now()

 

[Mile-O]

I added the statement, but got the error
"You can't assign a value to this object"

On what line?

here is the whole thing as it stands. The report part runs fine.

As I said, you don't need that much code:

    DoCmd.OpenReport "rptinvoice", acPreview, , "[new invoice No] = """ & Me.[new invoice no] & """"
    Me.[Amount Submitted] = Me.[labor amount] + Me.[oh Amount]
    Me.[Invoice Date] = Now()

So, which line?

 

[Ste4en]

On what line, so which line??

Not sure what the question is, the report runs and preview opens. Then an error message pops open with the message described above.

Stay with me...

thanks

 

[Mile-O]

You shouldl have these lines in your sub:

On Error GoTo macUpdateSubmit_Err

    Me.[Amount Submitted] = Me.[labor amount] + Me.[oh Amount]
    Me.[Invoice Date] = Now()
    DoCmd.OpenReport "rptinvoice", acPreview, , "[new invoice No] = """ & Me.[new invoice no] & """"

macUpdateSubmit_Exit:
Exit Function

macUpdateSubmit_Err:
MsgBox Error$
Resume macUpdateSubmit_Exit

With this line:

On Error GoTo macUpdateSubmit_Err

Comment it out:

' On Error GoTo macUpdateSubmit_Err

Run this and the error should stop on the offending line of code. Report back.

 

[Ste4en]

I kept getting errors with the lines in your last post...the whole thing looks like this at the moment:

Private Sub Command22_Click()

Me.[Amount Submitted] = Me.[labor amount] + Me.[oh Amount]
Me.[Invoice Date] = Now()
DoCmd.OpenReport "rptinvoice", acPreview, , "[new invoice No] = """ & Me.[new invoice no] & """"

End Sub


I ran it and get the error runtine error 2448, "you can't assign a value to ths object"

when I it debug the following line is highligted

Me.[Amount Submitted] = Me.[labor amount] + Me.[oh Amount]
when i delete that line the following line is highlighted.

Me.[Invoice Date] = Now()


With just the following it does the report part fine.


Private Sub Command22_Click()
DoCmd.OpenReport "rptinvoice", acPreview, , "[new invoice No] = """ & Me.[new invoice no] & """"

End Sub

thanks

 

[Mile-O]

Are [Amount Submitted], [labor amount], [oh Amount], and [Invoice Date] the names of controls on your form?

 

[Ste4en]

Those are fields on my form.

 

[WayneRyan]

Ste4en,

If you get your form in Design View,
Right-Click on one of your controls (fields),
The first entry is its name.
The second entry is its ControlSource.
You should refer to it by its name.

If that doesn't do it, you have a non-updateable
recordset, but I don't think so.

Wayne

 

[Rich]

Why are you using vba to do this at all? a calculated field on the Report will do it without any code or even a query. It looks as though you're storing a calulated value, not a good idea

 

[Ste4en]

I figured out my problem. The form was based on a query which is not updatable. I changed it to be based on the original table and the code provided in earlier posts works.

However - the reason it was based on a query is because I only wanted to show certail records in the form, those that have not been submitted yet.

So I filitered by form for amount submitted = 0, and see that in the form properties there is an entry
[Amount Submitted]=0, however this does seems to be ignored when I reopen the form of refresh the data.

What is the proper way to filter the form.

The reason I am trying to update these fields is beacause the amount submitted (yes also the total invoice amount) and submitted date are data that I need to maintain for comparison with the accounting system.

 

[Rich]

again you only need a calculated field in your query for both this form and for accounting purposes.

 

[Ste4en]

I know what you are saying, but I do need something to tell me that the invoice was processed, and so using the date submitted = now() - I took the invoice total amount out - as you say thats a calculated field.

thanks

 

[Rich]

=Date() may be better if you don't need the actual time part, do you mean processed or printed

 

[Ste4en]

another improvement, thanks