Access 2000 question

[Guest]

Sorry to post this again but I have not gotten a response and I really do
need help. I was designing a db in Access 2003 but the code acts differently
on Access 2000.. I have a reminder form that has 2 listbox and a navigation
control subform. The one listbox(located in the form header) is populated
with criteria the same as the form. The second listbox (located on form
detail) is populated by a txtbox on the form to retrieve the items associated
with that ID since each one can very. I have the onClick event on the first
listbox set to findrecord for easy navigation. Herein is where the problem
began. On the Form Current event I have the following to highlight the
current record in the one listbox
Dim lngPOCRec As Long
lngPOCRec = Me.CurrentRecord - 1
Me.ListUnrecOnEmployee.SetFocus
Me.ListUnrecOnEmployee.ListIndex = lngPOCRec

This works fine on 2003, I can click on the navigation subform and the
listbox and both will cycle through the records accordingly. However on
2000, it does not shift focus back to the form detail when I click on either
one, forcing you to have to click twice slowly on the nav subform and the
other command buttons. I found that if I would add Me.Dirty=False after the
code above, the subform will work fine but the listbox onclick will give
runtime error 2115 - The macro or function set to the BeforeUpdate or
Validation rule property of this field is preventing Microsoft Access from
saving the data field. This points to the Me.Dirty=False in the code. How
can I get around this weird variation between 2000 and 2003. I use this db
at work so I can not use 2003 or else this would not be an issue. What is
strange is that the file format is 2000 meaning I do not have to convert it.
Please help and many thanks.

 

[Geoff]

The one listbox(located in the form header) is populated

with criteria the same as the form. ... I have the onClick
event on the first listbox set to findrecord for easy navigation.

When using a List Box to find records, you need to have code in the
AfterUpdate event of the List Box, not the OnClick event. As you may know,
Access will write the AfterUpdate event code for you automatically. (See
next.)

To have Access write the AfterUpdate event code automatically, follow these
steps:

1. Open the form in design view.
2. Open the Toolbox.
3. Ensure the "Control Wizards" button is depressed in the Toolbox.
4. Create the List Box.
5. When the wizard pops up, take the option to Find a Record.
6. Complete the wizard.

The wizard will write the AfterUpdate event code, but not the code you need
in the Form's OnCurrent event. As you may know, you need code in the
OnCurrent event to keep your first List Box synchronised with the current
record when the form's record navigation buttons are used. The OnCurrent
event usually needs to set the value of the List Box to the primary key in
the form's record source, eg:

Me.ListBox1.Value = Me.PrimaryKeyTextBox.Value

I'm afraid I don't understand what your second List Box is doing, so cannot
help there.

Regards
Geoff

 

[Guest]

Thanks for the prompt reply. I was not having a problem with the second
listbox, sorry if I misleaded you. What you suggested worked perfectly
after I created a new form to get the wizard to work and then copied the code
to my current form. For some reason it would not work with the sql I wrote
in the recordsource, it gave an error about the Where condition. I still am
trying to get a good handle on recordset.clone and how it works and I see now
that my code was way off with using findrecord. Plus I did not know that the
code for a listbox needed to be in the AfterUpdate Property, thanks much for
that. Last question for general knowledge, the code generated by the wizard
was -

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[POID] = " & Str(Me![ListUnrecOnEmployee])
Me.Bookmark = rs.Bookmark

Should I make the code absolute by adding this?
rs.FindFirst "[POID] = " & Str(Me![ListUnrecOnEmployee].Column(0))


Thank you for your time. I will be sure to post your reply on my previous
message.

Pete

:

 

[Geoff]

Pete,

I still am trying to get a good handle on recordset.clone
and how it works

You can read the help topic on recordset clone method as follows:

1. In the VBA editor, click in the word "clone" so the cursor is within
the word "clone".
2. Press F1.

And here are a few of my comments. The one thing to watch out for is
bookmarks.

1. If you clone a recordset, both the original recordset and the cloned
recordset with have the same bookmarks. This means that you can use the
FindFirst method on the cloned recordset (rs.FindFirst). This makes the
current record in the cloned recordset the record you're searching for.
Then, you can set the bookmark of the form's recordset (Me.Bookmark) equal
to the Bookmark of the cloned recordset (Me.Bookmark = rs.Bookmark). This
makes the record you selected in the Listbox current in the form.

2. In contrast, if you create a second recordset from the same data (but
you don't use the clone method to clone the original recordset), then the
bookmarks in the second recordset will not be the same as the bookmarks in
the original recordset. In this case, you cannot use a bookmark from the
second recordset with the original recordset (ie you cannot equate them as
in Me.Bookmark = rs.Bookmark). Hopefully, you won't encounter this, but
it's just as well to be aware of it.

Should I make the code absolute by adding this?
rs.FindFirst "[POID] = " & Str(Me![ListUnrecOnEmployee].Column(0))

I don't think it matters too much so long as the right side of the equation
returns the [POID] number you're searching for. I assume Column(0) contains
the [POID] number and column(0) is the default column.

I would mention that my wizard created the following code, which is slightly
different to yours above, especially the use of the Nz() function:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Customer_ID] = " & Str(Nz(Me![List10], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I confess I didn't know that a List Box could return Null - but it would
seem that, to cover all eventualities, it's safest to use the Nz() function.
This ensures that, if Null is encountered in the List Box, then Null is
replaced by zero (0). In this case, FindFirst searches for something, ie
zero (rs.FindFirst "[POID] = 0").

Also notice that, if the FindFirst method fails to find the record you want,
then there will be no current record in the cloned recordset and no Bookmark
to use (ie the cloned recordset will have reached the EOF or End-of-File
marker, which is beyond the last record). Therefore, the rs.EOF property is
tested before bookmarks are equated (If Not rs.EOF then Me.Bookmark =
rs.Bookmark).

Geoff