One form multiple companies how to enter data accordingly

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

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?

(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

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

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

I guess im missing something to pass the data. I have redone your app 5 times on mine...

...and the password is???

Demo

...
You mean
   demo

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

I cannot open that database. It says its in an inconsistant state...

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.

What excatly did you change?

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.

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