Continuous form issue

[tucker61]

My users input data into a continuous sub form, I look at a value of a field from this sub form and dependent on what is entered into the field I have a button that emails out to different people.

Quite often the email address's are left blank, I have worked out that this is because the operator has moved to a blank record before hitting the email button.

What do i need to do to stop this form from being blank, and to look up to either the first or last value.

the highlighted field is called tbsubcat.

I have tried setting tbsubcat as a unbound control and then using VB to get the first or last value (code below), but i get the error message Compile Error Message or data member not found.

tbSubCat.RecordSource = "SELECT tblQCNonConformanceDetail.Job_ID, Last(tblcatalog.Sub_Category) AS LastOfSub_Category " & vbCrLf & _
"FROM tblQCNonConformanceDetail INNER JOIN tblcatalog ON tblQCNonConformanceDetail.Cat_No = tblcatalog.Cat_No " & vbCrLf & _
"GROUP BY tblQCNonConformanceDetail.Job_ID " & vbCrLf & _
"HAVING (((tblQCNonConformanceDetail.Job_ID)=[Forms]![frmNonConformance]![Job_ID]));"

 

[Ranman256]

either put in validation code so the user wont leave the record with an empty email
or
when sending emails, query only recs that have non null emails.

I do my checks via code (not in the field property)
All in 1 place, easier to change...say a SAVE_click event...

usage:
if IsValidForm() then docmd.openquery "qaAddNewRecord"

Private Function IsValidForm() As Boolean
Dim vMsg
Select Case True
   Case IsNull(dtpWeekOf)
      vMsg = "Date field missing"
   Case IsNull(cboUser)
      vMsg = "User name is missing"
   Case IsNull(txtEmail)
      vMsg = "Email field is missing"
End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required Field"
IsValidForm = vMsg = ""
End Function

 

[Pat Hartman]

If you always want to send an email when something is changed, don't rely on the user to do it. Do it in the AfterUpdate event of the subform.