Microsoft Access List Box and Record Not in Synch

[KeithMcVay]

I am trying to build a form that contains a list box with "CLIN" ID numbers,
which are text due to some being 11000A, etc. on a table.

What I want to be able to do is select a CLIN ID in the list box (say 10001,
which is the 2nd id) and have the record number change to "2 of 366" (there
are 366 CLINs in total). This does not happen.

If I change the record number to 2 it highlights the second CLIN, which seems
right and if I search by CLIN it changes the record number and highlight the
CLIN, which also seems right. However I need to be able to select a CLIN and
have the record number change.

Does this have to be done in VBA?

 

[David H]

So you have the ListBox on a form and somewhere nearby want to display
something that says Record [x] of [x] Records?

The .ListIndex property of the ListBox will provide you with the index of
the item that is currently selected, basically the location of the item in
the list. It is 0-based so if the .ListIndex is 2, the actual position is 3.

http://msdn.microsoft.com/en-us/library/aa196331(office.11).aspx

The .ListCount property will give you the number of items in the list box,
also 0-based.

http://msdn.microsoft.com/en-us/library/aa196327(office.11).aspx

The following should come close in giving you what you want...

1) Create an unbound control.
2) Set its control source to ="Record " &
[Me].[listBoxControlName].[listIndex] & " of " &
[Me].listBoxControlName].[listCount] & " Record(s)"

3) In the AfterUpdate event of the List Box call the .Requery method of the
unbound control as in

[Me].[UnboundControlName].Requery

This will update the text as different items are selected.

HOWEVER, the ListBox will only contain a static list of records in the
underlying datasource. If additional records are added, they will not be
reflected in the ListBox unless you call the .Requery method on the ListBox.

You also have the option of using a subform to display the records which
will handle displaying the record [x] of [x]. Depending on the navigation
that you need you might want to go that route.

 

[KeithMcVay]

In response to: So you have the ListBox on a form and somewhere nearby want
to display
something that says Record [x] of [x] Records?

Correct. I have a list box on a form and when I choose the second selection
in the list box. I want the Record (which is automatically on the bottom of
form, next to the search button) to change to the corresponding selection.
Currently, if I select the right arrow next to the word record and change it
to 2 it will auto select the second selection. I want it to work both ways.
Hope that is more clear.

So you have the ListBox on a form and somewhere nearby want to display
something that says Record [x] of [x] Records?

The .ListIndex property of the ListBox will provide you with the index of
the item that is currently selected, basically the location of the item in
the list. It is 0-based so if the .ListIndex is 2, the actual position is 3.

http://msdn.microsoft.com/en-us/library/aa196331(office.11).aspx

The .ListCount property will give you the number of items in the list box,
also 0-based.

http://msdn.microsoft.com/en-us/library/aa196327(office.11).aspx

The following should come close in giving you what you want...

1) Create an unbound control.
2) Set its control source to ="Record " &
[Me].[listBoxControlName].[listIndex] & " of " &
[Me].listBoxControlName].[listCount] & " Record(s)"

3) In the AfterUpdate event of the List Box call the .Requery method of the
unbound control as in

[Me].[UnboundControlName].Requery

This will update the text as different items are selected.

HOWEVER, the ListBox will only contain a static list of records in the
underlying datasource. If additional records are added, they will not be
reflected in the ListBox unless you call the .Requery method on the ListBox.

You also have the option of using a subform to display the records which
will handle displaying the record [x] of [x]. Depending on the navigation
that you need you might want to go that route.

I am trying to build a form that contains a list box with "CLIN" ID numbers,
which are text due to some being 11000A, etc. on a table.

[quoted text clipped - 9 lines]

Does this have to be done in VBA?

 

[GB]

If I am not mistaken, if you were to add your button again to the form, and
have the Control Wizards option selected on the Toolbox toolbar, one of the
selections would be to "Find a record on my form based on the value I
selected in my combo box. (Okay assuming that you add a combo box. )
This would generate VBA code to do the update. Below is the code auto
generated by Office 03.

Private Sub Combo9_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[TableField1] = '" & Me![Combo9] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The recordset may need to refer to your data that is "at the bottom of the
screen" if you are using a subform.

In response to: So you have the ListBox on a form and somewhere nearby want
to display
something that says Record [x] of [x] Records?

Correct. I have a list box on a form and when I choose the second selection
in the list box. I want the Record (which is automatically on the bottom of
form, next to the search button) to change to the corresponding selection.
Currently, if I select the right arrow next to the word record and change it
to 2 it will auto select the second selection. I want it to work both ways.
Hope that is more clear.

So you have the ListBox on a form and somewhere nearby want to display
something that says Record [x] of [x] Records?

The .ListIndex property of the ListBox will provide you with the index of
the item that is currently selected, basically the location of the item in
the list. It is 0-based so if the .ListIndex is 2, the actual position is 3.

http://msdn.microsoft.com/en-us/library/aa196331(office.11).aspx

The .ListCount property will give you the number of items in the list box,
also 0-based.

http://msdn.microsoft.com/en-us/library/aa196327(office.11).aspx

The following should come close in giving you what you want...

1) Create an unbound control.
2) Set its control source to ="Record " &
[Me].[listBoxControlName].[listIndex] & " of " &
[Me].listBoxControlName].[listCount] & " Record(s)"

3) In the AfterUpdate event of the List Box call the .Requery method of the
unbound control as in

[Me].[UnboundControlName].Requery

This will update the text as different items are selected.

HOWEVER, the ListBox will only contain a static list of records in the
underlying datasource. If additional records are added, they will not be
reflected in the ListBox unless you call the .Requery method on the ListBox.

You also have the option of using a subform to display the records which
will handle displaying the record [x] of [x]. Depending on the navigation
that you need you might want to go that route.

I am trying to build a form that contains a list box with "CLIN" ID numbers,
which are text due to some being 11000A, etc. on a table.

[quoted text clipped - 9 lines]

Does this have to be done in VBA?

 

[David H]

So when you click an item in the list box, you want the form to automatically
navigate to the record and display it?

If that's the case, you'll want to google around the internet for 'QUERY BY
FORM' which is essentially taking a value from an unbound field and using it
locate the record. Quite easy to implement.

In response to: So you have the ListBox on a form and somewhere nearby want
to display
something that says Record [x] of [x] Records?

Correct. I have a list box on a form and when I choose the second selection
in the list box. I want the Record (which is automatically on the bottom of
form, next to the search button) to change to the corresponding selection.
Currently, if I select the right arrow next to the word record and change it
to 2 it will auto select the second selection. I want it to work both ways.
Hope that is more clear.

So you have the ListBox on a form and somewhere nearby want to display
something that says Record [x] of [x] Records?

The .ListIndex property of the ListBox will provide you with the index of
the item that is currently selected, basically the location of the item in
the list. It is 0-based so if the .ListIndex is 2, the actual position is 3.

http://msdn.microsoft.com/en-us/library/aa196331(office.11).aspx

The .ListCount property will give you the number of items in the list box,
also 0-based.

http://msdn.microsoft.com/en-us/library/aa196327(office.11).aspx

The following should come close in giving you what you want...

1) Create an unbound control.
2) Set its control source to ="Record " &
[Me].[listBoxControlName].[listIndex] & " of " &
[Me].listBoxControlName].[listCount] & " Record(s)"

3) In the AfterUpdate event of the List Box call the .Requery method of the
unbound control as in

[Me].[UnboundControlName].Requery

This will update the text as different items are selected.

HOWEVER, the ListBox will only contain a static list of records in the
underlying datasource. If additional records are added, they will not be
reflected in the ListBox unless you call the .Requery method on the ListBox.

You also have the option of using a subform to display the records which
will handle displaying the record [x] of [x]. Depending on the navigation
that you need you might want to go that route.

I am trying to build a form that contains a list box with "CLIN" ID numbers,
which are text due to some being 11000A, etc. on a table.

[quoted text clipped - 9 lines]

Does this have to be done in VBA?