Need to refine functionality of a command button

[arcuspluvius]

I'm putting together a new database for my father who runs his own business. I set him up with a tiny MS Works database for keeping track of addresses but now that the business has grown, I'm trying to replace that with something more powerful.

I warn you now, while I'm quite good with computers and from my job I know a little about database structure and the basic principals of SQL, I am not a professional and this is my first major dabble in building my own relational database. I'd really appreciate any help that anyone can give me.


I've tried to set up an interface using one main form, a tabbed area and sub-forms where all the data from all the related tables can be viewed easily.

I'm trying to create a command button which will generate a report for an individual invoice. For invoices I have set up an 'index' table and then an 'itemisation' table for the individual items to be included. The 'index' table is displayed as a subform on my main form. (There is also a subform in one of the tabs in my tabbed area which has the summary form and an embedded subform showing the itemisation.)

Using the Access wizard I have created a button which generates a report detailing all the invoices connected to a particular customer. I need to refine the button so that it pulls up one invoice at a time - i.e. restrict the records based on the unique identifier of my invoice index table.

If I open the subform individually (i.e. not embedded in the main form) I can achieve the desired effect by using a macro, refined by the following 'WHERE' clause:
[inv_InvoiceID]=[Forms]![Invoice details]![InvoiceID]

Once I open the form as a subform of my main form, however, it stops working. I get a little box asking me to define the parameter of the piece of code above. (Note: This macro ran sucessfully on both the invoice subforms mentioned above when opened individually.)

I have worked out that the reason the macro stopped working on my form when it becomes a sub form is that it's getting confused about where the objects are that I'm asking it to compare. Having looked on the internet I've found lots of articles describing how to make reference to controls on a subform using a statement of the format:
Forms![form name]![control name].Form.[control name]
but I just can't get it to work.

I was hoping that someone would either be able to help me make the macro work or explain to me how to alter this code, which is the VBA code provided by the Access wizard to refine the report to only pull up records for one invoice at a time:

Option Compare Database

Private Sub itemisation_Click()
On Error GoTo Err_itemisation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Itemisation for invoices"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_itemisation_Click:
Exit Sub

Err_itemisation_Click:
MsgBox Err.Description
Resume Exit_itemisation_Click

End Sub
Private Sub Preview_report_Click()
On Error GoTo Err_Preview_report_Click

Dim stDocName As String

stDocName = "Invoices"
DoCmd.OpenReport stDocName, acPreview

Exit_Preview_report_Click:
Exit Sub

Err_Preview_report_Click:
MsgBox Err.Description
Resume Exit_Preview_report_Click

End Sub


Many thanks
SGR

 

[TheAceMan1]

How are ya arcuspluvius . . .

Have a look here: How to refer to a control on a subform or subreport in Access

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]