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

[Welshgasman]

I would ask 'why not present the user with just the items relevant to them?
Then no need for highlighting?

[easyrider]

Sorry for the brief hiatus - had to work. I rebuilt my little testing DB to test the suggestions that have been proposed.

Here it is. ListBox-PickList Test.zip

Vlad,

I've commented out my observations in the ShowAllocatedItems procedures of the two edit user forms (EditUser_F & EditUser2_F). EditUser_F is sorted alphabetically by name and EditUser2_F is sorted by Item_ID (PK). Both can be called from MainForm_F for comparison.

I also left my original code in this procedure on both edit forms, but it is commented out.

To summarize:
1. when the listbox is bound to Item_ID, all items are selected on load and they cannot be selected/deselected
2. when the list is unbound, the y assignment with DCount throws a run-time error 3075

Thanks!
-Bill

[Gicu]

Hi Bill,

Here is the updated file, sorry for this, I wrote it in Notepad++ so couldn't check, it was missing .ItemData(i) after the Me.ItemList in the dCount.

Cheers,

[easyrider]

Hi Bill,

Here is the updated file, sorry for this, I wrote it in Notepad++ so couldn't check, it was missing .ItemData(i) after the Me.ItemList in the dCount.

Cheers,

You are the man!! If I didn't live nearly 3,000 miles away, drinks would be on me. So, until you are better paid, THANK-YOU, THANK-YOU, THANK-YOU!

[Welshgasman]

@Gicu,
How do the next/previous buttons gets enabled/disabled depending on record postion?

[easyrider]

@Gicu,
How do the next/previous buttons gets enabled/disabled depending on record postion?

Welshgasman,

Probably should another topic here on the forum, but this is what I use:

' custom navigation buttons
Private Sub Form_Current()
Dim rst As DAO.Recordset
' N is the total number of records, C is the pointer to the current record
Dim n, C As Long

Set rst = Me.RecordsetClone
If rst.RecordCount <> 0 Then
With rst
.MoveFirst
.MoveLast
n = .RecordCount
End With
End If

C = Me.CurrentRecord

If C = 1 Then
GoToPrev_BTN.Enabled = False
GoToFirst_BTN.Enabled = False
Else
GoToPrev_BTN.Enabled = True
GoToFirst_BTN.Enabled = True
End If
If C = n Then
GoToNext_BTN.Enabled = False
GoToLast_BTN.Enabled = False
Else
GoToNext_BTN.Enabled = True
GoToLast_BTN.Enabled = True
End If
rst.Close
'refresh the listbox after any navigation button is clicked
Me.ItemList.Requery

End Sub

-Bill

[isladogs]

Re Vlad:

You are the man!! If I didn't live nearly 3,000 miles away, drinks would be on me. So, until you are better paid, THANK-YOU, THANK-YOU, THANK-YOU!

You can always send him a donation via his website Home - ForestByteApps

[Gicu]

@easyrider
You're very welcome!

@Gicu,
How do the next/previous buttons gets enabled/disabled depending on record postion?

@Welshgasman : are you talking about the main form (which has the code shown above by Bill) or the two edit forms. Not sure if the sample provided is a true replica of the original db but I would make the two edit forms unbound and remove the built-in navigation buttons like in the attached updated sample.

@isladogs

[Welshgasman]

What ever form opens at db open.
There are macroes in there, but no vba, so was curious as to how the buttons know how the buttons get enabled/disabled?

[June7]

MainForm_F opens by default. It has VBA in form OnCurrent event that enables/disables buttons.

[Welshgasman]

MainForm_F opens by default. It has VBA in form OnCurrent event that enables/disables buttons.

Damn , sorry. missed that completely.