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-30T21:51:27+00:00

Well that one about the Report Width has to do with Paper Size (and margins)

I see the other two they are the result of your Groupings

OfficeName Header needs to be oName

ProviderName Header needs to be Expr1
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-30T21:27:27+00:00

Thank you. I thought this would reset the search, but used your code instead.

Private Sub BtnReset_Click()

    Me.cboProviderName = ""

    Me.cboVisitType = ""

    Me.cboSpecialty = ""

    Me.cboInsurance = ""

    Me.txtSubSpecialty = ""

End Sub

I had corrected the insurance status yesterday by using the little triangle to figure it out - very handy to have. I've opened the report in Design mode, and there doesn't seem to be any errors other than the page width.

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

0 comments No comments
Anonymous

2018-10-30T20:52:43+00:00

At least one thing resolved.

As for the Form not clearing, you will probably need a Clear button, something like...

Dim ctl As Control

For Each ctl In Me.Section("Detail").Controls

Select Case ctl.ControlType

Case acTextBox, acComboBox

ctl.Value = Null

Case acCheckBox

ctl.Value = False

End Select

Next

To do additional checks to the Report... Open the report in Design Mode and look for some little triangles, they will point to what controls on the Report do not match the Recordsource.
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-30T20:45:44+00:00

Hi Gina,

Thank you for your suggestions. I opened the report right from the Navigation Pane and the pop ups still occurred. I have tried everything to remove the two pop ups. I guess I have to delete that report and do a new one from scratch as I have spent over eight hours trying to fix the pop ups.

I'm also noticing that my search isn't resetting. So when the user returns to the search from the form, the search won't work. The user has to close everything then open it again for the search to work.

The first four are Combo List Boxes, and the last item is a text box. So I've updated the code - thank you so much.

Now my code is:

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

On Error Resume Next

        Dim strWhere As String

        Dim strSQL As String

        Dim lngLen As Long

    'Number field example. Do not add the extra quotes.

    If Not IsNull(Me.cboProviderName) Then

        strWhere = strWhere & "([pProviderID] = " & Me.cboProviderName & ") AND "

    End If

    If Not IsNull(Me.cboVisitType) Then

        strWhere = strWhere & "([vVisitID] = " & Me.cboVisitType & ") AND "

    End If

    If Not IsNull(Me.cboInsurance) Then

        strWhere = strWhere & "([piInsuranceID] = " & Me.cboInsurance & ") AND "

    End If

    If Not IsNull(Me.cboSpecialty) Then

        strWhere = strWhere & "([sSpecialtyID] = " & Me.cboSpecialty & ") AND "

    End If

    If Not IsNull(Me.txtSubSpecialty) Then

        strWhere = strWhere & "([pSubSpecialty] = """ & Me.txtSubSpecialty & """) AND "

    End If

    lngLen = Len(strWhere) - 5

        If lngLen <= 0 Then

            strSQL = strSQL

            DoCmd.OpenReport "rptResults", acViewReport

            DoCmd.Maximize

        Else

            strWhere = Left$(strWhere, lngLen)

            strSQL = strSQL & " WHERE " & strWhere

            DoCmd.OpenReport "rptResults", acViewReport, , strWhere

            DoCmd.Maximize

            Reports![rptResults].Filter = strWhere

            Reports![rptResults].FilterOn = True

        End If

End Sub

Private Sub cboInsurance_Click()

    Me.cboInsurance.Requery

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

Private Sub cboVisitType_Click()

    Me.cboVisitType.Requery

End Sub

And my row sources are:

SELECT tblProvider.pProviderID, [pLast] & " " & [pFirst] AS Provider FROM tblProvider ORDER BY tblProvider.pLast;

SELECT tblVisit.vVisitID, tblVisit.vType FROM tblOfficeProvider INNER JOIN tblVisit ON tblOfficeProvider.opOfficeProviderID = tblVisit.vOfficeProviderID WHERE (((tblOfficeProvider.opProviderID)=[Forms]![frmSearch]![cboProviderName])) ORDER BY tblVisit.vType;

SELECT tlkpInsurances.iInsuranceID, tlkpInsurances.iName FROM tlkpInsurances INNER JOIN tblProviderInsurance ON tlkpInsurances.iInsuranceID = tblProviderInsurance.piInsuranceID WHERE (((tblProviderInsurance.piProviderID)=[Forms]![frmSearch]![cboProviderName])) ORDER BY tlkpInsurances.iName;

SELECT tlkpSpecialty.sSpecialtyID, tlkpSpecialty.sSpecialty FROM tlkpSpecialty ORDER BY sSpecialty;
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-30T19:53:59+00:00

Waiting to hear back about the report...

In the interim, if the last two additions are text them you need to change the delimiters, see below...

If Not IsNull(Me.cboSpecialty) Then

strWhere = strWhere & "([sSpecialtyID] = """ & Me.cboSpecialty & """) AND "

End If

If Not IsNull(Me.txtSubSpecialty) Then

strWhere = strWhere & "([pSubSpecialty] = """ & Me.txtSubSpecialty & """) AND "

End If
Was this answer helpful?

0 comments No comments