Problem With Dialog Form Linked To Generate Report

A

Aamer

I have No idea why this code is still not working.

let me explain what I did

1. Created a popup form "Dialog General Purchase"
2. Placed a List Box which collects the Company Names from "General Purchase
Detail Querry" The feild for Company name is "Pur CompanyName" Grouped them
so they are not duplicated.
3. Placed a Button on the "Dialog General Purchase" Form
4. in the properties of the button on clik i cliked on CODE BUILDER and
entered the following code.

DoCmd.OpenReport "General Purchase Detail", , , "[pur companyname] = """ _
& Me.[lstpur companyname] & """"

after i do the above it still gives me error.



John W. Vinson said:
it did not work

I am selecting company name field name is "pur companyname"
no its not looking with customer id
report name is "General Purchase"

I have tried the following:

DoCmd.OpenReport "General Purchase", , , "pur companyname = " & Me.lstpur
companyname

but its not woking.
can you pls fix it.

Two problems that I see: for one, you (unwisely) used a blank in the
fieldname. If you use blanks or other special characters in fieldnames you
must enclose the fieldname in square brackets. In addition, if you're
searching for a text company name, you must include the syntactically required
quotemarks. Since a company name might contain an apostrophe (Joe's Diner for
example) you must delimit with doublequotes; to include a doublequote in a
string delimited with doublequotes, you must use TWO doublequotes. Try

DoCmd.OpenReport "General Purchase", , , "[pur companyname] = """ _
& Me.[lstpur companyname] & """"
 
A

Arvin Meyer [MVP]

I still think that you need to use the CompanyID and not the CompanyName.
Make it the first column of your list box and set its column width to
zero(0").

That said. There's no way that we can help you without giving us the full
error message.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Aamer said:
I have No idea why this code is still not working.

let me explain what I did

1. Created a popup form "Dialog General Purchase"
2. Placed a List Box which collects the Company Names from "General
Purchase
Detail Querry" The feild for Company name is "Pur CompanyName" Grouped
them
so they are not duplicated.
3. Placed a Button on the "Dialog General Purchase" Form
4. in the properties of the button on clik i cliked on CODE BUILDER and
entered the following code.

DoCmd.OpenReport "General Purchase Detail", , , "[pur companyname] = """ _
& Me.[lstpur companyname] & """"

after i do the above it still gives me error.



John W. Vinson said:
it did not work

I am selecting company name field name is "pur companyname"
no its not looking with customer id
report name is "General Purchase"

I have tried the following:

DoCmd.OpenReport "General Purchase", , , "pur companyname = " &
Me.lstpur
companyname

but its not woking.
can you pls fix it.

Two problems that I see: for one, you (unwisely) used a blank in the
fieldname. If you use blanks or other special characters in fieldnames
you
must enclose the fieldname in square brackets. In addition, if you're
searching for a text company name, you must include the syntactically
required
quotemarks. Since a company name might contain an apostrophe (Joe's Diner
for
example) you must delimit with doublequotes; to include a doublequote in
a
string delimited with doublequotes, you must use TWO doublequotes. Try

DoCmd.OpenReport "General Purchase", , , "[pur companyname] = """ _
& Me.[lstpur companyname] & """"
 
A

Aamer

AAAAAAAHHHHHHHHHH!!!!!!!!!!!

am going nutz trying to fix this issue. i tried everything you said, even
put the customer id


lets see what i am doing wrong.

1. Made a form named it Dialog PW3
2. Placed a Combo Box "SELECT [Company Names].CoId FROM [Company Names];"
.......... CoId is COMPANY ID
3. In the properties of the combo box , in Event Procedure After Update I
added the following code

DoCmd.OpenReport "GP PW3 Report", , , "[Company Names] = """ _ & Me.[lstpur
companyname] & """"

4. after i select the Company Name Or Company ID it should run GP PW3 Report.
the report querry name name is GP PW3 Querry


Please if you could help me sort out this problem it would be appreciated.

or i can get some sort of sample that would be helpful.
or if u want i can send you my working so u can see the problem and guide me.

thank u so much for your efforts.

regards
aamer sheikh









Arvin Meyer said:
I still think that you need to use the CompanyID and not the CompanyName.
Make it the first column of your list box and set its column width to
zero(0").

That said. There's no way that we can help you without giving us the full
error message.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Aamer said:
I have No idea why this code is still not working.

let me explain what I did

1. Created a popup form "Dialog General Purchase"
2. Placed a List Box which collects the Company Names from "General
Purchase
Detail Querry" The feild for Company name is "Pur CompanyName" Grouped
them
so they are not duplicated.
3. Placed a Button on the "Dialog General Purchase" Form
4. in the properties of the button on clik i cliked on CODE BUILDER and
entered the following code.

DoCmd.OpenReport "General Purchase Detail", , , "[pur companyname] = """ _
& Me.[lstpur companyname] & """"

after i do the above it still gives me error.



John W. Vinson said:
On Fri, 25 Dec 2009 10:38:01 -0800, Aamer
<[email protected]>
wrote:

it did not work

I am selecting company name field name is "pur companyname"
no its not looking with customer id
report name is "General Purchase"

I have tried the following:

DoCmd.OpenReport "General Purchase", , , "pur companyname = " &
Me.lstpur
companyname

but its not woking.
can you pls fix it.

Two problems that I see: for one, you (unwisely) used a blank in the
fieldname. If you use blanks or other special characters in fieldnames
you
must enclose the fieldname in square brackets. In addition, if you're
searching for a text company name, you must include the syntactically
required
quotemarks. Since a company name might contain an apostrophe (Joe's Diner
for
example) you must delimit with doublequotes; to include a doublequote in
a
string delimited with doublequotes, you must use TWO doublequotes. Try

DoCmd.OpenReport "General Purchase", , , "[pur companyname] = """ _
& Me.[lstpur companyname] & """"


.
 
J

John W. Vinson

AAAAAAAHHHHHHHHHH!!!!!!!!!!!

am going nutz trying to fix this issue. i tried everything you said, even
put the customer id


lets see what i am doing wrong.

1. Made a form named it Dialog PW3
2. Placed a Combo Box "SELECT [Company Names].CoId FROM [Company Names];"
......... CoId is COMPANY ID

So your combo box shows ONLY the Company ID... not the company name? Isn't
that a bit inconvenient for the user? You still haven't described the
fieldnames and structure of your tables, so I'm guessing here... but wouldn't
your [Company Names] table contain both a (numeric?) CoID and also a company
name?

If it does, normally you would want a rowsource like

"SELECT [Company Names].CoId, [Company Names].[Company Name] FROM [Company
Names] ORDER BY [Company Name];"

and use a column count of 2 (include both the ID and the name in the combo
box's data), a Bound Column of 1 (to store the CoID), and a ColumnWidths
property such as

0.0";1.5"

to conceal the ID and display the company name.
3. In the properties of the combo box , in Event Procedure After Update I
added the following code

DoCmd.OpenReport "GP PW3 Report", , , "[Company Names] = """ _ & Me.[lstpur
companyname] & """"


If you're in fact using a numeric ID, you don't need the extra quotes. Is
there in fact a *FIELD* named [Company Names] in the Report's Recordource
query? Or is it CoID (I'd have expected the ID)? Assuming that you do have a
CoID field in the Report's recordsource query the OpenReport line should be

DoCmd.OpenReport "GP PW3 Report", , , "[CoID] = " & Me.[lstpur companyname]

4. after i select the Company Name Or Company ID it should run GP PW3 Report.
the report querry name name is GP PW3 Querry

If the above doesn't work, please post the SQL view of GP PW3 Querry and
indicate the datatypes of the fields involved.
Please if you could help me sort out this problem it would be appreciated.
or i can get some sort of sample that would be helpful.

The Northwind sample database contains examples of how to do this.
 
A

Aamer

I have tried every possible way to do as you told
I FAILED .... I am self taught person.

If you could help me I would be greatful, as this is very important for me.
If could make a sample and send it to my email so i know exactly how you
have done it.

(e-mail address removed)

or if you want i can e-mail you my project.

Thank You so much


aamer

KenSheridan via AccessMonster.com said:
Aamer:

You could try a slightly different approach, which uses a list box, as you
did originally, but also has the added bonus of allowing you to select more
than one company if desired from the list, and open a single report for all
the selected companies. This not preclude your selecting just one company of
course.

To do this set up the list box like this:

For its RowSource property:

SELECT CoId, [pur companyname] FROM [Company Names] ORDER BY [pur companyname]
;

For its other properties:

Name: [lstpur companyname]
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
MultiSelect: Simple or Extended as preferred.

If you are using imperial rather than metric units Access will convert them
automatically.

Add a button to the form to open the report, with the following in its Click
event procedure:

Dim varItem As Variant
Dim strCompanyIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.[lstpur companyname]

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCompanyIDList = strCompanyIDList & "," & ctrl.ItemData(varItem)

Next varItem

' remove leading comma
strCompanyIDList = Mid(strCompanyIDList, 2)

strCriteria = "CoID In(" & strCompanyIDList & ")"

DoCmd.OpenReport "GP PW3 Report", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No companies selected", vbInformation, "Warning"
End If

As you are filtering the report on the basis of the CoId column rather than
the company name this does mean that the report's underlying query must
include the CoId column of course, even though you won't show it in the
report.

Ken Sheridan
Stafford, England
AAAAAAAHHHHHHHHHH!!!!!!!!!!!

am going nutz trying to fix this issue. i tried everything you said, even
put the customer id

lets see what i am doing wrong.

1. Made a form named it Dialog PW3
2. Placed a Combo Box "SELECT [Company Names].CoId FROM [Company Names];"
......... CoId is COMPANY ID
3. In the properties of the combo box , in Event Procedure After Update I
added the following code

DoCmd.OpenReport "GP PW3 Report", , , "[Company Names] = """ _ & Me.[lstpur
companyname] & """"

4. after i select the Company Name Or Company ID it should run GP PW3 Report.
the report querry name name is GP PW3 Querry

Please if you could help me sort out this problem it would be appreciated.

or i can get some sort of sample that would be helpful.
or if u want i can send you my working so u can see the problem and guide me.

thank u so much for your efforts.

regards
aamer sheikh
I still think that you need to use the CompanyID and not the CompanyName.
Make it the first column of your list box and set its column width to
[quoted text clipped - 58 lines]

--



.
 
A

Aamer

I have tried every possible way to do as you told
I FAILED .... I am self taught person in access.
using Access 2007

If you could help me I would be greatful, as this is very important for me.
If could make a sample and send it to my email so i know exactly how you
have done it.

(e-mail address removed)

or if you want i can e-mail you my project.

Thank You so much


aamer



John W. Vinson said:
AAAAAAAHHHHHHHHHH!!!!!!!!!!!

am going nutz trying to fix this issue. i tried everything you said, even
put the customer id


lets see what i am doing wrong.

1. Made a form named it Dialog PW3
2. Placed a Combo Box "SELECT [Company Names].CoId FROM [Company Names];"
......... CoId is COMPANY ID

So your combo box shows ONLY the Company ID... not the company name? Isn't
that a bit inconvenient for the user? You still haven't described the
fieldnames and structure of your tables, so I'm guessing here... but wouldn't
your [Company Names] table contain both a (numeric?) CoID and also a company
name?

If it does, normally you would want a rowsource like

"SELECT [Company Names].CoId, [Company Names].[Company Name] FROM [Company
Names] ORDER BY [Company Name];"

and use a column count of 2 (include both the ID and the name in the combo
box's data), a Bound Column of 1 (to store the CoID), and a ColumnWidths
property such as

0.0";1.5"

to conceal the ID and display the company name.
3. In the properties of the combo box , in Event Procedure After Update I
added the following code

DoCmd.OpenReport "GP PW3 Report", , , "[Company Names] = """ _ & Me.[lstpur
companyname] & """"


If you're in fact using a numeric ID, you don't need the extra quotes. Is
there in fact a *FIELD* named [Company Names] in the Report's Recordource
query? Or is it CoID (I'd have expected the ID)? Assuming that you do have a
CoID field in the Report's recordsource query the OpenReport line should be

DoCmd.OpenReport "GP PW3 Report", , , "[CoID] = " & Me.[lstpur companyname]

4. after i select the Company Name Or Company ID it should run GP PW3 Report.
the report querry name name is GP PW3 Querry

If the above doesn't work, please post the SQL view of GP PW3 Querry and
indicate the datatypes of the fields involved.
Please if you could help me sort out this problem it would be appreciated.
or i can get some sort of sample that would be helpful.

The Northwind sample database contains examples of how to do this.
 
J

John W. Vinson

I have tried every possible way to do as you told
I FAILED .... I am self taught person in access.
using Access 2007

If you could help me I would be greatful, as this is very important for me.
If could make a sample and send it to my email so i know exactly how you
have done it.

First let's try once more here. I need to know more about the structure of
your database.

Please post (in text) the fieldnames and data types of the fields in your
tables; and post the SQL view of the Recordsource for your form and your
report.
 
A

Aamer

Ken,

Thank you So so very much Ken, I am greatful to you for your help.
now I will fix the rest of it based of your teaching.
You are great help fixing my database.

Aamer Sheikh

KenSheridan via AccessMonster.com said:
Aamer:

I've mailed you a simple demo file which shows how to open a report for a
single customer using a combo box, or for one or more customers using a multi-
select list box.

BTW if you post your email address here, or in any public forum or newsgroup,
be sure to 'munge' it, e.g. shahid<dot>skrappz<at>gmail<dot>com. Otherwise
you might find it gets harvested by spammers and you start getting unwanted
and annoying emails.

Ken Sheridan
Stafford, England
I have tried every possible way to do as you told
I FAILED .... I am self taught person in access.
using Access 2007

If you could help me I would be greatful, as this is very important for me.
If could make a sample and send it to my email so i know exactly how you
have done it.

c

or if you want i can e-mail you my project.

Thank You so much

aamer
AAAAAAAHHHHHHHHHH!!!!!!!!!!!
[quoted text clipped - 49 lines]
The Northwind sample database contains examples of how to do this.

--



.
 

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

Top