trying to create a scheduling doctor appointments in Access 2013

Anonymous

My boss asked me to do a new Access database from scratch. I’ve never done an Access database on my own before, and would appreciate any advice you guys have. This is to help employees schedule appointments for 46 medical doctors in 14 different medical offices.

As for my relationships, I hope I’ve set up everything correctly:

As you can see, I’ve set up two junction tables, but am not quite sure how to set up a search query with junction tables. I’ve looked in my two Access 2013 books, and I’ve also looked online but am not having much luck. For example, with the table Junction_Provider_Insurance, I’m not sure how to search the Junction table to pull up the doctor and if the doctor is contracted, not contracted, needs authorization or pending for the insurance. So if a doctor’s name and an insurance name such as Aetna is searched, I want the result to show that the doctor is or is not contracted with Aetna in ReportResults.

As for the search, I originally set up a wildcard search for all the search boxes. My original intention was that the user could type in anything they wanted in one or two or three of the boxes (such as doctor’s name, visit type and insurance) and do a search where the results come up. However this is not working at all and time is of the essence as I’ve already missed my deadline to turn in this a week ago.

Due to missing the deadline, I decided to change the wildcard text search to a combo box for the Doctor’s Name, Medical Office Name, Visit Type, Specialty and Insurance in the hopes the search will finally work. I’m thinking the best way to handle this is to remove the medical office, and then just have the doctor’s name, then when that comes up, to cascade into a new combo box for the visit type (new patient, follow up, pre-op, post-op, etc.) Or is there a better way to do this? I still need the results to show in the report too.

Thank you so much for your time and help. I really appreciate it very much.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

136 answers

Anonymous

2018-10-26T22:13:39+00:00

I keep trying so many things. I must have ten different versions of the file now and none work. I was supposed to be done yesterday with this project and to start my next new project. Management is not happy with me and I am under a ton of pressure and stress to get this done ASAP.

Here is my latest attempt:

Option Compare Database

Option Explicit

Private Sub BtnReset_Click()

    Me.cboProviderName = ""

    Me.cboVisitType = ""

    Me.cboSpecialty = ""

    Me.cboInsurance = ""

    Me.txtSubSpecialty = ""

End Sub

Private Sub BtnSearch_Click()

End Sub

Private Sub cboProviderName_AfterUpdate()

' set Visit Type and Insurance combo boxes to Null

' and requery controls to show Visit Type in selected Provider

    Me.cboVisitType = Null

    Me.cboVisitType.Requery

    Me.cboInsurance = Null

    Me.cboInsurance.Requery

' requery form to show Provider choice

    Me.Requery

End Sub

Private Sub cboVisitType_AfterUpdate()

' set Insurance combo box to Null

' and requery control to show Insurance in Visit Type

    Me.cboInsurance = Null

    Me.cboInsurance.Requery

' requery form to show Visit in selected Visit Type

    Me.Requery

End Sub

Private Sub cboInsurance_AfterUpdate()

' requery form to show Insurance name in selected Insurance

    Me.Requery

End Sub
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-27T00:04:39+00:00

Well, the only thing I can suggest is you take out any data (leave some sample data) and zip it up and send...

https://www.access-diva.com/contact.php

It's the weekend and I have a bit of time to spare. Oh, it would help if you also send what you want the Form to do so I don't have to search thru this thread.
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-27T20:15:30+00:00

Ok- I've put up a stand alone scheduling applet that looks like what you need. I haven't yet put in the filter for the physicians (s.a. internist, neurologist, etc) but I could add that on tonight. It sets up appointments based on physician selected and date.

Here's how it looks like - the "appointments" for today are for physician "Jose Booker M.D." If you select a new date and/or new physician and the list is empty, then click on the "Time Slot Fill" buttons to activate that date with 10, 15, 20 or 30 minute time slots. That's one of the most complicated parts of this scheduler, and as in the case of a lot of my EMR, it came directly from these Microsoft Forums some many years ago. The app is done in Access 2000 format so as to remain compatible with all Access versions for the past 18 years. The calendar IS NOT an ocx control, so it'll work in MS Access 2010 and newer versions of Access. The clickbuttons, although they have, say, a printer icon and "Print Yellow Sheets" statement together will work in all Access versions as they are really small bmp pictures.

Download it from here: (URL removed- see final version below)

Al
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-28T18:31:29+00:00

Database received and returned.
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-28T18:50:47+00:00
Final version is up:
1. all bugs removed
2. has 3 filters- date, physician, specialty
3. the associations between the subforms to mainform became a nightmare through vba ([subform].RecordSource) so I simply linked the subforms to the main form.
4. the specialty combobox also has a button lateral to it to remove this particular filter.
5. I've deleted many of the fields that were not needed in the few tables used.
6. I signed it.
URL for download (preview will not show, but download button is active): URL

Together with Gina's appointment applet you'll now have a host of ideas to work with- you're final version should really look good. Good luck!
Was this answer helpful?

0 comments No comments