varying expressions in tables

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.

--
Greg

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200701/1

.