Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    31

    Unanswered: Problem with SQL Update Query in Form

    We have a form that we are using for certain people to make corrections to a table. They use a form that contains certain values, and those values are then loaded into the main table which our application uses.

    Their interface is a Form with a Subform. The form contains the main table data, the subform contains the data that they update, and they do have a good master/child link.

    On the subform there are 2 fields which they change, and one which is calculated based on the date field they edit.

    [line_rr]
    [new_due_dt_t]
    [new_start_dt_t] this is the calculated field.

    Once those changes are done, they simply need to press a command button to upload the new values into the main table.

    Problem is when the button is pressed, rather then it working it prompts a dialog box asking for each of the Me![values].

    Here is the SQL Statement. What am I missing?
    Code:
    Private Sub Command19_Click()
    
       Dim ord_no As String
       Dim A4GLIdentity As Double
       Dim line_rr As String
       Dim new_start_dt_t As Double
       Dim new_due_dt_t As Double
       Dim sqlstr As String
        
    
    sqlstr = "UPDATE dbo_PPORDFIL_SQL_06 SET dbo_PPORDFIL_SQL_06.due_dt = Me![new_due_dt_t]," & _
    "dbo_PPORDFIL_SQL_06.start_dt = Me![new_start_dt_t]," & _
    "dbo_PPORDFIL_SQL_06.user_def_fld_5 = Me![line_rr] " & _
    "WHERE (((dbo_PPORDFIL_SQL_06.ord_no) = Me![ord_no]) " & _
    "And ((dbo_PPORDFIL_SQL_06.A4GLIdentity) = Me![A4GLIdentity]));"
    
    DoCmd.RunSQL sqlstr
    Me.Refresh
    
    
    End Sub

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's doing exactly what you're telling it to do

    Do a MsgBox sqlstr and read the string; notice anything?
    Code:
    "And ((dbo_PPORDFIL_SQL_06.A4GLIdentity) = " & Me![A4GLIdentity] & "));"
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2004
    Posts
    31
    That is exactly what I get for typing that at 11:00 at night. I get burned by those "& and & " every time.

    Thanks. Made day much better.

    Not quite....
    Blech. Datatype mismatch.
    Last edited by Rex Deckard; 07-02-08 at 10:45.

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Should new due date be a date rather than double?

  5. #5
    Join Date
    Mar 2004
    Posts
    31
    Normally yes, but with our application, all dates are stored as numbers. The field format is Long Integer. I tried changing it from Double to Long and got the same result.

    Found it, the problem was the ord_no field. Which is not needed as the true unique id between the Forms is the A4GL.
    Last edited by Rex Deckard; 07-02-08 at 11:11.

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try wrapping line_rr in in chr(34)s.

  7. #7
    Join Date
    Mar 2004
    Posts
    31
    Quote Originally Posted by rogue
    Try wrapping line_rr in in chr(34)s.
    Yep, that was required as well. Saved me a headache.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    info - Access JET datetimes **ARE** doubles.

    continuing a long confused grumble: yes most Microsoft examples use Me!controlName, but why?
    in the old days, the useage was:
    Collection!MemberOfCollection
    Object.Property
    the form Me is clearly an object, not a collection (tho Me.Controls is a collection)
    intellisense understands Me. and does not understand Me! so this is not just my oldfashioned-think.

    is it worthwhile mentioning:
    a/ where are your tables (JET, disconnected SQLserver, linked SQLserver)
    b/ what are your field datatypes

    izy
    currently using SS 2008R2

Posting Permissions

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