Changing Query Search Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a query defined and I would like to change the search criteria and
table name values programmatically. Any feedback would be appreciated.

Thanks, Eddie
 
Eddie's Bakery and Cafe' said:
Hi, I have a query defined and I would like to change the search
criteria and table name values programmatically. Any feedback would
be appreciated.

Thanks, Eddie
Click to expand...

If you're talking about a stored query, you can use the DAO QueryDef
object to change the query's SQL on the fly. However, if you're going
to be changing both source tables and criteria whenever you use it, I'm
not sure how much point there is in working with a stored query. Can
you give some background as to what you're trying to achieve?
 
Hi Dirk, thanks for taking the question. I have defined a single List Box
control and two command button controls. The command buttons are used for
NEXT and PREVIOUS commands. I would like to populate the List Box with new
table values (i.e., recipe names) based on search criteria each time the
Next/Previous buttons are selected. The search criteria will be based on a
value selected from the List Box. If the Previous button is selected, I need
to show the previous table from the List box. The idea is to be able to
navigate through tables based on a search key (primary/foreign keys) in the
forward and backwards directions. Below is an example of the type of data
that might be in the List Box and the sequence of how the search might be
implemented:

Search Start:
Menu TOC <--> Recipe TOC <--> Recipe Categories <--> Recipe Names

I defined the Text Box with multiple columns, two columns for the keys and
one for the name values. I only display the name values in the text box.
The hidden columns are used to retrieve the key value that will be used in
the search operation.

I hope this explains the problem and thanks for your help.

Regards, Eddie
 
Sorry, but there was a typo in my last message. I defined a List Box control
with multiple columns, not a Text Box.

Thanks, Eddie
 
Eddie's Bakery and Cafe' said:
Hi Dirk, thanks for taking the question. I have defined a single
List Box control and two command button controls. The command
buttons are used for NEXT and PREVIOUS commands. I would like to
populate the List Box with new table values (i.e., recipe names)
based on search criteria each time the Next/Previous buttons are
selected. The search criteria will be based on a value selected from
the List Box. If the Previous button is selected, I need to show the
previous table from the List box. The idea is to be able to navigate
through tables based on a search key (primary/foreign keys) in the
forward and backwards directions. Below is an example of the type of
data that might be in the List Box and the sequence of how the search
might be implemented:

Search Start:
Menu TOC <--> Recipe TOC <--> Recipe Categories <--> Recipe Names

I defined the Text Box with multiple columns, two columns for the
keys and one for the name values. I only display the name values in
the text box. The hidden columns are used to retrieve the key value
that will be used in the search operation.

I hope this explains the problem and thanks for your help.
Click to expand...

I'm not sure that I do understand what you're trying to do, but I have a
glimmering. You've got a hierarchical table structure, and you want the
list box to display all items from a particular level of the hierarchy
that match the one that was selected at the next higher level. So
you're using this list box and buttons to "drill down" to the
information you want. Is that right?

It would help to know the fields, keys, and relationships of the tables
involved. One thing you haven't mentioned, but that I think you need,
is a structure to keep track of where you are in the table hierarchy and
how you got there. That is, you need that if you want to pop back up
the stack to the previous level and still have the same item selected in
the list box as was there when you drilled down. If you don't care
about that, all you really need to know at any given moment is what
level you're at.
 
Hi Dirk, you are correct, I am using a hierarchical table. The tables are
defined as follow:

Table 1 - MenuCategory:
Field 1: MenuCategoryKey (Primary Key)
Field 2: MenuKey (Foriegn Key)
Field 3: MenuCategoryName (Text Value)

Table 2 - RecipeType:
Field 1: RecipeTypeKey (Primary Key)
Field 2: MenuCategoryKey (Foreign Key)
Field 3: RecipeTypeName (Text Value)

Table 3 - RecipeCategory:
Field 1: RecipeCategoryKey (Primary Key)
Field 2: RecipeTypeKey (Foreign Key)
Field 3: ReceipCategoryName (Text Value)

Table 4 - RecipeName:
Field 1: RecipeNameKey (Primary Key)
Field 2: RecipeCategoryKey (Foreign Key)
Field 3: RecipeName (Text Value)

The table’s relationships are a "one to many".

The List Box has three columns. Two for the keys and one for the Text Value
Col1 = Table Primary Key (field 1) - Not Visible
Col2 = Table Foreign Key (field 2) - Not Visible
Col3 = Name Value (field 3) - Visible

Also, the logic for the Next and Previous buttons are as follows:

Function RecipeNextLevel_Click()
AddRecipeLevel = AddRecipeLevel + 1 ' AddRecipeLevel is a global variable
....
End Function

Function ReciepPreviousLevel_Click()
AddRecipeLevel = AddRecipeLevel -1
....
End Function

The logic for the List Box is as follows:

Function Startup ()
Populate List Box based with data from the MenuCategory table where Name
Value = “Bakeryâ€
End Function

Function RecipeListBox_Click()
If (AddRecipeLevel = 1) then
Populate ListBox with data from RecipeType table using
RecipeListBox.Column(2) as search criteria
Else If (AddRecipeLevel = 2)
Populate ListBox with data from RecipeCategory table using
RecipeListBox.Column(2) as search criteria
Else If (AddRecipeLevel = 3)
Populate ListBox with data from RecipeName table using
RecipeListBox.Column(2) as search criteria
Else
ListBoxError = True
Endif
....
End Function

This is a simplified view of the program, but I hope it demonstrates what I
am trying to do. If you think I am on the wrong track and there is a better
way of doing this, please let me know.

Thanks for all your help,

Eddie
 
That looks plausible to me, Eddie, though there seems to be a
contradiction with what you said before about how the user interface
should work. Before, you said that the user would select an item in the
list box and then click NEXT or PREVIOUS to populate the list box. Now,
you propose to use the list box's Click event to populate it with the
next level of data, so it will be populated as soon as the user clicks
on an item in the list. You'll need to make up your mind about that.
One possibility would be to have a single click on the control do
nothing but select an item, until a button is clicked, but have a
double-click on an item both select the item and populate the list box
with the records related to that item (by calling the NEXT button's
Click event procedure. But that's up to you.

Populating the list box would be done by code along these lines:

'---- start of example code -----

' executed after AddRecipeLevel has been changed

Dim strTable As String
Dim strKeyField As String
Dim strRowSource As String

Select Case AddRecipeLevel
Case 1
strTable = "MenuCategory"
strKeyField = "MenuKey"
Case 2
strTable = "RecipeType"
strKeyField = "MenuCategoryKey"
Case 3
strTable = "RecipeCategory"
strKeyField = "RecipeTypeKey"
Case 4
strTable = "RecipeName"
strKeyField = "RecipeCategoryKey"
Case Else
' This shouldn't be allowed to happen.
MsgBox "You can't go any farther in that direction."
If AddRecipeLevel < 1 Then
AddRecipeLevel = 1
Else
AddRecipeLevel = AddRecipeLevel - 1
End If
Exit Sub
End Select

strRowSource = "SELECT * FROM " & strTable

With Me.RecipeListBox

If Not IsNull(.Value) Then

'*** NOTE: This assumes that the key fields are all
numeric,
' not text. For text fields, we need to add quotes.

strRowSource = strRowSource & " WHERE " & _
strKeyField & " = " & .Value

.Value = Null

End If

.RowSource = strRowSource

End With
'---- end of example code -----

Not included in the above code is any mechanism for preserving what was
selected at each level as you drill down, so that you the same item can
be automatically selected as you pop back up using the PREVIOUS button.
There are a number of ways you could do this. You could use an array to
implement a stack, but it may be simpler to use a delimited list
assigned to the list box's Tag property, append each level's key to the
list as you drill down, and strip off the last delimited item each time
you pop back up and assign it to the list box (after resetting the list
box's rowsource).
 
Hi Dirk, as usually you are correct. My error, the code should have shown
the tables in the List Box being updated when the Next/Previous buttons are
selected, not when they select a value from the List Box. My code example
needs to be changed to reflect this requirement. Sorry for the confusion,

Regards,

Eddie
 
No problem, Eddie. But note that there are some ways in which drilling
down in the list box is the same as popping back up, and some ways in
which the two functions are different. It would make sense to isolate
the function that populates the list box at a specified level (and for a
specified foreign key). Let's see, this function would need arguments
to tell it (1) what level (= table) to show, (2) what foreign key value
(if any) to use in the rowsource's WHERE clause so as to restrict the
list items to just those wanted, and (3) what primary key (if any) to
automatically select in the list box after the rowsource is reassigned.

Given a function that will perform this function, all your buttons need
to do is keep track of (and manipulate) the current level and the stack
of foreign key values and primary key values at each level. Just
thinking at random, suppose you had these module-level variables:

Private Type StackEntry
strTableName As String
strPKField As String
strFKField As String
strPKValue As String
strFKValue As String
End Type

Dim arrListStack(1 To 4) As StackEntry

Dim intListLevel As Integer

You'd have a function to initialize the stack, like this:

'----- start of code -----
Private Function InitializeListStack()

arrListStack.strTableName(1) = "MenuCategory"
arrListStack.strPKName(1) = "MenuCategoryKey"
arrListStack.strFKName(1) = "MenuKey"
arrListStack.strPKValue(1) = vbNullString
arrListStack.strFKValue(1) = vbNullString

arrListStack.strTableName(2) = "RecipeType"
arrListStack.strPKName(2) = "RecipeTypeKey"
arrListStack.strFKName(2) = "MenuCategoryKey"
arrListStack.strPKValue(2) = vbNullString
arrListStack.strFKValue(2) = vbNullString

arrListStack.strTableName(3) = "RecipeCategory"
arrListStack.strPKName(3) = "RecipeCategoryKey"
arrListStack.strFKName(3) = "RecipeTypeKey"
arrListStack.strPKValue(3) = vbNullString
arrListStack.strFKValue(3) = vbNullString

arrListStack.strTableName(4) = "RecipeName"
arrListStack.strPKName(4) = "RecipeNameKey"
arrListStack.strFKName(4) = "RecipeCategoryKey"
arrListStack.strPKValue(4) = vbNullString
arrListStack.strFKValue(4) = vbNullString

intListLevel = 1

End Function

'----- end of code -----

The list box's Click event would store the current value of the list box
in arrListStack.strPKName(intListLevel). Clicking the NEXT button would
be equivalent to "pushing the stack" -- it would increment inListLevel,
assign the current value of the list box to
arrListStack.strFKName(intListLevel), clear
arrListStack.strPKName(intListLevel), and call the function to populate
the list box. Clicking the PREVIOUS button would decrement intListLevel
and then call the function to populate the list box.

One thing you'd want to watch out for is the fact that any unhandled
error will reset your VB project, causing the array and intListLevel to
lose all their values. You'd want to have error-checking code to call
the initialization routine again in the event that happens.
 
Hi Dirk, this all looks very good and quite elegant. I just got back from
the Bakery and will try implementing it later today. I can’t thank you
enough for your help and support.

Regards, Eddie

PS: I am still working on the Sourdough Starter. I am having a lot of
problems with it. For some reason the acidity level it too low. I am
thinking of discarding it and re-growing the culture. Anyway, I will figure
it out and you will be one of the first to sample the bread.
 
Eddie's Bakery and Cafe' said:
Hi Dirk, this all looks very good and quite elegant. I just got back
from the Bakery and will try implementing it later today. I can't
thank you enough for your help and support.
Click to expand...

Think nothing of it.
PS: I am still working on the Sourdough Starter. I am having a lot of
problems with it. For some reason the acidity level it too low. I am
thinking of discarding it and re-growing the culture.
Click to expand...

Who knew it was so complicated? I thought it was just, like, y'know ...
bread. ;-)
 
Back
Top