Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Invalid Property Value error

    Hi,

    I keep getting the Invalid Property Value error when trying to assign a value to a parameter using QDef.

    The value in question is the body of a letter, so is assigned a data type of memo.

    This is the code:

    Code:
        Set QDef = CurrentDb.QueryDefs("qryMailMergeAll")
            
        QDef.Parameters("[Forms]![frmLetters]![txtLetter]").Value = [Forms]![frmLetters]![txtLetter]
        QDef.Execute
        DoCmd.OpenQuery "qryMailMergeAll", , acAdd
    The query that this parameter is placed in is a Make Table query, I use this to make a table for the mail merge, then I can just set the datasource in the word document to that table.

    Anyone have any ideas how I can fix this?

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I just tried a lot of different ways, but it looks like the query does NOT like to deal with memo fields. I would suggest putting this data into the field in the table with VBA, rather than a query. Something like the following code.
    Code:
      dim rs as DAO.recordset
      Set rs = CurrentDB().OpenRecordset("tblMemoField", dbOpenDynaset)
      rs.AddNew
        rs.MemoField = Forms!frmMemoForm!MyMemoField
      rs.Update
    HTH,

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Thanks, that sounds like what I need to do.

    But what does the rs.MemoField represent, as it isn't a property of the recordset?

    Thanks

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Ops, it should be rs!MemoField. My bad!
    MemoField is a field in the table.

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    I have tried this, but when It gets to the point of adding, it says that it can't find the field in the collection.

    So I added the field in my table, now it runs ok, but doesn't update with what is in the control on the form.

    Code:
    Dim rs As DAO.Recordset
    Set rs = CurrentDb().OpenRecordset("tblMailMerge", dbOpenDynaset)
    rs.AddNew
        rs!LetterBody = Me.txtLetter.Value
    rs.Update

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    What does it update the table with?

    Also, what does it update the table with if you take ".Value" off the end of the "rs!LetterBody = Me.txtLetter.Value" statement?

  7. #7
    Join Date
    Sep 2005
    Posts
    240
    Quote Originally Posted by GolferGuy
    What does it update the table with?

    Also, what does it update the table with if you take ".Value" off the end of the "rs!LetterBody = Me.txtLetter.Value" statement?
    It doesn't update the table, but keeps it's orginal value.

    Same thing happens when I take off the .Value.

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I think the RT 3271 "Invalid Property Value" stems from the DAO parameter not being able to take more than 255 characters.

    I think if you open a recordset and use .AddNew or use dynamic SQL, you'll be able to insert the whole body into the memofield. For instance (air code)

    currentdb.execute "insert into tblMailMerge (LetterBody ) values ('" & string(1000, "@") & "')"

    BTW - addnew shouldn't update anything, it should add a new record. Also - typing Me!txtLetter, is a shorthand for Me!txtLetter.Value, since the value property of controls are the default property.
    Roy-Vidar

  9. #9
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I just tested the .AddNew and the most characters I could get into the memo field was 1042. So, the dynamic SQL would be the way to go. Thanks Roy-Vidar.

  10. #10
    Join Date
    Sep 2005
    Posts
    240
    Thanks for your help, I've tried your solution RoyVidar and it does work.

    I have numerous records already in the table, I take it if I change the SQL to an update query it will replace the existing values with the new one, rather than just adding one on the end?

  11. #11
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Yes, the Update query will change values of existing record(s).

Posting Permissions

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