Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    subform problem (97)

    I

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform problem (97)

    Just a quick guess but you may need to remove the word subform from the gotorecord method argument "tblNewMainCopy subform".

  3. #3
    dave_hill
    Guest

    Re: subform problem (97)

    GoToRecord won't work in this case, because it will always look to the main form for the record.
    Edited by Charlotte

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: subform problem (97)

    I find the best way to maneuver in a subform from a mainform is via the subform recordsetclone. First of all, you need to know the name of the subform control on the mainform. The subform control name may or may not be the same as the name of the subform itself. I'll just use subformcontrolname.
    <pre>with Me.subformcontrolname.form.recordsetclone
    .findfirst "refno=" & txtrefno
    if .nomatch=true then ' means you didn't find it
    exit sub
    end if
    Me.subformcontrolname.form.bookmark = .bookmark
    end with
    </pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform problem (97)

    Hi Mark,
    I tried your suggestion but the .noMatch=True criteria is set for all values I query on, whether they

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: subform problem (97)

    Are your fields you are search against numeric or strings? This would affect how you form the FindFirst where string.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: subform problem (97)

    Try testing this way:
    <pre>if Not .nomatch then ' means you did find it
    Me.subformcontrolname.form.bookmark = .bookmark
    End If</pre>

    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform problem (97)

    no Charlotte, If Not .NoMatch Then did not make any difference.
    Mark, I only tested your code for 1 field, refNum & that comes from a query, a concatenation of 2 number fields. InvoiceNum, the one I didn

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform problem (97)

    Unfortunately, no Charlotte, did not make any change at all.

  10. #10
    dave_hill
    Guest

    Re: subform problem (97)

    Mohammed-
    All code shown here should go in the form module. Replace generic names
    with the name of the objects in your app.

    I'll first address the subform control source issue.
    If you don't want any records to show when you open a form, you must have
    the subform's controls .ControlSource property empty.

    In code it would look like :

    Sub Form_Open
    SetControlState False
    ... whatever else
    End Sub

    Sub SetControlState(pbState as Boolean)
    Dim sfrm as Form

    Set sfrm = Me!SubformName.Form
    With sfrm
    If pbState Then
    !Text1.ControlSource = "Field1"
    !Text2.ControlSource = "Field2"
    !Text3.ControlSource = "Field3"
    Else
    !Text1.ControlSource = ""
    !Text2.ControlSource = ""
    !Text3.ControlSource = ""
    End If
    End With

    End Sub

    I would recommend re-naming the Ref# and Invoice# fields to RefNum and
    InvNum. Even though the # sign is allowed in naming fields, you always
    have to bracket the field name when you refer to it in code (i.e. [Ref#])

    In the AfterUpdate event of the RefNum and InvNum fields:

    Private Sub RefNum_AfterUpdate()
    Dim strSQL as String
    Dim sfrm as Form
    ' reset the control source for controls so they will now accept data
    SetControlState True

    Set sfrm = Me!SubformName.Form
    strSQL = "SELECT Field1, Field2, Field3 " _
    & "FROM Table1 " _
    & "WHERE Field1=" & RefNum & ";"
    sfrm.RecordSource = strSQL
    sfrm.Requery
    End Sub
    This assumes that Field1 corresponds to your Reference number field. Of
    course, error checking isn't included here for brevity.
    The after update event of InvNum would have similar code.

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: subform problem (97)

    I just sort of assumed since you used the phrases "refnum" and "invoicenum" that you were dealing with numbers! You must modify your code to enclose the value of the controls in apostrophes, like this:
    <pre> invoicenum='" & txtinvoicenum & "'"
    </pre>


    And invoicenum is a memo field? Why not a text field?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    melhado
    Guest

    Re: subform problem (97)

    Mark,

    Is this the same idea if I want to move to a new record in a subform from code on the main form?

    I have a command button on the main form with code that asks user for criteria, then finds the main form record meeting it, and brings up the related subform records. It then moves to the subform record and places the focus where I want it (which you helped me with, thank you). Works fine.

    What I want to do is have that subform record open to a new record. I discovered that DoCmd.GoToRecord doesn't do subforms, even though the line above it sets the focus to it.

    Here is the pertinent piece of code; Hopefully, it is enough. It is DAO and the staret of it sets all references and declares variables; the rst is Set as the main form.recordset clone (Set rst =Me!RecordsetClone), input boxes are presented to gather the criteria, a message box asks for confirmation, and then it starts the find on the return of the message box OK:

    <font color=blue>
    If MsgBox(strConfirm, 289, "PLEASE CONFIRM REQUEST") = vbOK Then

    <font color=448800>'find the record</font color=448800>
    rst.FindFirst strSearch
    Forms!frmL60Tin!sfTin.SetFocus
    <font color=red>This didn't work; I thought setting the focus would tell DoCmd which to advance</font color=red> DoCmd.GoToRecord , , acNewRec
    Forms!frmL60Tin!sfTin.Form!txtOp.SetFocus

    End If <font color=448800>'end if of confirmation message box

    'continue with code to find record and
    'report if it is valid or not</font color=448800>
    If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark

    Else
    .....</font color=blue>

    It then give message that record not found .. and so on... trhough error trapping and setting the rst to Nothing .. etc.

    With the exception of getting the subform to show a new record, it works.

    How would I (or would I ?) use
    <font color=blue>with Me.subformcontrolname.form.recordsetclone</font color=blue>
    to tell it to go to the new record of the subform after it finds the main one, and does it make a difference whether that new record is the first one?

    Would it be as simple as just setting the subform's cycle property to Current record and Data Entry to yes?

    thanks for feedback

    'dave

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: subform problem (97)

    >>Forms!frmL60Tin!sfTin.SetFocus
    DoCmd.GoToRecord , , acNewRec<<

    Try this instead:

    Forms!frmL60Tin!sfTin.setfocus
    RunCommand acCmdRecordsGoToNew
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    melhado
    Guest

    Re: subform problem (97)

    Mark,

    Worked like a charm. Thanks

    At least I had the idea right that setting focus was the first step. Had no idea that RunCommand was also a method, thought it was only part of the DoCmd object.

    Thank you for that tip as well

    'dave

  15. #15
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: subform problem (97)

    >>Had no idea that RunCommand was also a method, thought it was only part of the DoCmd object.<<

    It is actually, but the DoCmd. is implied if not specified. I don't know why your other doCmd didn't work, as I only tried the RunCommand version.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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
  •