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-26T17:26:51+00:00

Hi Gina,

This is everything I have. I think I'm just really confused. I need the Provider's Name first (cboProviderName), then Appointment Type (cboVisitType), then the Insurance (cboInsurance). I just don't seem to be getting the hang of it.

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_Change()

    If Me.cboProviderName = "" Then

        Me.cboProviderName = 1

    End If

    strSQL = "SELECT [tblVisit].[vVisitID], [tblVisit].[vType], IIf([fObsolete]=True,'x',''), fVisitID, fkKeywords, fIdentifier, fvType " & _

                "FROM tblVisit ORDER BY [vType];" & _

                   "WHERE fVisitID = " & Me.cboVisitType.Value & " " & _

                        "ORDER BY vType ASC , fVisitID"

                Me.cboVisitType.RowSource = strSQL

                Me.cboVisitType = Me.cboVisitType.ItemData(0)

End Sub

Private Sub cboVisitType_AfterUpdate()

    Me.cboProviderName = Null

    Me.cboProviderName.Requery

End Sub
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-25T23:42:13+00:00

Which example are you following? If Example A then where is the code because nothing above is in the On_Change event of a combo box. So, in the On_Change event of the Provider Combo Box you would apply the filter based on the Visit Type? But where is Visit Type, in the Record Source or did you mean Appointment Type?

If you are instead using this...

https://www.access-diva.com/f14d.html

Then still the code is missing whether you are using Example A or Example B.
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-25T22:37:24+00:00

I've tried everything following Gina's instructions. Nothing is working and I'm really frustrated.

First cbo is ProviderName

Second cbo is VisitType

I want only appointments (visit type) specific to that provider to show up in the second box. I don't want ALL visits. A neurologist does not do a pediatric appointment.

This is what I have:

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()

    Me.cboVisitType.Requery

    Me.cboVisitType = Null

End Sub

Private Sub cboVisitType_AfterUpdate()

    Me.cboProviderName = Null

    Me.cboProviderName.Requery

End Sub

Thank you so much for all your time and help.
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-24T22:27:26+00:00

Perhaps this will help...

https://www.access-diva.com/f14.html
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-24T21:57:48+00:00

I'm struggling with my search form. I would like for the user to select the provider's name, then the appointment box should populate based on which provider was selected.

This is what I have so far:

Any advice or suggestions are greatly appreciated. Thank you so much for your time and help.

Robbi
Was this answer helpful?

0 comments No comments