Trouble previewing report from Subform/Form

[Lynndyhop]

Hi there!

This discussion group is a lifesaver and I'm asking for a life ring one more
time....

I have a Form with a Subform of invoices. The form is linked to a table with
ReferenceID being the Key. The Subform is linked to a table 'Invoices' with a
key InvoiceID and is linked in relationships with a one ReferenceID to many
InvoiceID. I have created the report and am working on the code for the cmd
button and keep getting an error ' Method or data Member Not found'.

I've *starred* the text the error is highlighting. Here is the code:

Private Sub PreviewInvoice_Click()
On Error GoTo Err_PreviewInvoice_Click

Dim MyWhereCondition As String

MyWhereCondition = "ReferenceID= " & Me*.ReferenceIDtxt* & " AND InvoiceID =
" & Me.FormMembershipInvoices.Form.InvoiceIDtxt
DoCmd.OpenReport " MembershipInvoicesGeneration ", acPreview, ,
MyWhereCondition


Exit_PreviewInvoice_Click:
Exit Sub

Err_PreviewInvoice_Click:
MsgBox Err.Description
Resume Exit_PreviewInvoice_Click

End Sub

Many many thanks for your help!!

 

[Allen Browne]

Suggestions:

1. Is there a control named ReferenceIDtxt on the form that contains this
code? What kind of control is it? (Hopefully not a Label or something like
that?)

2. Sometimes the Detail section of a form goes blank (when there is no
record to show, and no new record can be added.) When this happens, Access
gets confused and can throw this error. Does that describe your case?

3. Sometimes VBA highlights the wrong part of the line, i.e. the error could
be later.
a) Is the subform *control* named FormMembershipInvoices?
Or is that merely the name of the form loaded into the subform?

b) Does the subform contain a text box named InvoiceIDtxt?
Or is that merely the name of a field in the subform's source table/query?

It would also be a good idea to ensure Name AutoCorrect is turned off. Then
compact/repair, decompile, and compact again. Detailed steps here:
http://allenbrowne.com/recover.html

 

[Lynndyhop]

Hi Allen!

Answers to your questions:

1. There is a control - a text box named Reference ID on the main form. The
Reference ID is the key for the CompanyData table. The form is tabbed, and
the reference ID has been copied onto each tab, but I am referencing the name
of the ReferenceID on the tab with the invoices subform. I have a cmd button
on the form (PreviewInvoice) that I have inserted the code into.

2. I don't think this is the case. I am clicking on a record in the subform
(one invoice for a particular company, in a datasheet subform) that has
detail listed.

3. I had wondered if this is where I am going wrong as this as this is a new
bit of code for me and I think I'm getting mixed up as to what is called
what, and what item fits into what part of the code....
a) The subform control is named InvoiceIDtxt (another text box in the
datasheet - the key-not an autonumber) the sub form is named
FormMembershipInvoices outside of the main form, but its loaded into the main
form as Invoices (just realized this is different - hoping I'm onto
something... Do I have it mixed up?

b) The subform does contain a textbox named InvoiceIDtxt. It's also called
that in the sub form designed outside of the main form, and in the table it's
based on, it's called InvoiceID.

4. Done all the steps except on the compile, it's stopping on this same bit.

Hope that gives you some more information!

Thanks again,

Lynndyhop

 

[Allen Browne]

I didn't follow that.

You say you have a text box named:
Reference ID
with a space. Then you talk about:
ReferenceID
without a space. But in your original post, the expression was:
MyWhereCondition = "ReferenceID= " & Me*.ReferenceIDtxt* & ...
where the stars were (presumably) not actually part of the name.

Is there a text box named:
ReferenceIDtxt
or not?

(You really do have to be this pedantic for Access to understand you.)

 

[Lynndyhop]

Hi Allen,

Appreciated :0) And this is where I think I am getting things mixed up -
sorry for the confusion - the textbox is named ReferenceIDtxt in the subform,
and the table it's based off, is named ReferenceID. No spaces - typo on my
part....

And yes - the stars were not part of the name - just to show what was being
highlighted by the error.

Thanks!

Lynndyhop

 

[Allen Browne]

If it's in the subform, but the command button is on the main form, you will
need to use the subform control's name and the .form bit, as well as the
control name.

For details, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Of course, that will refer to whichever is the current record in the
subform.

 

[Lynndyhop]

Hi Allen!

I finally got it to work - I think I was making the Sub a bit more
complicated than I needed to. Looking at the 'Me' format and the link you
gave me really helped. Here's what I ended up with - much simpler:

Private Sub PreviewInvoice_Click()
On Error GoTo Err_PreviewInvoice_Click

Dim MyWhereCondition As String

MyWhereCondition = "(InvoiceID = " & Me.Invoices.Form.InvoiceIDtxt & ")"
DoCmd.OpenReport " MembershipInvoicesGeneration ", acPreview, ,
MyWhereCondition

Exit_PreviewInvoice_Click:
Exit Sub

Err_PreviewInvoice_Click:
MsgBox Err.Description
Resume Exit_PreviewInvoice_Click

End Sub

Thanks again for your help!