How to obtain the value of a record in DCount criteria argument

[easyrider]

OK, so I know that this is not possible. Per Microsoft's documentation: "Use the DCount function to count the number of records in a domain when you don't need to know their particular values."

But what if I do want to know what the value of those records are?

Background:

Existing DB with a junction table (JT) to handle a M:M relationship between Users & Items. Originally had a form with checkboxes for each Item and got help on this forum in this thread to enable the user's items on the form via DCount. The client then decided that they preferred a Listbox of the items instead of the checkboxes. I tweaked the code for a listbox and all was well in the universe until I added a new item to the Item table. The form's listbox is sorted by item name when the form loads. Now of course, the For loop incorrectly enables the listitems since the listbox items are sorted by name instead of Item_ID. If I change the rowsource to sort the listbox by ItemID, it works but the list is not alphabetized which looks bad.

Since I cannot get the value of the Item using DCount, what could I use to iterate the JT, obtain the Item's value, and select the listbox items associated with said user based on that value instead of the loop iteration?

Thanks in advance guys,
-Bill

ps: I cannot post the DB, as it contains existing employee information. Although my status is "novice", I am comfortable with VBA code and some query design.

[orange]

I suggest you make a copy of the database and keep only enough records to highlight the issue.
Change the names etc to anonymize the data and post.

[Welshgasman]

Puzzled here.
You were using Dcount() in the other thread with criteria.?
Why can't you do the same now?

[Gicu]

Could you please show us the code you use now to select the listbox items? You would probably need to use a DLookup on the ItemsTable to get the PK based on the name.

Cheers,

[easyrider]

I cannot post the database as it already contains proprietary company information in addition to almost 1800 employees - way to much cleanup/name changing

[easyrider]

Puzzled here.
You were using Dcount() in the other thread with criteria.?
Why can't you do the same now?

Because I added a new item to the ListT, Item3.5 after a significant amount of data was already in the JT. The listbox is sorted by name, alphabetically. If I sort it by the Item's PK, it works, but is not alphabetized. I put together a little module to show you the difference.

The user has items 1, 2 & 4 in the junction table.

This list box is sorted by name:




This one is sorted by Item_ID

[easyrider]

Could you please show us the code you use now to select the listbox items? You would probably need to use a DLookup on the ItemsTable to get the PK based on the name.

Cheers,

Here's the code, Vlad. It's called on the Form_Load event.

Private Sub ShowAllocatedItems()
Dim i As Integer
Dim y As Integer

For i = 1 To DMax("Item_ID", "Item_T")
y = DCount("*", "UserXItem_JT", "User_ID=" & Me.User_ID & " AND Item_ID=" & i)
If y > 0 Then
ItemList.Selected(i - 1) = True
End If
Next i
End Sub

Yeah, I thought about DLookup too. But I've read several places that DLookup shouldn't be used inside of a loop due to performance issues. Unless, I use a recordset object - which I'm not opposed to.

[Welshgasman]

I cannot post the database as it already contains proprietary company information in addition to almost 1800 employees - way to much cleanup/name changing

Not really?
https://www.accessforums.net/showthr...t=randomize#19

[Welshgasman]

Here's the code, Vlad. It's called on the Form_Load event.

Private Sub ShowAllocatedItems()
Dim i As Integer
Dim y As Integer

For i = 1 To DMax("Item_ID", "Item_T")
y = DCount("*", "UserXItem_JT", "User_ID=" & Me.User_ID & " AND Item_ID=" & i)
If y > 0 Then
ItemList.Selected(i - 1) = True
End If
Next i
End Sub

Yeah, I thought about DLookup too. But I've read several places that DLookup shouldn't be used inside of a loop due to performance issues. Unless, I use a recordset object - which I'm not opposed to.

So you are showing items in the list, nothing to do with the user, just not selecting them?

[easyrider]

Not really?
https://www.accessforums.net/showthr...t=randomize#19

Due to the nature of my employer's business, I am forbidden to post the database, randomized or not. I have provided answers to your initial question, with screenshots.

[easyrider]

So you are showing items in the list, nothing to do with the user, just not selecting them?

The listbox shows all of the items in the table, what I want is the listbox to highlight/select the items associated with said user when the form loads. See screenshots I posted earlier. Thanks!

[June7]

I am not quite understanding what you want so I will focus on this element "get the value of the Item". Possibly include item value as a field in listbox RowSource. Reference that field by listbox column index to pull value.

[Gicu]

Bill,

The dLookup use in loops applies when you have over thousands of items, with your limited list it shouldn't be a problem. Just so I get it right, the listbox is based on the tblItems, the bound column is IndexId and in its row source you sort by Item Name. And you want to select all items for the current user that have at least one record for them in the join table tblUserItem. Is that the gist of of it?

Cheers,

[easyrider]

Bill,

The dLookup use in loops applies when you have over thousands of items, with your limited list it shouldn't be a problem. Just so I get it right, the listbox is based on the tblItems, the bound column is IndexId and in its row source you sort by Item Name. And you want to select all items for the current user that have at least one record for them in the join table tblUserItem. Is that the gist of of it?

Cheers,

Pretty much, yeah. Just to clarify though, the listbox is unbound, the row source is:

"SELECT [Item_T].[Item_ID], [Item_T].[ItemName] FROM Item_T ORDER BY [Item_ID];"

And the bound column = 1

[Gicu]

Can you please try this?

Private Sub ShowAllocatedItems()
Dim i As Integer
Dim y As Integer


For i = 0 To Me.ItemList.ListCount - 1
    y = DCount("*", "UserXItem_JT", "User_ID=" & Me.User_ID & " AND Item_ID=" & Me.ItemList) 'listbox is bound to Item_ID
    Me.ItemList.Selected(i) = Iif(y>0,True,False)
Next i


End Sub

Cheers,