Help with search form

[elite311]

I have been reading for a few days now and trying different things and I'm almost getting this to work but I am stuck and hoping someone can help me.

I have 2 combo boxs on my form the first one searchs T_Markets for the field "Market" and the 2nd combo box searchs T_Service_Activities for the field "Activty"

What I want to do is be able to select a market in the first combo box and have a list box display a list of avilible techs in that market number then when I select an activity from the 2nd combo box I want to narrow down the list of techs that can do that activity.

I have it working right now for the first combo box using this code

Private Sub market_AfterUpdate()
With Me![TechName]
    If IsNull(Me!Market) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [TechName] " & _
                   "FROM T_Service_Tech_Info " & _
                   "WHERE [MarketNumber]=" & Me!Market
    End If
    End With
End Sub

But I can't figure out how to narrow the list box down by activity as well.

Then I want to be able to click on the name of the tech in the list box and populate text fields based on what fields I have in the T_Service_Tech_Info that are assocaited the the tech name but am unsure how to do that.

Any help would be greatly appreciated!

 

[Freshman]

I am sure there must be lots of methods but here is mine:

Create 2 "lookup" tables:
1. List1 with one field listing all the Markets
2. List2 with the first field listing all the Markets and the second field next to it, llisting all the Activities for that Market.

Eg:
List1
MarketA
MarketB
MarketC

List2
MarketA Food
MarketA Drink
MarketB Shirt
MarketC Sweets
MarketC Nuts

On your form (Form1):
Base Combo1 on List1's field
Base Combo2 on the following source:
SELECT List2.List2, List2.ListID FROM List2 WHERE (((List2.ListID)=Forms!Form1!Combo1));

So now, if you select a Market in Combo1, only the Activities for that Market will appear in Combo2

PS: Remember to add a Refresh command to the GotFocus event of Combo2

 

[boblarson]

PS: Remember to add a Refresh command to the GotFocus event of Combo2

Correction REQUERY (not refresh)

 

[elite311]

That doesn't really solve the issue but thanks for the thoughts! The problem I have on this is that I have a lot of tables that are linked to each other.

So

T_Markets is linked to T_Service_Tech_Info

and then

T_Service_Tech_Info is linked to T_Activites_Select

So I want Combo 1 to select Market then the list box to display all the techs with that market that work in that market.

Then when I select an activity from combo 2 I want the list box to change to only techs that work in that market that can do that activity.

So the activity is not depent on the market at all, it is it's own table.

I attached this code to combo 1

Private Sub market_AfterUpdate()
With Me![TechName]
    If IsNull(Me!Market) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [TechName] " & _
                   "FROM T_Service_Tech_Info " & _
                   "WHERE [MarketNumber]=" & Me!Market
    End If
    End With
End Sub

And that works fantastic! I pick a market and the list box shows techs that work in that market. I just can't figure out how make the second combo box for the activity narrow it down even more.

I also can't figure out how to make the text box that I have on my formn show his "Truck Type" from T_Service_Tech_Info when I click on his name in the list box. I keep getting errors

 

[boblarson]

We'll need to see your table structure to help further. You might want to post a copy of your database (minus any sensitive data) so we can look.

 

[elite311]

Here ya go!

The Form I am trying to make work is F_Service_Techs_Lookup.

You will see that by selecting the market it works but that it I can't seem to get the rest to work

 

[boblarson]

Okay, so how are you linking the techs with what activities they can do? I am not seeing where that happens.

 

[elite311]

In the T_Service_Actvitities table it lists the activitys and the techs are linked them them if you open the table and expand the record it shows what techs can to what activity

 

[elite311]

anyone have any ideas? I just can't figure this out

 

[elite311]

I've been working on this all night again, is there anyone that can help me on this? please

 

[Freshman]

See if this does what you wanted it to do.
I created a new Query1 and simply based the rowsourse of the TechNames on that Query which looks at the selection of the Activity combobox after Update.

Sorry about the file size but I can't do zip (only rar which is not accepted).

 

[elite311]

That worked perfect! thanks so much for the help.

I was stuck thinking it had to be done all though VBA and not a query. I was looking at the one you created and changed it a bit to read the market number from combo 1 aswell that way it only shows techs in that market that can do the activity.

Can I ask more more question?

Now that it works is there a way to make the activity combo box go blank everytime you select a new market? I only ask because I notice now if I have an activity selected and I change the market it doesn't re-filter the results until I re-select the activity.

 

[Freshman]

Adding the last two lines to your excisting event code should do it:

Private Sub market_AfterUpdate()
With Me![TechName]
If IsNull(Me!Market) Then
.RowSource = ""
Else
.RowSource = "SELECT [TechName] " & _
"FROM T_Service_Tech_Info " & _
"WHERE [MarketNumber]=" & Me!Market
End If
End With
Me.Activity = Null
Me.Requery
End Sub

 

[elite311]

Worked perfect!

Thanks again for the help, I really appreciate it!

 

[Freshman]

No problem glad I could help