120 likes | 128 Views
Case Study with Interesting features including: Continuous Form allowing User to Move Rows Around (Like A Spreadsheet but Better). Tony Bayliss FCCA Senator System Consultants Limited. www.senatorsys.co.uk tbayliss@senatorsys.co.uk. My First Little Database. Dataease For DOS – Still going!.
E N D
Case Study with Interesting features including:Continuous Form allowing User to Move Rows Around (Like A Spreadsheet but Better) Tony Bayliss FCCA Senator System Consultants Limited www.senatorsys.co.uk tbayliss@senatorsys.co.uk
Previous Access User Group Talks • Linking Access to accounts systems, particularly Sage 50 with Developer VB toolkit • Controlling user access using VBA behind menu forms. • School Admission System allowing online applications into a MySQL Database with PHP web form and Access front-end. • Downloads on the above at www.senatorsys.co.uk plus: • Name and address upper and lower case fix. VBA utilities allowing for most unusual capitalisations, – Mc, O’, etc and UK post codes. • Convert amounts in numbers to words up to 1000 pounds. • Tony Bayliss, Senator System Consultants Limited tbayliss@senatorsys.co.uk
Overview of Whole System:Client powder coats aluminium sections & sheetsPrevious system Unix-based needed replacing.My system in use since 2013 comprises:1. Sage 50 for accounts 2. Custom Access/SQL applications: a. Job/Sales/Works Order Processing b. Despatch and Invoicing c. Production and Transport Scheduling (added 2018)Over a million order lines processed so far Tony Bayliss FCCA Senator System Consultants Limited www.senatorsys.co.uk tbayliss@senatorsys.co.uk
Interesting Features • Some customer parts trigger several internal sub jobs • Some customer parts have several “additional operations” • So, one line on an order may result in extra chargeable invoice lines • How do you show the additionals – subform within a subform? • I decided on a button to view/edit additionals in a dialog box but showing number of additionals and summary on main screen.
Production Manager’s Daily Chore. 5 of These Spreadsheets. Jobs to Move, Delete, etc
Production Scheduling • About 1000 jobs outstanding at any time. • Need scheduling into different production lines • Some jobs involve operations on more than one line • Previously done by manually entering job summaries into spreadsheets (one per line) and moving them around using insert rows, cut and paste. • Job details already in Access/SQL database so big time saving if we can replicate the spreadsheets in Access. • Also schedules of jobs for one customer are sent to them by email – again done on spreadsheets by copying and pasting from full schedules.
Solution • Asked advice on Access User Group Listserver first (big response), then…. • New table to store Production Schedule Items. 65,000 records created in one year. (SQL view to extract only items not complete) • Button within job entry to create and view/edit items in production schedule table (may be several schedule operations on different lines) • When items are despatched from job system, they are complete and corresponding schedule items are completed automatically so disappear from the schedules. • Screens to create, view/edit all production schedule items for each line. • Two fields needed in schedule table. Priority number and Select (Yes/No). • Change sequence using priority numbers and move several at a time using select field. Several items can have same priority numbers and there will be gaps in priority numbers (happens automatically when jobs despatched and completed). • So much quicker than insert, cut and paste on a spreadsheet.
Schedule edit screen • One screen for each production line • Continuous form with each scheduled production item • Button to print the schedule. Red highlight facility • Each line has a priority number and a select box • Can just change priority no. of one or more lines then click Re-Sort • To move an item or items: • a. Select with tick box (or button to select all with same priority) • b. Move button asks what is new priority number for selected item(s) • c. It asks: if existing item(s) with that number, move those down? (i.e. add 1 to priority number for that and following numbers) • Renumber all to remove gaps (e.g. where items have been completed/despatched)
In summary:A simple template for a continuous form where rows can be moved around singly or in blocks – almost like a spreadsheet but better and quicker. Tony Bayliss FCCA Senator System Consultants Limited www.senatorsys.co.uk tbayliss@senatorsys.co.uk