Re: [MS_AccessPros] Search Form Query not keeping the results

 

I don't think I have seen a reply to this question. If so, please excuse my reply.  You can try replacing your code with the following which adds the filtering to a single sub and calls it from the AfterUpdate of the combo boxes:

Option Compare Database

'--------------------------------------------------------------------------------------------------------------------

'The code below allows one to chose a value from a combo box and only have those values displayed in the query/table

'--------------------------------------------------------------------------------------------------------------------

Private Sub cboPlanType_AfterUpdate()

BuildSQL

End Sub

'The code below is set up for a combobox, one would chose a review name, and it will display those reviews

'--------------------------------------------------------------------------------------------------------------------

Private Sub cboReviewType_AfterUpdate()

    BuildSQL

End Sub

Private Sub BuildSQL()

    Dim strSQL as String

    Dim strWhere As String

    strWhere = "1 = 1 "

    If Me.CboPLANTYPE & "" <> "" Then  'has user made selection

        strWhere = strWhere & " AND [PLAN_TYPE_CODE] ='" & Me.CboPLANTYPE & "' "

    End If

    If Me.cboReviewType & "" <> "" Then 'has user made selection

        strWhere = strWhere & " AND [REVIEW_NAME] ='" & Me.cboReviewType & "' "

    End If

    strSQL = "Select * From Qry_RAU_PLAN_TOTALS where " & strWhere

    Me.Qry_RAU_PLAN_TOTALS_subform.Form.RecordSource = strSQL

    Me.Qry_RAU_PLAN_TOTALS_subform.Form.Requery

End Sub

Regards,

Duane Hookom

Switzerland

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of pbabadie@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, March 10, 2017 2:08 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Search Form Query not keeping the results

 

Hello

I have created a search form by following youtube tutorials

I need to have 3 (or more) search criteria. First I have a combo box where I can chose an option and those values are displayed in a table(split form). Then I created another combo box where I want to chose a value but keep the first combo box results. I followed the tutorial and I cant determine whats wrong, both of the combo box work, but If I chose an option from one, gives me the correct results, then chose the next combo box, it resets the query. Now I do have it set to Requery as in the code below but so did the youtube tutorial

If anyone can help out I would be much appreciative. 

Below is the code that I have:

Option Compare Database

'--------------------------------------------------------------------------------------------------------------------

'The code below allows one to chose a value from a combo box and only have those values displayed in the query/table

'--------------------------------------------------------------------------------------------------------------------

Private Sub cboPlanType_AfterUpdate()

Dim myPlanType As String

myPlanType = "Select * From Qry_RAU_PLAN_TOTALS where ([PLAN_TYPE_CODE] ='" & Me.CboPLANTYPE & "')"

Me.Qry_RAU_PLAN_TOTALS_subform.Form.RecordSource = myPlanType

Me.Qry_RAU_PLAN_TOTALS_subform.Form.Requery

End Sub

'The code below is set up for a combobox, one would chose a review name, and it will display those reviews

'--------------------------------------------------------------------------------------------------------------------

Private Sub cboReviewType_AfterUpdate()

Dim myReviewType As String

myReviewType = "Select * From Qry_RAU_PLAN_TOTALS where ([REVIEW_NAME] ='" & Me.cboReviewType & "')"

Me.Qry_RAU_PLAN_TOTALS_subform.Form.RecordSource = myReviewType

Me.Qry_RAU_PLAN_TOTALS_subform.Form.Requery

End Sub

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___