I am having a problem and I can't seem to figure out what is wrong.
This is it:
I have a table with a memo field called MonthComment. It also has a field called Historic_comments
I have a form containing 2 text boxes. One text box called Historic_comments and the other called New_comments. The Historic_comments textbox contain any comment that was on a record at the time the form is loaded, and this information comes from the MonthComment field in the Table.
Persons can now type comments in the New_comments textbox. The system will check if a comment existed before, and if it does, it will combine the old comment with the new comment and put this combined data in the Historic_comments textbox. It will then take it from the data from the Historic_comment textbox and store it in the MonthComment field in the table.
I use the Dcount function to check if there is a comment for the record. Each record in this table must have a comment, so if a comment does not exist, it means that the record does not exist in this table. I store the result in a variable called Comment_exist.
I use the following command to do the saving:
If comment_exist > 0 Then 'comment exist, so update the comment
DoCmd.RunSQL "Update tblComments set MonthComment = forms!wk12rp!historic_comments where customer_code = forms![Get list of customers]!cust_num"
Else 'comment doesnt exist: Add the customer code and the comment
DoCmd.RunSQL "Insert into tblComments (customer_code, monthComment) values (cust_num,new_comments)"
I am having two problems with this: The first is with the Insert Into command. If the comment for the record is about 100 or less characters, this works fine. However, if the comment is longer than that, it gives me a "Invalid argument" error.
The other problem is with the Update command. If the comment is short, it works fine. If you input a series of short comments (eg. Called and left message) it will keep updating the MonthComment field until the total length of the field is to be about 100 characters. My taking on this is that, since the MonthComment field is a Memo field, it should accept any length string. Am I wrong? What am I doing wrong? How can I get around this?
I really need your help in the shortest possible time.