Data entry and list of existing records on same form in Access 2007
I have a simple form that adds a new record into a table called Transactions, which has such fields as Name, Transaction Number etc. and a button whose event adds the new record.
All well and good but I want the form to be a bit more clever than that:
1. Some of the fields should have fixed / limited values, for example Transaction Type should be limited to DD, CHQ+ and several other fields- so users can't add other (incorrect) data into the field. Obviously a Select / dropdown is ideal for this but I can't see how to convert the input (which the form adds by default) to a Select box.
2. Name should work as follows: If ContactType is set to Customer, it checks that the same Name is in the Customers table. If ContactType is set to Supplier, it checks that the same Name is in the Suppliers table- and so on.
3. Transaction Numbers should be unique- they're not involved in any calculations so I did as advised by the documentation and imported them as text fields- but ideally the new Transaction Number (for the new record) should be (The highest existing one) plus 1, and this should autofill into the field when data is entered, so that the user doesn't have to look up existing Transaction Numbers and work out what the next one should be.
4. Lastly, the form also needs to display a "window" to the table of existing Transactions, with a vertical scrollbar, allowing users to see all the current transactions (e.g to make sure the one they're entering hasn't already been entered) and also sort them by whichever field they want for ease of reference.
Anyone know how these items can each be achieved? Sure, I know they're not show stoppers, but I want the users to have the easiest time possible when entering or editing data -> less support time!
