Columns setting in report

[June7]

Even if that alternative could be done, the 'labels' would still occupy space. I doubt page would be wide enough.

GreyTEMP table does not have fields I specified.



Code is not latest version.

Read post 11 again.

Get code working and then can deal with issues of report design.

[deepaksharma]

This is beyond my understanding. I tried as much as I could, but this time I failed in every attempt. Secondly, you told that when more than one user uses this program, it will show temp file error, so I found my suggested solution to be ok. Is there any simple way to ensure that the labels in columns other than the first column become white or disappear? I will done set label space.

[June7]

Is there any simple way to ensure that the labels in columns other than the first column become white or disappear?

No.


Until you describe exactly what went wrong with your attempt to follow my instructions, cannot advise correction. 2 simple steps modify your database to run suggested code:

1. create GreyTEMP table with fields: RowNum, OfferID, Label, Col1, Col2, Col3, Col4

2. put code in a general module or behind a form, not behind report

When that works, modify report to use GreyTemp table, possibly as a subreport. And on further review, I think GREY_SHADE table will have to be removed from query and employ another method to get the aggregate Sum of Meter. But will deal with that after you get suggested code to run.


And yes, if there will be multiple simultaneous users, database should ALWAYS be split design - even if you do not use temp table. Temp table should be in frontend.

[deepaksharma]

No.


Until you describe exactly what went wrong with your attempt to follow my instructions, cannot advise correction. 2 simple steps modify your database to run suggested code:

Thank you very much June it is working now. Now some questions are coming to my mind.
1. Shades are appearing in this report but meter column is not appearing, what needs to be done for that.
2. How will the meter total be obtained?
3. How will the graytemp file empty after closing the print preview or after the page is printed?
4.How to print multi pages.
5. If multiple users will print this report, then how will we remove the error or how will we stop the second user from printing until the printing process of the first user is completed.

Please check the attachment and Thank you very much again.

[June7]

1. set textboxes CanGrow property to Yes or resize them large enough to display all data

2. two options - DSum() domain aggregate function expression in textbox or build an aggregate query and join that query to GREY_OFFER in report RecordSource query

Whichever used, will need same date range filter criteria used by the main query. Advise not to use query popup inputs. Reference controls on form for user inputs.

3. don't need to - GreyTEMP is emptied at beginning of procedure in preparation to receive data

4. don't know what you mean - report will print as many pages as necessary to output all data in Detail section

5. split db - GreyTEMP is located in frontend - each user runs their own copy of frontend


What purpose does OfferNumber serve?

[deepaksharma]

1. set textboxes CanGrow property to Yes or resize them large enough to display all data

Thank you very much for replying.

1. Cangrow property has been set, now it is working.
2. The meter is still not able to total, I am making more efforts for this.
3. I have checked the greytemp file many times, it does not empty but data keeps getting added to it.
4. By multi page printing I mean printing multiple offers. Actually offer means order, you can understand it like printing a bill from bill number or printing a mark sheet from roll number or printing a passport from passport number. Similarly, this program is designed for order booking. The order is called offer and offers will be printed by offer number, so I want to know how to print multiple offers.
5. I am unaware of fronted method, I will study about it on Google, please tell me a little whether multiple copies of greytemp file will have to be made or multi users will be able to work with one gretemp file.
Thank you.

[ArviLaanemets]

5. I am unaware of fronted method, I will study about it on Google, please tell me a little whether multiple copies of greytemp file will have to be made or multi users will be able to work with one gretemp file.
Thank you.

When you split your DB to back- and front-ends, then generally back-end contains all components to which several users your DB must have access to, or where all users are storing some shared data (usually Tables and queries).
The front-end is where all forms, reports, and most of code used to process the data, are stored. It can also contain e.g. some tables, or queries, in case those are meant for use only from this instance of front-end (or in case the query gets data from table in this front-end).
DB can have one, or several back-ends (usually one), and must be stored on network resource available for all users. The back-end can be created in Access, or in some other DB system Access is able to connect with (e.g. SQL Server).
Front-end(s) of-course are created in Access - otherwise it isn't Access DB anymore.
Every user MUST have his/her own instance of DB's front-end. It can be on HD of user's computer, or on network resource in same LAN as back-end. When front-end is situated in users computer, the user starts the front-end from there. When front-end is situated on LAN drive, the user can start the front-end either from his/her computer, or from his/her Terminal Server profile in same LAN (the last option also allows to work with split DB remotely).
The front-end has links to tables (needed for this front-end) in back-end DB(s). When designing the database, you can determine how records in back-end are locked - but usually Access default settings are good enough.
You create at least one front-end DB. When all users can work with it, you simply create copies of this front-end, and distribute those to users. But you also can create any number of different front-ends, depending on what user needs, or to which data he/shy is allowed the access to. E.g. accontant and master in production usually work with entirely different set of data.

As the GreyTemp file will be in user's copy of front-end, and the front-end may be saved anywhere in computer or LAN, the user has access, of-course only user of this front-end has guaranteed access to it (unless all people in your LAN have the full access to all other computers/user profiles )

[deepaksharma]

I have created a query named Print to link data in the report. I am trying many times to create an expression to get the total meter in it. One thing I cannot understand is why the sequence of shed and meter gets messed up in the report whenever i mess with the query.
Please see the attachment.
Thank you.

[deepaksharma]

When you split your DB to back- and front-ends, then generally back-end contains all components to which several users your DB must have

thank you..

[June7]

I already described options for total calculation - item 2 in post 20. What is is not clear?

I don't know why data arranges like that. Data is different from the posted db. I would have to see data you are working with.

[deepaksharma]

I already described options for total calculation - item 2 in post 20. What is is not clear?

I don't know why data arranges like that. Data is different from the posted db. I would have to see data you are working with.

1.I am learning how to create an aggregate function expression and how can i print multiple offers.
2. The data format is being shown differently so I am attaching the file. Is it possible that the data format does arranged in the print query.
3. Please also check that the graytemp file is not getting emptied automatically.

Thank you.

[June7]

What do you mean by 'print multiple offers'? Do you want to print them in one report? Look at using GROUPING SECTION on Offer.

Change SQL statement to use ORDER BY.
Set rs = CurrentDb.OpenRecordset("SELECT * FROM GREY_SHADE ORDER BY OfferID, Shade")

GreyTEMP is emptied at beginning of procedure with:
CurrentDb.Execute "DELETE FROM GreyTEMP"
Don't have to empty table at end of procedure but can if you want.

RowNum does not have to be an Autonumber type, Number (Long Integer) would be fine. I included this field thinking it might be useful but not really seeing a need for it.

[deepaksharma]

Set rs = CurrentDb.OpenRecordset("SELECT * FROM GREY_SHADE ORDER BY OfferID, Shade")
[/B]

Thanks, I have updated the code as per your suggestion.
1. Now all the sheds and meters are showing in a row.
2. I had set the header and footer wrong in the report due to which multi offers were not being printed. I have corrected it so now multi offers are being printed. Please check.
3. To calculate total meter I have linked gray shade file in print query please check it also.

Now the problem comes that

a. In gray shed file the data is shown in the actual sequence as it is entered but in gray temp file its sequence gets messed up causing the data to be shown in the wrong sequence in the report.
b. I was thinking that the shed and meter data related only to the offers that will be printed will be temporarily copied in the gray temp file, but all the sheds and meters of the gray shed file are being copied in the grey temp file, Due to there is a possibility of increase in the size of the program and slowing down of the program for some time.

Is it possible to copy the shades and meters only of the offer's to be printed to the gray temp file? If this is possible please suggest a solution.

Thank you very much again.

[June7]

What would determine the correct sequence? Can't rely on Shade field because this is text and alpha sort rules apply: A10 will sort before A2 and Blue before Red.
If you want to rely on Greyshadeid field for order, use it in ORDER BY clause instead of Shade.

Add whatever filter criteria you want to the recordset SQL with WHERE clause. How do you want to select offers for printing? By OfferDate or OfferID?

The pint query has criteria for OfferID range. These are popup input prompts. Suggest you instead reference controls on a form. My preference is to not have parameters in query. I apply filter criteria to report like:

DoCmd.OpenReport "Grey_Offer", acPreview, , "OfferID BETWEEN " & Me.cbxStart & " AND " & Me.cbxEnd

Can reference these same controls to provide criteria for recordset:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM GREY_SHADE WHERE OrderID BETWEEN " & Forms!formname.cbxStart & " AND " & Forms!formname.cbxEnd & " ORDER BY OfferID, Greyshadeid")

Example of a "search" form to build filter criteria http://allenbrowne.com/ser-62.html

[June7]

I already said can't have both Grey_Shade and GreyTEMP in report recordsource.

Remove Grey_Shade and Sum calc will have to be provided by either:

1. DSum() expression in textbox: =DSum("Meter", "Grey_Shade", "OfferID=" & [OfferID])

or

2. Build and save an aggregate query object
SELECT OfferID, Sum(Meter) AS SumMeter FROM Grey_Shade GROUP BY OfferID;
Join that query to Grey_Offer table in pint query
Textbox ControlSource: =SumMeter