Where string updates 1ste record in table

[RustyDWO]

I have this search form finaly working that it shows the search result in a subform.
In brief the idea of how it works.
In a form I have 5 combo's for search criteria which are combined to a search string.
When hit the search button the code below uses this string as the WHERE function.
All goes as planned, however when leaving the search form, the search criteria, also when all empty, is put on the first record of the table attached to the searchform!

What can be the problem here?

Here is the code:

Private Sub Zoekknop_Click()
Dim strSQL As String
Dim strWhere As String
strWhere = Zoeken_op()

strSQL = "SELECT DISTINCTROW Organisaties.Plaats, " & _
"Organisaties.Doelgroep, Organisaties.Categorie, " & _
"Organisaties.Subcategorie, Organisaties.Trefwoord, " & _
"Organisaties.Organisatienaam, Organisaties.OrganisatieID as Recordnr " & _
"FROM Organisaties " & _
"WHERE " & strWhere & ";"

Me!Zoekresultaat.Form.RecordSource = strSQL
Me!Zoekresultaat.Form.Requery

If Me.Zoekresultaat.Form.RecordsetClone.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "Er zijn geen aktiviteiten die voldoen aan de zoek criteria.", vbInformation
Me.Catzoek.SetFocus
Exit Sub
End If

Exit_Zoekknop_Click:
Exit Sub

End Sub

 

[downwitchyobadself]

Hi,

The coding problem is probably not in the Sub you provided, but in the Zoeken_op() function returning the query's where.

On another note, you don't need both these lines

Me!Zoekresultaat.Form.RecordSource = strSQL
Me!Zoekresultaat.Form.Requery

as switching the RecordSource is an automatic requery--you're requerying twice, which means that part of the procedure takes twice as long to run.

Providing the code to the function that creates the WHERE statement will probably help get you an answer.

 

[RustyDWO]

'downwitchyobadself'
Thanks for the reply. B-)
Here is the code for the WHERE statement

Private Function Zoeken_op() As String
Dim strWhere As String

If Not IsNull(Me!Plaatszoek) Then
strWhere = "[Organisaties].[Plaats] = " & Chr(34) & Me!Plaatszoek & Chr(34)
End If

If Not IsNull(Me!Doelgroepzoek) Then
If strWhere <> &quot;&quot; Then
strWhere = strWhere & &quot; AND &quot;
End If
strWhere = strWhere & &quot;[Organisaties].[Doelgroep] = &quot; & Chr(34) & Me!Doelgroepzoek & Chr(34)
End If

This last double IF statement is repeated 3 more times!

Print strWhere

Zoeken_op_Afsluiten:
Zoeken_op = strWhere

End Function


By deleting those 3 extra IF's I noticed this Print statement which was used for testing.
Can this be the problem?
Also for some reason I can't loose the & char(34) because then it doesn't work nomore.

Thanks for replying

 

[downwitchyobadself]

Please also provide an explanation of the field types involved--I don't read Dutch, so I don't even know what types of fields you're querying.

It would be helpful to see information on each field--if you're using Chr(34) they had best all be text fields--plus a finished result of the function, and maybe even a sample of what data you're getting or not getting back that is the error.

 

[RustyDWO]

'downwitchyobadself'
Thanks for the quick reply.

Here is the result of the WHERE function

[Organisaties].[Plaats] = &quot;Bergschenhoek&quot; AND [Organisaties].[Categorie] = &quot;Educatie&quot; AND [Organisaties].[Subcategorie] = &quot;Taal / Rekenen&quot; AND [Organisaties].[Trefwoord] = &quot;Engels&quot;

All field types are TEXT

If the searchform is closed the words between &quot;&quot; are placed in the first record of the table, which is the RecordSource of the Form, in the respectieve fields.

 

[RustyDWO]

'downwitchyobadself'

Does the previous post give enough info now?

I got the tip to change the form's data property DATA ENTRY from NO to YES.
Although this seemed highly unlikely, the table is not modified nomore.

Can anybody explain this to me please?

Thanks for any reply