Table and form based off a different table - see if a common record exists in the table and insert a "yes" or "no" value in the form

Question

I have an access database that my team uses to track projects. We use it as a VCB.

On the main form (Projects) we view it and make updates on the form for the projects - which in turn updates the table linked to that form.

However, we also have a separate table (Comments) where we store comments related to the projects. The Primary Key for both tables is ProjectID.

There is only 1 Project entry on the Projects form but there can be 0 to Many on Comments on the comment table.

What I am trying to achieve is have the Project Form (our VCB) look at the comments table and see if there is a record in the table with the same ProjectID - if there is insert a "Yes" value into my textbox on the project form but if there is not then insert "NO".

Its just an easy visual aid to see if comments exist for the projects on the VCB (we handle comment entry and viewing in a separate form)

I am stumped on which route to take. Ive tried a few things and gotten stuck in loops. Any help is appreciated

Dim strSQL As String
Dim rs As Dao.Recordset
Dim db As Dao.Database

strSQL = "SELECT * FROM COMMENTS WHERE [PROJECTID] = " & Me.PROJECTID & ""

Set db = CurrentDb
Set rs = db.OpenRecordset("COMMENTS")

rs.MoveFirst

Do Until rs.EOF = True
 Set rs = db.OpenRecordset(strSQL)
   If rs.RecordCount = 0 Then
     Me.CommentTxtBox.SetFocus
     Me.CommentTxtBox.Text = "NO"
  Else
     Me.CommentTxtBox.SetFocus
     Me.CommentTxtBox.Text = "YES"
  End If
 rs.MoveLast
Loop

rs.Close
Set rs = Nothing

I'm getting every entry a NO in the field and it errors out because the ProjectID is not in the comments table(which it might not be) so it says no record found.

HansUp HansUp · Accepted Answer · 2015-02-23T15:18:16

DCount() could make your code simpler.

Dim lngComments As Long
Dim strComments As String

lngComments = DCount("*", "COMMENTS", "[PROJECTID] = " & Me.PROJECTID)
If lngComments > 0 Then
    strComments = "YES"
Else
    strComments = "NO"
End If
Me.CommentTxtBox.Value = strComments

Notice by assigning the value to the text box's .Value property (instead of its .Text property), you don't have to bother about SetFocus.

I think that is a simpler version of your current approach. However I'm uncertain whether that is the best approach. Perhaps you could get what you need by setting the text box's Control Source property to an expression which uses IIf to evaluate DCount:

=IIf(DCount("*", "COMMENTS", "[PROJECTID] = " & [PROJECTID]) > 0, "YES", "NO")

Table and form based off a different table - see if a common record exists in the table and insert a "yes" or "no" value in the form

1 Answers