Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    15

    Unanswered: ADO Recordset update error

    I have been so far learn and implement at the same time an Access 2007 project OK. The last problem is that when I use the ADO Recordset to change an asset record of an asset-table, which has a look-up field , hierarchyID, to another table, I got runtime error of -2147467259 (80004005) that tells me I cannot change it since there is a related record with the hierarchy-table. How can I fix this. By the way, the relationship between the table is just referrential.
    Attached is my code. It's is the get-it-work, i know I need to organize it better. Advices are very much appreciated.
    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The error you indicated (related record) is usually a sign that a record in the main relational table doesn't exist for the relational record to be created. Sometimes a simple Refresh command (if adding new records) on your main form is needed to then create the relational record.

    First try doing it manually in the table. If that works, then you know it's form related. On the subform (or form), I will usually make sure the linking field is on the subform and set the defaultvalue to =Forms!MyMainForm!LinkingIDField

    But I could be offbase here. I'll try to look at your example when I get a chance.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2009
    Posts
    15
    Actually, all of this tracking and updating are behind the sceen. Let me tell you how the program work.

    The main form has 4 tabbed pages; its ControlSource is a qryAllAsset which is a union of 5 "select * from table inner join with an hierarchy-table, etc" ; I did it for easy since the 5 system data are independent of each other but reside in the same LAB. The first page has data analysis, no form involved, just labels and some txtboxes show result of DCount; the second page has a continuous form that show all 5 system assets base on the main form controlSource, and 6 button to filter by system or by all. The third page has 5 hierarchy subforms; I cannot make parent/child link on them since if I do so, each subform as well as the 5 asset-subform on the second page, only show the first entry although they were designed in continuous form. The fourth is the one I am trying to finish.

    Specifically, it has two button to choose Add new asset and another to change Status of the asset. The AddNew button and 5 add-only subform work fine, and each of them only has one field parent/child link is the AssetID.
    The Change-Status button select two option-groups for asset-system and field-category, one search txbox and one Status-combobox.
    When user select this button, the first search is to find the SPARE record, get info to fill half of the transaction logging, track-record, field, then it exit the sub-procedure and return to the tabbed page for get user's input for the second search. In the code, to-be-Out is the second asset, the badRec recordset is for it; the to-be-In is the first asset, the spareRec is for it.

    Since I have to search for the record, I have to use ADODB recordset and connection to make it easier because the application will be loaded and run on a sharepoint. Everything running fine until I try to update and it did not work. And all of this to show you details of the project.

    Please help, I have due date Friday this week. Thanks
    Last edited by cspectra; 11-28-09 at 18:53.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The recordsource for your form or subforms are typically based on only 1 table (not a bunch of linked tables). If you try to base your form/subform's recordset (ie. recordsource) by linking together 5 or 6 tables together, you'll most likely encounter problems in some way or another. 1 table per form (typically). If you need to get values from other tables on a single form/subform, use comboboxes, listboxes, dlookups, or expressions in unbound fields on the form.

    Is this by chance a school project?
    Last edited by pkstormy; 11-30-09 at 22:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Your text file in the attachment doesn't make quite sense to me (but I don't use sharepoint.)

    Maybe someone else can make sense of this but if it's an extract from your mdb, you'll need to supply the mdb for me to help you.

    If all you're trying to do is make a search type form (which the text file looks like it's trying to do), I suggest you perhaps look at the many, many examples in the code bank on how to easily do this (there's a reason we upload these examples to help others.)

    It just seems like you're taking the long route to do something which is fairly easy to do.
    Last edited by pkstormy; 11-30-09 at 22:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Oct 2009
    Posts
    15

    Thumbs up

    pkstormy;
    Thanks for following up.
    I got it by temporarily changing the View-Only subForm to normal mode and it go. I will switch it back to verify later. The next step will be implementing password enforcement on all Add/Modify buttons, but it is out of this question. I will open another thread. Thanks for all your tips, I'd learn a lot!

Posting Permissions

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