Suljettu

Private project: Microsoft Access and VBA

This is intended as a private project. Please do not bid on this project unless you have contacted me previously.

FIXES

1) The 'Total Price' that the user selects on the ProductShipment subform doesn't save. For example, try changing one of the 'price type' combo boxes so that the total refreshes. Then save the record, exit the form, and re-load the form and you'll find the new total didn't save (even though the field's control source is set to the 'Total Price' field).

2) For most forms printing an individual supplier/transport company/shipping agent's details comes up on 1 landscape page. But when printing an individual customer's details, it goes over to 2 pages. Could we have this on 1 landscape page please?

Now for the additional requirements (I'm hoping this will be most of the additional work, but my uncle may also have some changes to suggest when I meet with him next). So I will keep you posted.

NEW REQUIREMENTS

1) You will see that now, when the ProductShipment filter is set to 'on,' three overall totals appear at the bottom of the screen (at the moment displaying #Error). Could you please calculate these totals for the filtered shipments and print the totals as a 'footer' on the FilteredShipments report?

2) I've also added an 'email this shipment' and 'email filtered shipments' button to the ProductShipment form. Could you please edit the code for these buttons so that it uses the default e-mail client to e-mail the current/filtered shipments (in RTF format) with an e-mail subject 'FRESCO shipment report' and e-mail text 'Find a FRESCO shipment report attached.' Please check that the reports look consistent in RTF format, as I know that sometimes they mess up!

3) I tried to set validation rules and user-friendly text for all the required fields in all tables (see the table design view). However when a field is left blank on any of the forms, I still get the standard system message that the field is null (not my more user-friendly custom messages as defined in the 'validation text' fields. Could you fix this for me please? Ideally, I would like to achieve this at the table validation level rather than using VBA to validate or trap the errors at the form level. But if you need to do it at the form level, you can. You can use the validation text that I've already defined in table design view.

4) You'll see that I have designed the structure (but not the functionality) of a 'reports' form (which loads when a button is pressed on the ProductShipment form). At the moment, there are a few things on the form that need fixing:

4a) I sometimes get the message that the record cannot be saved when I close the form (and a separate table loads with my selected combo values stored). I think this must be to do with the combo boxes.

4b) The ContactFrame and the text boxes and labels within it (Telephone, TelephoneLabel, Fax, FaxLabel, EMail, EMailLabel) don't quite work properly yet. What I want is for these only to be enabled when one of the first 3 options are selected from the 'create report for products' option list, and only when a particular customer/supplier/transport company/storage company is selected. So when '(All suppliers)' is selected, those widgets should be greyed-out (as the idea is for the text boxes to display the telephone number, fax number and e-mail address of the selected customer/supplier/transport company/storage company). See the 'AfterUpdate' property of the SupplierFilter combo for my attempt at making this work.

4c) This means also making the text boxes to display the telephone number, fax number and e-mail address of the selected customer/supplier/transport company/storage company) :-)

5) Now for the reports themselves. There are 6 different reports that we need (and you can use the same format/labels as the existing TheShipment report). In particular, note that many label names (particularly for the totals) are different in TheShipment report.

For each report, you'll see buttons at the bottom of the form that should allow the user to:

- Save the report as an RTF file

- Print/fax the report (don't worry about faxing - just make the report print-preview).

- E-mail the report. This should use the default e-mail client to e-mail the report (in RTF format) with an e-mail subject 'FRESCO supplier/customer/storage agent/transport company report' and e-mail text 'Find a FRESCO supplier/customer/storage agent/transport company report attached.' The subject and body text will depend on which report has been selected (i.e. supplier/customer/storage agent/transport company report).

Currently, there is some code attached to the buttons, but you'll need to edit it to make them work correctly (and save/print/e-mail the correct report).

The reports described in 5a and 5b will not be displayed by date. However, the other reports will only display records either dispatched/collected on a particular date, or between 2 dates (see the date selection widgets on the Reports form). So, for 5c-5f, the selected date or dates on the form should be used to only display ProductShipment records in the report with a 'dispatch date' on or between the entered dates.

Also for 5c-5f, it should be possible to use the combo boxes on the Report form to choose a particular customer/supplier/storage location/transport company to display the relevant report for (e.g. if a particular supplier is selected in the SupplierFilter combo box, the report described in 5c will only list details for the selected supplier, not for all suppliers).

All reports should also list the report creation date (i.e. today's date), probably in the footer (I think you tend to do this anyway with your reports). They should also all have the company letterhead in the header and footer (see the report 'Letterhead' for an example).

5a) UNALLOCATED STOCK REPORT

Storage Location Name (Group by, ascending order)

AWB (Group by, ascending order)

Supplier Name

BoxNo (group by, ascending order)

Customer (no need to display this field - see 'Like' matching below)

Count of BoxNo (for each shipment)

Count of BoxNo (for each storage location)

Count of BoxNo (for all storage locations)

This report should only show records related to a customer with a customer name that contains the text 'UNALLOCATED' (Like "*UNALLOCATED*").

5b) FRESCO FREEZER REPORT. Same as 5a, but only showing records with a customer name that contains the text 'FRESCO FREEZER' (Like "*FRESCO FREEZER*). Once again, please use 'Like' to match the customer, not the CustomerID as, if my uncle accidentally deletes the customer, it will be difficult to make sure the new customer has the same CustomerID.

5c) SUPPLIER (PACKING LIST). This report may not be necessary as the information in it is used as an input for the database, not an output.

Supplier Name (Group by, ascending order)

AWB (group by, ascending order)

Arrival date

Product description (group by, ascending order)

BoxNo (ascending order)

Net Weight

Pieces per box

Customer name

Dispatch date

Count of BoxNo (for each product)

Sum of Net Weight (for each product)

Count of BoxNo (for each product)

Sum of Net Weight (for each shipment)

Sum of Total price or Price per kilo (for each shipment)

Count of BoxNo (for each supplier)

Sum of Net Weight (for each supplier)

Sum of Total price or Price per kilo (for each supplier)

Count of BoxNo (for all suppliers)

Sum of Net Weight (for all suppliers)

Sum of Total price or Price per kilo (for all suppliers)

5d) CUSTOMER (SALES ORDER) REPORT

Customer Name (Group by, ascending order)

Product description (group by, ascending order)

AWB (group by, ascending order)

BoxNo (ascending order)

Net weight

Dispatch date

Total price or Price per Kilo (i.e. whichever price was used according to 'price type' x rate, so that all can be shown in GBP)

Count of BoxNo (for each product)

Sum of Net Weight (for each product)

Sum of Total price or Price per kilo (for each product

Count of BoxNo (for each customer)

Sum of Net Weight (for each customer)

Sum of Total price or Price per kilo (for each customer)

Count of BoxNo (for all customers)

Sum of Net Weight (for all customers)

Sum of Total price or Price per kilo (for all customers)

5e) STORAGE/CLEARING AGENT (RELEASE FORM) REPORT

Stograge Location Name (group by, ascending order)

Customer name (group by, ascending order)

AWB (group by, ascending order)

Transport company

BoxNo (ascending order)

Net weight

Dispatch date

Count of BoxNo (for each customer)

Sum of Net Weight (for each customer)

Count of BoxNo (for each storage location)

Sum of Net Weight (for each storage location)

Count of BoxNo (for all storage locations)

Sum of Net Weight (for all storage locations)

5f) TRANSPORT COMPANY (COLLECTION/DELIVERY) REPORT

Transport Company (group by, ascending order)

Customer name (group by, ascending order)

AWB (group by, ascending order)

Storage Location

BoxNo (ascending order)

Net weight

Dispatch date

Count of BoxNo (for each customer

Sum of Net Weight (for each customer

Count of BoxNo (for each transport company)

Sum of Net Weight (for each transport company)

Count of BoxNo (for all transport companies)

Sum of Net Weight (for all transport companies)

I hope I've included all the required fields for these reports, but we might need to add one or two more later if I've been forgetful!

6) Finally, I realise that you are a much better VBA coder than me. So could I ask that you check all the code used in the database? In particular, I have left out many error-handling subroutines (so at the moment, this might lead to entire forms breaking). Could you add some error-handling to the existing code please, where I've not done it yet? Don't worry about places where there IS currently error-handling, but an error description box is not displayed (I removed some of the error descriptions, particular when the user cancels printing, as the error descriptions were not helpful).

Let me know how much you want to charge for the above requirements and if there are any of them that you don't want to do.

Thanks a lot,

Stephann

Taidot: Visual Basic

Näytä lisää: access vba save record code, vba code create xml files, within subject design, where to get screen printing done, where find a coder, where do i find a coder, where can i find format, when you mess up at work, what to do when you mess up at work, what are work values, we transport, we need sales agent, vba work, vba with, vba load, sales agent requirements, re label, realise design, rate visual basic, property sales agent, project work example, project messages, project details example, product supplier database, product label design price list

Tietoa työnantajasta:
( 7 arvostelua ) London, United Kingdom

Projektin tunnus: #466133

2 freelanceria on tarjonnut keskimäärin 350 $ tähän työhön

BSoftTech

Hi, I've expertise in Visual Basic. Reviewed your requirement. I'm interested in provding this solution. Please let me know if we can talk about the project details Regards, John

450 $ USD 10 päivässä
(1 arvostelu)
2.4
edussooriya

Please check my PM

250 $ USD 10 päivässä
(0 arvostelua)
0.0