Move large text between tables

[mutiger83]

I have tried and tried and cannot seem to accompish what I need to ... please help

I have a form with multiple fields, one of which is a memo field where I need to list multiple comments in reference to the record. This memo field needs to be built from a series of 'predefined' comments. These commments are stored in another table. Here is my sample layout of tables:

Table 1:
RecordID
Name
Comments (memo field)

Table 2:
CommentName
CommentText (memo field)

So what I need to accomplish is when I open a form built from Table 1, I want a drop down menu with the CommentNames in it and then by clicking a button it copies the CommentText from Table 2 into Comments on Table 1. The catch is that I need to be able to concatenate the comments together. In other works, if you pick Comment1 from the drop down and it transfers the text after clicking a button, I must then be able to pick another comment from the list and have it add it on to what is already in the Comment field .

I tried to program this many times through VBA in access and the best I could accomplish was it transfering over the first 255 characters. This doesn't make sense though, since both are memo fields. In any case, I am starting fresh now and need some help.

Hope I have made this clear enought, but please let me know if I can clarify futher. Thanks in advance!!!

 

[Remou]

Use DlookUp to get the comment from the Comment table. This way, you will not be limited to 255 characters, as far as I recall.

 

[mutiger83]

I tried this without any luck...anyone see anything wrong?

Private Sub Command56_Click()

Dim selectedcomment As String
Dim varx As Variant

selectedcomment = Me.Combo54.Value 'get comment selected in combo54 drop down menu

varx = DLookup("[CommentText]", "BidComments", "[CommentName] = " & selectedcomment)

Me.BidInfo.Value = varx


End Sub

* combo54 is the drop down where I pick the CommentName
* CommentText is the actual memeo field in the BidComments Table where the text I want to transfer is stored
* CommentName is the field in the BidComments Table that is identical to the options in combo54
* Bidinfo is the field where I want to store the text

 

[Remou]

It is a name and therefore text, you need some single quotes:
[tt]DLookup("[CommentText]", "BidComments", "[CommentName] = '" & selectedcomment & "'")[/tt]

 

[mutiger83]

Thanks for your help!

I did this and no error, however, no effect... this is my code now...

Private Sub Command56_Click()

Dim selectedcomment As String
Dim varx As Variant

selectedcomment = Me.Combo54.Value 'get comment selected in combo54 drop down menu

varx = DLookup("[CommentText]", "BidComments", "[CommentName] = '" & selectedcomment & "'")

Me.BidInfo.Value = varx


End Sub

 

[Remou]

I suspect that your combo has more than one column. If this is the case, you need to make selectedcomment equal to the column with the name in it, probably column 1 (starting from 0):
[tt]selectedcomment = Me.Combo54.Column(1)[/tt]

 

[mutiger83]

Thank you so much! It workes perfect now!