Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bookmark problem (Access 97)

    I have another table (TableName: tbl_IDNumber) which contains a list of all ID Numbers and the people's names.

    I have a form which displays an ID Number , a text field containing an email address, and 4 boolean (Yes/No) field (TableName: tbl_EmailAddress).

    On the form, I am using a combo box which displays the IDNumber. The controlsource for the combo box is IDNumber field in the tbl_EmailAddress table.
    The row source for the combo box is a SELECT statement which returns the IDNumbers and names from the tbl_IDNumber table.

    I am able to view and add IDNumbers in the form to the tbl_EmailAddress table.

    With records that already exist in tbl_EmailAddress, I want to actually go to that record and display it on the form, and this is where the problem comes in.

    I am using bookmarks at the moment and I get the following error message:
    "You can't go to the specified record. You may be at the end of a recordset"

    My code is as follows:

    Dim strIDNumber As String
    Dim myRecordClone As Recordset

    strIDNumber = Me!cboIDNumber

    Set myRecordClone = Me.RecordsetClone

    myRecordClone.FindFirst "[IDNumber] = '" & Me!cboIDNumber & "'"

    If myRecordClone.NoMatch Then
    SendKeys "{ESC}"
    DoCmd.GoToRecord A_FORM, Me.Name, A_NEWREC
    Me!cboIDNumber = strIDNumber
    Else
    Me.Bookmark = myRecordClone.Bookmark
    EndIf

    Set myRecordClone = Nothing

    Can anyone help me solve this one? (I'm still pretty new to the front end UI of Access)

    James

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Bookmark problem (Access 97)

    Your code looks like it should work (at least as for handling the bookmarks), but there are several things that bother me.

    First of all, don't use SendKeys; it is not reliable, and causes some problems that Microsoft has acknowledge but never fixed). If you are trying to undo an entry in a control, use "cboID.Undo".

    Where did you place your code? That is, in which event procedure?

    I am not confortable with using a bound combo box enter search criteria. As soon as you enter anything, you cause the current record to be dirty. This may have a variety of consequences, including accidentally changing the value of the ID field in a record.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bookmark problem (Access 97)

    Thanks for the tip about the undo, I was wondering how to get this to work reliably.

    The code is in the AfterUpdate method for the combo box. If there is a better place for it, I would gladly accept the advice.

    If the combo box was not bound, wouldn't it still be dirty if you had to change something in it, even accidentily?

    James

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmark problem (Access 97)

    The dirty property applies to the form's recordset, not to a control. Unbound controls don't affect the dirty property of the recordset.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bookmark problem (Access 97)

    Does that open the possibility of changing it to an unbound control and changing my code to cater for the change?

    James

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmark problem (Access 97)

    I just posted code for something like this in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=61891&Search =true&Forum=All_Forums&Words=charlotte&Match=Usern ame&Searchpage=1&Limit=25&Old=allposts&Main=61813> this thread</A>
    Charlotte

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Bookmark problem (Access 97)

    The "dirty" applies to the form, not the control. A form is said to me dirty (which you can test for) when one or more bound controls have been changed, but those changes haven't been written back to the underlying recordset yet.

    The AfterUpdate event is the proper place for the code. However, I would use an unbound combo box. For the bound control, I would just use a textbox. I typically put my "Find" combo boxes in my form's header or footer, to keep them out of the way of the data.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bookmark problem (Access 97)

    Thanks for the advice.

    I am unsure of you are meaning in your last sentence when you refer to having your "Find" combo boxes in your form's header/footer to keep them away from the data?

    James

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bookmark problem (Access 97)

    Thanks - I'll try this out and see what I come up with.

    James

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Bookmark problem (Access 97)

    Quite simply, I don't like the idea of allowing using a bound data control to serve double duty as a means of allowing user's to find a record. So, I put tan unbound combo box in the form header, which allows them to select a specific ID from the box and have the form display that record. Then I only need a bound textbox control to display the ID in the form's detail section.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •