Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    35

    Unanswered: subreport and code help

    I have the following code which places values in a table to keep track of changes made (tblPAIRSCHANGES). It works on every one of my forms, except for one that has a subform. When I make changes in the subform, I get errors such as: "Compile Error, method or data member not found" and "Run Time Error 3265 Item not found in this collection". Is there a way that I have to designate that this field (i.e. UNIT_A_TERM) is on a subform? I hope I explained this right. Thanks!

    Private Sub UNIT_A_TERM_AfterUpdate()
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb()
    Set rs = db.openrecordset("Select*from[tblPAIRSCHANGES]")
    rs.AddNew
    rs![OLDUNIT_A_TERM] = UNIT_A_TERM.OldValue
    rs![UNIT_A_TERM] = UNIT_A_TERM.Value
    rs![DATE] = Now()
    rs.Update

  2. #2
    Join Date
    Nov 2004
    Posts
    23
    You can try this it has worked for me.

    Private Sub UNIT_A_TERM_AfterUpdate()
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb()
    Set rs = db.openrecordset("Select*from[tblPAIRSCHANGES]")
    rs.AddNew
    rs![OLDUNIT_A_TERM] = Forms!(Form Name)!(subform control name).Form!UNIT_A_TERM.OldValue
    rs![UNIT_A_TERM] = Forms!(Form Name)!(subform control name).Form!UNIT_A_TERM.Value
    rs![DATE] = Now()
    rs.Update

  3. #3
    Join Date
    Jun 2004
    Posts
    35
    I must be the biggest moron around, but I can't get this to work. I tried:

    rs![OLDUNIT_A_TERM] = Forms!frmCABLES!frmPAIRS DATA.Form!UNIT_A_TERM.OldValue

    where frmCABLES is the main form and the frmPAIRS DATA is the name of the subform that is on the frmCABLES. I've tried ('s and ['s but I either get a message saying that the declaration character does not match declared data type or main menu cant find the form referred to in a macro or expression.

    Please tell me what I am doing wrong.

  4. #4
    Join Date
    Nov 2004
    Posts
    23
    I tried it this way and it worked

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.openrecordset("SELECT tblPAIRSCHANGES.* FROM tblPAIRSCHANGES;")

    rs.AddNew
    rs![OLDUNIT_A_TERM] = UNIT_A_TERM.OldValue
    rs![UNIT_A_TERM] = UNIT_A_TERM.Value
    rs![Date] = Now()
    rs.Update

    DAO 3.6 Object Library is enabled

    Hope it works for you.

    Jim

  5. #5
    Join Date
    Jun 2004
    Posts
    35
    Now I feel really stupid. I can't get it to work this way either. I don't understand why this won't work. On the same subform, under another field name, it works just fine. I even copied and pasted from one part that worked and then copied the field name in the window of VB to make sure I didn't fat finger anything. I keep getting the "runtime error 3265 Item not found in this collection".
    Last edited by marshals; 11-22-04 at 16:57.

  6. #6
    Join Date
    Jun 2004
    Posts
    35
    I finally figured out why this wasn't working. There were some typos in a related query and once I fixed those, everything worked just fine. Thank you for your help.

    And, of course, I have another dumb question. Same code, tables, etc. I want to put all related changes in one row across the table. For example,

    If I change cable #45 to say it is now cat5, 34 feet long and is on drawing number 254, how can I get all 3 of those changes on one line? Right now, each entry has its own line in the table. I thought maybe a query, then I was looking at iif, then statements, could someone recommend the best (and easiest) way to do this?

Posting Permissions

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