question: Tab control update to filter records

[Jasontm01]

Ok, as suggested in a previous post I managed to combine my data to one table. Now I have a problem getting my tabs to work right.

What I would like to have is a Tab form with 10 tabs. One tab per location. Im trying to get it so when a user clicks a tab for a location it filters the records accordingly.

Here is what I have done, but it isnt working right:

Private Sub Form_Load()
Me!LocationTabs = 0
Call Location_Change
End Sub

Private Sub LocationTabs_Change()
Me.RecordSource = "SELECT * FROM tblCustomerData " _
& " WHERE Location = Me!LocationTabs.Value " _
& " ORDER BY CompanyName ASC;"
End Sub

LocationTabs is the name of the tab control itself
CompanyName is a text box
tblCustomerData is where the form gets its data
Location is the name of the field that contains the location name


What is happening is when I open the form or Click a tab it pops up a box asking me what the "Me!LocationTabs.Value" is. If I type a valid location in that box and hit ok the tab does update with just those records.

I may be doing something wrong but I though it was supposed to grab the "Me!LocationTabs.Value" from the Name or Caption fields of the tab pages.

Can someone point me in the right direction for this? Thanks

 

[KeithWilliams]

Hi,

I think you should be concatenating the value of Me!LocationTabs.value with the rest of the SQL statement, as in this example from the A2K help:

RecordSource Property Example

Sub cmboCompanyName_AfterUpdate()
    Dim strNewRecord As String
    strNewRecord = "SELECT * FROM Customers " _
        & " WHERE CustomerID = '" _
        & Me!cmboCompanyName.Value & "'"
    Me.RecordSource = strNewRecord
End Sub

Hope that helps,
Keith.

 

[dcx693]

Try this syntax correction:

Private Sub LocationTabs_Change()
Me.RecordSource = "SELECT * FROM tblCustomerData " _
& " WHERE Location = " & Me.LocationTabs & _
& " ORDER BY CompanyName ASC;"
End Sub

You need to have a form parameter that you are referencing outside of the quotation marks for it to evaluate it. If you don't it will pass the literal form reference and not the value of the form reference.

 

[Jasontm01]

Ok, here is what I tried based on your suggestion. Keep in mind, I am not using a combo box for this, I am using a tab control. It should be able to do what I want based on some other things I have read but I cannot get it to trigger the filter. When I did this code nothing happens at all. The form loads with no records.

Private Sub LocationTabs_Change()
Dim strRecordList As String
strRecordList = "SELECT * FROM tblCustomerData " _
& " WHERE Location = '" _
& Me!LocationTabs.Value & "'"
Me.RecordSource = strRecordList

End Sub

I think the problem is it doesnt know what "Me!LocationTabs.Value" should be. Like its not reading it from the name/caption field in the tab controls.

Am I going about this the wrong way perhaps? I would like to keep the tab controls for this filtering form but cant seem to figure out how to make it work.

 

[Jasontm01]

Try this syntax correction:

Private Sub LocationTabs_Change()
Me.RecordSource = "SELECT * FROM tblCustomerData " _
& " WHERE Location = " & Me.LocationTabs & _
& " ORDER BY CompanyName ASC;"
End Sub

You need to have a form parameter that you are referencing outside of the quotation marks for it to evaluate it. If you don't it will pass the literal form reference and not the value of the form reference.

I just tried this one also and it is poping up a box asking me for "Me.LocationTabs"
If I type in a valid location the tab shows the correct data, however I do not want it to popup like that. I would prefer it read the location name directly from the tab page name/caption.

 

[dcx693]

Is LocationTabs the name of the field in the query feeding into your form? As well as the name of the control on your form? If so, you should consider renaming the control to txtLocationTabs and using that reference in your code.

 

[Jasontm01]

LocationTabs is the name of the tab control itself. I have a field in the table called Location. I have tabs on the tab control named after each of the 10 locations.

I have another database that I filter in a similar way with alphabetical tabs and it works fine but I cant figure out why this one doesnt.

Side note: if I use the exact syntax you put in your post I get an error about expected end of expression right at the & symbol on the last line. If I move the " after the WHERE Location = It doesnt give me the error but still doesnt work right.



Here is an example of the code from the other database:

Private Sub Form_Load()
Me!TabCtl0 = 0
Call TabCtl0_Change
End Sub

Private Sub TabCtl0_Change()
Me.RecordSource = "SELECT * FROM Customers " _
& " WHERE CompanyName LIKE '" & Chr(97 + Me!TabCtl0.Value) & "*' " _
& " ORDER BY CompanyName ASC;"
End Sub

In that database the tabs are labeled a-z and caption a-z. This one works perfectly when I click a tab it updates and filters the list based on the alphabet tab I clicked.

 

[dcx693]

Sorry about the syntax. This should be correct:

Private Sub LocationTabs_Change()
  Me.RecordSource = "SELECT * FROM tblCustomerData"  & _
      " WHERE Location = " & Me.LocationTabs & _
      " ORDER BY CompanyName ASC;"
End Sub