Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to replace string in sql (Access 2000)

    I have a working SQL for updating as follows:

    strSQL = "UPDATE Products SET Students = Students1 - " & [Forms]![frmClasses]![SubformClasses].[Form].[benches] & strWhere

    This sql works.For a better view i want to replace a part of the sql with a new variable called MySubform :


    Dim MySubform As String
    set MySubform = [Forms]![frmClasses]![SubformClasses].[Form]


    Then i tried to insert this part into my sql as follows

    strSQL = "UPDATE Products SET Students = Students1 - " & MySubform.[benches] & strWhere

    But now i receive an error, the part MySubform is not recognized in he new sql.

    Could somebody tell me what i have missed?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: how to replace string in sql (Access 2000)

    You would have to declare MySubform as a Form, not a String, to set it to the subform's Form property. Instead of this, I would do it this way:

    Dim strBenches as String
    strBenches = [Forms]![frmClasses]![SubformClasses]![benches]

    The SQL statement will then read:

    strSQL = "UPDATE Products SET Students = Students1 - " & strBenches & strWhere

    HTH

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

    Re: how to replace string in sql (Access 2000)

    If it's from within the form that contains MySubform, this should work:

    strSQL = "UPDATE Products SET Students = Students1 - " & Me.MySubform![benches] & strWhere

    You seem to need the bang (!) rather than the dot (.) when you're addressing subform controls.
    Charlotte

Posting Permissions

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