The simplest way to deal with it is to add a line of code to your function which says, in effect, if the checkbox isn't checked, then exit the function.
Assuming that your function is behind the form, and the checkbox is named checkbox22 and is on either the form or the subform, that line would be one of these two, respectively.
Code:
If NOT Me.checkbox22 Then Exit Function
If NOT Me.lnkToSubform.Form.Checkbox22 Then Exit Function
If the function is in a general module, then you'll need to establish addressability to the form first, something like this
Code:
If NOT Forms!MyForm.Checkbox22 Then Exit Function
If NOT Forms!MyForm.lnkToSubForm.Form.Checkbox22 Then Exit Function
You can see this page http://access.mvps.org/access/forms/frm0031.htm for a handy guide to how to refer to a particular control, based upon where you are.
QUICK REWRITE AND CLEANUP
I took the liberty of straightening up your function while I was looking at it. You weren't clearing out the values after each record, so if the second lastname was NULL, you were loading the first record's lastname value into the second record's lastname field, and so on.
Code:
Public Function addRecords()
Dim db As Object
Dim rcd As object
Dim rcdNew As DAO.Recordset
Dim intCount As Integer
'Table Variables
Dim strFirstName As String
Dim strLastName As String
Dim strCompany As String
Dim strCounty As String
Dim strCategory As String
Dim strTitle As String
Dim strAddressLine1 As String
Dim strAddressLine2 As String
Dim strPostalCode As String
Dim strTown As String
Dim strEmail As String
Dim numID As Integer
' skip the whole routine if the box isn't checked
If NOT Me.checkbox22 Then Exit Function
'Delete old results
DoCmd.OpenQuery "Filter_Results_Delete"
'Address current database
Set db = CurrentDb
'Open Email Table recordset
Set rcdNew = db.OpenRecordset("Filter_Results", dbOpenDynaset)
'Open your master recordset
Set rcd = Me.View_Contacts_subform.Form.RecordsetClone
' Exit the routine if there are no records to process.
intCount = rcd.RecordCount
If intCount = 0 Then Goto addRecords_Complete
'Move to the first record in your filtered set
rcd.MoveFirst
numID = 0
'********************************************
' Copy each record in the filtered master set
' to the results set
'********************************************
Do Until rcd.EOF
numID = numID + 1
' For each field in the record, we take the input and
' concatenate it to the empty string "". That way,
' if the old input is Null or empty, then the new
' field string will at least be a valid empty string.
strFirstName = "" & rcd![FirstName]
strLastName = "" & rcd![LastName]
strCompany = "" & rcd![Company]
strCounty = "" & rcd![County]
strCategory = "" & rcd![Category]
strTitle = "" & rcd![Title]
strAddressLine1 = "" & rcd![AddressLine1]
strAddressLine2 = "" & rcd![AddressLine2]
strTown = "" & rcd![Town]
strEmail = "" & rcd![Email]
strPostalCode = "" & rcd![PostalCode]
'Create a new record in your email table
rcdNew.AddNew
'populate the fields
rcdNew![FirstName] = strFirstName
rcdNew![LastName] = strLastName
rcdNew![Company] = strCompany
rcdNew![County] = strCounty
rcdNew![ID] = numID
rcdNew![Category] = strCategory
rcdNew![Title] = strTitle
rcdNew![AddressLine1] = strAddressLine1
rcdNew![AddressLine2] = strAddressLine2
rcdNew![Town] = strTown
rcdNew![Email] = strEmail
rcdNew![PostalCode] = strPostalCode
'Save the record
rcdNew.Update
'Move to the next Record in your Filtered set
rcd.MoveNext
Loop
'tidy things up
addRecords_Complete:
Set rcd = Nothing
rcdNew.Close
Set rcdNew = Nothing
Set db = Nothing
End Function
Various notes -
For each field in the record, we take the input and concatenate it to the empty string "". That way, if the old filtered input is Null or empty, then the new field string will at least be a valid empty string.
Since intCount isn't being used anywhere, it was probably intended to exit the routine if there were no records to process. So I coded that.
Since numID never got a value, I just coded it as sequential from 1 by 1.
I moved the rcd.MoveNext line after you complete processing the prior record. It's not a good idea to do it in the other order, since when you come back to support the code in a year, you might forget the record isn't there anymore and try to do something with the record that was already left behind...
The EOF property being true means there are no more records to process. You shouldn't think of that has having a record with all NULL values, so much as having no record at all.
In general, I would prefer to test that checkbox's value before calling the routine, but if that's not practical, then the way it's coded here should work. However, it's all aircode, so be sure to back up your database before using this code, and be very skeptical until you've proved to yourself that it works. Then back up the databse again. "Too many backups" is a nonsense phrase.
Running code in function if checkbox is ticked
Windows 7 32bit 
Access 2003
Reply With Quote
Originally Posted by lewis1682
