Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unhappy Unanswered: Update Query Fails??!!

    I am running an update query that collects data from a text box on a form and then writes to a memo field in a linked table.

    When I run the query using a cmdButton on the form, the query fails with an Invalid Argument error message if I exceed 127 characters.

    If I launch the query directly and add the data in the QBE grid, I can add as much as I like (up to of course 65000 characters)

    I have debugged all the code on the cmdButton, and it fails at the query.

    If I run the query outside of the form, but leave the UpdateTo set to the form field, the query fails.



    Here is the query: UPDATE tblService SET tblService.Comments = [Forms]![frmUpdateServiceData]![txtNewComments] WHERE (((tblService.ServiceID)=[Forms]![frmViewAll]![ServiceID]));

    Next, the cmdButton Code:

    Private Sub cmdUpdateService_Click()
    On Error GoTo Err_cmdUpdateService_Click


    DoCmd.SetWarnings False


    'Prompt user to update address information
    'If address data yes, load the address form
    'if no, then requery the ViewAll Form and close this one.


    Dim strQuery As String
    Dim strQuery1 As String

    Dim Reply As VbMsgBoxResult

    strQuery = "qryUpdateServiceData"
    strQueryComments = "qryUpdateServiceComments"

    DoCmd.OpenQuery strQuery, acNormal, acEdit

    DoCmd.OpenQuery strQueryComments, acNormal, acEdit
    ' code fails here on previous line and returns "Invalid Argument"

    'logic for the next form/requery of main form.

    Reply = MsgBox("Congratulations! You have updated the record." & vbCrLf & _
    "Would you like to update an address record now?" & vbCrLf & _
    "Select Yes to update an address or No to close the form.", vbYesNo + vbInformation _
    , "Continue to Addresses?")

    If Reply = vbYes Then
    DoCmd.OpenForm "frmUpdateAddress", acNormal
    ElseIf Reply = vbNo Then
    cmdClose_Click
    DoCmd.Close acForm, "Form_frmViewAll"
    DoCmd.OpenForm "frmViewAll", acNormal
    Form_frmViewAll.SetFocus
    Exit Sub
    End If

    Exit_cmdUpdateService_Click:
    Exit Sub

    Err_cmdUpdateService_Click:
    MsgBox Err.Description
    Resume Exit_cmdUpdateService_Click

    End Sub

    The field tblService.Comments is defined as a memo field, and as indicated I have no problem if I write the data change directly into the query.

    When I debug or walk through the code, the only error that occurs is when the second query is called.

    I initially had the comments part of the update process set to run as part of the strQuery, but pulled it out and now run it separately to try and solve this problem.

    The query works fine until I have 128 characters in the txtNewComments field, then it returns the Invalid Arguments error.

    There are <200 records in the table.

    Any insight, or anything else that could help solve this, again gratefully received.

    Don

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: Update Query Fails??!!

    Are you using ADO?

    In that case write the text directly in the query which you said works i.e.

    Connection.Execute = "UPDATE tblService SET tblService.Comments =" & [Forms]![frmUpdateServiceData]![txtNewComments] & " WHERE (((tblService.ServiceID)=[Forms]![frmViewAll]![ServiceID]));"

    Also I would recommend a download of this control on download.com which has made my life a lot easier for updating, adding new, deleting etc... Here is the link:

    http://download.com.com/3000-2404-10...ml?tag=lst-0-1


    Originally posted by donr
    I am running an update query that collects data from a text box on a form and then writes to a memo field in a linked table.

    When I run the query using a cmdButton on the form, the query fails with an Invalid Argument error message if I exceed 127 characters.

    If I launch the query directly and add the data in the QBE grid, I can add as much as I like (up to of course 65000 characters)

    I have debugged all the code on the cmdButton, and it fails at the query.

    If I run the query outside of the form, but leave the UpdateTo set to the form field, the query fails.



    Here is the query: UPDATE tblService SET tblService.Comments = [Forms]![frmUpdateServiceData]![txtNewComments] WHERE (((tblService.ServiceID)=[Forms]![frmViewAll]![ServiceID]));

    Next, the cmdButton Code:

    Private Sub cmdUpdateService_Click()
    On Error GoTo Err_cmdUpdateService_Click


    DoCmd.SetWarnings False


    'Prompt user to update address information
    'If address data yes, load the address form
    'if no, then requery the ViewAll Form and close this one.


    Dim strQuery As String
    Dim strQuery1 As String

    Dim Reply As VbMsgBoxResult

    strQuery = "qryUpdateServiceData"
    strQueryComments = "qryUpdateServiceComments"

    DoCmd.OpenQuery strQuery, acNormal, acEdit

    DoCmd.OpenQuery strQueryComments, acNormal, acEdit
    ' code fails here on previous line and returns "Invalid Argument"

    'logic for the next form/requery of main form.

    Reply = MsgBox("Congratulations! You have updated the record." & vbCrLf & _
    "Would you like to update an address record now?" & vbCrLf & _
    "Select Yes to update an address or No to close the form.", vbYesNo + vbInformation _
    , "Continue to Addresses?")

    If Reply = vbYes Then
    DoCmd.OpenForm "frmUpdateAddress", acNormal
    ElseIf Reply = vbNo Then
    cmdClose_Click
    DoCmd.Close acForm, "Form_frmViewAll"
    DoCmd.OpenForm "frmViewAll", acNormal
    Form_frmViewAll.SetFocus
    Exit Sub
    End If

    Exit_cmdUpdateService_Click:
    Exit Sub

    Err_cmdUpdateService_Click:
    MsgBox Err.Description
    Resume Exit_cmdUpdateService_Click

    End Sub

    The field tblService.Comments is defined as a memo field, and as indicated I have no problem if I write the data change directly into the query.

    When I debug or walk through the code, the only error that occurs is when the second query is called.

    I initially had the comments part of the update process set to run as part of the strQuery, but pulled it out and now run it separately to try and solve this problem.

    The query works fine until I have 128 characters in the txtNewComments field, then it returns the Invalid Arguments error.

    There are <200 records in the table.

    Any insight, or anything else that could help solve this, again gratefully received.

    Don

  3. #3
    Join Date
    Jan 2004
    Posts
    3

    Re: Update Query Fails??!!

    Thanks, that works.

    Appreciate it.

    Don

Posting Permissions

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