Append contents of textbox onto memo field (1 Viewer)

[add1989]

I have a form which modifies the 'calls' table. The calls table has customer ID which is linked to customer ID in the customers table.

Customer | Calls
-------------------------------------------------
Customer ID | Call_ID
CustName | Customer ID
Notes | Notes

I want to be able to click a button on my form to copy the contents of the 'notes' field on my form, onto the end of a notes memo field in the customer table, while still having the text be saved to the relevant call record.

Can anyone help?

Adam

 

[John Big Booty]

Try something like;

Me.MemoFieldName = Me.MemoFieldName & vbCrLf & Me.Notes

 

[gfultz]

BobLarson is right! You could relate the call records to the customer and then merge them together in any form or report you need. The only "Trick" to this is that you must usea Union All Query with your query and an empty Table template (Table is the first selecgt statement) to force access to understand that you want more than 255 chars on your concatenated note records.

 

[add1989]

We have two databases... one is used by our CRM software which I have linked a table into my little database I'm using for my own little project, therefore comments need to go into the main database for the company but I also want them in mine.. I know this is redundant but it's required at the moment.

Thanks for your help though!

Adam

 

[add1989]

I've tried two sets of code at the moment... neither work ;(

Can someone please help me enhance one of them?

Set db = CurrentDb
Set rs = db.OpenRecordset("Bemerkung")
rs.Edit
rs("Bemerkung").Value = Bemerkung.Value & vbCrLf & Me.notes.Value
rs.Update
rs.Close

-

Dim strSQL As String

strSQL = "UPDATE Bemerkung SET Bemerkung =" & Chr(34) & Me.notes & Chr(34)
strSQL = strSQL & " WHERE Nummer = " & Me.call_id & ";"
CurrentDb.Execute strSQL

The SQL statement doesn't yet cater for the existing text, as I'm not sure how to do this

NOTE: Table is called Bemerkung (german for 'Remarks') and also has a column called 'Bermerkung'. The other column is Nummer (customer_id).

 

[gfultz]

Two things.

1. A linked table can still be setup with relationships and the like. I do this currently with an MDB back-end and pull data from 13 linked SQL Server tables and do exactly what you want to do with no issues.

2. Your code references Bemerkung. It looks like that is a field and not a table. Typically you open a recordset by specifiying a table or query to open, or you use a sql statement to select the relevant info. For the Sql, you need to specify the table and the field you want updated. http://www.w3schools.com/sql/sql_update.asp the correct syntax is:
Update TABLE NAME
Set FIELD NAME = Some value
Where ANOTHER FIELD NAME = Some Value

I still recommend pulling this data from the linked table in your forms or reports so you aren't storing data in two places. As long as you always have access to the linked table when working in the DB, this will work.

 

[add1989]

NOTE: Table is called Bemerkung (german for 'Remarks') and also has a column called 'Bermerkung'. The other column is Nummer (customer_id).

See above

But storing the data in only once place is out of the question. What my boss wants, my boss gets. I simply need to know how to append the text in Form!textField1 to the memo field in Bemerkung.Bemerkung. I cannot fathom the code side of how to do it having never worked with VBA before.

 

[vbaInet]

I just have to reiterate Bob's concerns again re saving the same data in two tables. If you're trying to do this then it would seem your data structure is not properly normalized.

I want to be able to click a button on my form to copy the contents of the 'notes' field on my form, onto the end of a notes memo field in the customer table, while still having the text be saved to the relevant call record.

Does the highlighted statement mean you want to do something like this:

Calls - 'Notes' field has text: "Called to log a fault 10/2/2010"
Customer - 'Notes' field has text: "Logged fault 3/9/2009. Fault followed up by engineer 5/9/2009."

Once merged the Notes field on the Customers table would then be:
Customer - 'Notes' field has text: "Logged fault 3/9/2009. Fault followed up by engineer 5/9/2009. Called to log a fault 10/2/2010"

Something like the above?

 

[add1989]

Absolutely correct

This way each record in 'calls' has it's own note associated with it, but the memo field in Customers (or Bemerkung as the table is called) has all the notes related to that customer. I know there are better ways of working with this but this is the way I have been asked to do it.

Any help would be appreciated!

Adam

 

[vbaInet]

You can still achieve exactly what your boss wants but in a more succint way. This would require that you to have a date/time for each note and you can create a function to concatenate all the notes onto a textbox for display purposes.

With your boss' proposal, what would happen if that same memo is appended TWO times or MORE? There won't be a way of checking whether that memo has already been appended to the field. That's one other drawback.

If you still want to go ahead with it then have a look at the DLOOKUP() function:

http://www.techonthenet.com/access/functions/domain/dlookup.php

I'm not sure what the effects would be if you have a very long memo but it may end up truncating it.

Use that function to retrieve the current Notes field in the Customers table and save it into a String variable. Concatenate the Calls-Notes field onto that variable and UPDATE your Customers-Notes field with that concatenated string variable.

 

[add1989]

I have been trying to follow the guidance of vbaInet's last comment for a while now but cannot seem to get it to work. Could somebody expand on what he/she means please? I understand the principle of it, just not necessarily how to implement it (in Access 2007).

 

[vbaInet]

Based on your PM let's use your recordset instead:

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [[COLOR=Red][B]NotesFieldName[/B][/COLOR]]  FROM Bemerkung WHERE [Customer ID] = " & Me.CustomerID)

with rs
   if .bof and .eof then
      msgbox "Customer not found"
      .close
      db.close
      exit sub
   end if
   .Edit
   rs("[[COLOR=Red][B]NotesFieldName[/B][/COLOR]]").Value = rs("[[B][COLOR=Red]NotesFieldName[/COLOR][/B]]").Value & vbnewline &  Me.notes.Value
   .Update
   .Close
   db.close
end with

Again, I should remind you that you should tell your boss that this is very bad practice. I'm only giving you this because it seems your boss is hassling you

Is your boss aware that you can make all the notes appear for that customer without having to "double-save" the same note?

 

[add1989]

My boss has made clear that the access database I'm writing at the moment is ancillary and should not affect the way our main CRM program works in any way.

Changing the structure of the current database would also stop the CRM program from working.

I know it's not good to double-save, but it's the best I can do at the moment.

Thank you very much for your help!

 

[add1989]

Private Sub memofield_Click()
On Error GoTo Err_memofield_Click

Set db = currentDb
Set rs = db.OpenRecordset("SELECT [Bemerkung] FROM Bemerkung WHERE [Nummer] = " & Me.Combo28)

With rs
   If .BOF And .EOF Then
      MsgBox "Customer not found"
      .Close
      db.Close
      Exit Sub
   End If
   .Edit
   rs("[Bemerkung]").Value = rs("[Bemerkung]").Value & vbNewLine & Me.notes.Value
   .Update
   .Close
   db.Close
End With
    
Exit_memofield_Click:
    Exit Sub

Err_memofield_Click:
    MsgBox Err.Description
    Resume Exit_memofield_Click
    
End Sub

This is the code I'm now using. I get a 'data type mismatch in criteria expression' error. Bemerkung is Memo, Notes is Memo, Nummer is Text, and Combo28 is text.

I tried changing 'currentDb' to "Calls" (with the database being calls.mdb) but then it just highlighted the sub declaration in yellow.

Google says that this error might not simply mean what it says. Any ideas?

 

[vbaInet]

I suppose that's your boss' prerogative

By the way, you can change this line:

rs("[NotesFieldName]").Value = rs("[NotesFieldName]").Value & vbnewline &  Me.notes.Value

To this:

.fields("[NotesFieldName]").Value = .fields("[NotesFieldName]").Value & vbnewline &  Me.notes.Value

Good luck!

 

[add1989]

I still get the data type mismatch error with this code change.

I have checked that all fields are the correct data type, and I have messed around with the code a little, particularly the select statement, but I still get the error?

 

[vbaInet]

Our messages crossed.

Use this:

set rst = db.OpenRecordset("SELECT [Bemerkung] FROM Bemerkung WHERE [Nummer] = [COLOR=Red][B]'[/B][/COLOR]" & Me.Combo28 & "[COLOR=Red][B]'[/B][/COLOR]")

Notice the bit in red. That's how you pass text datatype.

 

[add1989]

This seems to have worked perfectly!

Thank you very very much!

 

[vbaInet]

Great! Glad that's working for you.

Forgot to mention, remember to dim the objects:

Dim [B]db[/B] as DAO.Database
Dim [B]rst [/B]as DAO.Recordset

 

[add1989]

What does this do? It seems to be working without it?