click to a record (2000)

[iksotof]

Hello, I have a list box that displays outstanding cases on a form (form B, List [img]/forums/images/smilies/cool.gif[/img]. I need to be able to click on a case in the list then have the full case/record details appear in a seperate form (form main) which is already open.. The link is by a common numeric ID.

Kind regards Darren.

[HansV]

You can use the On Click event of the list box (or the On Dbl Click event, if you prefer). For illustration purposes, I will use the following names. Replace them with the appropriate names in your code:

List box: ListB
Other form: MainForm
ID field: CaseID

Private Sub ListB_Click()
With Forms![MainForm]
.Filter = "[CaseID] = " & Me.[CaseID]
.FilterOn = True
End With
End Sub

The square brackets around a name are only necessary, strictly speaking, if the name contains spaces or unusual characters. If you use the On Dbl Click event, the first line becomes

Private Sub ListB_DblClick(Cancel As Integer)

The rest remains the same.

[iksotof]

Thanks Hans

[iksotof]

Hi, Ihave followed the routine but it is still not playing ball, this is my code

Private Sub ListCase_Click()
With Forms![frmMain]
.Filter = "[refID] = " & Me.[list].Column(2)
.FilterOn = True
End With
End Sub

column 3 is where the ID sits on the list.

Thanks Darren.

[HansV]

So what is wrong?
- Do you get an error message? If so, which error message, and whoch line is highlighted if you click Debug?
- Do you get an unexpected result? If so, what?
- Does nothing happen at all?

[iksotof]

It works but doesn't jump to the ID record I require.

Thanks.

[HansV]

I'm sorry to keep on harping on this, but that is a very ambiguous answer. What do you mean by "it works" if at the same time "it doesn't jump to the ID record I require". Please try to be specific. <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

[iksotof]

Sorry, I am now at home and was working from memory on the problem which is at work. The message is
runtime error 2465, microsoft access can find the field 'l' referred to in your expression.

Kind regards Darren.

[HansV]

Now, that's great, isn't it, if Access can find the field <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Are you sure that refID is the correct field name?
Is it possible that the 3rd column of ListCase - i.e. ListCase.Column(2) - is empty/null? You should be able to check that if you click Debug when the error occurs, by hovering the mouse pointer above ListCase.Column(2) in the code.

[iksotof]

Resolved, it is named refId on the form but it's name on the table is referralID, I have changed it to referralId and it now works fine.

Thanks Darren.