Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Bookmark Problem With List (2000 / SP3)

    I've successfully been re-using code provided to me by this group to open frmMembershipStatusEdit from frmMembershipStatusAdd, where the Member/BusinessName box on frmMembershipStatusEdit is populated by the LastName/Business box on frmMembershipStatusAdd.

    There is a list box called lstStatusHistory that "on update" bookmarks the the fields on frmMembershipStatusEdit. Presently all of the membership status that are in the table are listed in this list box. I would like to be able to only show the status history for the member who is in the Member/BusinessName box, and then be able to select the item to edit from list. I've tried a variety of approaches using code. My problem is that if I limit the list than the bookmark doesn't work. On the other hand, if don't limit the list, the user has to scroll thru mountains of names to find what they want. I'm trying to avoid having to have the user type in the last name due to potential for errors in data entry causing issues. I'd simply like them to point and click.

    Thanks!
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bookmark Problem With List (2000 / SP3)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I'm sorry, I don't understand your description. What do you mean by bookmarking fields? And can you try to describe more clearly what you want to accomplish?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmark Problem With List (2000 / SP3)

    Oh Han's please don't apologize, I'm sure its my description!

    By "bookmark" I mean that when I click a renewal status in the list, the form populates with the data that is in the corresponding underlying row. The problem is that list is huge to scroll though to find the row needed. I'd like the list to choose from be limited to the name that the form opens to in member/businessname.

    Thank you!

    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bookmark Problem With List (2000 / SP3)

    It should be possible to change the row source of the list box to display only the status history for the selected member. If the row source is something like SELECT Field1, Field2 FROM tblSomething now, it could be expanded to SELECT Field1, Field2 FROM tblSomething WHERE [Member/BusinessName] = [Forms]![frmMembershipStatusEdit]![Member/BusinessName]. You must substitute the actual table and field names, of course.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmark Problem With List (2000 / SP3)

    Hi Hans,

    Well, I thought for a minute I had it but seems not. Here is the code that is used to fill the list box. I get no errors either when the form opens or when the list box fills. The form opens in "add" mode and after the new status is added, it is saved and the list box fills with the following code:

    SELECT [tbl Membership Status].ID, [tbl Database].SALUTATION, [tbl Database].[LASTNAME/BUSINESS], [FIRST NAME] & " " & [middle name/initial] & " " & [SUFFIX] AS Expr1, [tbl Membership Status].[RENEWAL STATUS], [tbl Membership Status].[DATE MEMBERSHIP BEGINS], [tbl Membership Status].[CURRENT MEMBERSHIP LEVEL]
    FROM [tbl Database] INNER JOIN [tbl Membership Status] ON [tbl Database].ID = [tbl Membership Status].[MEMBER/BUSINESSNAME]
    WHERE ((([tbl Membership Status].[MEMBER/BUSINESSNAME])=[Forms]![frmMemberStatusAdd]![Member/BusinessName]))
    ORDER BY [tbl Database].[LASTNAME/BUSINESS];

    The table does in fact only load with the satus that correspond to the member name in [member/businessName]. However, when I click on them, they do not change the data on the form to correspond to the row chosen in the list.

    The code that is in the "After Update" event is:

    Dim rs As Object

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

    When I removed this to see if that made a difference it didn't.

    Thanks,
    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bookmark Problem With List (2000 / SP3)

    I'm still utterly <img src=/S/confused.gif border=0 alt=confused width=15 height=20>. "tbl Database" contains a field ID that is linked to MEMBER/BUSINESSNAME in "tbl Membership Status", but there is also a field LASTNAME/BUSINESS that seems to have a connection with MEMBER/BUSINESSNAME. And "tbl Membership Status" also contains a field named ID.

    Two things to try:

    1. Perhaps the condition in the row source of the list box should refer to frmMemberStatusEdit instead of frmMemberStatusAdd:
    ... WHERE [tbl Membership Status].[MEMBER/BUSINESSNAME]=[Forms]![frmMemberStatusEdit]![Member/BusinessName]

    2. Is the ID field in "tbl Membership Status" a text field? In that case, try using

    rs.FindFirst "[ID] = " & Chr(34) & Me![lstStatusHistory] & Chr(34)

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmark Problem With List (2000 / SP3)

    >>I'm still utterly . "tbl Database" contains a field ID that is linked to MEMBER/BUSINESSNAME in "tbl Membership Status", but there is also a field LASTNAME/BUSINESS that seems to have a connection with MEMBER/BUSINESSNAME. And "tbl Membership Status" also contains a field named ID.

    I'm sorry Hans. Yes each table does have an "ID" catagory as the unique ID. I guess I should give one or the other a different name.

    Two things to try:

    >>1. Perhaps the condition in the row source of the list box should refer to frmMemberStatusEdit instead of frmMemberStatusAdd:
    ... WHERE [tbl Membership Status].[MEMBER/BUSINESSNAME]=[Forms]![frmMemberStatusEdit]![Member/BusinessName]

    I haven't had a chance to try this but am confused as to why I would change refer to frmMemberStatusEdit as the form I am working with is frmMemberStatusAdd (I may have typyed the name wrong to you originally as there is a form in the database called frmMemberStatusEdit) I sent the code directly from the SQL view.

    >>2. Is the ID field in "tbl Membership Status" a text field? In that case, try using

    rs.FindFirst "[ID] = " & Chr(34) & Me![lstStatusHistory] & Chr(34)


    No, in each table [ID] is an autonumber. In Tbl Database it refers the to the member, in Tbl MemberShip Status it refers to each status that is entered.

    Alicia

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bookmark Problem With List (2000 / SP3)

    Hi Alicia,

    If ID is numeric, it seems to me that the join in the SQL is wrong: ... ON [tbl Database].ID = [tbl Membership Status].[MEMBER/BUSINESSNAME] ...
    The left hand side is numeric, and the right hand side is a name, so a text, I assume.

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmark Problem With List (2000 / SP3)

    Thanks for bearing with me Hans.

    Tbl Database has an autonumber field called [ID] set as the primary Key. Tbl Membership Status also has an autonumber field called [ID]which isn't meant to correspond to the member name but rather to individual statuses, and is set as the primary key. Tbl membership has a field called Member/BusinessName which looks back to Tbl Database to Find the member name. This no new renewal statuses are added to members who aren't in the demographic database. When I tried to join look back to the LastName/Business field in Tbl Database I got an error message that said there was no primary key. When I joined it to the ID the referential integrity was able to be set.

    >>If ID is numeric, it seems to me that the join in the SQL is wrong: ... ON [tbl Database].ID = [tbl Membership Status].[MEMBER/BUSINESSNAME] ...

    What would you suggest??


    >>The left hand side is numeric, and the right hand side is a name, so a text, I assume.

    This is correct.

    Leesha

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bookmark Problem With List (2000 / SP3)

    I don't understand this at all. You must join tables on fields of the same type: numeric vs numeric, or text vs text. You seem to have been able to create a join, and set relational integrity on a text field (Member/BusinessName) vs a numeric field (ID); this should have been impossible.

    I don't think we'll get further with this the way it is. If possible, could you post a stripped down version of your database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]Perhaps I or another Lounger will be able to find how to make it work...

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmark Problem With List (2000 / SP3)

    Hi Hans,

    Now I'm really stumped. Every time I've used the lookup wizard to point a name in one table back to a name in another table - with both being text, I've gotten an error message re a primary key. So, if I point it to the ID box the referential integrity has taken. Your comment re numeric to text being impossible made my go back and look and sure enough the member/business name field in tbl Membership Status is now a numeric field!!! I've looked at other databases I've done and its the same in all because the error message has always been the same. Thus, when I'm doing reports or forms, queries etc. I always have to use the actual names fields from the original database the that was looked back to so that I don't get numbers. Now I'm sure you're totally confused. I'm so sorry.

    Anyway, I've stripped the thing bare and the smallest I can get it to, zipped is 132kb and that is sending the tables which are split separately.

    Thank you,
    Leesha

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bookmark Problem With List (2000 / SP3)

    Hello Alicia,

    Are you sure that you have removed all tables, forms etc. that are not necessary to understand the problem you are having? And that you have removed most records from the remaining tables?
    One other thing you can do is convert the database to Access 97 format; this usually results in a substantial reduction in size compared to Access 2000 format, so you may be able to get it below 100 KB. (See Tools | Database Utilities | Convert to Prior Access Database Version)

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

    Re: Bookmark Problem With List (2000 / SP3)

    Are you getting yourself confused between what you see in a field with a combobox lookup (i.e., you see a name but the field is actually a numeric field) and the actual value stored in the the field? If your lookup has more than one column, then what you see is probably not be the actual value in the field. Field lookups in tables are more trouble than they're worth in my opinion. Yes, they mean that when you create a form based on that table, you will automatically get a combobox for that field, but they cause so much confusion among people who are just learning to use Access that they're a feature I could happily live without!
    Charlotte

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmark Problem With List (2000 / SP3)

    Yep, I stripped it to bare bones. Converting to 97 helped. Here's the attached file.

    Leesha
    Attached Files Attached Files

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmark Problem With List (2000 / SP3)

    At this point I'm not sure what I'm looking at. When I look back to the tables in design view they show the member/business name in frm Membership Status as being a numeric field. What do you suggest I do vs using the wizard in order to have the same functionality of a lookup box via wizard? I just uploaded the stripped down version of the database to Han's last post.

    Thanks!

    Leesha

Page 1 of 2 12 LastLast

Posting Permissions

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