Fill in check mark on a form based on a new record in a separate form (not a subform)

[sleake]

I have a form (frm1DocumentRecord) that has on it a button to open a second form (frm2SharePointComment). The first and second forms are linked using the record ID from frm1. frm2 has Save, Delete, and Close buttons. frm1 has a yes/no field on to indicate that a record exists frm2. Likewise, if the user deletes the record in frm2, I would like the check mark to disappear.



I'm challenged by how to write code to do this. I'm thinking it would be based on a either a new record in frm2 or the presence of a record, maybe triggered by a click on Save (AfterUpdate) or Delete when a record is deleted. When the form is first opened, it has a blank record, so I don't want that to create the check mark.

Is somthing like this the approach to take? If so, I have no idea of what to put in <Something> below.
If Me![<Something>], Forms![frm1].[ckCheckmark] = -1
Else Forms![frm1].[ckCheckmark] = 0

[June7]

Why save this value to table? It can be calculated when needed. Use a domain aggregate function (DLookup or DCount) expression in textbox.

[sleake]

I supposed it doesn't have to be in the table. I've looked up domain aggregate functions, and it looks as if DLookup would be the function to use, because there will never be more than one record in either table -- they have a 1-to-1 relationship. But I am still lost. I found, but don't know what to do with the following: DFunctionName("<Fieldname >", "<RecordSource>", "<Criteria Expression>")

DLookup("SSN", "tblEmployee", "[EmpID] = 16")Returns the social security number of employee number 16.
DSum("Price", "tblOrderDetails", "[OrderNum] = " & Me.txtOrderID)
Maybe:
DLookup ([SPCommentID], "tblSPComment", = " & Me!ckSP_Comment")(check box is ckSP_Comment)

I figured if there were a comment record, there would be a comment ID (number) in tblSPComment. So this would go in the checkbox on the first form? How do I tell it to assign the value -1? I tried the above, and of course nothing happened. And how do I keep the check box empty if there is no comment record?

[June7]

1-to-1 relationship? Why not 1 table? The 'child' table is just for a comment field, nothing else? Not every record will have a comment? Could just include the 'child' table in the form RecordSource (join type "Include all records from {master table} and only those from {child table} that match". Then bind textbox to the comment field.

However, I am thinking this takes normalization one step too far. Unless there will be multiple comment records for each parent record, I would just put comment field in parent table, even if there will be a lot of blanks.

It is a balancing act between normalization and ease of data entry/edit.

[sleake]

Your point is well taken. Originally I thought there might not be a comment for every parent record -- hence the check mark. But in working with this, I find there will need to be a comment for each parent record. So I may as well combine the tables and make it one form. A lot simpler -- I was just stuck in my original concept.
Thanks for clearing my fog!