Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Update field question

Discussion in 'Information Technology' started by pokdbz, Jul 28, 2006.

  1. pokdbz

    pokdbz
    Expand Collapse
    Guest

    I have this statement:
    DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
    = " & Me.Studyholder & " WHERE Patient_Registry.ScreenID = '" &
    Forms![NewStudy]![ScreenID] & "'"


    When I run it a box comes up and whatever I have in Studyholder it has up at
    the top of the little box. Then when I type something it updates the field.
    What am I doing to make this box come up it should just update on its own
    without being prompted.

    Patient_Registry is the table which needs the update
    StudyTitle is the field in Patient_Registy that needs the update
    Studyholder is the text I would like to be updated
    The current form is NewStudy
    ScreenID is the specific place I would like it to be updated at.
     
  2. Loading...


  3. IT Pro Dave

    IT Pro Dave
    Expand Collapse
    Guest

    Is the value for Studyholder a string? Try

    DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
    > = '" & Me.Studyholder & "' WHERE Patient_Registry.ScreenID = '" &
    > Forms![NewStudy]![ScreenID] & "'"



    "pokdbz" wrote:

    > I have this statement:
    > DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
    > = " & Me.Studyholder & " WHERE Patient_Registry.ScreenID = '" &
    > Forms![NewStudy]![ScreenID] & "'"
    >
    >
    > When I run it a box comes up and whatever I have in Studyholder it has up at
    > the top of the little box. Then when I type something it updates the field.
    > What am I doing to make this box come up it should just update on its own
    > without being prompted.
    >
    > Patient_Registry is the table which needs the update
    > StudyTitle is the field in Patient_Registy that needs the update
    > Studyholder is the text I would like to be updated
    > The current form is NewStudy
    > ScreenID is the specific place I would like it to be updated at.
     
  4. bcMcKinley@gmail.com

    bcMcKinley@gmail.com
    Expand Collapse
    Guest

    This should work for you:
    '********Code Begin

    Dim strQry As String

    strQry = "UPDATE Patient_Registry SET Patient_Registry.StudyTitle =
    Forms!NewStudy.Studyholder WHERE Patient_Registry.ScreenID =
    Forms!NewStudy.ScreenID"

    DoCmd.RunSQL strQry

    '********Code End

    Make sure to put the strQry= line all on one line or break it up with
    underscores
     
  5. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    The way you posted it:

    DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
    = " & Me.Studyholder & " WHERE Patient_Registry.ScreenID = '" &
    Forms![NewStudy]![ScreenID] & "'"

    The way it should work:

    DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
    = '" & Me.Studyholder & "' WHERE Patient_Registry.ScreenID = '" &
    Forms![NewStudy]![ScreenID] & "'"

    The only difference is putting the single-quotes in around Me.Studyholder,
    as you did with ScreenID's comparison to Forms![NewStudy]![ScreenID].

    If StudyTitle is a string field, and Me.Studyholder as well, this should
    work.
     
  6. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    even better(?)

    Dim Db AS DAO.Database

    Set Db = Access.CurrentDb
    Db.Execute "....", DAO.DbSeeChanges ' for paranoid <g> backend's
    MSSQL/Oracle


    Pieter
    (and yes I prefer QueryDefs with parameters et al)

    "Rick Wannall" <cwannall@yahoo.com> wrote in message
    news:zKocg.75534$_S7.37038@newssvr14.news.prodigy.com...
    > The way you posted it:
    >
    > DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
    > = " & Me.Studyholder & " WHERE Patient_Registry.ScreenID = '" &
    > Forms![NewStudy]![ScreenID] & "'"
    >
    > The way it should work:
    >
    > DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
    > = '" & Me.Studyholder & "' WHERE Patient_Registry.ScreenID = '" &
    > Forms![NewStudy]![ScreenID] & "'"
    >
    > The only difference is putting the single-quotes in around Me.Studyholder,
    > as you did with ScreenID's comparison to Forms![NewStudy]![ScreenID].
    >
    > If StudyTitle is a string field, and Me.Studyholder as well, this should
    > work.
    >
    >
    >
     

Share This Page