Save subforms filtered results to table.

[ItsMe]

If you wish, you can take a look at this. Quite simply, I created a control that copies the recordset into your desired email table.



BTW, your form looks nice. When I build forms they are as appealing as a 1950's hospital.

[ItsMe]

I've started to look into your challenge. First, you need to change your validation process from "If Not IsNull(Me.s_PostalCode)" because when you click on the clear button, you clear it by inserting a "blank" space, which then make the field non-null.
For instance, if after clicking the Clear button you enter a company for criteria and clicked on the search, your condition when checking the PostalCode is no longer null, but a space. When you do the search, your statement is looking for PostalCode="" and Company="whatever the user entered"; thus the search fails, unless there is a record with a "blank" space for postal code.

Will keep looking into other problem areas.

mrojas is right. My all time favorite for discovering empty fields is John L. Viescas' IsNothing function. I use it all the time.

Public Function IsNothing(ByVal varValueToTest) As Integer
'-----------------------------------------------------------
' Does a "nothing" test based on data type.
' Null = nothing
' Empty = nothing
' Number = 0 is nothing
' String = "" is nothing
' Date/Time is never nothing
' Inputs: A value to test for logical "nothing"
' Outputs: True = value passed is a logical "nothing", False = it ain't
' Created By: JLV 01/31/95
' Last Revised: JLV 01/31/95
'-----------------------------------------------------------
Dim intSuccess As Integer
On Error GoTo IsNothing_Err
IsNothing = True
Select Case VarType(varValueToTest)
Case 0 ' Empty
GoTo IsNothing_Exit
Case 1 ' Null
GoTo IsNothing_Exit
Case 2, 3, 4, 5, 6 ' Integer, Long, Single, Double, Currency
If varValueToTest <> 0 Then IsNothing = False
Case 7 ' Date / Time
IsNothing = False
Case 8 ' String
If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
End Select

IsNothing_Exit:
On Error GoTo 0
Exit Function
IsNothing_Err:
IsNothing = True
Resume IsNothing_Exit
End Function

[lewis1682]

Thank you for your help. I am still confused about how I can get these filtered results to then make the labels and send emails. The problem is that not all the fields that are required for the labels is not on the subform but in the Contacts Database. I really wish there was a simple way to make this happen. I use to use some queries using the Like function and show the results in the subform from the textboxes. But then if the user hasn't imputed certain data then that data with blank fields would not show. That's when I moved onto using the filter which constructs the WHERE statement and displays the correct data. Now I just need a way to use this data. Maybe if I added a ID field would we be able to match the ID's using a query and then pull out all of the data with them ID's

[ItsMe]

Thank you for your help. I am still confused about how I can get these filtered results to then make the labels and send emails. The problem is that not all the fields that are required for the labels is not on the subform but in the Contacts Database. I really wish there was a simple way to make this happen. I use to use some queries using the Like function and show the results in the subform from the textboxes. But then if the user hasn't imputed certain data then that data with blank fields would not show. That's when I moved onto using the filter which constructs the WHERE statement and displays the correct data. Now I just need a way to use this data. Maybe if I added a ID field would we be able to match the ID's using a query and then pull out all of the data with them ID's

You are hitting the nail on the head. We understand you want to create emails from data within tables. First, you need a foundation that the user can not break. If you can wrap your head around writing some SQL to hold the value of a recordset you will be better off.

Are you able to transfer your filtered results to the subform's recordset?
Have you started to look for an alternative to the IsNull?

I believe the first step will be to get the data you want into that email table of yours. Once you understand how that works you will probably ask yourself why you moved it to the email table in the first place.

Let us know where you want to start. You asked a couple questions here in this thread and I believe they have been addressed. Have you tried the DB example I provided? Does this offer some of the functionality you need?

[June7]

Could open a recordset object in VBA and cycle through the records and send email to each. Writing to a 'temp' table should be a last resort when all else fails.

[lewis1682]

If you wish, you can take a look at this. Quite simply, I created a control that copies the recordset into your desired email table.

BTW, your form looks nice. When I build forms they are as appealing as a 1950's hospital.

Okay so I now have buttons which save the recordset onto the table with help from the file you sent me. However if there is blank field ie on FirstName then there is a error. It says "Invalid use of null" Error number 94. When I hit debug it takes me to the strFirstName = rcd![FirstName] Line. Do we need a way to skip it if it equals null?

[ItsMe]

Strings can not hold a null value. I commented in the code that you will need to do some validation. There is one example within the loop of verifying if data exists. I did not want to put too many validations in there. I did not want the code to become too confusing.

You don't have to do your validation right there either. You could create rules that this is a required field at the time of user input. Another approach is to hold many variable values.

Does the code I offered make sense? It is the approach I use to cycle through records from one table or recordset and update or export to other tables or files, etc. I just want to provide you with an opportunity to follow what you feel comfortable with. I have some time today to help you through it. Are you on a tight schedule to get this running?

[lewis1682]

Strings can not hold a null value. I commented in the code that you will need to do some validation. There is one example within the loop of verifying if data exists. I did not want to put too many validations in there. I did not want the code to become too confusing.

You don't have to do your validation right there either. You could create rules that this is a required field at the time of user input. Another approach is to hold many variable values.

Does the code I offered make sense? It is the approach I use to cycle through records from one table or recordset and update or export to other tables or files, etc. I just want to provide you with an opportunity to follow what you feel comfortable with. I have some time today to help you through it. Are you on a tight schedule to get this running?

Yes I am on tight schedule, it needs to be up by Friday, (20th). I am unsure how I can get this to work, if the fields it is saving to the table contains data it works, if it doesn't then it throws up the error and the record is missed off the table. Do you know what is going on? For example I may have only a company name with a address so no FirstName or last name. Then it throws up the error with invalid use of null and causes that record not to save into the table.

Thank you for the help you are very helpful.

[ItsMe]

Standby, I will post some steps to take. You can start by creating a module to hold a public function. Copy the function from post 17. Paste it into your module and comment within to give John L. Viescas credit.

[ItsMe]

I am assuming you are not working within the example that I added that huge ugly control. You need to paste the following after the string declarations within the code from that control (where you are getting the current error).

strFirstName = ""
strLastName = ""
strCompany = ""
strCounty = ""
strCategory = ""

[ItsMe]

Immediately after the Do until rcd.EOF line you need to replace "strFirstName = rcd![FirstName]"

with this

If Not IsNothing(rcd![FirstName]) Then
strFirstName = rcd![FirstName]
End If

[ItsMe]

Let me know where that takes you. It should illustrate some importatnt steps when creating recordsets. If it works for you, you may want to....

Do Until rcd.EOF 'EOF is the last record and contains no data

If Not IsNothing(rcd![FirstName]) Then
strFirstName = rcd![FirstName]
End If

If Not IsNothing(rcd![LastName]) Then
strLastName = rcd![LastName]
End If

If Not IsNothing(rcd![Company]) Then
strCompany = rcd![Company]
End If

If Not IsNothing(rcd![County]) Then
strCounty = rcd![County]
End If

If Not IsNothing(rcd![Category]) Then
strCategory = rcd![Category]
End If

rcd.MoveNext 'Move to the next Record in your Filtered set

'End code

This is you cycling through a recordset and, with adjustments, the principal can be applied to create your email, labels, etc. Another approach to cycle trhough records is Do While i < intMynumberOfRecords +1. There is more than one way to skin a cat. You can also use SQL SELECT INSERT and WHERE. I am not great at writing SQL.

[lewis1682]

That works perfectly thank you so much.

[ItsMe]

If you like those results you should start considering passing your original where clause to the subform's recordsource. Currently, it is passed to the .filter and then to the recordsource. Also, mrojas mentioned the isnull thingy you got going on there. See if you can't incorporate the IsNothing function you now have in your toolbox. Use the example here and post if you have questions.

[lewis1682]

How will I add in a checkbox into the table, if I have a field on the Filter_Results and on the subform have one called Include I want to add in the -1 or 0 into the Filter_Results?