how can i open a report from a subform

R

RS Controls

I have a simple database it is based on company data i.e., customer data
(name/address 10 fields) invoice data (50 fields includes things like line
item descript, units, cost per and such)
My objective is to create a form to select the active customer and a sub
form to display or enter those customer’s invoices. Then to print a report
based on the combined information to create an invoice to mail out.
What I have is a sub form (invoicedata) attached to a form (customerdata)
and it works fine I have a report (report1) that looks like an invoice with
all the fields from a select query called (reportquery) I have my two tables
called (customerdatatable) and (invoicedatatable) on the (reportquery). the
relationship between the tables is customerid and invoiceid I want to search
for a particular invoice based on the invoicenumber field that comes form a
control on the sub form called advinvnumber (this is a combo box I use to
select the particular invoice I am working with)
In the query (reportquery) under field name invoicenumber table
invoicedatatable in the criteria I have entered many things and have the same
result when I run the form. I select an invoice and click the command button
to preview the report. I get a dialog box wanting the invoice number.

Things I have entered into the criteria field
(1) Forms.customerdata.invoicedata.form.advinvdata
(2) Forms!customerdata!invoicedata!form!advinvdata
(3) [Forms].[customerdata].[invoicedata].[form].[advinvdata]
(4) ) [Forms]![customerdata]![invoicedata]![form]![advinvdata]

Can some one tell me my mistakes your help would be much appreciated
 
R

RS Controls

Thanks Al i tried that and have the same results i have tried many things in
the criteria field. actuly i cut and pasted your statement the query added
brackets to it
[Forms]![customerdata]![invoicedata].[form]![advinvdata] is this correct ?
it looks and sounds good when viewing as SQL
this is the last 5 lines of the SQL

FROM CustomerData INNER JOIN InvoiceData ON
CustomerData.CustomerID=InvoiceData.InvCustCode
WHERE
(((InvoiceData.InvNumber)=Forms!customerdataform!invoicedataform.form!advinvdata))
ORDER BY InvoiceData.advInvNumber;
would there be any way i can send you this program in a email and let you
take a look at it
thanks again for your help
Al Campagna said:
RS,
Forms!customerdata!invoicedata.form!advinvdata
should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

RS Controls said:
I have a simple database it is based on company data i.e., customer data
(name/address 10 fields) invoice data (50 fields includes things like line
item descript, units, cost per and such)
My objective is to create a form to select the active customer and a sub
form to display or enter those customer's invoices. Then to print a report
based on the combined information to create an invoice to mail out.
What I have is a sub form (invoicedata) attached to a form (customerdata)
and it works fine I have a report (report1) that looks like an invoice
with
all the fields from a select query called (reportquery) I have my two
tables
called (customerdatatable) and (invoicedatatable) on the (reportquery).
the
relationship between the tables is customerid and invoiceid I want to
search
for a particular invoice based on the invoicenumber field that comes form
a
control on the sub form called advinvnumber (this is a combo box I use to
select the particular invoice I am working with)
In the query (reportquery) under field name invoicenumber table
invoicedatatable in the criteria I have entered many things and have the
same
result when I run the form. I select an invoice and click the command
button
to preview the report. I get a dialog box wanting the invoice number.

Things I have entered into the criteria field
(1) Forms.customerdata.invoicedata.form.advinvdata
(2) Forms!customerdata!invoicedata!form!advinvdata
(3) [Forms].[customerdata].[invoicedata].[form].[advinvdata]
(4) ) [Forms]![customerdata]![invoicedata]![form]![advinvdata]

Can some one tell me my mistakes your help would be much appreciated
Click to expand...
Click to expand...
 
T

tina

i'm guessing that "invoicedata" is the name of the subform, as you see it in
the database window. it may have a different name *within the customerdata
form*. to find the correct name for the reference, open form customerdata in
Design view. click ONCE on the subform *within the customerdata form*, to
select it. in the Properties box, click on the Other tab and look at the
Name property. that's the name you need to use in the reference, where you
now have "invoicedata".

hth


RS Controls said:
Thanks Al i tried that and have the same results i have tried many things in
the criteria field. actuly i cut and pasted your statement the query added
brackets to it
[Forms]![customerdata]![invoicedata].[form]![advinvdata] is this correct ?
it looks and sounds good when viewing as SQL
this is the last 5 lines of the SQL

FROM CustomerData INNER JOIN InvoiceData ON
CustomerData.CustomerID=InvoiceData.InvCustCode
WHERE
(((InvoiceData.InvNumber)=Forms!customerdataform!invoicedataform.form!advinv
data))
ORDER BY InvoiceData.advInvNumber;
would there be any way i can send you this program in a email and let you
take a look at it
thanks again for your help
Al Campagna said:
RS,
Forms!customerdata!invoicedata.form!advinvdata
should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

RS Controls said:
I have a simple database it is based on company data i.e., customer data
(name/address 10 fields) invoice data (50 fields includes things like line
item descript, units, cost per and such)
My objective is to create a form to select the active customer and a sub
form to display or enter those customer's invoices. Then to print a report
based on the combined information to create an invoice to mail out.
What I have is a sub form (invoicedata) attached to a form (customerdata)
and it works fine I have a report (report1) that looks like an invoice
with
all the fields from a select query called (reportquery) I have my two
tables
called (customerdatatable) and (invoicedatatable) on the (reportquery).
the
relationship between the tables is customerid and invoiceid I want to
search
for a particular invoice based on the invoicenumber field that comes form
a
control on the sub form called advinvnumber (this is a combo box I use to
select the particular invoice I am working with)
In the query (reportquery) under field name invoicenumber table
invoicedatatable in the criteria I have entered many things and have the
same
result when I run the form. I select an invoice and click the command
button
to preview the report. I get a dialog box wanting the invoice number.

Things I have entered into the criteria field
(1) Forms.customerdata.invoicedata.form.advinvdata
(2) Forms!customerdata!invoicedata!form!advinvdata
(3) [Forms].[customerdata].[invoicedata].[form].[advinvdata]
(4) ) [Forms]![customerdata]![invoicedata]![form]![advinvdata]

Can some one tell me my mistakes your help would be much appreciated
Click to expand...
Click to expand...
Click to expand...
 
R

RS Controls

Thanks Tina and Al both of you got me on the right track i was telling it the
wrong name form but fixing that give me a blank report ,still scratching my
head i tried putting a text box on the subform and set the sorce to
invoicenumber it displayed the same info as the combo box does how ever using
that name in the criteria give me the results i needed .going back and trying
to use the combo box sitll doesnt work (go figure )all the same problem
solved and yall did good
THANKS AGAIN
RS Controls

tina said:
i'm guessing that "invoicedata" is the name of the subform, as you see it in
the database window. it may have a different name *within the customerdata
form*. to find the correct name for the reference, open form customerdata in
Design view. click ONCE on the subform *within the customerdata form*, to
select it. in the Properties box, click on the Other tab and look at the
Name property. that's the name you need to use in the reference, where you
now have "invoicedata".

hth


RS Controls said:
Thanks Al i tried that and have the same results i have tried many things in
the criteria field. actuly i cut and pasted your statement the query added
brackets to it
[Forms]![customerdata]![invoicedata].[form]![advinvdata] is this correct ?
it looks and sounds good when viewing as SQL
this is the last 5 lines of the SQL

FROM CustomerData INNER JOIN InvoiceData ON
CustomerData.CustomerID=InvoiceData.InvCustCode
WHERE
(((InvoiceData.InvNumber)=Forms!customerdataform!invoicedataform.form!advinv
data))
ORDER BY InvoiceData.advInvNumber;
would there be any way i can send you this program in a email and let you
take a look at it
thanks again for your help
Al Campagna said:
RS,
Forms!customerdata!invoicedata.form!advinvdata
should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have a simple database it is based on company data i.e., customer data
(name/address 10 fields) invoice data (50 fields includes things like line
item descript, units, cost per and such)
My objective is to create a form to select the active customer and a sub
form to display or enter those customer's invoices. Then to print a report
based on the combined information to create an invoice to mail out.
What I have is a sub form (invoicedata) attached to a form (customerdata)
and it works fine I have a report (report1) that looks like an invoice
with
all the fields from a select query called (reportquery) I have my two
tables
called (customerdatatable) and (invoicedatatable) on the (reportquery).
the
relationship between the tables is customerid and invoiceid I want to
search
for a particular invoice based on the invoicenumber field that comes form
a
control on the sub form called advinvnumber (this is a combo box I use to
select the particular invoice I am working with)
In the query (reportquery) under field name invoicenumber table
invoicedatatable in the criteria I have entered many things and have the
same
result when I run the form. I select an invoice and click the command
button
to preview the report. I get a dialog box wanting the invoice number.

Things I have entered into the criteria field
(1) Forms.customerdata.invoicedata.form.advinvdata
(2) Forms!customerdata!invoicedata!form!advinvdata
(3) [Forms].[customerdata].[invoicedata].[form].[advinvdata]
(4) ) [Forms]![customerdata]![invoicedata]![form]![advinvdata]

Can some one tell me my mistakes your help would be much appreciated
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform link problem 1
Create "One" Record from the "Many" Table 5
Code to Search by Field in Subform 1
how to have a form that generate invoice no. 2
How would I add "shipping charge" to an Invoice (form/subform) ? 4
How to make select B subquery on select A? 2
Can I retrieve information from a table onto a form? 1
Problem with subform control 1

Top