Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recordset error with subform (Access 97)

    Hi,

    I'm new to Access and hoping someone will be able to point me in the right direction. I've simplified the problem as follows:

    I have 2 forms:
    MainFormTechnician (displays Tech Number and Tech Name)
    SubFormClient (displays Client Number and Client Name)

    The relationship is one to many. I created a combo box on the main form, so when a Tech Number is selected, their clients display in the subform.

    I also need to do this in reverse ie When a Client Number is selected, their Technician displays.

    So I added another combo box to the main form, which contains the Client number. I put some code in the AfterUpdate Event. It works, but then the Tech number combo box fails. I get "Runtime error 2105: You can't go to the specified record. You may be at the end of a recordset." If I then select a value from the Client number combo box, I get "Runtime error 3020: Update or CancelUpdate without AddNew or Edit".

    Any help would be much appreciated, as I don't know much about recordsets.

    Erica.

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

    Re: Recordset error with subform (Access 97)

    Is the combo box for Tech Number bound to the Tech Number field? That could cause problems (a combo box used to search for a record should not be bound.)

    If possible, could you post a stripped down version of the database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) 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]That would enable Loungers to take a look at the problem directly.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset error with subform (Access 97)

    It sounds to me as if you need to have an initial form where you select that you want to view the main form which displays technicians containing a subform of clients or a different main form to display clients with a subform for technicians.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    New Lounger
    Join Date
    Mar 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset error with subform (Access 97)

    Both my combo boxes are unbound.

    I have attached the database (all data is fictional btw).

    Thanks for helping,
    Erica.
    Attached Files Attached Files

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

    Re: Recordset error with subform (Access 97)

    The problem is that the After Update event of the Client Number combo box (Combo 10) modifies the current record: the line

    [Tech Number].Value = y

    changes the value of the Tech Number field. The combo box should only search for a record, not change it. So remove the above line, and add the following line immediately above End Sub:

    Combo8_AfterUpdate

    This will perform the search for the Tech Number.

  6. #6
    New Lounger
    Join Date
    Mar 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset error with subform (Access 97)

    Hans, thank you sooooo much. That's exactly what I wanted to do. Your solution was extremely easy to understand and implement. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Erica. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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