Solved

One form multiple companies how to enter data accordingly

Posted on 2011-09-22
16
331 Views
Last Modified: 2012-05-12
Im trying to setup a db for us using Access 2010. Here is what I got.

1) I have multiple companies, and one form that list / add companies.
2) Once you select your company you will start adding information for that particular company, or run reports on that particular company.

Should I create a table for each company that is created? or is there a way to use one form for all?

I know this is not complicated but I rarely use access.
0
Comment
Question by:cnl83
  • 8
  • 8
16 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36582904
Craete One table:
tblCompanies
cID
cName
cAddress
cPhone
...etc


Then create a form from this table using the form wizard.

You then use this form for your CRUDS (Create, Read, Update, Delete, Search)

Now, since you say "run reports on that particular company.", it is not clear if more is involved here besides companies (Orders, Employees, ...etc)

So you will have to be a bit more specif as to what your ultimate question is here.

JeffCoachman
0
 

Author Comment

by:cnl83
ID: 36583009
Ok im tracking jobs completed for different customers.

I want to create a drop down box that list all the companies. After I select the company, then I add a new record.

So I have one table that contains all of my companies, and one table that contains all of the jobs completed for all of these different companies. How do I link the two?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36583436
(I want to create a drop down box that list all the companies. After I select the company, then I add a new record.>

Add a new record for what?
Do you mean create a new Job for a selected customer?

The two tables would seem to have to be linked on the CompanyID (cID):
tblCompanies
cID (Primary Key)
cName
cAddress
cPhone
...etc

tblJobs
jID (Primary Key)
j_cID (Foreign Key)
jName
jCompetedDate


You would have to create a from with a combobox to select the company.
Then use code like this to open the Jobs form with a new Job for that company.

DoCmd.OpenForm "frmJobs", , , , acFormAdd, , Me.cID

Then on the Open event of theJobs form do something like this:

If me.OpenArgs<>"" then
    me.cid=me.openargs
end if


JeffCoachman

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36583692
Actually the code on the open event could be like this:
    If Me.OpenArgs <> "" Then
        Me.j_cID.SetFocus
        Me.j_cID = Me.OpenArgs
    End If

In any event, there are many ways to do this depending on your overall design considerations.

So you may very well have to change this as your design evolves.
But it does as you requested, and should get you off to a good start.
Sample attached

;-)

JeffCoachman

Access-EEQ-27322773NewOrderForEx.mdb
0
 

Author Comment

by:cnl83
ID: 36584314
I tried to follow your example that you uploaded, but Im obviously missing something. Maybe you can tell me where im going wrong. Patient-Disclosures.accdb
0
 

Author Comment

by:cnl83
ID: 36584705
I guess im missing something to pass the data. I have redone your app 5 times on mine...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36584837
...and the password is???
0
 

Author Comment

by:cnl83
ID: 36584857
Demo
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36585036
...
You mean
   demo
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36585105
You have a *Lot* of work to do on this database before it will be ready for release...

It was very confusing for me to go through it and make the needed changes.

But first, note that you have asked a question that requires you to have a fairly good understanding of how an MS Access database is designed.

Your database contained more than a few issues that would make it difficult to follow

So If I were you I would first stop and post a question about the design and linking of the tables *First*.
Before ever worrying about code, forms, reports, or creating new records for selected companies...

Some of these issue include:
1. You are storing the company name in the PatientDiscloseure form, when this data should be "looked up" (as in my sample)
2. You named both of your Primary keys "ID"
The primary keys need descriptive names:
PatientID, StudentID, CompanyID, StaplerID, BasketBallID, ChalupaID, MustardID, MiddleEarthID, ToothPasteID, ...etc
Make sense....

3. You need to adopt a standard naming convention:
http://www.xoc.net/standards/rvbanc.asp
http://en.wikipedia.org/wiki/Leszynski_naming_convention

4. Certain words like "Pages" are key words in VBA, and should not be used as object/field names.

5. Where is your Patient table?...
Again, the PatientDiscloseure table is storing the Patient name, when it should be storing the PatientID.

6. It is not clear what the other fields represent, so they may need to be adjusted as well.
...etc

7. How much do you know about Database normalization and table design?
These principles must be employed first, ...again, way before you ever think about forms...
You should have your entire database normalized and related first, before you ever make your first form, to make sure that it is structurally sound.

8. You have a "New" button the the disclosure form.
This may be confusing to users because you can also create a new record from the New Patient form
(which is also a bad name because that form is use to "select" a Company" and create a new "patient disclosure" record, ...not create new Patients)

This all being said, you can see the attached revised sample DB.

So here again, I recommend that you stop and first post a question about the design and relationships of your tables first.

;-)

JeffCoachman
Patient-Disclosures-2-.accdb
0
 

Author Comment

by:cnl83
ID: 36586810
I cannot open that database. It says its in an inconsistant state...
0
 

Author Comment

by:cnl83
ID: 36586895
I finally got your database open, but it does not work.... When I launch new patient's form, the data should populate over the patientdisclosures form.

I understand all the suggestions you have made, and I had planned on going back to clean everything up. I really just put this together quickly so that I can accomplish this one task before I went any further.

0
 

Author Comment

by:cnl83
ID: 36586928
What excatly did you change?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36587420
<What excatly did you change?>
Did you read my big post above...?
Some of the things were listed there.
There were quite a few changes I had to make, I can't remember all of them.

This is why I stated that your question here requires you know how to examine a database completely, in order to detect the changes...

What I would have done was to try this alone, on a simple new database, before trying to insert it into your existing application.
Just to test the basic concept first.

But first start by deleting the code reference to: A3DOfc.ocx
I don't know what this is, and because I don't have it installed on my computer, it is generating errors and not letting me compile the code.

Do things like encrypting the DB and adding a password for *after* the database is designed and tested fully.
In the mean time, your Network administrator can restrict access to the file by permissions.

<When I launch new patient's form, the data should populate over the patientdisclosures form.>
I don't know what " the data should populate" means...?
Your requirement was to create a new Child record (Patient Disclosure) for the selected Parent record (Company).
This is what the sample does.

New Sample and Screencast attached

You will have to study it carefully and completely.

But in the mean time, post that question regarding the overall table design and relationships.

;-)

JeffCoachman
boag2000-502879.flv
Patient-Disclosures-2-1.accdb
0
 

Author Comment

by:cnl83
ID: 36587537
I did read your post and I understand what you are saying. Im a php programmer, and have not messed with access much. The forms in this application are just mockup form, and nothing of any signficance. I really appreciate your help. Now I'll try to go back and reverse engineer it.

Thanks for your help.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36587597
OK, no problem.

This isi why I just wanted to present a "Framework" of what needed to be done.

I am glad i was able to help.

;-)

Jeff
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now