Using a Temporary Variable to Search a Form

[James_liv]

Afternoon,

Im not great with this so please treat me as a newbie.

I have a database and on it I have a 'Print Yes/No' field.

When a user changes it to 'Yes' I want the form to be printed but in an invoice format. To do this I have created a separate Invoice Style Form with much of the same data on it.

When it is changed to 'Yes' my thoughts are to use a Macro to copy the unique invoice number into a Temporary Variable, switch Forms to the more print friendly invoice version, search the invoice Number for the Temporary Variable and print it before switching back to the previous form.

Im struggling with it.

I think i've manage to copy the unique number to the TempVar and switch the form but I cannot seem to be able to perform a search using the TempVar.

Is this the easiest way of completing this task?
Can anyone advise how I perform this search?



Below is how I currently have it.

Thank you for the help.

[Bob Fitz]

I would create a report based on the data used for your form and then have a button on the form which would sent the report to the printer when it is clicked

[James_liv]

Thanks for the reply Bob,

I understand that but be it a form or a report the data entry form is visually very different from the invoice print view.

And so wouldn't I be in the same position that you enter the data in the form, need to switch that to the report and then search for that record prior to printing? The invoice is A4 size and so its only one record/invoice per page.

I mean, it cant really be that hard (caveat, im rubbish!). Its just taking a piece of data from one form (invoice Number) and automatically switching form and finding it in the same invoice number field on another form. Thats it!

Regards

[Bob Fitz]

Thanks for the reply Bob,

I understand that but be it a form or a report the data entry form is visually very different from the invoice print view.

And so wouldn't I be in the same position that you enter the data in the form, need to switch that to the report and then search for that record prior to printing? The invoice is A4 size and so its only one record/invoice per page.

Regards

You would create the report once and have it appear however you want it.
The line of code fired by clicking the button would be used to identify the current records Primary Key and use that to determine which record is used to populate the report.

[James_liv]

You would create the report once and have it appear however you want it.
The line of code fired by clicking the button would be used to identify the current records Primary Key and use that to determine which record is used to populate the report.

When I do that it lists every record one after another in a long scroll which goes on and on for the hundreds of records in the database. That may work but its really not what I was looking for at all.

What I need to do is to use either a macro or VB script to take the invoice number, switch form and search for that invoice number in the second form.
Is there an easy way of doing this? It seems like this would be quiet a common requirement?

[Bob Fitz]

When I do that it lists every record one after another in a long scroll which goes on and on for the hundreds of records in the database. That may work but its really not what I was looking for at all.

Show us the line of code used to open/print the report

[James_liv]

Hi Bob,

I think we're mis-understanding each other.

The form that you enter the data into is not a report. If I convert it to a report then it chains all of the records together and....well, its not the invoice. I need it to print the data on an invoice as opposed to the data entry screen.

I need it to switch from the data entry form to the invoice, find the respective invoice (be it report or form) and then print it. None of the calculations work like this either and it its just a long chain of every record one after another. See below.

This really isnt what I was looking for Bob. I just need to take the invoice number from a one form and find the corresponding record on another form.

[Bob Fitz]

Printing forms is not the usual way to go about things. Nor would I recommend it. As I said in post #4, you can restrict the printing of a report to a single record.
I have already asked, in post #6, for you to show us the line of code you are using to open the report so that we can see why the criteria to limit the records is not being matched.

[kd2017]

You use DoCmd.OpenReport with a where parameter like "ID=" & Me.ID to open the report on just a single record. Don't use a second form for this.

Do a Google search on DoCmd.OpenReport to study how to use it and it's parameters

http://codevba.com/msaccess/docmd_op...m#.XuJfL5BOmDY

(Edit) I hope that didn't come off as dismissive! But DoCmd.OpenReport with a where parameter is what you're after here. Until we can see your actual database and object names we can't give you exact code.

[Bob Fitz]

You use DoCmd.OpenReport with a where parameter like "ID=" & Me.ID to open the report on just a single record. Don't use a second form for this.

Do a Google search on DoCmd.OpenReport to study how to use it and it's parameters

http://codevba.com/msaccess/docmd_op...m#.XuJfL5BOmDY

(Edit) I hope that didn't come off as dismissive! But DoCmd.OpenReport with a where parameter is what you're after here. Until we can see your actual database and object names we can't give you exact code.

And a Microsoft page on the delights of using the Docmd.OpenReport method can be seen at: https://docs.microsoft.com/en-us/off...cmd.openreport

[kd2017]

And what's more, use DoCmd.OpenForm with a where clause to open a form to a specific record the exact same way as .OpenReport, still no need to use a temporary variable per OP.

[James_liv]

Printing forms is not the usual way to go about things. Nor would I recommend it. As I said in post #4, you can restrict the printing of a report to a single record.
I have already asked, in post #6, for you to show us the line of code you are using to open the report so that we can see why the criteria to limit the records is not being matched.

Hi Bob,

I am just using a simple PrintObject Command in a Macro for printing.

I have it working now, the DoCmd suggestion worked. I did it as per the below.

DoCmd.OpenForm "frmRepairInv", , , "[Invoice Number]=" & Me.[Invoice Number]

I cant say I understand how it works tbh but between your advice and suggestions its finally working.
I will try using this in combination with the report suggestion and see how I get on with it. Ill report back!

Many thanks

[Bob Fitz]

Hi Bob,

I am just using a simple PrintObject Command in a Macro for printing.

I have it working now, the DoCmd suggestion worked. I did it as per the below.

DoCmd.OpenForm "frmRepairInv", , , "[Invoice Number]=" & Me.[Invoice Number]

I cant say I understand how it works tbh but between your advice and suggestions its finally working.
I will try using this in combination with the report suggestion and see how I get on with it. Ill report back!

Many thanks

Please do.
OpenForm and OpenReport are very similar.
To open the report you will need something like:

DoCmd.OpenReport "[YourReportName]", , , "[Invoice Number]=" & Me.[Invoice Number]

BTW it would be better not to have spaces or special characters except the underscore, in the names of any Access Objects (Tables, Fields, Queries, Controls etc)
So "Invoice_Number" or "InvoiceNumber", rather than "Invoice Number"

The spaces etc will catch you out and will cause you additional typing as you become more proficient in using code which is so much better than using macros.

Post back if you need any further help