Dynamic query to show each user only records relevant to them on logging in

[wazimu13]

I have an access 2010 web database, modified from a microsoft template (Desktop inventory database) that several users will need to log in to. The database contains a table of products.
The challenge is, every user needs to only see a subset of these products and never see the whole list.
At the moment i have some code to modify an existing query based on the logged in user's details. As they log in, some tempvars are created and these are used to modify the query criteria.
This works well when the first user logs in, but the moment the next user logs in, the query is modified again and the product list refreshes and now his products are shown and not the first users! Im thinking i need to dynamically create a permanent query for each user on log in?
Or is a better way to accomplish what im trying ? im quite new to access and struggling. Can anyone assist please?




Here is my code so far:

Button on login form has the following code that collects the user's details

Private Sub cmdLoginMine_Click()
Dim ID as long, strEmpName as string,strZondsc as string,strgrpdsc as string

ID = DLookup("ID", "Employees", "Login='" & Me.txtUser.Value & "'")
strEmpName = DLookup("FullName", "Employees", "Login='" & Me.txtUser.Value & "'")
strgrpdsc = DLookup("MyGrpdscs", "Employees", "Login='" & Me.txtUser.Value & "'")
strzondsc = DLookup("MyZondscs", "Employees", "Login='" & Me.txtUser.Value & "'")

TempVars.Add "tmpEmployeeID", ID
TempVars.Add "tmpEmployeeName", txtUser.Value

I then call a function that modifies the existing query, populating it with this users details for the criteria

qryEdit strgrpdsc, strzondsc, ID


Sub qryEdit(strgrpdsc As String, strzondsc As String, ID As Long)
Dim qdf As DAO.QueryDef
Dim qdfOLD As String

Set qdf = CurrentDb.QueryDefs("InventoryQryforDS")
    With qdf
        .SQL = "SELECT Products.ProductCode, Products.ProductName, Products.GRPDSC, Categories.Category, Inventory.Available " & _
               "FROM (Categories INNER JOIN Products ON Categories.ID = Products.CategoryID) INNER JOIN Inventory ON Products.ID = Inventory.ProductID " & _
               "WHERE Products.GRPDSC in (" & strgrpdsc & ") and Categories.Category in (" & strzondsc & ") and products.ownersid =" & ID & _
               " ORDER BY Products.ProductCode"


    End With
Set qdf = Nothing

End Sub

The results of the query are shown on a form, which is what is currently requerying and showing the wrong data.
THe data is shown on a form, linked to one of the new style navigation buttons as shown.Therecordsource property of the form is the query that's populated as described above.

thanks

[pbaldy]

I would use this method to see how the SQL is ending up:

BaldyWeb-Immediate window

that should show you what value is wrong in it.