recognize record

[alex]

Hello all,

Using Access ’03…

I have the following code:

Private Sub Store_Click()
Const Q As String = """"
Const QQ = Q & Q

DoCmd.SetWarnings False
If Len(Me.Store) > 30 Then
DoCmd.RunSQL "SELECT TBL_Store_Compose.* INTO TBL_Store_Compose_View
FROM TBL_Store_Compose WHERE TBL_Store_Compose.Store like " & Q &
Replace(Left(Me.Store, 30), Q, QQ) & "*" & Q
Else
DoCmd.RunSQL "SELECT TBL_Store_Compose.* INTO TBL_Store_Compose_View
FROM TBL_Store_Compose WHERE TBL_Store_Compose.Store = " & Q &
Replace(Me.Store, Q, QQ) & Q
End If
DoCmd.SetWarnings True
DoCmd.OpenForm "FRM_Store_Compose_True"
DoCmd.Close acForm, "FRM_ListView"
End Sub

This code executes upon a user clicking a store name in a datasheet
Form. The code works well and has for several months.

A few days ago, however, I had to change the Form to accommodate the
same store, but multiple locations. E.g.,

Where Store
--------- -------
Boston Gap
Brighton Gap
Boston JCrew
Saugas JCrew

Now, (as you could probably tell by the code) when a user selects a
store (which is a hyperlink), the database doesn’t know “Where.” When
the Form opens, I have multiple records instead of one. I need to
modify the SQL code above to recognize both Where and Store when the
user selects a store. I tried Me.Where, but since the user isn’t
selecting "Where," the database doesn’t recognize it.

Any thoughts,
alex

 

[Erez Mor]

hi alex
if you're into refining your solution, using strings and/or names (store
names in your case) as means for searching, filtering and foreign-keys is not
a good idea, and your current problem is a (simple) example of why
if you change the stores table to something like
storeID (AutoNumber)
StoreName(Text)
StoreCity(Text)

and change your TBL_Store_Compose table to hold the StoreID number instead
of the store name(if you want the datasheet to show the name, use a 2 column
combobox with the StoreID and StoreName, hiding the StoreID column) then your
queries will look alot simpler and run much faster
DoCmd.RunSQL "SELECT TBL_Store_Compose.* INTO TBL_Store_Compose_View
FROM TBL_Store_Compose WHERE TBL_Store_Compose.StoreID=Me.StoreID

the user is clicking the (visible) storename in the combo, but the program
actually sends the StoreID a a parameter

hope it makes sense to you, feel free to ask more if you need
good luck
Erez.

 

[alex]

hi alex
if you're into refining your solution, using strings and/or names (store
names in your case) as means for searching, filtering and foreign-keys isnot
a good idea, and your current problem is a (simple) example of why
if you change the stores table to something like
storeID (AutoNumber)
StoreName(Text)
StoreCity(Text)

and change your TBL_Store_Compose table to hold the StoreID number instead
of the store name(if you want the datasheet to show the name, use a 2 column
combobox with the StoreID and StoreName, hiding the StoreID column) then your
queries will look alot simpler and run much faster
DoCmd.RunSQL "SELECT TBL_Store_Compose.* INTO TBL_Store_Compose_View
FROM TBL_Store_Compose WHERE TBL_Store_Compose.StoreID=Me.StoreID

the user is clicking the (visible) storename in the combo, but the program
actually sends the StoreID a a parameter

hope it makes sense to you, feel free to ask more if you need
good luck
Erez.














- Show quoted text -

Thanks for the help Erez...i'll look at the modification.

alex