RE: varying expressions in tables

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@xxxxxxxxxxx via AccessMonster.c" wrote:

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


.

Relevant Pages

  • varying expressions in tables
    ... i've got essentially a sales tracking database with hard-coded product ... form) let a store specify which products they use. ... Dim iName As Integer ... Dim strProducts As String ...
    (microsoft.public.access.modulesdaovba)
  • RE: Tracking History Challenge
    ... Name, Home, Pager, Cell, etc. ... Then the second table would have StoreID, EmpID, StartDate, EndDate, etc. ... The subform would have a listbox to choose the manager from employee list. ... instance of them managing a store. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Tracking History Challenge
    ... store) for what is existing at that time - and create a second table with the ... "KARL DEWEY" wrote: ... Then the second table would have StoreID, EmpID, StartDate, EndDate, etc. ... The subform would have a listbox to choose the manager from employee list. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Does the StoreID in Outlook change?
    ... If an item is in a PST file for example, and it remains in the same PST file the StoreID should not change. ... We have seen a case where the StoreID of the Outlook data store appears to ... Are there scenarios where the StoreID of the data store will change? ...
    (microsoft.public.vsnet.vstools.office)
Loading