|  | recognize record | | |
| | | alex |  |
|  Posted: Wed Sep 03, 2008 11:08 am Post subject: recognize record |  |
| |  | |
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 | |
| | Posted: Wed Sep 03, 2008 11:08 am Post subject: RE: recognize record | |
| | | |
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" wrote:
| Quote: | 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
|
|
| |
| | | alex | |
| | Posted: Thu Sep 04, 2008 8:52 pm Post subject: Re: recognize record | |
| | | |
On Sep 3, 8:58 am, Erez Mor <Erez...@discussions.microsoft.com> wrote:
| Quote: | 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" wrote:
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- Hide quoted text -
- Show quoted text -
|
Thanks for the help Erez...i'll look at the modification.
alex |
| |
|
|
