How To Tables and Forms

[Jonah74]

Hi

I'm a fairly experienced user of spreadsheets but think I now need a database to help record a charities voluntary car services ever growing list of clients and their appointments. I have the data already on a spreadsheet but is now getting rather unwieldy to handle.

I have read up on what Access can do and with the help of many tutorials and videos I have managed to create the tables, forms etc that I think I need but can't work out how to get the main form and / or subforms to display the information so users can enter data and print it.



I will try to describe what I think I need but I will be guided by the experts on here to tell me if I'm not doing something right.

First the form should display the data about the client. (The source data being the client table, ClientsT), primary key set to ClientID. The form should also display where the client is going i.e. doctor's, dentist's, hospitals (This is destination table DestinationT), a one to many table as one client can go to multiple destinations. To tie this all together there should be a journey reference with collect time, day, date and return day, date and time and other key bits of info. This I think is the key table, (JourneyT), which has both the ClientID and the DestinationID as foreign keys.

My question is, how do I get Access to display all this where the charities booking clerk can select a client either from a combo box or list, if they are existing, or enter them into the database using the form if they are a new client? Then do the same for the clients destination which then produces a journey reference number using the JourneyID key? I've attached both the database and a PDF of an existing drivers’ sheet to show the kind of report I'm after to give to the volunteer drivers every day. I realise it’s quite a lot to for me to take in and I might be biting of more than I can chew but I do think Access should be able to help me do this. I just need a few simple pointers in layman's terms on how to do it. Any help, advice will be gratefully received.

Chris

[June7]

Use form/subform arrangement. Main form bound to Clients, subform bound to Journey. Combobox in subform to select Destination.

Review http://office.microsoft.com/en-us/ac...010098674.aspx

This setup can be used to display existing clients and related journey records as well as add new data. Can use the intrinsic Access navigation and search and filter tools or customize with code.

Here is one way to search for client on the main form: http://datapigtechnologies.com/flash...tomfilter.html
Put unbound combobox for selecting client filter criteria in the form header section.

Remember, the more 'user-friendly', the more code. http://office.microsoft.com/en-us/ac...010341717.aspx

[Jonah74]

Thanks for the reply. I'll have a go at setting this up shortly but just one question before I do. If I set the destinations up as a combo box how do I get it to show the full address in separate boxes as the combo box will only show the first line and is it possible to add new destinations to the combo or do these need to be added manually? Sorry I'm a complete newbie at this and this is a great challenge for me.

[June7]

Several ways to show the related address info. One is to use a multi-column combobox http://datapigtechnologies.com/flash...combobox3.html

The RowSource of the combobox could show the complete address concatenated into one field:
SELECT Destination.ID, Address & ", " & Street & ", " & [Town/City] & ", " & PostCode AS FullAddress FROM Destination ORDER BY Address;

Or if you don't want to concatenate:
SELECT Destination.ID, Address, Street,[Town/City], PostCode FROM Destination ORDER BY Address;

Then ControlSource of textboxes can refer to the columns of the combobox. Index of columns begins with 0 so Address is index 1:
=[comboboxname].[Columns](1)

Explore the NotInList event of combobox: http://support.microsoft.com/kb/197526


BTW, advise not to use spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names. If used, must enclose in []. Better would be TownCity or Town_City.