trying to create a scheduling doctor appointments in Access 2013

The forms open; and the reports all open.

The forms work sporadically in the search. Sometimes the search and reports come up correctly, other times the reports do not show any data. I am extremely frustrated. Sub-specialties still works great as I had that working correctly before the weekend. I've tried to copy whatever I did in sub-specialties into specialties three times this morning, to no avail.

Also the main search form is still not working right even though I removed Specialties and Sub-Specialties. Even if the search gives results (it comes up blank most of the time), it repeats the results:

I am also getting a box asking if I want to save changes, when I never made any changes, only when I run a search in the main search form:

My main search code:

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;

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

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

Private Sub cboInsurance_Click()

    Me.cboInsurance.Requery

End Sub

Here is what is working great - Sub-specialties:

My Sub-Specialties code which is working correctly:

SELECT tblOffice.oName, tblOffice.oUrgent, tblOffice.oNotes, tblOffice.oRefill, tblOffice.oBack, tblOffice.oFront, tblOffice.oEmail, tblOffice.oAddress, tblOffice.oCity, tblOffice.oState, tblOffice.oZip, [pFirst] & " " & [pLast] AS Provider, tlkpSpecialty.sSpecialty, tblProvider.pSubSpecialty, tblProvider.pNotes, tblVisit.vType, tblVisit.vArrival, tblVisit.vNotes, tblVisit.vLength, tlkpInsurances.iName AS Insurance, tlkpInsStatus.isStatus, tlkpSpecialty.sSpecialtyID FROM tlkpInsStatus INNER JOIN (tlkpSpecialty INNER JOIN (tlkpInsurances INNER JOIN (((tblProvider INNER JOIN tblProviderInsurance ON tblProvider.[pProviderID] = tblProviderInsurance.[piProviderID]) INNER JOIN (tblOffice INNER JOIN tblOfficeProvider ON tblOffice.[oOfficeID] = tblOfficeProvider.[opOfficeID]) ON tblProvider.[pProviderID] = tblOfficeProvider.[opProviderID]) INNER JOIN tblVisit ON tblOfficeProvider.[opOfficeProviderID] = tblVisit.[vOfficeProviderID]) ON tlkpInsurances.iInsuranceID = tblProviderInsurance.piInsuranceID) ON tlkpSpecialty.sSpecialtyID = tblProvider.pSpecialty) ON tlkpInsStatus.isStatusID = tblProviderInsurance.piInsStatusID ORDER BY tblOffice.oName, tblProvider.pLast, tblVisit.vType, tlkpInsurances.iName;

Private Sub BtnSearchSubSpec_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.cboSubSpecialty) Then

        strWhere = strWhere & "([ssSubSpecialtyID] = " & Me.cboSubSpecialty & ") AND "

    End If

    lngLen = Len(strWhere) - 5

        If lngLen <= 0 Then

            strSQL = strSQL

            DoCmd.OpenReport "rptResultsSubSpecialty", acViewReport

            DoCmd.Maximize

        Else

            strWhere = Left$(strWhere, lngLen)

            strSQL = strSQL & " WHERE " & strWhere

            DoCmd.OpenReport "rptResultsSubSpecialty", acViewReport, , strWhere

            DoCmd.Maximize

            Reports![rptResultsSubSpecialty].Filter = strWhere

            Reports![rptResultsSubSpecialty].FilterOn = True

        End If

End Sub

Private Sub cboSubSpecialty_Click()

    Me.cboSubSpecialty.Requery

End Sub

My Specialties code which is not working correctly:

SELECT tblOffice.oName, tblOffice.oUrgent, tblOffice.oNotes, tblOffice.oRefill, tblOffice.oBack, tblOffice.oFront, tblOffice.oEmail, tblOffice.oAddress, tblOffice.oCity, tblOffice.oState, tblOffice.oZip, [tblProvider].[pFirst] & " " & [pLast] AS Expr1, tblProvider.pSpecialty, tlkpSubSpecialty.ssName, tblProvider.pNotes, tblVisit.vType, tblVisit.vArrival, tblVisit.vNotes, tblVisit.vLength, tlkpInsurances.iName, tlkpSpecialty.sSpecialty, tblProvider.pProviderID, tblVisit.vVisitID, tblProviderInsurance.piInsuranceID, tlkpInsStatus.isStatus, tblProviderSubSpecialty.pssSubspecialtyID, tlkpSubSpecialty.ssName, tlkpSubSpecialty.ssSubSpecialtyID FROM tlkpSubSpecialty INNER JOIN ((tlkpInsStatus INNER JOIN (tlkpSpecialty INNER JOIN ((tblProvider INNER JOIN ((tblOffice INNER JOIN tblOfficeProvider ON tblOffice.[oOfficeID] = tblOfficeProvider.[opOfficeID]) INNER JOIN tblVisit ON tblOfficeProvider.[opOfficeProviderID] = tblVisit.[vOfficeProviderID]) ON tblProvider.[pProviderID] = tblOfficeProvider.[opProviderID]) INNER JOIN (tlkpInsurances INNER JOIN tblProviderInsurance ON tlkpInsurances.[iInsuranceID] = tblProviderInsurance.[piInsuranceID]) ON tblProvider.[pProviderID] = tblProviderInsurance.[piProviderID]) ON tlkpSpecialty.sSpecialtyID = tblProvider.pSpecialty) ON tlkpInsStatus.isStatusID = tblProviderInsurance.piInsStatusID) INNER JOIN tblProviderSubSpecialty ON tblProvider.pProviderID = tblProviderSubSpecialty.pssProviderID) ON tlkpSubSpecialty.ssSubSpecialtyID = tblProviderSubSpecialty.pssSubspecialtyID;

Option Compare Database

Option Explicit

Private Sub BtnResetSpecialty_Click()

 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

End Sub

Private Sub BtnSearchSpecialty_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.cboSpecialty) Then

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

    End If

    lngLen = Len(strWhere) - 5

        If lngLen <= 0 Then

            strSQL = strSQL

            DoCmd.OpenReport "rptResultsSpecialty", acViewReport

            DoCmd.Maximize

        Else

            strWhere = Left$(strWhere, lngLen)

            strSQL = strSQL & " WHERE " & strWhere

            DoCmd.OpenReport "rptResultsSpecialty", acViewReport, , strWhere

            DoCmd.Maximize

            Reports![rptResultsSpecialty].Filter = strWhere

            Reports![rptResultsSpecialty].FilterOn = True

        End If

End Sub

Thank you for your time and help.

Make the Forms work on their own, don't worry about anything else.  That way you can get the most important things workings!  Good luck!

Thank you again so much for your time and help. I got a reprieve until noon tomorrow as they really want this working. So I will work on trying to make the subforms available on their own and the subreports.

hi,RO!

I am afraid you had better step by step.

1,building query without parameters.

please show the sample datas what you had got as source tables and the query result based on these tables. 

if you got the correct answer then move to next step. if not, be free to discuss here.

2.add some parameters to querys you have set up.

3.the last step is to build form or report.

At first just to show information a little, just test table with few fields and add field one by one.

first thing first is to divide a big problem to several small questions,I think.

Returned.