Access Form Data Source

Good afternoon experts,
I am creating an Access database to hold training dates for the hospital I work at. I put in the ability to filter the records by clinic. The problem I am having is when changing from one clinic to another the first overall record is showing, instead of the first record from that clinic. I am not sure if I need to change the forms record source but I am not entirely sure how to do that programically either. I figured I could do something along the lines of;

Select Case Me.cboSectionChange
Case 1 To 38
Me.Form.RecordSource = "qryFilterRowSourceAll"
End Select


QryFilterRowSourceAll:   SELECT blah FROM blah WHERE dbMaster.Section=Forms!formMaster!cboSectionChange;

Any suggestions on how to get this working correctly or a more efficient means of getting this task accomplished would be appreciated.
V/R,
Shannon
LVL 1
ShannonCallahanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Do not do this on the Change event.
The change event fires for each individual character typed into the combobox.

Keep it simple, ...the standard way to do this is with the Filter Property

Sub cboSectionChange_AfterUpdate()
    me.filter="ClinicName=" & "'" & me.cboSectionChange & "'"
    me.FilterOn=True
End sub.


JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Can I ask where you got that filter code from?

You did not post any details on your combobox or the fields involved, nor the datatypes of these fields

But in general, your combobox will hold two fields,
1. The Primary key
2. The "Friendly value" (what the user understands.)

So in your case you would have a Clinic table:
tblClinics
cID (Primary Key, AutoNumber)
cName

Your combobox will have two columns, and a rowsource of:
SELECT cID,cName
FROM tblClinics

Then the combobox would "reference" cID, but display cName
make sense,


Then your code to filter the form would be like so:
Sub cboSectionChange_AfterUpdate()
    me.filter="cID=" &  me.cboSectionChange
    me.FilterOn=True
End sub

The code I posted previously would work on the clinic name only.

Make sense?

JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
sample may make this clearer
Database147.mdb
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ShannonCallahanAuthor Commented:
Thank you for your responses.

The "qryFilterRowSourceAll" is the query I am using to display the names of everyone listed under the clinic ID from the combobox selection. Meaning select a clinic via the combobox and it list first and last name of all people from that clinic inside a listbox.

I don't think I did a very good job of explaining this the first time, so let me try again. I have a combobox that pulls it's selection from tblClinics. From there, you make a selection of the clinic you would like to view and it displays the names of all the personnel from that clinic inside a list box;

When you select a name from the list box, there are txtboxes that populate with their training dates. This all functions correctly but now the issue;

When you change the clinic I want the form to automatically display the first record from that clinic, instead of displaying the last record viewed. Sorry for the confusion. Any suggestions?
V/R,
Shannon
0
Jeffrey CoachmanMIS LiasonCommented:
A bit cleared but a few more questions:
1. You said:
<you make a selection of the clinic you would like to view and it displays the names of all the personnel from that clinic inside a list box;>
So you change the combobox clinic and it populates the listbox.

Then you say:
<change the clinic I want the form to automatically display the first record from that clinic,>

So to be clear do you want Listbox, or the Form to display the first record?

2. How is the form/listbox displaying that last record to begin with?...  Normally a listbox or form will display the records according to the sort order in the recordsource (for a form) or row source (for a listbox)

...so perhaps you just need to change the source sort order...?.

JeffCoachman
0
ShannonCallahanAuthor Commented:
1. Yes, depending on the combobox (cboSectionChange) selection, it runs different quiries that change the data source of the listbox (lbNames);

Function FilterDataSource()

Select Case Me.cboSectionChange
Case 1 To 38
Me.lbNames.RowSource = "qryFilterRowSourceAll"
Case 39
Me.lbNames.RowSource = "qryFilterRowSourceAdmin"
End Select

End Function

Open in new window


When the combobox selection changes, the Listbox populates the names but the FORM is not showing the first record in the listbox. The form is showing the last record viewed. The listbox is use to navigate to records on the form/select a name on the listbox and it shows the training dates for that person.
V/R,
Shannon
0
Jeffrey CoachmanMIS LiasonCommented:
Then try setting the form recordsource to the same query as the listbox rowsource:...

Select Case Me.cboSectionChange
Case 1 To 38
Me.lbNames.RowSource = "qryFilterRowSourceAll"
me.Recordsource="qryFilterRowSourceAll"
Case 39
Me.lbNames.RowSource = "qryFilterRowSourceAdmin"
me.Recordsource="qryFilterRowSourceAdmin"
End Select
me.requery


But this is getting a bit confusing now, because I cant see how all this is working together, or if this is the most efficient approach.

Can you post a simple sample database>?
1. Back up your database(s).
2. If the database is split, combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any extraneous records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the Compact/Repair utility.
12. Remove any Passwords, Security and/or login prompts.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
Post the explicit steps to see the issue.
And if applicable, also include a clear, graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
0
ShannonCallahanAuthor Commented:
Open up the form "formMaster". Onload, the first person that you see the record for is "John Doe". Above the list box with the names, you will see the combobox with the label "Change Clinic Selection". Click the combo box and change the clinic to "Food Services". When you do this the only record that will show in the list box is "Bill Smith", however, the record displayed on the form is still "Jonh Doe" until you select a name in the listbox.

What I am training to accomplish is; I want the form to automatically show the first record displayed in the listbox after its data source has been updated via the combobox's selection.

V/R,
Shannon
p.s. Please do not judge, this is my first experience with Access and I have been "programming" for less than a year.
MCHAC-Training-Database--Copy.accdb
0
ShannonCallahanAuthor Commented:
Also, if you have a solution, please just type it here. I only have access to a work computer and I will not be able to download the fixed database. Thank you for your help.
V/R,
Shannon
0
Jeffrey CoachmanMIS LiasonCommented:
<Please do not judge, this is my first experience with Access and I have been "programming" for less than a year. >

So if there are things in your design that go against basic database principles, you do not what me to mention them.?
;-)
...because this is the main issue here....

If left as-is, your design will present you with many issues in the future.
This goes all the way back to your tables design, field and object names and mix of Macros and VBA for automation.

I strongly suggest you post a question about the proper design of this database first, (explain what the purpose of the database is and what it will track, ...etc)
There is no real reason to start creating forms, macros, queries, ...ect, ... with custom functionality, if the design has "oddities" (For lack of a better word) that will need to be changed anyway.
Also many of your design aspects are actually forcing you to use code and macros, where as normally, none would be required.
;-)


Ok enough of that...
;-)

To be fair, what you are asking for here can be done, but it will require a lot of non-standard functionality
I cannot in good conscience post a solution based on your current design.
I fell it will be a disservice to you.


So the solution here is not something I can just "type", ...this question is more complex than that.
Here is a sample database that is done in a more standard form.
Try to see if a friend or associate can download it for you if they have MS Access installed
You can take from it what you like.

JeffCoachman
Database148.mdb
0
ShannonCallahanAuthor Commented:
Thank you for your honesty. Can you please email me the database so I can take a look at it here? Shannon.c.callahan.mil@mail.mil
Thank you again.
V/R,
Shannon
0
ShannonCallahanAuthor Commented:
Thank you for the help and advice. The program is for a limited time until the hospital pays someone to come and do it professionally. I just work in the Behavioral Health Clinic and was asked if I could make a temporary system for them. With this in mind, hopefully they will have it done by the time the future issues become current issues. I will be inquiring as to a "proper design" for something like this for educational reason but I need to finish this project so I can get back to my regular duties. There is only so much I can get from "Access for dummies" lol
Thanks again.
V/R,
Shannon
0
Jeffrey CoachmanMIS LiasonCommented:
Shannon,

No problem.

I know that sometime you just have to "Get It Done"

I just did not want to let you go forward with this design without knowing of its shortcomings...

;-)

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.