Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2006
    Posts
    35

    Unanswered: Changing a record value

    ok, After spending too much time, I now need some expertise. I should have this figured out since it is so simple.

    I have a table (tblRequest) that has two fields I would like to update using a button. The first field is logical and I would like the value set to Y and the second field is a date field and I would like the value set to now(). The user would simply click on this button and the value in the record for these two fields shown would be changed and updated.

    I have VB6.3 and Access2002. I tried the INSERT INTO without success. I also tried using a macro with the SET VALUE command changed the value in all records not just the open. What am I overlooking?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    INSERT INTO tblRequest([logicalField], [dateField])
    VALUES ('logical value', now())
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hmmmm. you seem to be asking for an UPDATE rather than an INSERT

    assuming...
    your table has primary key "myID" datatype longinteger
    the current record myID is in formfield "thisID"
    logicalField is datatype boolean (True/False)
    dateField is datatype datetime
    then:

    strSQL = "UPDATE tblRequest " & _
    "SET [logicalField] = True, " & _
    "[dateField] = #" & format(now(), "mm/dd/yyyy hh:mm:ss") & "# " & _
    "WHERE myID = " & me.thisID & ";"
    currentdb.execute strSQL

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jan 2006
    Posts
    35
    IZY,
    Thank you, that did it .... for the most part. Nothing shows in the form but if I go into the table, the data has been added so there must be a refresh/update command necessary. If I click the command button, exit the database, reenter, then click into the field itself, the changed information is also shown.

    Details
    Software: Access 2003
    Table Name: Request
    Field:ReqNumber as autonumber & primary key
    Field ReqRoute9Approval (y/n)
    Field ReqApprovedDate9
    Form Name: Request

    Code I ended up with:

    Private Sub Command292_Click()
    strSQL = "UPDATE Request " & _
    "SET [ReqRoute9Approval] = True, " & _
    "[ReqApprovedDate9] = #" & Format(Now(), "mm/dd/yyyy") & "# " & _
    "WHERE ReqNumber = " & Me.ReqNumber & ";"
    CurrentDb.Execute strSQL

    End Sub

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    glad it works.

    how about a quick
    me.requery
    in that blank line you cunningly left between
    .execute
    and
    end sub

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jan 2006
    Posts
    35
    That did it! Life is good. Met goal of one thing new learned each day. I guess its time to go home.......

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    go for broke

    up at the top of your code module, type
    Option Explicit
    ...and your code will fall off a cliff

    add a line
    Private Sub Command292_Click()
    dim strSQL as string
    ...and sanity is restored.

    Explicit is good because:
    a/ it barks at you on compile when if you mis-spell the var name
    b/ it is faster (strSQL in your (and my) example will be a very slow Variant rather than a slightly slow String)

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good spot by Izy

    Addition: (standing on the shoulders of giants as ever......)
    Well worth popping at the top of every module and setting it as a default (Tools->Options->Require Variable Declaration). It will save you some very nasty debugging nightmares.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2006
    Posts
    35
    Good Point Pootle, Thanks Again Izy. Interesting exercise. I remember my early college-COBOL days of compiling: a gozillion compile errors: change a comma and you were down to a hundred; change a word you misspelled and you were down to two. Needless to say, it made a lasting impression and I put as much distance between me an COBOL as possible.

    One other question, this one relates to cursor control:
    What command would replicate the action of me clicking into a field to highlight that field? The code works fine if using my mouse I click into one of the fields on the form that will be changed then press the command button. If my cursor is in another field other than the changed fields then they are not updated.

Posting Permissions

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