Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More subform navigating (2000 (all updates))

    I realize this is a lengthy chunk of code, but I stripped a good lot of it out to shorten it.

    The critical part with which I need help is that which begins with

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

    Re: More subform navigating (2000 (all updates))

    This is very confusing. You *do* have a loop to process all subform records for deletion, but you don't have a loop to process subform records for copying to a new record. If you would add a loop now, you would create a new record in the main form for each subform record that meets the criteria. Is that really what you want?

    Could you try to explain again what you want to accomplish?

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More subform navigating (2000 (all updates))

    (Edited by HansV - inserted <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags to preserve spacing)

    Hans
    Sorry for the confusion. I *don't* want to create a new record in the main form for each subform record that meets the criteria. I *do* want to move the subform records that meet the criteria to the subform for the newly created record (that's the loop I need to work out).

    There are two sequences at work here. Maybe a narrative background will help clarify.

    The main form record shows John & Susan Smith...
    LastName: Smith
    FirstName1: John
    FirstName2: Susan

    Then there is a subform that holds Envelope assignments (for purposes of donations). If both John and Susan make donations under the same EnvNbr the AssignedTo designation is "A". If they make separate donations, John has an AssignedTo designation of "B" and Susan has an AssignedTo designation of "C"
    The subform for John & Susan Smith's main form record would show...<pre>EnvNbr StartDate EndDate AssignedTo
    100 date date B
    136 date date C</pre>

    Now suppose that, for reasons of death or separation or whatever circumstances, Susan Smith is to be removed from the joint record and a new record created for her alone. I can create the new record, no problem. Dealing with the subform is a bit more involved and problematic, mainly because there are two sequences.

    First sequence...
    Move any subform records with an AssignedTo designation of "C" (Susan's stuff) to the subform for her individual record.

    Second sequence...
    Go back to the bookmarked record, now belonging to John Smith alone, and remove from the subform any subform records with the AssignedTo designation of "C" but not touching any with an "A" or "B" designation.

    So you are correct that I already have a loop to deal with the Second Sequence, and it is working fine.

    I have to get the First Sequence right -- moving the subform records that meet the criteria "C" to the subform for the newly created Susan Smith main form record.

    At the end of both sequences, things would look this way.<pre>Main form record Smith, John
    subform EnvNbr StartDate EndDate AssignedTo
    100 date date B

    Main form record Smith, Susan
    subform EnvNbr StartDate EndDate AssignedTo
    136 date date C</pre>

    Does that clarify? (sorry that I can't seem to make the subform information line up in columns on this screen)

    Tom

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

    Re: More subform navigating (2000 (all updates))

    Now that I'm looking at it again, it appears to me that it can be done in a more efficient way. There is no need to move and delete subform records. Instead, execute an update query that changes the EnvNbr to 136 for the records with EnvNbr = 100 and AssignedTo = "C". You will need to requery the form after that.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More subform navigating (2000 (all updates))

    Hans
    Ummm, I wish it were that simple. But there will be *no* (EnvNbr = 100 And AssignedTo = "C").

    There are two records already present in the subform
    EnvNbr = 100, AssignedTo = "B"
    EnvNbr = 136, AssignedTo = "C"

    It's the already existing (EnvNbr = 136 And AssignedTo = "C") that I have to deal with. Which means, I think, moving it to the new record.

    Tom

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

    Re: More subform navigating (2000 (all updates))

    Sorry, I was inaccurate, but the basic idea remains the same. The subform records have a field that links them to the record in the main form. This is the field that has to be changed from the current one to the value in the new record in the main form.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More subform navigating (2000 (all updates))

    Hans
    I'm trying to get my head around it buI feel as if I am wading in where angels fear to tread!

    What links the subform to the main form is the UniqueID field.

    What I am having difficulty doing is selecting an EnvNbr from the original record only when the AssignedTo designation is "C". And the trouble is that over time there could possibly be more than one with the "C" designation (it's hard to explain; it has to do with donations categories requiring a different EnvNbr sequence). So I need to be able to select any, or all, with the "C" designation and get them...somehow...to the new record.

    Currently, I am trying to store the data from the subform fields in temporary variables in order to create the new record. I think that's the problem. I'm having trouble getting the logic correct to select a record only if it has a "C" designation, and I'm nowhere close to getting the logic if there is more than one "C" designation. That is what has made me think I need a loop.

    However, If I follow your logic about the Update Query, is there a way to embed that in the New Record process, so by some SQL it doubles back and changes the UniqueID in the original record to that of the new record...but, again, only if the AssignedTo designation is "C". Seems to me that some loop is still required, regardless of where I put it?

    Tom

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

    Re: More subform navigating (2000 (all updates))

    I have attached (necessarily incomplete code) to do what you want. Instead of a loop, an update query is executed.
    Attached Files Attached Files

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More subform navigating (2000 (all updates))

    Hans
    I had been trying to work on an SQL. After adding the completions, the SQL you sent me works perfectly.

    Once again, thanks so much for your help.

    Tom

  10. #10
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More subform navigating (2000 (all updates))

    Hans
    I would appreciate clarification of something...for my own learning.

    Why in the case of this SQL is the customary closing quote and semicolon (;") not required? In fact, it I attempt to put the ; and " in, I get an Expected End of Statement error.

    Tom

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

    Re: More subform navigating (2000 (all updates))

    A closing semi-colon is never required. It is part of the "official" definition of SQL statements, but they work just as well without them. A closing quote is NEVER part of an SQL statement, but if you set a string variable to an SQL string, you must close literal (non-variable) strings with a quote:

    strSQL = "SELECT * FROM tblOrders"

    or

    strSQL = "SELECT * FROM tblOrders;"

    In the code I posted earlier, the SQL string ends with a variable name, therefore there is no quote at the end. If you prefer to close the SQL statement with a semi-colon, do it as follows:<pre>strSQL = "UPDATE tblChildren SET MemberID = " & lngNewUniqueID & _
    " WHERE AssignedTo = 'C' And MemberID = " & lngOldUniqueID & ";"</pre>

    Note the quotes before and after the semi-colon - it is a one character string by itself

  12. #12
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More subform navigating (2000 (all updates))

    Hans
    Thanks for your explanation about the semicolon.

    In my testing process for creating a new record and moving subform records, a new problem has arisen. I will show abbreviated code below.

    The problem seems to start at the first time things move to the subform on the old record. At the lines following these.
    ' Go to subform on Old Record
    ' Put End Date on Envelope #s assigned to "C"

    Things work perfectly in 5 situations. (1) if the EnvNbr field is Null, (2) If there is a record with an AssignedTo field = "A", (3) if there is a record with an AssignedTo field = "B", (4) if there is a record with an AssignedTo field = "A" as well as "B" and/or "C" , (5) if there is a record with an AssignedTo field = "B" as well as "C"

    The code runs into trouble when there is only one AssignedTo and that is "C". What happens then is Error 3197, which indicates that another user is trying to update the form at the same time, and the screen update stops on the subform in the old record. Of course, it isn't the case because there is no other user working on the database.
    If I click on the OK button on the Error pane, the records update properly. But I can't quite get a handle on why this is happening in the first place. I have tried a number of possibilities as to how to change things around so this doesn't happen but to no avail so far.

    If you can spot something problematic, I would appreciate it.

    Thanks.
    Tom

    ' Keep track of location in database and Old Record ID
    varBookmark = Me.Bookmark
    lngOldUniqueID = Me.UniqueID

    ' Create new record
    DoCmd.GoToRecord , , acNewRec

    ' Retrieve New Record ID
    lngNewUniqueID = Me.UniqueID

    ' Go back to the old record to Edit
    Me.Bookmark = varBookmark

    ' Go to subform on Old Record
    ' Put End Date on Envelope #s assigned to "C"
    Me.Form![tblEnvelopeNumbers subform].SetFocus
    If IsNull(Me.Form![tblEnvelopeNumbers subform]![EnvNbr]) Then
    GoTo UpdateForm
    Else
    Set rst = Me![tblEnvelopeNumbers subform].Form.RecordsetClone
    With rst
    .MoveFirst
    Do Until .EOF
    .Edit
    If !AssignedTo = "C" And !EndDate > Date Then !EndDate = Date - 1
    .Update
    .MoveNext
    Loop
    End With

    ' Update query to move "C" records to new record's subform
    strSQL = "UPDATE tblEnvelopeNumbers SET UniqueID = " & lngNewUniqueID & _
    " WHERE AssignedTo = 'C' And UniqueID = " & lngOldUniqueID
    DoCmd.RunSQL strSQL

    ' Go to subform in new record
    ' change Envelope #s to "A"
    Me.Form![tblEnvelopeNumbers subform].SetFocus
    With rst
    .MoveFirst
    Do Until .EOF
    .Edit
    !AssignedTo = "A"
    .Update
    .MoveNext
    Loop
    End With

    ' Move to subform in old record to set remaining Envelope #s to "A"
    Me.Bookmark = varBookmark
    Me.Form![tblEnvelopeNumbers subform].SetFocus
    If IsNull(Me.Form![tblEnvelopeNumbers subform]![EnvNbr]) Then
    GoTo UpdateForm
    Else
    With rst
    .MoveFirst
    Do Until .EOF
    .Edit
    !AssignedTo = "A"
    .Update
    .MoveNext
    Loop
    End With
    Set rst = Nothing

    ' Update form
    UpdateForm:
    Me.Requery
    End If
    End If

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

    Re: More subform navigating (2000 (all updates))

    If there is only one subform record, and its AssignedTo field is "C", the update query is pulling the rug from under the subform, as it were - it tries to move the subform record to the newly created main record. This would leave the subform empty. This kind of thing can be tricky, Access sees the code manipulation as a separate "user". Insert a line

    DoCmd.GoToRecord , , acLast

    immediately above the DoCmd.RunSQL statement. Perhaps that will circumvent the problem.

  14. #14
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More subform navigating (2000 (all updates))

    Hans
    Well, that didn't do it. Same error 3197.

    And despite the fact I said in my last post that after pressing the OK button on the Error pane the Update continued satisfactorily that isn't totally true. It stops on the subform record in the "old" record. When OK is pressed, the focus remains on that old subform record, giving the appearance that the SQL isn't proceeding. When I go to the new record manually, the SQL runs, it updates that subform, moving the subform record from the old subform to the new one, but then it doesn't complete the remainder of the code manipulation in that new subform record.

    As for leaving the subform empty, the subform is left empty if there is only 1 AssignedTo "A" or "B" record. I can't figure out why the only time it's problematic is if there is only 1 AssignedTo "C" record.

    Tricky is right.

    Tom

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

    Re: More subform navigating (2000 (all updates))

    Try to do it as follows:
    - Prepare the SQL statement
    - Close the form
    - Execute the SQL statement
    - Reopen the form

    If that doesn't work either, what is the exact line on which the error occurs?

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
  •