Performing actions based on a specific record

[Lisa Burks]

I have two problems I am trying to solve.

1) I am needing to be able to double-click on a list and have it open
another form with the details. I will need the detailed form to be a
stand alone form, so I don't think I can use a sub-form. I've looked
through the group listings and can't seem to find a solution that
doesn't use a sub-form solution. Was thinking the SelTop or
CurrentRecord commands would do what I want, but can't find any code
examples that use it. Thinking I could attach some VB code to the
double-click and use the where function of a query to filter the
results in the detailed form. Hoping someone who's done this could
help!

2) I figure this one is easier and that I'm just not calling it the
proper name when I'm searching newsgroups, so I apologize if it's
already been asked. I have a form that allows users to look at each
record, one at a time by clicking the "play/fast-forward, rewind, etc"
buttons (forgot the formal name for it -- sorry). This can be
cumbersome if there are 1000s of records though, so I'm trying to give
the end-user another way to get to the record they need. I've added a
list box that shows all of the unique ID numbers. I'd like, as the
user goes forward/backward a record at a time, for the ID number in the
list box to be highlighted. Conversely, I'd also like for the user to
be able to highlight a record in the list box and have the form go to
that particular record.

I'm new to programming with Access and don't necessarily have the lingo
down, so I'm probably just not searching on appropriate key words. Any
help (with examples) would be greatly appreciated.

Thanks,
Lisa

[Lisa Burks]

More information:
On the second problem, when I make the controlsource equal to the
"ID Number", it seems to half work (i.e. when I click through the
entries, the list box shows the appropriate id number). However when I
click on a different record in the list, my form thinks that I am
changing the value of the current record. As a result, I get the
following error: "The changes you requested to the table were not
successful because they would create duplicate values in the index,
primary key, or relationship....", which makes sense. Is there a way
circumvent the form thinking that I'm wanting to change the current
record?... maybe using the "before update" function?

Again.. thanks for the help.

Lisa

[Lisa Burks]

Ok, I've been playing a little more with my problem #1 listed at the
beginning of this thread. In trying to identify the record
highlighted, I wrote the following code:

Private Sub CapMatList_Click()
Dim strDesiredFunctQuery As String
Dim ctl As Control
Dim varItm As Variant, intI As Integer

Set ctl = Me.CapMatList

For Each varItm In Me.CapMatList.ItemsSelected
For intI = 0 To ctl.ColumnCount - 1
If (intI = 0) Then
strDesiredFunctQuery = "Select [Request
Breakdown].[Details] from [Request Breakdown], [Capability Import]
where [Request Breakdown].[Capability]=""" & ctl.Column(intI, varItm) &
""""
Me!DesiredList.RowSource = strDesiredFunctQuery
Me.DesiredList.Requery
Debug.Print strDesiredFunctQuery
End If
Next intI
Next varItm
End Sub

This works when I have the multiselect set to Simple. However, when I
set it to None (which is what is desired), it will not go through the
loop. Any ideas?

Lisa

[Anthony England]

"Lisa Burks" <li*******@gmail.com> wrote in message
news:11*********************@z34g2000cwc.googlegro ups.com...

Ok, I've been playing a little more with my problem #1 listed at the
beginning of this thread. In trying to identify the record
highlighted, I wrote the following code:

Private Sub CapMatList_Click()
Dim strDesiredFunctQuery As String
Dim ctl As Control
Dim varItm As Variant, intI As Integer

Set ctl = Me.CapMatList

For Each varItm In Me.CapMatList.ItemsSelected
For intI = 0 To ctl.ColumnCount - 1
If (intI = 0) Then
strDesiredFunctQuery = "Select [Request
Breakdown].[Details] from [Request Breakdown], [Capability Import]
where [Request Breakdown].[Capability]=""" & ctl.Column(intI, varItm) &
""""
Me!DesiredList.RowSource = strDesiredFunctQuery
Me.DesiredList.Requery
Debug.Print strDesiredFunctQuery
End If
Next intI
Next varItm
End Sub

This works when I have the multiselect set to Simple. However, when I
set it to None (which is what is desired), it will not go through the
loop. Any ideas?

Lisa

Hi Lisa
You are making it all to hard for yourself. If the listbox has no
multiselct then you can simply write:
Me.MyListBox.Value
Normally for a list box the first column is set to the unique record id and
the bound column is set to 1. The first column can be made invisible
(column width=0) if the id is an arbitrary value not to be seen by the user.

As for your second point with the "play/fast-forward, rewind, etc" buttons,
thes are called Navigation Buttons and their function is not really to find
records. It looks as though you are not using the most basic of Access
functionality of putting your cursor in the field you want to search by and
pressing CTRL-F.