How can I get Access to append a memo correctly

[Hel27n]

Hello,

I have designed a table with a field in it which is set up as datatype ‘memo’.

I have a form where I want the user to enter details and then I use an append Query to add the details into the table.

The trouble is that when I run the query it will not add all the text. I know that memo should hold approx 64000 characters however when adding the record Access seems to truncate the text to a length similar to a text datatype.

Can anyone please explain to me how to rectify this situation?

 

[dhookom]

Could you share the SQL view of the append query?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Hel27n]

The code is on another pc, hold on and I'll get it

 

[Hel27n]

The field in CAPS is the memo field


INSERT INTO will ( willid, refno, WILL, [date of will], instructions, solicitor )
SELECT Forms!client!willid AS Expr1, Forms!client!refno AS Expr2, Forms!client!WILL AS Expr3, Forms!client![date of will] AS Expr4, Forms!client!instructions AS Expr5, Forms!client!solicitor AS Expr6;

 

[Hel27n]

Hello Again,

I was looking on this forum and seen that if the field is unbound (on the form) then Access does not know how to handle it correctly.

The only problem with this is that I need the field to be unbound as on occasions I need to amend the record (while keeping a copy of the original entry). If I set the field to bound it will over right the original entry.

Please help!

 

[dhookom]

Consider using code like the following that assumes the code is running in the form and both WillID and RefNo are numeric:

Private Sub cmdInsertRecord_Click()
    Dim strSQL As String
    strSQL = "INSERT INTO will ( willid, refno, WILL, " & _
       "[date of will], instructions, solicitor ) " & _
       "Values (" & Me!willid & ", " & Me!refno & ",""" & _
        Me!WILL & """,#" & Me![date of will] & "#, """ & _
        Me!instructions & """,""" & Me!solicitor & """"
    DoCmd.RunSQL strSQL
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Hel27n]

Hello,

I have added the code above and when I try and run it I get a
Run-time error '3134'
Syntax error in INSERT INTO statement

The fields are set up as follows:-

willid numeric
refno text
will memo
date of will date/time
instructions memo
solicitor text

 

[dhookom]

As stated, my assumption was that refno was numeric. Try add extra delimiting quotes for the refno field.

Private Sub cmdInsertRecord_Click()
    Dim strSQL As String
    strSQL = "INSERT INTO will ( willid, refno, WILL, " & _
       "[date of will], instructions, solicitor ) " & _
       "Values (" & Me!willid & ", """ & Me!refno & """,""" & _
        Me!WILL & """,#" & Me![date of will] & "#, """ & _
        Me!instructions & """,""" & Me!solicitor & """"
    DoCmd.RunSQL strSQL
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Hel27n]

Thanks for this. I changed the code to what you have given me above and also included a closing bracket and it now seems to be working fine.

Thanks for all you help. can you explain to me why code has to be used for adding appending memos? Also are there any further issues that I should be aware off?

 

[dhookom]

I think Access has an issue with the value coming from the form control. I'm not sure why it happens.

Most programs are full of limitations in one form or another.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fneily]

Hel27n
You answered your own question about why you need to use code - the memo field you created on the form is UNBOUNDED. Look at the Control Source option on the property sheet of the control and it will be blank. Thus, it is not associated with any field in any table.

 

[Hel27n]

Hello again,

I have noticed that I have another problem. When using the code provided by 'dhookom (Programmer)' everything appends ok.

However I have noticed that the date format is American. Ideally I would like it to enter the date in the following format dd/mmm/yyyy. On the table I have specified that this be the format of the date but when the code appends the data it is in American Date format.

 

[dhookom]

I don't work with other date formats. You may need to check this link to Allen Browne's page

http://allenbrowne.com/ser-36.html.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mrf1xa]

Just use the Format function- you can use this within the SQL to change the date to any format you want.

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[Hel27n]

I now need to update a record (instructions) in a table named will. I am using the following code however it returns a Syntax error. Can anyone tell me what may be wrong? When Debugging it highlights the 'DoCmd.RunSQL strSQL'

Private Sub add_instructions_Exit(Cancel As Integer)

Dim strSQL As String
Dim dateSQL As String

strSQL = "UPDATE will SET(solicitor) " & _
"Values (""" & Me!solicitor & """)WHERE will(willid) = " & Me!willid & ""
DoCmd.RunSQL strSQL
End Sub

 

[dhookom]

You are messing up your SQL syntax by confusing insert and update

   'assumes WillID is numeric and Solicitor is text
   strSQL = "UPDATE will SET solicitor = """ & _
       Me!solicitor & """ WHERE willid = " & Me!willid 
   DoCmd.RunSQL strSQL

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]