Advice for simple data entry form

[Fuergrissa]

DB can be downloaded here: https://dl.dropboxusercontent.com/u/...sportLog.accdb (1.3mb was too large for forum attatchment)



The DB is meant to track transports to/from our facility. There are tables for Officers (who do the transport), clients (who are transported), and the transport event itself. I'm trying to design a form that emphasizes ease of use since there will be a many different and irregular users.

Ive built a single form based on the transport table with subforms for the officer information and the client information. It seems like the default function of a sub form is to show records from the secondary table that are related to the record on the main form, but this is not what I want. The intent is to be able to enter data relevent to the transport event easily from one place, reguardless of what table that data is stored in. I'd like the form to operate as if the data were all stored on one large table, so that the user can start entering an officer name in a combo box, and if that officer is already in the table then great, record that officer as the transporting officer, but if he's not in there then you just keep entering the information. The important thing is that the process for the user doesn't change based on whether the officer/client/whatever already exists in the DB or not.

The last thing I want is for them to have to do is check if there is a pre-existing record for the officer here, then go somewhere else and create it, then remember the unique ID # for that officer and go back to the form to record it as the transporting officer, then do all that over again for the client, then finally finish the transport form with other details like times, custody status etc.

If anyone has any advice on how to design this, or knows of an example form that might be a good reference please let me know.

[rpeare]

I can't download it, it's just stalling out.

Try zipping up your file (do a compact and repair first) then uploading it to this site. zipping it should make it small enough to upload.

[June7]

It's an accdb file anyway, rpeare.

Options:

1. A form bound to Transport, select Officer and Client in comboboxes. If Officer or Client doesn't exist, use NotInList event to open form for entry of new record, then return to the Transport form. New Officer or Client now available for selection.

2. A form bound to Clients, subform bound to Transport with a combobox to select Officer. Again, use NotInList event to handle case if Client or Officer does not exist.

3. A form bound to Officers, subform bound to Transport with a combobox to select Client. Again, use NotInList event to handle case if Client or Officer does not exist.

Review https://www.accessforums.net/forms/q...54/index2.html

You have only one field for officer name (OfcName). This is usually a bad idea because people will not be consistent in entering name (Surname/First or First/Surname). Will they include courtesy titles (Mr, Ms, Mrs, Dr), suffixes (Jr, Sr, PhD, MD)? Name parts should be stored in separate fields.

[rpeare]

yeah but I have access to 2007 wed-friday not mon/tues but I can remote desktop to it if need be.

[Fuergrissa]

1. A form bound to Transport, select Officer and Client in comboboxes. If Officer or Client doesn't exist, use NotInList event to open form for entry of new record, then return to the Transport form. New Officer or Client now available for selection.

I'm working on this method with the subform opening in a popup as described in the thread you posted, but I have a quick question:

With a Combo box, how can I set the BoundColumn to Name (so that it will autofill based on the Name), but leave the ControlSource as ID (so that it stores the number rather than the name)?

EDIT* NVM I found it