ADO listbox form navigation issue.

[cannen]

This is a repost as I am down to the last hair on my head.

I am using a listbox on a tab control on my mainform that has alisting
of all records associated with form main record. when I select the
record in the list box that I want to navigate to. my form does not
update. Using the watch tool I can see that it is finding the record.
Below is the code I am using.
This is in access 2003 and is not a subform. I am new to using ado as
well. Im sure this is just something that I am doing wrong.
Private Sub lstVacations_Af terUpdate()
Dim rs As ADODB.Recordset
Dim intCurVacReques t As Integer
Dim mark As Variant

Set rs = rsVacRequest.Cl one 'This is record set for main form
intCurVacReques t = CInt(Me.lstVaca tions.Column(8) )

Do While Not rs.EOF
'MsgBox rs.Fields(0).Va lue
' MsgBox rs!intTimeOffId
rs.MoveNext
Loop

rs.MoveFirst
rs.Find "[intTimeOffId] = " & intCurVacReques t
mark = rs.Bookmark
rs.Bookmark = mark
Exit Sub

End Sub

[Alan Webb]

Re: ADO listbox form navigation issue.

Cannen,[color=blue]
> Do While Not rs.EOF
> 'MsgBox rs.Fields(0).Va lue
> ' MsgBox rs!intTimeOffId
> rs.MoveNext
> Loop[/color]

This does nothing. As a debug method clicking on Ok a bunch of times would
get irritating. I'd probably dump the output to the Immediate window with
Debug.Print . . .
[color=blue]
> rs.MoveFirst
> rs.Find "[intTimeOffId] = " & intCurVacReques t
> mark = rs.Bookmark
> rs.Bookmark = mark
> Exit Sub
>
> End Sub[/color]

If rs has no records then rs.MoveFirst would error out. It is useful to add
If not rs.EOF Then . . . Else MsgBox "No records." so you only try to move
to the first record if there is at least one row in the result set. Mark =
rs.Bookmark and rs.Bookmark does nothing. Try Mark = rs.Bookmark and
MyForm.Bookmark = Mark.
--
Alan Webb
knoNOgeek@SPAMh otmail.com
"It's not IT, it's IS

"cannen" <cannen@hotmail .com> wrote in message
news:1112709439 .982602.58750@g 14g2000cwa.goog legroups.com...[color=blue]
>
> Do While Not rs.EOF
> 'MsgBox rs.Fields(0).Va lue
> ' MsgBox rs!intTimeOffId
> rs.MoveNext
> Loop
>
> rs.MoveFirst
> rs.Find "[intTimeOffId] = " & intCurVacReques t
> mark = rs.Bookmark
> rs.Bookmark = mark
> Exit Sub
>
> End Sub[/color]

[cannen]

Re: ADO listbox form navigation issue.


Alan thanks for the fast response but the next question that I have is
when I try and do what you suggested I receive Run-time Error '2455'
you entered an expression that has an invalid reference to the property
bookmark.

the line in question is
me.Bookmark = mark

when I try to use
frmVacRequest.b ookmark = mark
I get an object required error message.

I guess im just not understanding what I am missing?
If Not rs.EOF Then
mark = rs.Bookmark
Me.Bookmark = mark
Else
MsgBox "none found"
End If

[Darryl Kerkeslager]

Re: ADO listbox form navigation issue.

I use this generic function for all listboxes, with these two constants:

Public Const TOP_ITEM = 1
Public Const NEW_ITEM = 2

Notes

1. Access 2000 mdb
2. Handles forms and subforms
3. The unbound listbox is always named Me!lstRecords with bound column set
to the primary key. Boring, but makes the coding easier.
4. Every form needs a textbox named Me!txtId with control source set to the
primary key. Boring, but makes the coding easier.

Public Sub List_Refresh(id As String, frm As Form, _
Optional move_cursor = 0)
Dim lst As ListBox
Dim rsc As Object
Set rsc = frm.Recordset.C lone
Set lst = frm!lstRecords
lst.Requery
If rsc.RecordCount > 0 Then
If move_cursor = NEW_ITEM Then
rsc.MoveLast
frm.Bookmark = rsc.Bookmark
lst.Value = frm!txtId
Else
If IsNull(lst.Valu e) Or move_cursor = TOP_ITEM Then
lst.Value = lst.ItemData(1)
End If
rsc.FindFirst id & "=" & lst.Value
If Not rsc.EOF Then frm.Bookmark = rsc.Bookmark
End If
End If
Set lst = Nothing
End Sub

Usage:

List_Refresh "employment_id" , Me
List_Refresh "program_id ", Me, TOP_ITEM

Hope this helps.


--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
"Alan Webb" <knogeek@hotmai l.com> wrote in message
news:OLOdnRJQGd t6B8_fRVn-hg@comcast.com. ..[color=blue]
> Cannen,[color=green]
>> Do While Not rs.EOF
>> 'MsgBox rs.Fields(0).Va lue
>> ' MsgBox rs!intTimeOffId
>> rs.MoveNext
>> Loop[/color]
>
> This does nothing. As a debug method clicking on Ok a bunch of times
> would get irritating. I'd probably dump the output to the Immediate
> window with Debug.Print . . .
>[color=green]
>> rs.MoveFirst
>> rs.Find "[intTimeOffId] = " & intCurVacReques t
>> mark = rs.Bookmark
>> rs.Bookmark = mark
>> Exit Sub
>>
>> End Sub[/color]
>
> If rs has no records then rs.MoveFirst would error out. It is useful to
> add If not rs.EOF Then . . . Else MsgBox "No records." so you only try to
> move to the first record if there is at least one row in the result set.
> Mark = rs.Bookmark and rs.Bookmark does nothing. Try Mark = rs.Bookmark
> and MyForm.Bookmark = Mark.
> --
> Alan Webb
> knoNOgeek@SPAMh otmail.com
> "It's not IT, it's IS
>
> "cannen" <cannen@hotmail .com> wrote in message
> news:1112709439 .982602.58750@g 14g2000cwa.goog legroups.com...[color=green]
>>
>> Do While Not rs.EOF
>> 'MsgBox rs.Fields(0).Va lue
>> ' MsgBox rs!intTimeOffId
>> rs.MoveNext
>> Loop
>>
>> rs.MoveFirst
>> rs.Find "[intTimeOffId] = " & intCurVacReques t
>> mark = rs.Bookmark
>> rs.Bookmark = mark
>> Exit Sub
>>
>> End Sub[/color]
>
>[/color]