Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clear data in a column (2003)

    Hi all,

    I am trying to find a way that when I click a button to append data into a table I want it to remove current data from one of the columns in the current rows of data before appending the new data.

    The column is called validated and it show the stautus of a record. I click a button which copys and pasteappends data then make the nessecary changes automaticaly like a new validated value.

    Heres a piece of the code from the button

    Screen.MousePointer = 11
    ' Set focus to the subform
    Me!FrmOUT0506Sub.SetFocus

    ' Sort the form on APPTID
    Me.OrderByOn = True
    Me!FrmOUT0506Sub.Form.OrderBy = "ApptID"

    ' Duplicate the last record
    RunCommand acCmdRecordsGoToLast
    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend

    ' Set the new values
    Me!FrmOUT0506Sub.Form.ApptID = Me!FrmOUT0506Sub.Form.ApptID + 1

    If Me!FrmOUT0506Sub.Form.F_LSPEC = "FRAC" Then
    Me!FrmOUT0506Sub.Form.F_CLINIC = "TOGC"
    Me!FrmOUT0506Sub.Form.F_SPECEDPC = "11000"
    GoTo Continue
    Else
    End If
    If Me!FrmOUT0506Sub.Form.F_LSPEC = "COLP" Then
    Me!FrmOUT0506Sub.Form.F_CLINIC = "GYNGC"
    Me!FrmOUT0506Sub.Form.F_SPECEDPC = "50200"
    GoTo Continue
    Else
    End If

    Me!FrmOUT0506Sub.Form.F_CLINIC = Me!FrmOUT0506Sub.Form.F_LSPEC & "GC"

    Continue:

    Me!FrmOUT0506Sub.Form.F_APPTDATE = CDate(txtDate)
    Me!FrmOUT0506Sub.Form.OutcomeCode = txtOutcome
    Me!FrmOUT0506Sub.Form.F_STATUS = "L"
    Me!FrmOUT0506Sub.Form.F_FATT = "2"
    Me!FrmOUT0506Sub.Form.F_DNA = "5"
    Me!FrmOUT0506Sub.Form.F_MEDSTAFF = " "
    Me!FrmOUT0506Sub.Form.F_CONSCLIN = ""
    Me!FrmOUT0506Sub.Form.F_LCONS = " "
    Me!FrmOUT0506Sub.Form.Validated = "Validated"

    Basically for the record I'm currently viewing a want all the data in the subform under the column 'validaterd' to delete first.

    Many thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: Clear data in a column (2003)

    Let's say that
    - The subform is based on a table named tblSub.
    - The subform is linked to the main form by a field called ID.
    - ID is a number field.

    You could do something like this:

    Dim strSQL As String
    ' SQL statement for update query
    strSQL = "UPDATE tblSub SET Validated = Null WHERE ID=" & Me.ID
    ' Temporarily suppress warnings
    DoCmd.SetWarnings False
    ' Execute the SQL statement
    DoCmd.RunSQL strSQL
    ' Turn on warnings again
    DoCmd.SetWarnings True
    ' Perhaps you need to refresh the subform
    ' Me.FrmOUT0506Sub.Form.Refresh

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear data in a column (2003)

    Thanks again.

    I am getting a data type mismatch on the following line

    DoCmd.RunSQL strSQL

    I am linking by ReferralID which is my uniquie identifer and is a text box although displays only numbers.

    Thanks

    Kevin
    Regards
    Gerbil (AKA Kevin)

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

    Re: Clear data in a column (2003)

    What is the field type of ReferralID in the tables?

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear data in a column (2003)

    Text also

    Thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: Clear data in a column (2003)

    Change the line

    strSQL = "UPDATE tblSub SET Validated = Null WHERE ID=" & Me.ID

    to

    strSQL = "UPDATE tblSub SET Validated = Null WHERE ID=" & Chr(34) & Me.ID & Chr(34)

    again, with the appropriate names substituted. Chr(34) is the double quote character ".

  7. #7
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear data in a column (2003)

    Thanks Hans,

    You had already taught me that before I don't know why I didn't do that in the first place I been using it alot since you taught me before.

    Many thanks works perfect
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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