Create Record Database Help

[charlesh]

HI,




I’m trying to create an access database that will function like a file I created in Excel.
The object is when the user selects an “MP” from the dropdown this action will populate the related data that is
Shade with “Green”.
When the “Provider” is selected this action will also look for the “Patients” that this provider has.
When you select the dropdown for the “Patient Name” you will be able to see all of the Patients for this provider.
When you select the “Patients Name” this action will look at the “Patients Record” and the populate all of the data filed that is highlighted in “Red”.
You will note that I’ll be expanding this as you will notice the command buttons on the Form.
I would appreciate any help with this.


Charles

[offie]

You'll want to use subforms.

Ex: For the ProviderDetail section
-make a query that has all the information you want to get in that one
-Make a form, which gets it's data from that query
-Add a combo box to the form (MP), selecting the "Find a record on my form... etc" option, and selecting MP
-for all the others, Add Existing Fields
-Test it out.

Once you have all your forms, you can make a main form and drag them into it and arrange them how you want.

Same thing for the red, though I did notice that 2 of the red would be in the green area... you'll just have to change the source of those 2, or just move them (easier)

On a form you can specify if the data is view only or you can edit it, I'm assuming you want to edit it. For the form you can make an after update event to save, so you don't have to press a button.

If you want to go next and previous, you can do it per form. I'm not sure how your data links up but maybe you could make one query and base it off that? (I don't work with combo boxes much so I'm not sure how the search feature would work with multiple boxes but you can always test it out.

[charlesh]

offie

Thanks for the reply. I do some research on your suggestion.
As for the combo box. In excel I have a code that when the provider is selected will use a filter set to look at the selected provider.
This selection in return will show only the Patients for that provider.
In my code I also have for the "Patient" name a bit of code that will add the names to the combobox.
Now when you select the Patient the code I have in place will look at the first row of data for that patient from the filtered list.
In my code I have a Command button when pressed will go to the Next "Visible" record for the patient.
This is what I would like to achieve in Access.
And, too as my profile shows I'm a "Newbie"

[June7]

Sounds like you want dependent (cascading) combo/list boxes. Review http://datapigtechnologies.com/flash...combobox2.html

For adding new patients, need to add a new record in Patients table. You might find the combobox NotInList event helpful. http://www.blueclaw-db.com/access_no...ed_example.htm

Also, code to move to next record. http://stackoverflow.com/questions/6...cord-until-eof

[ssanfu]

What you are looking for is "Cascading combo boxes".

You could start here:
http://www.fontstuff.com/access/acctut10.htm
http://www.fmsinc.com/blog/post/Micr...ist-Boxes.aspx

Or google "cascading combo boxes access 2010"

[charlesh]

june7 and ssanfu,

Thanks for the information..
I'll see what I can learn from them.
Will keep you informed.

Charles

[charlesh]

HI,

After looking at the suggestions and trying. I came to the conclusion I need more help.
I watch the "DataPig?" example. But I could not get it to work in my case. So I must be missing something.
If some one will be kind and help this "Old" fool I would be happy.
My case is.
1 Select the Provider
2. From the provider selection show only the Patients
in the combo for the Patient.


Thanks
Charles

[June7]

You should not be repeating provider information in patients table (Provider NPI, Department, Specialty). A basic principle of relational database is to not duplicate data. Once you fix the tables, options:

1. A bound form can enter data into only one table. If you want to do data entry into both tables, set up a form/subform arrangement. Main form bound to providers and subform bound to patients. http://office.microsoft.com/en-us/ac...010098674.aspx. Then combobox in patients subform can be made dependent on MP. Right now you have no code that causes the patients combobox to requery after selecting MP. Will probably need code in event behind the subform to requery patients combobox.

2. Make form RecordSource a query joining the two tables, join type 'Include all records from patients ...', bind textboxes to fields from provider table and set them as Locked Yes, TabStop No

With either option, can use this technique to filter records http://datapigtechnologies.com/flash...tomfilter.html. Other techniques don't use parameterized query and will involve a little more VBA.

All of the data controls on your form are unbound. If you want data to display then controls must be bound to fields of the form's RecordSource. Otherwise, need a LOT of code to populate controls.

You are using a small font size, suggest increase to 10. Hard to read text against that dark red.

[charlesh]

june7,

Thanks for the update will see what I can do with this info.
As for the "Background" Red. I only used it for reference not intending to use it with the completed file.

Charles

[charlesh]

HI,

I've managed to populate the "Patients" name in the combo for "Patient". However it repeats the "Patients" name.
Ie: I have a Patient name "Mike1" several times.
I would like to see it and any other duplicate names only once in the "Patient Name" combo.
As mentioned I can do this in excel by using a filter, but I've look at several suggestion on the web, but to no avail.

Charles

[June7]

Do you have a table of Patients where each record is unique for a patient? Use that table as source for patient combobox RowSource.

[charlesh]

June7,

Yes, each patient has its own unique record. However the "Patients Record" will hold the same Patients Name (and Record Id) several times.
Right now i have this as the Source code.

SELECT [Patients Record].[Patient Name] FROM [Patients Record] WHERE ((([Patients Record].MP)=[Forms]![frmPatient].[MP]));

MP is the "Provider"

With this it "List" all of the Patients names for the provider. Regardless if its a dup.

Charles

[June7]

A table of patient info (name, birthdate, gender, phone) would have ONLY one record for each patient.

[charlesh]

Thanks,

Will this help in the way I want my file to work?
When I select the "Provider" and from the "Patients Record" (not the Patient info tbl when created)the "Patients Name" combo will display the name of each Patient only one time?

Charles

[June7]

Combobox RowSource that pulls from Patients table will have each patient listed once, as long as the patient is unique in that table. However, it won't know if someone entered same patient again with a different spelling of their name.

Suggest RowSource sql like:

SELECT PatientID, LastName, & ", " & FirstName AS PID FROM Patients;