varying expressions in tables

[kaosyeti]

hey... i've got essentially a sales tracking database with hard-coded product
types. i want to change the way my database is written so that using a form,
anyone can list the products their store wants to sell and it may be
different from my own. for instance...

now, i have numerous forms and reports that pull productA, productB, productC,
etc. from one table. what i'd like is to create a table where i can (using a
form) let a store specify which products they use. so if one store uses
productA and productZ, i don't need to have 26 fields in each form/report
where 24 would be 'null'.

this can be either in some kind of dlookup as a control source for the
various textboxes in the forms and reports or can be with VBA, i don't care.
the number of products used by each store would vary, but if there were 10,
that would be a lot. the issue is that some stores may use products that my
store does not. i tried something like this in the on-open event of one form:


Dim i As Long
Dim iName As Integer
Dim strProducts As String

i = 1
iName = 1
For i = 1 To 1000
If IsNull(DLookup("[products]", "tblproducts", "[id] = " & i)) = True
Then
i = i + 1
Else
(strProducts & iName) = DLookup("[products]", "tblproducts", "[id] =
" & i)
i = i + 1
iName = iName + 1
End if
Next i

End Sub
the idea is to have strProducts1 be the first choice, then strProducts2 etc...
but the (strProducts & iName) part of the expression is not a valid way to
declare a variable, apparently. is there something really obvious that i'm
missing or what? thanks for helping.

 

[Damian S]

Hi Greg,

How about something like this:

tblStores
StoreID autonumber
StoreName text
etc

tblProducts
ProductID autonumber
ProductName text
etc

tblStoreProducts
StoreID number (not 0)
ProductID number (not 0)

Link StoreID to StoreID, ProductID to ProductID in the various tables. This
will link each Store to the Products they wish to sell. Then it's a simple
query to return them.

Hope this helps.

Damian.

 

[kaosyeti]

yeah, relationships are not my strong suit (have never had the need to play
with them since starting this project a year ago) but this really isn't the
area i'm having trouble. where my real concern lies is that each store uses
specific reports to track the sales of their products, but that the products
change from store to store. to illustrate, one report is a detail report
with a header that has the customer's name in the first column, then a few
columns of transaction info, then 7 columns of products. in the detail
section of the report, each product column has the dollar value of the
product for that transaction. so:

Store #1
Cust Name Transaction Num Date Product A
Product B Product C
------------------------------------------------------------------------------
----------------------------------------------------------
Smith 123456 01/15/2007 $200
$300 $99
Jones 456789 01/16/2007
$500 $200

Store #2
Cust Name Transaction Num Date Product B
Product F Product K
------------------------------------------------------------------------------
----------------------------------------------------------
Doe 789012 01/15/2007 $150
$300 $200
Johnson 654321 01/16/2007 $210
$400

Etc...

How do i make this report GENERIC enough to have each product's text box pull
the right products, when i don't know the name of the field in the query to
pull? in this example, i have to have 11 columns to make sure i cover all of
the possible products (A through K), yet i only have room for 7.

i guess i'm stuck where my query's fields would be expressions that have
generic names (productA, productB) and i can have 7 columns that pull 7
products (A through G) for this report, but how do i write the query without
knowing the product names for each store until that store populates the table
with the products it sells?

thanks for taking another look

Hi Greg,

How about something like this:

tblStores
StoreID autonumber
StoreName text
etc

tblProducts
ProductID autonumber
ProductName text
etc

tblStoreProducts
StoreID number (not 0)
ProductID number (not 0)

Link StoreID to StoreID, ProductID to ProductID in the various tables. This
will link each Store to the Products they wish to sell. Then it's a simple
query to return them.

Hope this helps.

Damian.

hey... i've got essentially a sales tracking database with hard-coded product
types. i want to change the way my database is written so that using a form,

[quoted text clipped - 36 lines]

declare a variable, apparently. is there something really obvious that i'm
missing or what? thanks for helping.

 

[kaosyeti]

if you're ready to see a COMPLETELY complicated method to do what i want,
read on and see how i solved it (at least for now)...

i have 1 table with each product and an id number

on one form, i have a dlookup textbox to get the name of the first product
and dlookup to get the id number of that product. then i move on to the next
control where i use dlookup with the criteria that [id] > the previous id
number (getting up to 8 products here this way). some of these controls are
tagged with Produ1, Produ2, etc. and others with Name1, Name2, etc. (there
are tags named bottom1 and bottom2, but they're just the bottom-most controls
on the form that i want to reposition so there's not a huge gap once i
determine that there are no more products to list). the first control is:

name: txtboxProdu1
source: =DFirst("[Products]","tblProducts","[id] >0")
tag: Produ1

name: txtboxProduID1
source: =DLookUp("[id]","tblProducts","[Products] = " & Chr(34) &
[txtboxprodu1] & Chr(34))
visible = false

the 2nd through 8th controls are:

name: txtboxProdu2
source: =DFirst("[Products]","tblProducts","[id] >" & [txtboxproduid1])
tag: Produ2

name: txtboxProduID2
source: =DLookUp("[id]","tblProducts","[Products] = " & Chr(34) &
[txtboxprodu2] & Chr(34))
visible = false

and so on....

then in the on_current event for this form, i put this code:


Dim currControl As Control
Dim holdPlace As Integer
Dim booSwitch As Boolean
Dim holdBottom As Long

booSwitch = True
holdPlace = 1

For Each currControl In Me.Controls
If Left(currControl.Tag, 5) = "Produ" Then
If IsNull(currControl) = True Then
holdPlace = Right(currControl.Tag, 1)
Exit For
End If
End If
Next currControl

i = holdPlace

While i <= 8
For Each currControl In Me.Controls
Debug.Print currControl.Name
If currControl.Tag = "Produ" & holdPlace Then
If booSwitch = True Then
holdBottom = currControl.Top
booSwitch = False
End If
currControl.Visible = False
End If
If currControl.Tag = "Name" & holdPlace Then
currControl.Visible = False
End If
If currControl.Tag = "bottom1" Then
currControl.Top = holdBottom + 20
ElseIf currControl.Tag = "bottom2" Then
currControl.Top = holdBottom + 400
End If
Next currControl
holdPlace = holdPlace + 1
i = holdPlace
Wend

So far, it seems to work and it doesn't even appear to be slow.
unfortunately, this is 1 form out of about 10 that i need to overhaul this
way and there are probably about 10-12 reports that will need the same thing.
if any of you can see what i'm trying to do by looking at this code (god
bless you if you can), can you suggest a better solution to do the same? i'm
probably going about this the TOTALLY wrong way, making things far more
complicated than they need to be, but this is all i've been able to come up
with. as i said in a different post, relationships are not my strong suit
and i'm probably missing a key bit there to make this easier.

yeah, relationships are not my strong suit (have never had the need to play
with them since starting this project a year ago) but this really isn't the
area i'm having trouble. where my real concern lies is that each store uses
specific reports to track the sales of their products, but that the products
change from store to store. to illustrate, one report is a detail report
with a header that has the customer's name in the first column, then a few
columns of transaction info, then 7 columns of products. in the detail
section of the report, each product column has the dollar value of the
product for that transaction. so:

Store #1
Cust Name Transaction Num Date Product A
Product B Product C
------------------------------------------------------------------------------
----------------------------------------------------------
Smith 123456 01/15/2007 $200
$300 $99
Jones 456789 01/16/2007
$500 $200

Store #2
Cust Name Transaction Num Date Product B
Product F Product K
------------------------------------------------------------------------------
----------------------------------------------------------
Doe 789012 01/15/2007 $150
$300 $200
Johnson 654321 01/16/2007 $210
$400

Etc...

How do i make this report GENERIC enough to have each product's text box pull
the right products, when i don't know the name of the field in the query to
pull? in this example, i have to have 11 columns to make sure i cover all of
the possible products (A through K), yet i only have room for 7.

i guess i'm stuck where my query's fields would be expressions that have
generic names (productA, productB) and i can have 7 columns that pull 7
products (A through G) for this report, but how do i write the query without
knowing the product names for each store until that store populates the table
with the products it sells?

thanks for taking another look

Hi Greg,

[quoted text clipped - 27 lines]