Report from Form

[LisaJill]

I built myself a database to track hours.

From this I create invoices and track them. Previously I had to enter the Invoice information in the form. Also, when I created the invoice I had to enter it in pop up boxes.

I did some searches and found out about parameter queries. I've been trying to make the subform Invoice pass the company name and Date From info to the query behind the report

The relevate information is

Report: InvoiceCreator
Query: qryInvoiceCreator
Form: Invoice
Fields in Form: Company, DateFrom

I tried to do this two ways... the first, and preferable way, is to double click the Invoice ID. I put this event in the OnDoubleClick:

Private Sub InvoiceID_DblClick(Cancel As Integer)


DoCmd.OpenReport InvoiceCreator, acViewNormal, qryInvoiceCreator, [Forms]![Invoice]![DateFrom], acWindowNormal

End Sub

That gave me this error:

Run-time error "2497"

The action or method requires a Report Name argument.

I tried to append InvoiceCreator on the end in the [subargs] section but that didn't help.

I thought that the DoCmd.OpenReport(InvoiceCreator) should do the trick?

So, I tried to see if Access could help me out a bit =) I used the command button creator wizard and it gave me this:

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String

stDocName = "InvoiceCreator"
DoCmd.OpenReport stDocName, acPreview

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub

So, in the hopes of passing the information, I tacked this on to the end of the DoCmd.OpenReport:

, qryInvoiceCreator, [Forms]![Invoice]![DateFrom]

So it looks like this

DoCmd.OpenReport stDocName, acPreview, qryInvoiceCreator, [Forms]![Invoice]![DateFrom]

With the thought that if I could just get the DateFrom field working, I could worry about which company it was later

But that still requests the input boxes from the query - no error, I just still have to enter the information manually.

Anyhow, this is really my first attempt at VBA Code, and I'm missing something. I've searched through the forums to find this much but can't seem to find a way to fix it and make it actually work. Also, how do I add multiple arguments so that it can input Company and Date? Is it And or & or something else?

Thank you in advance!

 

[LisaJill]

In addition to the above I just attempted to steal the code from the command button and do this:

stDocName = "InvoiceCreator"
DoCmd.OpenReport stDocName, acPreview, qryInvoiceCreator, [Forms]![Invoice]![DateFrom], acWindowNormal

That killed any errors (until I hit cancel, when it asks me to debug ) but it still pops up the window and asks me to input the parameters.

*sighs*

 

[Hayley Baxter]

Hi Lisa

Are you saying that you would like to search by two fields, one of those being company name and the other being datefrom and you would then like to return these results in a report?

You could use a dynamic query where the results you choose in your form will be returned in the query but instead of opening the query open the report instead.

The other way is using a query and having this look for a value in your form ie say you had a combo box for the company name you could use the criteria Forms![FrmName]![ComboName] in your query and base your report on the query.

This example is in Access 2000, not sure which version you are using.

 

[Hayley Baxter]

You'll find an example of the other method in this thread.

Thread

 

[LisaJill]

Ok, I've tried both of these methods.

They work -really- great when in the main form Invoice. But when I try to use them from Company (where Invoice is a subform) they bring up pop ups asking for me to input Forms!Invoice!Company, Forms!Invoice!Datefrom and Forms!Invoice!DateTo

Why would they work from the main form and not the subform? I have those fields in the subform - they're exactly the same form for that matter....

Thannks for any further help with this =)

 

[Hayley Baxter]

Right so you've got a main form called Invoice and a subform or is Invoice your subform? Any chance you could post your db and I'll have a look for you? or even just a screen shot so I can see what you have.

Hay

 

[LisaJill]

The database is too big but I'll put it on my website.

Invoice is a form that I've used as a subform in Company. Dunno if that makes sense hehe.

http://www.lisa-jill.com/Job Tracker.zip

Thank again for your help!

It may be a bit messy, I'm just doing this for my own personal benefit and am not really that good at it *blushes*

 

[LisaJill]

I accidentally discovered that this works from Company if I have Invoice open and hidden/minimized, or whatever, as long as it's open.

is that normal? Hwo do I avoid having to open both?

Thanks!

 

[Hayley Baxter]

Hi Lisa

I'm afraid I'm getting a bit lost now, do you have MSN or AOL so I can discusss with you on a one to one basis? Need a few things cleared up.

Let me know
Hay

 

[LisaJill]

Certainly,

AOL: LyssaJyll
MSN: Odinwoof

But I'll explain.

Initially I wasn't going to use subforms, so I made a form called Invoice using Access's Wizard.

Then I decided to make a subform on my Company form (these are the real names, I know they should have a 'frm' before them but I dind't do that)

So, what I did is add the subform by creating the tool and following the wizard. The wizard asked what form to base it off and I said "invoice' the original form, then called it the same.

Perhaps I should have made it a different form, to avoid this? In the forms listing I only have one Invoice form that I can see, but I wonder if I have now two controls with the same name? All the modifications that I do in either place show up in both tho, so I thought it was just a mirror.

Anyhow, I'll be around on and off til Sunday, then back a lot on Monday - have a guest and stuff. Thank you very much for your assistance, I really appreciate it.

 

[Hayley Baxter]

It wont be a problem getting this working Lisa, I just don't want to change your design too much so I would like to correspond with you to see how you would like to perform the search. Are you in the US? I'll be around on MSN tomorrow morning but that might be too early for you (your time) so perhaps I'll catch you on Sunday evening (my time) I'll make some changes for you to have a look at and if you like that you can keep it but if not we can discuss it in more detail.

I'll add you to my contacts when I get home as I'm still at work just now and don't have messenger here.

Speak to you soon
Hay

 

[LisaJill]

Is the database deisgned really badly? I wouldn't be surprised *grins*

I'll catch you around I'm sure, I'm usually up til 3/4am, just now that I have a visitor til Sunday (yes, in US - NY) so I'm sort of sneaking in posts here and there... making this thing work is addicting!

The one line summary of how I want the search to work:

Double click invoice ID and it creates that invoice based off those dates/that company.

Gotta jet, lunchtime!

-Lisa

 

[Hayley Baxter]

Hi Lisa

ok that helped clear things up a bit for me. I have stopped the parameter asking for the company name you had [Forms]![invoice]![Company] change that to [Forms]![company]![Company] as you are looking for the selected name on your main form called company and not your subform called invoice.

Next step regarding your dates, do you only want to capture the Datefrom field and have this automatically passed to the report without being prompted or do you want date from and to?

I have tried to get in touch with you on AOL but guess you've been busy, I might be around tomorrow night but if not I'll look for further posts on here.

Hay

 

[LisaJill]

I want from and to, based off the dates in the invoice subform =)

Thank you again, going to sleep now - spent all day in NYC!

-Lisa

I'll be around Sunday afternooon/Evening ny time if you have any more questions. I really appreciate your help with this.