Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problems problems (Access 2000)

    I do some work for a charity, and I agreed (it now seems very foolishly) to look at putting their charity donations into a database. Currently, these are recorded on paper and then, sometimes, typed into an Excel spreadsheet. Creating the necessary DB seemed so easy!

    Because they have over 15000 different donors, looking through a list to see if the latest donation comes from a donor that has already given is unworkable. Also, because chains of shops contribute, uniqueness of donor is only ensured by name and address
    Silverback

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

    Re: Problems problems (Access 2000)

    To start with 4: yes, doing anything beyond the basics in Access involves writing Visual Basic code (excuse the pun)

    1./3. You can use an unbound combo box (i.e. its Control Source is blank) to locate a donor; you could also use a list box for this purpose. The easiest way to create one is using the Combo Box wizard:
    - Make sure that Control Wizards are ON (second button on the Toolbox is down).
    - Create a combo box on the form.
    - Select the 3rd option, then follow the instructions.

    2. Do you really mean a subform in the technical sense of the word, i.e. a form placed on the main form?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems problems (Access 2000)

    Hello Hans
    Thank you so much for your help so far.
    After I posted my questions yesterday, I had some breakthroughs last night.
    1. I reread Help and the combo box wizard (carefully this time) and found the option to jump to the record specified by the typing/selection in the combo box. This is working OK apart from one thing (see later)
    2. Sorry to mislead - Form 2 is not a sub-form; it's a form that is invoked by clicking a command button on Form 1. I have managed to force a requery of the Form 1 combo list. I created an event procedure for the Form1 combo box OnEntry event and used [objectname.]Requery. This successfully updates the donor list so that when back on Form 1 and the combo box drop down is clicked, the new donor entry just made on Form 2 is present.
    New Question : is the OnEntry event the correct combo box event to hang this procedure on?
    3. Sorted by getting 1 right.
    4. Thought so.
    New Question : Choosing the 'jump to the selected record' option for the combo box caused Access to generate an OnUpdate procedure. However, when the name typed in the combo box is not in the list, the user realises that it is necessary to create a new donor. The field has to be cleared first (if it isn't, Access errors with an Item not in List error) but the typing has triggered the OnUpdate event. Consequently, when the a New Donor button is clicked, a Run Time Error 94 - Invalid use of null is generated. I presume this is because the OnUpdate procedure is attempting to jump to a record referenced by a null DonorID (?). How do I avoid this happening, please?
    Thanks again for your help - making progress! (although haven't managed to get the Switchboard Add-In working even though I followed the MS 'fix'; will keep trying)
    Silverback
    Silverback

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

    Re: Problems problems (Access 2000)

    What you are looking for is the On Not In List event of the combo box. For this to work, you must set the Limit To List property of the combo box to Yes.

    Searching for NotInList in this forum will turn up many threads about the Not In List event, for example <post#=314477>post 314477</post#> and <post#=146637>post 146637</post#>.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,430
    Thanks
    1
    Thanked 33 Times in 33 Posts

    Re: Problems problems (Access 2000)

    There are some commercially available products that keep track of donors and their donations, which you might want to investigate before going to alot of work for this charity. Some are rather expensive, but I know of one, Supporter Donation System, that is very reasonably priced .
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems problems (Access 2000)

    Hans
    Thanks again
    Two last questions (for a while)
    Q1 :I've got the NotInList event working (after a fashion) but before the form opens, there is a generic MS Access error - The text you entered is not in List ; how do I prevent this from appearing, please.
    Q2 : If I OK the Access error, the New Donor form is displayed as I want. When the new donor is entered, and return is made to the main form, the text typed (which generated the NotInList error) is still there. How is this field be cleared, please?
    Silverback

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

    Re: Problems problems (Access 2000)

    Q1. We would have to know the exact setup to be able to help with this.
    Q2. I don't understand why you would want to clear the combo box, but you can set it to Null in the code.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems problems (Access 2000)

    Mark
    Humble apologies for time taken to reply.
    Thanks for your interest in my posting.
    It's a long story, but the bottom line essentially is that the charity wants Access skills. They have a couple of bespoke DB's already and the major one was produced by a company which has now gone out of business. It looks like a bad buy, since the users say that they use only a fraction of the capabilities. Add to this that they have (for charity/tax reasons) had to create another company whose donations have to be kept completely separate from the others, and you can see that this led to a request for Access skills.
    I have dug myself out of the holes I was getting into (thanks to the lounge again) and am enjoying the learning experience - although it is a but frustrating at times. My wife is a relational DB expert (worked for years on supermarket 'loyalty' DBs with up to terabytes of data) but never had to work with 'Front Ends' - only issues like designing DBs and writing SQL queries to produce reports (usually that cornflakes were their best seller!)
    So, we're getting there, but again, I thank you for your interest in my posting.
    Regards
    Silverback.
    Silverback

Posting Permissions

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