Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Table (2007)

    I have a form that is based off a query. The query uses tblPO. The form currently opens in add mode. The user completes all the required fields and clicks submitt. Here is the code that the is in the onclick event:
    'Write submit bit in tblPO
    Dim strSQL As String
    Dim str2SQL As String
    ' Create SQL string for update query
    strSQL = "UPDATE tblPO SET Submitted = True WHERE [PO#] = " & Me.txtPO
    str2SQL = "UPDATE tblPO SET Rejected = False WHERE [PO#] = " & Me.txtPO
    'Execute the update SQL
    CurrentDb.Execute strSQL
    CurrentDb.Execute str2SQL

    The problem is that the Submitt field in the table is not getting updated on a new record. If I open a record that was already created, the above code works fine. I think it might have something to do with the record not being saved. Does this sound right?

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

    Re: Update Table (2007)

    If your form is based on (a query based on) tblPO, you don't need to run an SQL statement to set a value. You should be able to set the value directly in the form.
    Moreover, if you want to set the Submitted value of a record to True in a new record, and the Rejected value to False, you can do so by setting the Default Value properties of these fields. In fact, setting the Default Value of Submitted to True should be sufficient, for if you don't specify a default value for a Yes/No field, it will be False.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Table (2007)

    I don't want to set the default value of submitted to True because the user can fill part of the form out and come back later and fill the rest of it out. Therefore I don't want it to say submitted until the user clicks submit. That being said, would I need to run a SQL statement to set the value from VBA in the onclick?

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

    Re: Update Table (2007)

    No, you'd just set the value of the fields:

    Me.Submitted = True
    Me.Rejected = False

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Table (2007)

    I currently don't have any objects on the form bound to Submitted or Rejected. I will created a text box that is bound to each field and then me.txtbox = true should work. Right?

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

    Re: Update Table (2007)

    It should be enough if the fields Submitted and Rejected are part of the Record Source of the form, at least it is in Access 2002. If that doesn't work in Access 2007, you can place text boxes bound to these fields on the form, and set their Visible property to No if you'd rather not display them.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Table (2007)

    They are part of the record source.... I am not sure why it didn't work.

    But after trying a few things, here is what worked:
    [Submitted] = True
    [Rejected] = False

    Thanks for you input. You are the greatest <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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