Copying a field from a subfile to a memo field on the main form.

[Henley12]

I have a database that has a Labor subform associated with it. When you open up the labor form, you can enter various information. There is also a Comments field. On my main form, I have a Comments field as well. Is there a way to populate the main form with the comments from the labor subform? Keep in mind, there may well be more than one labor record per main form record.

 

[boblarson]

That is something you should NOT do. You should not be storing redundant data. You can use a query to pull together the comments for display.

 

[Henley12]

Is there a way to display them in the main form comments field, then? This just makes it easier to see what has been done when someone is closing out a work order.

 

[boblarson]

This is likely to be kind of slow, depending on how many records you have.

1. Make sure the comments field on the main form is not bound.

2. You can create a function to concatenate all of the comments based on the current main record ID (the field which is in the Master/Child links) and then set it to be the control source to = ConcatComments ([YourIDFieldNameInBrackets])

Function ConcatComments(lngID As Long) As String
Dim strComments As String
Dim strSQL As String
Dim rst As DAO.Recordset

strSQL = "SELECT CommentsFieldNameHere FROM YourDetailsTableNameHere " & _
            "WHERE YourIDFieldName = " & lngID

Set rst = CurrentDb.OpenRecordset(strSQL)

Do Until rst.EOF
    strComments = strComments & rst(0).Value & vbCrLF
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing

(air code - untested)

 

[Henley12]

I'm afraid I'm not very familiar with where to create functions. Where would I put this code?


On a side note..........is there any way to keep this board from logging me out? It seems to be set on 10 minutes, or so. Quite annoying.

 

[boblarson]

I'm afraid I'm not very familiar with where to create functions. Where would I put this code?

Start a new MODULE and then paste it in just after anything that says OPTION XXX (like OPTION EXPLICIT and OPTION COMPARE DATABASE)

Name the module something OTHER than the name of the function.

On a side note..........is there any way to keep this board from logging me out? It seems to be set on 10 minutes, or so. Quite annoying.

I don't know of anything that would cause that - do you check the REMEMBER me box upon logging in? If so, it may be a Group Policy set by your Admin because it never logs me out. I have it checked and I can open any browser at any time and I'm there.

 

[boblarson]

Oh, and make sure to change the applicable parts of the code to refer to your actual items in your database.