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

[WesHarding]

Hi Everyone,



To begin, I have a search form that gives me results on a number of parameters such as first last name, farm name, community etc. This form runs off of a query, that draws information from two tables: tbl_contacts and tbl_WHO (basically a table detailing farm name and other one to many details).

I have created an add form that successfully puts information into these tables. Where my problem arises however, is these are not showing up in the query that draws from these tables (and thus feeds into my search form).

Has anyone had this issue before? All advice is greatly appreciated.

Sincerely,

Wesley

[ranman256]

so if you can open the table, find the new records,
then SOMETHING in the query is preventing the retrieval. Either the filter, the criteria, a refresh.

[WesHarding]

I removed the filtering criteria and they appear in the query thank you!

Now, I want to keep the search capabilities of this morning. So the question is: what about my criteria is stopping these new records from entering the query?

here is the SQL view of the query I am using:

SELECT tblContacts.FirstName1, tblContacts.LastName1, tblContacts.FarmerContactID, tblWHO.Phone1C1, tblWHO.Phone2C1, tblWHO.Phone1C2, tblWHO.Phone1C3, tblWHO.FarmName, tblWHO.FarmPhone1, tblWHO.FarmPhone2, tblWHO.FarmCivicAddress, tblWHO.FarmCommunity, tblWHO.FarmPostalCode, tblWHO.Province, tblWHO.Email, tblWHO.Website, tblWHO.Fax, tblWHO.Facebook, tblWHO.DateofEntry, tblWHO.Notes, tblWHO.RR, tblWHO.FarmerID, tblWHO.VERIFIED
FROM tblWHO LEFT JOIN tblContacts ON tblWHO.FarmerID = tblContacts.[FarmerContactID]
WHERE (((tblContacts.FirstName1) Like "*" & [Forms]![MainSearchForm]![txtfirst] & "*") AND ((tblContacts.LastName1) Like "*" & [Forms]![MainSearchForm]![txtlast] & "*") AND ((tblWHO.FarmName) Like "*" & [Forms]![MainSearchForm]![txtfarm] & "*") AND ((tblWHO.FarmCivicAddress) Like "*" & [Forms]![MainSearchForm]![txtaddress] & "*") AND ((tblWHO.FarmCommunity) Like "*" & [Forms]![MainSearchForm]![txtcommunity] & "*"));


Can you see my issue in here?

[aytee111]

You are using AND's, which means that all the fields on the form must contain a value, none can be blank. Could that be the problem?

[WesHarding]

Absolutely. I noticed that while looking at my SQL, and it seems that from other forums i've read this is often a common issue. I have attempted to get rid of these and values. However, I am so far unsuccessful with my attempts. I am doing these configurations in the design tab of the query, and it seems that these "AND" values may be a default I have to change. Any ideas?

[aytee111]

I have been searching for a recent post which covered this but can't seem to find it. There are a few ways to do this, but basically you have to build the SQL string in VBA. Here is one that I found:

Dim SQL As String

SQL = "SELECT tblName.* From tblName WHERE (1=1)"

If Not IsNull(Me.combo1) Then
    SQL = SQL & " And ([Field1] Like ""*" & Me.combo1 & "*"")" ' I am using like statements here, but that is because this is a search tool.
End If

If Not IsNull(Me.combo2) Then
    SQL = SQL & " And ([Feild2] Like ""*" & Me.combo2 & "*"")"
End If

Docmd.RunSQL SQL

End Sub

[WesHarding]

This would be my first time involving and SQL with a query so I may need to tip toe through this. Would I get into the code builder through the query or would I add this code to the VBA where one clicks (button) to activate the query?

[aytee111]

This would be done just before the query runs, it sounds like it is a button which runs the query so that is the right place. The code above is not a query, it is an SQL string that is created and is run thru VBA alone and the query does not exist. You can do it this way or you can make the change to an existing query. It is the before and after that is different, the concatenation of the WHERE string stays the same.

To change an existing query:

Dim qdf As QueryDef, strSQL AS String
Set qdf=CurrentDB.QueryDefs("qryname")
...create the SQL
qdf.SQL=strSQL

[WesHarding]

I have attempted my SQL statement. However, I am receiving an error upon attempting a search.

"Invalid SQL statement expected: 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

Private Sub Command68_Click()
Dim SQL As String

SQL = "qry_contacts_WHO"
Me.Form.RecordSource = SQL


Me.Form.RecordSource = SQL

Me.Form.Requery


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
DoCmd.RunSQL SQL
End Sub


The top SQL is my search to activate the query. Perhaps I need to make changes towards that?

[aytee111]

Firstly, you are using the string at the top as the record source but you haven't created it yet. The record source can be set during design mode of the form and not touched again. Then at the end you are running the query - what do you want, to see the query results or to requery the form?

In creating the string you are using two variables - strSQL and SQL - select one and use that one throughout. Remove (1=1), ythat isn't part of your database.

Change the DoCmd.Run to Debug.Print - this will show you what the query looks like before trying to actually run it. Once it shows you what the string contains, copy and paste it into a new query design and try and run it - see if it runs successfully. You will be able to tell easily where the errors are occurring.

Change the double/double quotes to singles : " and ([FirstName1]) like ""*" & Me.txtfirst & "*"")" ', should be
" and ([FirstName1]) like '*" & Me.txtfirst & "*')"

[WesHarding]

Well I got it to work! Unfortunately it looks to be that this isn't an "AND" or "OR" problem sadly. I am still have the same issue

I also tried another method from a forum about using the Nz function for each field, which also didn't give success. I'm not sure what else about the query is stopping these records from appearing

[aytee111]

Post your SQL.

[WesHarding]

Correction: I did some tinkering around in SQL of the query. It is literally exactly the conditions I placed on the search query that are inhibiting new records. If I made them "OR" new records appear, but the downside is now that the search capabilities are gone for the form.

[aytee111]

I can't help you unless I have things to look at, like your resulting SQL and the code to produce it.

[WesHarding]

oh i'm sorry I thought I posted it. My mistake.

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,"") & "*"));