First Access Database Looking for Help with

[scarroll825]

Hi all,



I working on my first access database in an effort to replace the daily task system my company uses (just emails, its very cumbersome). I'd have forms that populate tables with task details, then a report to display daily/weekly summaries.

The fields I would need for the tasks are:

• Staff name
• Date
• Task type
• Client name
• Client contact (for a phone call or visit)
• Client address (if it is an in-person visit)
• Description
• Duration
• Travel time (again for a visit only)
• Mileage (^)

I'm thinking I'll probably need a second table with client information (Name, Address, Contact(s)) with some kind of relationships setup to make the form filling easy.

I'd like for users to be able to fill out one form for each task that will create a record with that task info.

I'd like the client information to populate from their selection (if they enter a client name, the appropriate address and contact will also populate, but be editable).

One issue I am having is if I want a combobox that will auto-expand with the client names I need a separate table with all the client names, and have the selection populate to the client name field on the Task table. How can I have it so that if the user enters a client name that is not on the clients table, it will add a new record for that new client name?

I'm just hoping someone can get an idea for what I'm trying to do, and point me in the right direction.

[ranman256]

yes, you need :

tClient table
tStaff table
tTask table with all possible tasks to do

tStaffTasks, staff id with assigned tasks
--------------
StaffTaskID (autonum)
StaffID
Task
Date
etc..

you will have a Form for all tables and 1 for entry of StaffTasks
select staff person
select task
etc

[June7]

Adding records to a 'lookup' table 'on-the-fly' during data entry requires code. That's what the combobox NotInList event is for. http://www.fontstuff.com/access/acctut20.htm

See if these tutorials will help you through other issues http://www.rogersaccesslibrary.com/

[nick404]

First, don't use Name as a field title- Access reserves Name for its uses. Go with ClientName or something similar. If you have ClientName set as the client information table's Primary Key you can do a 1-to-many relationship between ClientName (from client info table) as the 1, to the client name field (in your first table) as the many. Reinforce integrity as well.

As far as this combobox thing you want.. I am not 100% sure what you want. You can set the combo record source as ClientName the client info table for starters and have spots for the rest of the information (address, contact, &c.) to populate. If it is a new client they are entering, in the combo boxes Data properties where is says "Limit to List" select no and they can enter in new data not already on the list.

[ssanfu]

Here are some links to choosing a PK field and Do's and Don'ts . The PK can be a text field, but it is not a good idea.

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

then:

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm


I always use an autonunber as my PK field.

[scarroll825]

Fist off thank you all for pointing me in the right direction. I was able to get my tables and forms setup how I need them.

Now my question is: How can I share this with team members so they can use the form to enter data? Really they only need access to fill out the form, and view a report. I'm not sure if there is an easy way to do this with sharepoint, but I have a feeling that will be the best bet.

[June7]

Is everyone on the same network server? Split the db. Backend with tables in a shared folder on server. Frontend with links to backend tables and has queries, forms, reports, code is copied to each user computer.

[nick404]

step by step on splitting a db
https://support.office.com/en-us/art...3-51b1d73498cc

[scarroll825]

That is the only issue, many of us work remotely, and don't have access to the network all the time.

[June7]

SharePoint is a common topic. Here is one recent thread https://www.accessforums.net/access/...ses-52957.html

[nick404]

do you have a portal for a shared drive?

[scarroll825]

We don't, is it possible to split the db, and have the tables on sharepoint then have users input through forms on the front end?

[nick404]

I do not know too much about SharePoint. You can split the database using it, though. Here is a link on splitting using sp.
https://support.office.com/en-za/art...2b54db252c#bm2