Columns setting in report

[deepaksharma]

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

1.Actually, I wanted the report to be shown in the sequence in which the entries are made in the shed and meter sub forms, but if it is working on alpha sort rule then it is fine. there is no problem. Thank you.

2. I want to select the offer number to print the offers.

3. As per your suggestion I have removed the criteria from the print query.

4. As per your suggested filter criteria, I have currently blocked this code from the print button in the offer entry form.
And instead of this, the PrintSelect form has been set to open which will ask the user for the range of offer numbers to print the offer.

'DataToFourColumns
'DoCmd.OpenReport "GREY_OFFER", acViewPreview


I worked very hard the whole day today on the code given by you and the link given by you but still I am not able to code this print range form (PrintSelect) please help me in this.

5. Please also explain that instead of all the data of greyshed file being copied in the greytemp file, only the data related to the range of offer numbers which will be entered in the print select form should be copied in the greytemp file. How would this be possible?

6. The expression you suggested for total meter was not working even after several attempts, after that I added the offer ID field in the print query and added that field to the report, which made the expression work now. now I will set the offer ID text box invisible. thank you very much.

Please check the attachment

[mike60smart]

Hi
I modified the Preview report Button to now open the report and only show the range of numbers entered.

[deepaksharma]

Hi
I modified the Preview report Button to now open the report and only show the range of numbers entered.

Thank you. Please also explain how this happened. Along with this, also explain that when multiple frontend copies of this file will be created, then a linked copy of the greytemp file will have to be kept in each frontend program or one original copy of the greaytemp file will have to be created on each.
Thanks again.

[deepaksharma]

i already said can't have both grey_shade and greytemp in report recordsource.
[offerid])

hi june
please explain note #31 point 5
thank you!!!!

[June7]

As suggested in post #29, like:

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

[June7]

Along with this, also explain that when multiple frontend copies of this file will be created, then a linked copy of the greytemp file will have to be kept in each frontend program or one original copy of the greaytemp file will have to be created on each.

There is no "linked" copy in frontend. It is an actual table built in the frontend, not a link.

[deepaksharma]

There is no "linked" copy in frontend. It is an actual table built in the frontend, not a link.

I have renamed both the text boxes in the print select form "cbxStart" and "cbxStart" put this coding on the OK button.

'DoCmd.OpenReport "Grey_Offer", acPreview, , "OfferNumber BETWEEN " & Me.cbxStart & " AND " & Me.cbxend

Now this form is working well but this following coding I could not understand where and when it is used

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

[Welshgasman]

Why oh why would you name a textbox starting the cb?

If anything I would put that down as an attempt at a combo? Most people would use txt for textbox.

[June7]

My example was presuming a combobox for selecting OfferID from dropdown list. Use whatever names you want for your controls and adjust code accordingly. These are examples. Adapt to your circumstances.


That Set rs code is in VBA procedure for populating GreyTEMP. Find it and modify. Know your code.

[deepaksharma]

My example was presuming a combobox for selecting OfferID [/B].

1. Thank you very much, now I understood that on every frontend program all the tables have to be kept linked but the import copy of the greytemp file has to be kept.

2. now i am getting the same offer ID range in the print preview which is entered in the offer ID range print select form.

3. I have updated the code suggested by you in the model. Now the same shades are being copied in the graytemp file whose range's offer ID is entered in the prientselect form.

Now only one small problem is left that I do not enter the offer ID in the print select form but I enter the range of offer numbers there, so please give your valuable suggestions by modifying the set rs = code to be inserted in the module.
Thank you.

[June7]

I already showed that code in post 29 and 35 (looks like I accidentally type OrderID). Use your actual form and control names, which appear to be:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM GREY_SHADE WHERE OfferID BETWEEN " & Forms!PrintSelect.txtFrom & " AND " & Forms!PrintSelect.txtTo & " ORDER BY OfferID, Greyshadeid")


Now where are you calling this procedure? I don't see it in the button macro.

[deepaksharma]

I already showed that code in post 29 and 35 (looks like I accidentally type OrderID). Use your actual form and control names, which appear to be:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM GREY_SHADE WHERE OfferID BETWEEN " & Forms!PrintSelect.txtFrom & " AND " & Forms!PrintSelect.txtTo & " ORDER BY OfferID, Greyshadeid")


Now where are you calling this procedure? I don't see it in the button macro.

You are right, you had typed the order ID, I used it as offer ID and it is working properly but I am giving print preview from the offer number field.
Please see gray offer table there is a field offer number whereas offer id is an autonumber field created to create a table relation.

[June7]

OfferNumber is not in Grey_Shade table. This means the VBA SQL statement will have to JOIN tables to pull data from Grey_Offer.

Set rs = CurrentDb.OpenRecordset("SELECT Shade, Meter, OfferNumber FROM GREY_OFFER INNER JOIN GREY_SHADE ON GREY_OFFER.OfferID = GREY_SHADE.OfferID " & _
                "WHERE OfferNumber BETWEEN " & Forms!PrintSelect.txtFrom & " AND " & Forms!PrintSelect.txtTo & " ORDER BY OfferNumber, Greyshadeid")

Modify rest of code to replace OfferID with OfferNumber. Rename field in GreyTEMP.

Personally, I would prefer to use a combobox to select offer and do all filtering on OfferID.

Probably should make sure txtTo input is greater than txtFrom. Can use ValidationRule and ValidationText properties to manage that.



All modules should have Option Explicit in header. Can set VBA Editor to include this automatically with new modules. Tools > Options > check Require Variable Declaration
Will have to manually add to existing.

[deepaksharma]

OfferNumber is not in Grey_Shade table. This means the VBA SQL statement will have to JOIN tables to pull data from Grey_Offer.

Thank you very much, the more I thank you, the less it would be, because of you, such a complex question has been solved and I have got to learn a lot from you. Now I don't want to bother you any more, but if you tell me how the space interval between the meter and the shed line shown in the report can be increased, it will be very kind of you.
Thanks again.

[Micron]

how the space interval between the meter and the shed line

Increase the line spacing property to be larger than 0"