Editing records

[shariq1989]

So I am currently working on my first database ever. I have come a long way in terms of learning Access (3 months of development) and now the whole database is built. Currently, I am involved in taking little bits of the database and improving them. I think of them as upgrades. One of the main things I was told early on was "data in tables will be edited through forms". I need some help improving my forms because the way they are set up right now, the user may have to click "previous" hundreds of times in order to find the record they need. An example is below:

I created a form called "frmPurchasing" which feeds a table called "tblPurchaseRecords". Once the order is received, the user has to click through records until he finds the pertinent one and clicks "Received", which deletes the record. What are better ways of designing this form? One of the ways I was considering but will need help with would be to have all the records show up in a list box in the form with two buttons (Delete and New) under the list box. The user can either double click any old order from the list box, in order to pop it up in the form I have currently so that it may be edited. New would pop up the form I currently have but in a New Record position. Delete would obviously delete the record selected in the list box. How does this sound?

[Rod]

Hi Shariq,

I'm not quite sure how the database relationship diagram is relevant; is this a mistake? Anyway let's get onto your question.

It will come as no surprise that there are many ways to satisfy your requirement; some will be more appropriate to your situation and some less so. First let's discuss your 'list box' solution.

To display orders in a list box or combo box does not alleviate the need to scroll through all the orders on file until you find the pertinent one. The box will have to be quite wide to display all the information required to determine uniqueness and therefore 'feel' clumsy. Further I hope you aren't thinking of placing the 'delete' and 'new' buttons on each line of the list; it won't work!

As you are quite new to Access I won't prolong the discussion of every possible technique and solution but point you directly at the 'built-in' form design in Access. Experiment with what Access calls a split form.



Select tblPurchaseRecords in the left-hand pane. Click create on the ribbon tabs and then click split form. Access will create a form; the upper half of which is a single record suitable for editing in the usual way; the lower half will be a list of your purchase records, one per line. You may edit either half in design view.

So why is this better?

1. Access takes care of the synchronisation. Click on a line in the lower half and Access positions the upper half at that record.
2. The full Access feature of filtering and sorting is available for the list in the lower half thereby allowing the user to more quickly find any single record.
3. To delete a record, highlight it in the lower list and press [Delete].
4. To add a new record go to the end of the list or use the navigation buttons at the foot of the form.

If this does not fulfil your requirements then get back to us explaining why.