Can forms be used for multi-user database (1 Viewer)

[johnboytweed]

I was on here yesterday to inquire about my db design, and I was told that forms cannot be used for a multi-user database unless they are read-only. I was hoping someone could confirm or deny that statement.
My attempt was to create a form that was based upon all the tables I had created.
I need something to bring all my tables information together so that data can be added to it all from one location. What other options are out there if this is not possible?
I've reviewed the Northwinds database and that example shows multiple users and it has an "new order" form. I want to do something similar but instead I would need an enrollment form.

 

[Beetle]

That is absolutely not true. Forms are an essential part of any well designed Access application, whether single or multi-user. They are the main method by which data entry is handled. When you have information from multiple tables that you need to display in one place, then you would typically use a main form / sub form interface, perhaps with a tab control if you have several sub forms to deal with.

 

[johnboytweed]

I'm so frustrated. I just wanted to try and setup a form that would tie all my tables together. Now I deleted that form and of course all the work that was involved is gone as well. This was an all day discussion yesterday.
So just to be clear, if I setup an "enrollment form" that form can be used by multiple users at the same time to enter information about that enrollment? This form needs to save all the data to different tables (seems pretty standard). I was told yesterday that a form like that in a multi-user setting could only be a "read-only" form to view data and not a data entry form because all the tables tied to that form would be locked by one person so that it could not be used by others at the same time?
I need the form to be used by two to three people at most throughout any given day.

 

[gemma-the-husky]

how many tables do you have?

you cannot necessarily have a single form with all your tables. often you would not want to.

that is most probably the issue

 

[Beetle]

I was told yesterday that a form like that in a multi-user setting could only be a "read-only" form to view data and not a data entry form because all the tables tied to that form would be locked by one person so that it could not be used by others at the same time?

Again, not true, unless someone intentionally changed the record level locking for the database. By default, Access is set up to be multi-user friendly, but if someone went in and changed the record level lock type then you could have a scenario like that, but that is not the default behavior.

As Dave hinted at, sometimes people make the mistake of trying to combine all of their tables together in one query, then use this query as the record source of a single form. When done this way, the query itself often becomes read only due to the number and/or nature of the joins, and therefore any form based on this query would be read only as well.

The proper way to set up a form to handle information from multiple tables is to use a form/sub form like I mentioned in my previous post. There are several examples of this in Northwind. One of them is the Order Details form. In this form you select a customer in the main form, then there is a tab control with three tabs, each with a different subform. Each sub form displays related customer information from a different table. The multiple tables are tied together within the form using the Master/Child link properties of the Subform Controls (a Subform Control is the window that holds the sub form).

 

[johnboytweed]

I have about 13 tables in all. I haven't setup any queries either. All I did was setup all the tables and then made then set the relationships. I was just trying to create an entry form so that all the information would go into all the different tables correctly.
This started because I couldn't figure out how to create a combo box that was bound to the form.
My problem was that I had a form that looked fine, but when I tried to enter data to it it wouldn't go anywhere. I figured out that some of my combo boxes were unbound and when I tried to edit the source I was unable to select the tables in the subform to make it bound.
After speaking yesterday I deleted my form and now I have had to recreate it again. I just don't know what to do because if I can't create a form to enter data, I don't know what I am suppose to do to bring all the different tables together in order to enter new records.
If attaching a sample would help, I will be happy to do it. I'm new to Access and I'm just trying to learn the right way to do it.

 

[Beetle]

You can attach a sample file here if you want and I will have a look. Just be sure to remove any sensitive data (if there is any) in your application first.

 

[johnboytweed]

What I ended up doing yesterday was deleting my form and creating a subform only that included all my tables (as per the advice i received). I haven't designed the layout or anything at this point because in the middle of doing that i was told that that wouldn't help me have a form that could be edited by multiple people either. Now my db is just sitting here because I don't know if I'm going in the right direction or not.

 

[johnboytweed]

Sorry, I sent that last message before I saw your reply. Let me clean it up and attach in a few minutes.

 

[Beetle]

Upload a copy here and I'll see what you've got going on. If you don't really have any usable forms at this point that's fine. I can create a few generic example forms that you can use just as a general guide for creating your own forms, then I'll post the db back for you to look at.

 

[johnboytweed]

Okay,
Sorry I didn't see your last response. I tried to just "clean up" the "subform" template I started on yesterday. Please forgive the table structure if you look in the relationships. I've messed with this database for an embarrassingly long time.
The break down of the tables was essentially:
Active/Inactive (combo box)
Status(combo box)
Division to Hospital to Clinic Corp to DBA (doing business as)
and I had those all in combo boxes on form cascading upon the prior selection
Then I had the physician entry form
and then I had all these "vendors tables" tied to the physician table, along with the auditchecklist table and billing table
I created forms for all of these yesterday and then created this primitive form.
Some of the tables arent being used right now if you see in the relationships.
I wasn't sure about the structure so I've gone back and forth on how the layout should be.
Sorry to ramble. Any help would seriously be appreciated.

Thank you

 

[johnboytweed]

Also, I noticed that all the forms work independently of each other. Is there a way to bind all the forms to one control so that I can hide all the navigation icons on each subform so that it looks clean and just click on a "New Record" button so that all the forms will refresh to a blank form and save what was just entered?

 

[jdraw]

I have about 13 tables in all. I haven't setup any queries either. All I did was setup all the tables and then made then set the relationships. I was just trying to create an entry form so that all the information would go into all the different tables correctly.

There are processes to go through to identify the subjects involved in a database. In addition there are some basic rules for ensuring the tables do not have recurring/repeating data.
I suggest you review the following to help with relational database design, normalization and entity relationship diagramming.

http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452 (relational principles with normalization links within)
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

In my view you need to design the database before physically building tables and forms. I'm not saying you can't do it differently, I am saying this way works.

Good luck with your project.

 

[Beetle]

OK, having looked at your db I can see several problems with your table structure and that is probably also the main reason why you are struggling with your form design. If the table structure is faulty, then every other aspect of the application design (queries, forms, reports) will be more difficult and ultimately faulty as well. I can't detail everything that may be wrong with the structure because I don't know what your data model should look like to begin with (in other words, I don't have a true understanding of the entities and attributes that would be necessary for the proper design of your application), or what your business rules are. However, there are some things that stand out that I can point to.

1) You have a table named AuditChecklistTable with several Yes/No fields for indicating (I'm guessing) which items were verified during some type of audit. A multitude of Yes/No fields is not a good way to store this type of data. It's difficult to query against and, even worse, if you need to add a new type of attribute some time in the future it requires you to add a new field to your table and subsequently go back and re-design every object (query, form, report) that is based on that table. A table with a bunch of Yes/No fields is usually an indication that there is a many-to-many relationship that should be managed with a junction table.

2) There are several examples of redundant data storage as well;

• In the Physician Table you store the medical license# and expiration date. If a Physician can only have one license then this is appropriate. However, you also have a MLTable that stores both of these fields as well along with a PhysicianID field, which would indicate that a Physician could have more than one license, in which case you would not have those fields in the Physician table. Plus, you also have these fields showing up again in both the Documentaion table and the Enrollments table.
  
• This goes for your DBA table as well. In here you have fields for DBA Name, Address, City and Zip, which is normal. Then, looking at the Enrollments table, you have all those same fields repeated.

Attributes like this should be stored in one place only. If you need to relate that data to another table, then you store only the Primary Key value in the related table, not the attributes (fields) themselves.

The best advice I could give you here is to work on getting your table structure properly normalized before proceeding with query, form or report design. The links that JDraw provided would be a good place to start. I'm not saying this will be easy. This is not exactly a simple data model if you're new to all of this. It will take some time and effort to understand how to design it properly, but if you really want to have a reliable, well functioning application, that's what you'll need to do. You can certainly get help from this forum while you're in the process of normalizing your table structure, but trying to force some type of form design to work with your current table structure will ultimately lead to nothing but problems and headaches for you.

 

[johnboytweed]

So how does one become "fluent" in database design? Read lots of forums?

Beetle
Thank you for taking the time to look at this. I seriously appreciate it. I feel like it's a complicated structure as well, so its really made me stay up many late nights trying to figure out away to get it all to function. Regarding the addresses though I don't think that there is a redundant pattern. The address entries are based upon possible variances in: "ship to" address (items ship to alt address from business address)
"bill to" address (where bill is sent and that can vary from the b.address)
"sold to" address - address of business

I basically tried to create something and then had several requests to add this information to the design, and well it hasn't gone well.
I will work on the normalization of my tables. This (like most things) has taken much longer than I ever imagined.

jdraw
Thank you for the links and suggestions. I guess the only reason I tried to create a form was because I wasn't sure if it was all working correctly. I thought that would be the best way to see if it worked, but I guess that wasn't the right way either. So...How can you check to see if it works without creating forms?

 

[jdraw]

I recommend working through the entity relationship diagramming material with your own data.
Make a list of the business facts. Create some test data, including some bad records. Work through the test data and your evolving relationships; reconcile every anomaly/error/uncertainty that arises. Is it an issue with the relationship, a missing attribute,error in test data, bad business fact??? Work through and adjust as needed. If you have to make some forms, and queries to ensure you can retrieve/update data then do so, but only in the sense of testing/validating the model.
In my view the model is the key. Get it right and things are facilitated; get it wrong, or short change the design validation and you can be in for a long list of issues that you'll try to work around.
Do what you have to to get the model correct, then
move to actual physical building the database. It's very much like having a plan and a design for building a house or a jet airplane.

Good luck.

 

[gemma-the-husky]

johnboy

developing access skills needs a lot of patience, and time.

are you competent in excel? there are some similariities but also quite a lot of important differences.

access can be taught, but really getting familiar with access is not so much a gradual process, more one of overcoming a series of "quantum jumps". learning to use access properly is a level of magnitude higher than excel. you can get results from excel without touching macros. you just cannot do anything useful in a database without vba code.

among others
you need to understand tables, and normalisation
you need to understand select queries, and action queries
you need to understand how forms work
you need to understand the access event model
you need to understand vba programming
and some of it is techniques that the books don;t teach you, but are obvious once someone shows you how!

if you are self taught then really you have to start with a basic primer, and go through it carefully. It would be very difficult if not impossible to jump straight into a complex buisiness project, as you seem to be doing.

if you or your company can afford it, then a different solution would be to get personal tuition and assistance in specific areas as you develop your skills.

good luck

 

[johnboytweed]

Well I was actually thrown into this project because I have strictly worked in Excel for the last six years or so. I guess they thought because I could drive a car, I could fly a plane. I've been reading everything I can find as I come up against it. I have a better understanding of it, but I can't find anyone that works with Access at all.
I realize that these are two different animals so I'm just trying to figure out how I can do this. My department asks me about every week or so "where are we on this" and that gets frustrating. I want to make this work really badly, but it's just one thing after another.
As far as getting "personal tuition and assistance" as you wrote it earlier, how would you look into something like that?

 

[RandyStalding]

The last time I developed a multi-user Access application I started from scratch. In this case scratch means interviewing group members and collecting existing forms and spreadsheets that each individual needed to to their job. The goal of these interviews is twofold: 1) get the users to tell you things or show you things they do that are not documented or are unknown to their supervisors, 2) get the users to buy into the applications design. The latter point is very important to them and tends to keep them from presenting you with new requirements after you are well into the design effort.

You may discover that four out of eight users really need to enter or change a small collection of data items. After evaluating your collection of forms, report, etc., you may want to create dummy forms using a product like Microsoft's Visio. You can then have a group meeting to present the forms and make changes as suggested. Field data types, lengths, unique ids and common terms will likely come out of this meeting. This review is another opportunity to get users to buy into the application design. After everyone is happy with the forms (this may take several iterations), you can then begin to create tables, relationships, queries and reports.

There is one document that I omitted that you may wish to use as you are collecting requirements information, it is referred to as " homogeneous sentences". It amounts to a one or two page paper that explains everything you know about what each member of the groups does with the information necessary to do their jobs. I keep all of the documents used in developing the application in a binder. People will question why I did something and I find it useful to go back to the dummy forms and show then the notes and their comments.