Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access 2000 question

Discussion in 'Information Technology' started by default105, Jul 28, 2006.

  1. default105

    default105
    Expand Collapse
    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.
     
  2. Loading...


  3. Geoff

    Geoff
    Expand Collapse
    Guest

    > 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


    "default105" <default105@discussions.microsoft.com> wrote in message
    news:B442D51C-E6CC-4469-8180-66C4D353EBA8@microsoft.com...
    > 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.
     
  4. default105

    default105
    Expand Collapse
    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" wrote:


    > 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
     
  5. Geoff

    Geoff
    Expand Collapse
    Guest

    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
     

Share This Page