Newly Added Records Not Appearing in Query or Search Form Output---Any Ideas??

[aytee111]

What is this? It doesn't match your last post (#9)

[aytee111]

If you are lost and confused, don't worry, that's allowed! Happens to everyone.

[WesHarding]

Yes sorry, I went to bad over being a little tired out from pecking away at that.
I isolated the problem to the SQL of the query that I use. I have the SQL of the button that activates the query working just fine. When I began alter the "AND" and "OR" relationships between criteria in the query (qry_contacts_WHO) I isolated the problem. I can confirm it is an "AND" problem. When I replaced all of the "AND"s with "OR", all my newly input records appeared in the query. However, using the "OR" made my search method useless as it now doesn't really allow my criteria to sieve through my contacts.

Do you have any thoughts on a way to retain my search capabilities while keep the "OR" between criteria? I will re-post the SQL and bold the isolated problem makers.

SELECT tblWHO.FarmerID, tblWHO.Phone1C1, tblWHO.Phone2C1, tblWHO.FarmName, tblWHO.FarmCivicAddress, tblWHO.FarmCommunity, tblWHO.FarmPostalCode, tblWHO.Province, tblWHO.Email, tblWHO.Website, tblWHO.Facebook, tblWHO.Notes, tblWHO.RR, tblWHO.VERIFIED, tblContacts.ContactID, tblContacts.FirstName1, tblContacts.LastName1, tblContacts.FarmerContactID
FROM tblWHO LEFT JOIN tblContacts ON tblWHO.FarmerID = tblContacts.FarmerContactID
WHERE (((tblWHO.FarmName) Like "*" & Nz(Forms!MainSearchForm!txtfarm,"") & "*") And ((tblWHO.FarmCivicAddress) Like "*" & Nz(Forms!MainSearchForm!txtaddress,"") & "*")) Or (((tblWHO.FarmCommunity) Like "*" & Nz(Forms!MainSearchForm!txtcommunity,"") & "*") And ((tblContacts.FirstName1) Like "*" & Nz(Forms!MainSearchForm!txtfirst,"") & "*")) OR (((tblContacts.LastName1) Like "*" & Nz(Forms!MainSearchForm!txtlast,"") & "*"));


I found it odd that just those two were the problem makers, but through trials one at a time, that's what I determined

[aytee111]

I am still confused, it looks as tho we are back to post #1 and posts 6-10 have been thrown out. I am looking for the Debug.Print of the query that results from that piece of code.

[WesHarding]

I ran the sql that you assisted in helping me design with the debug.print, however I must be doing something wrong. I am not presented with the debug.print of the query

The query still runs but the new records still are not appearing, here is the SQL I entered with your assistance.

Private Sub Command223_Click()
Dim qdf As QueryDef, strSQL As String
Set qdf = CurrentDb.QueryDefs("qry_contacts_WHO")
strSQL = "SELECT tbl_WHO.* From tbl_WHO WHERE (1=1)"
If Not IsNull(Me.txtfirst) Then
SQL = SQL & " and ([FirstName1]) like '*" & Me.txtfirst & "*')"
End If
If Not IsNull(Me.txtlast) Then
SQL = SQL & " and ([LastName1]) like '*" & Me.txtlast & "*')"
End If
If Not IsNull(Me.txtfarm) Then
SQL = SQL & " and ([FarmName]) like '*" & Me.txtfarm & "*')"
End If
If Not IsNull(Me.txtaddress) Then
SQL = SQL & " and ([FarmCivicAddress]) like '*" & Me.txtaddress & "*')"
End If
If Not IsNull(Me.txtcommunity) Then
SQL = SQL & " and ([FarmCommunity]) like '*" & Me.txtcommunity & "*')"
End If
Debug.Print


Me.Requery


End Sub

[aytee111]

You need to tell it what to print
Debug.Print something.

Before you do this, revisit post 10

[WesHarding]

Okay some progress! I made the corrections after reviewing post 10. The attempted to get the debugger to run the query. I don't believe I made the correct assumption. I receive an invalid or unqualified reference from my attempt.

Dim qdf As QueryDef, strSQL As String
Set qdf = CurrentDb.QueryDefs("qry_contacts_WHO")
strSQL = "SELECT tbl_WHO.* From tbl_WHO"
If Not IsNull(Me.txtfirst) Then
SQL = SQL & " and ([FirstName1]) like '*" & Me.txtfirst & "*')"
End If
If Not IsNull(Me.txtlast) Then
SQL = SQL & " and ([LastName1]) like '*" & Me.txtlast & "*')"
End If
If Not IsNull(Me.txtfarm) Then
SQL = SQL & " and ([FarmName]) like '*" & Me.txtfarm & "*')"
End If
If Not IsNull(Me.txtaddress) Then
SQL = SQL & " and ([FarmCivicAddress]) like '*" & Me.txtaddress & "*')"
End If
If Not IsNull(Me.txtcommunity) Then
SQL = SQL & " and ([FarmCommunity]) like '*" & Me.txtcommunity & "*')"
End If
Debug.Print .qry_contacts_WHO


Me.Requery


End Sub

[aytee111]

Don't come back here until you have followed the instructions in post 10

[WesHarding]

Hi again,

I felt like I was being a little annoying as I had literally no idea what was going on with the code. I didn't want to be of any more confusion to you or those following this thread, so I went out and scowered/tested until I FINALLY fixed this. It actually wasn't that hard, but I didn't understand the concept of what was going on with my database which made things confusing for me.

long story short it was a null value issue.

For those looking for the fix:

Nz(your variant such at Forms!Form!txtbox, isnull(your variant such at Forms!Form!txtbox))

why this was so hard for me to grasp, I dont know. Just another learning curve with this program. thank you for the patience once again aytee111