Refresh Subform with results

W

Woodies_46

Hi all i'm just having a little trouble.

What I have is a search form called [Searchfrm] with critera on it,
tick boxs and stuff. On this form I have a subform called {subsearch]
the feilds in this table are linked to a query. Down the bottom of the
the form I have a search button when click writes up a SQL and sends it
to the premade query which works great.

The trouble is when I go to refresh the subfrom[subsearch] on
[searchfrm] it dosn't update with the new information. It seems to
flash so it looks like it trying to refresh but the prevoiues values
stay there :(.

The only time I can get it to show me the corrrect results is if I
close and open the form agian .

Any help would be really apprectiated.

Thanks
 
D

Duane Hookom

Apparently you are running some code in the On Click of the button. Could
you share this code as well as the Record Source of the subform?
 
W

Woodies_46

Ok the code for the button is,

'VARS
Dim sqlStr As String, ctl As Control, str As String, wherestr As String
str = "[Maps And Plans]" & "." & "[Map_ID]" & ", " & "[Maps And Plans]"
& "." & "[Titile]" & ", " & "[Maps And Plans]" & "." & "[Plan Map No]"
& ", "
sqlStr = ""
wherestr = "WHERE "

'ITERATE CONTROLS
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl = True Then

End If
ElseIf ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
wherestr = wherestr & "(((" & "[Maps and Plans]" & "." & "[" &
ctl.Name & "]" & ")" & "Like" & "'" & ctl.Value & "'" & ")" & ")"
End If
End If
Next ctl


'NO CONTROLS CHOSEN OR COMPLETED
If wherestr = "WHERE " Then
MsgBox "Please select a box", , "System Name"
wherestr = "WHERE"
Exit Sub
End If


'TRIM AND DROP TRAILING COMMA
'sqlStr = Trim(sqlStr)
'sqlStr = Left(sqlStr, Len(sqlStr) - 1)


'SHOW DATA
sqlStr = "SELECT " & "[Maps and Plans].*" & " FROM [Maps and Plans] " &
wherestr & ";"
CurrentDb.Querydefs("Maps and Plans Query").SQL = sqlStr

'REFRESH RESULTS
Me.[QrySubSearchBox].Form.Requery


And the record Source for the subform is Maps and Plans Query
 
D

Duane Hookom

I would just use:
Me.[QrySubSearchBox].Form.RecordSource = sqlStr

Consider simplifying to this:
str = "[Maps And Plans].[Map_ID], [Maps And Plans].[Titile]" _
& ", [Maps And Plans].[Plan Map No], "

Also, notice your Titile field might be a typo that would prohibit your sql
update.
 
D

Duane Hookom

You can also add code like:
debug.Print sqlStr
near the end of your code to check for errors.
--
Duane Hookom
MS Access MVP
--

Duane Hookom said:
I would just use:
Me.[QrySubSearchBox].Form.RecordSource = sqlStr

Consider simplifying to this:
str = "[Maps And Plans].[Map_ID], [Maps And Plans].[Titile]" _
& ", [Maps And Plans].[Plan Map No], "

Also, notice your Titile field might be a typo that would prohibit your
sql update.
--
Duane Hookom
MS Access MVP
--

Ok the code for the button is,

'VARS
Dim sqlStr As String, ctl As Control, str As String, wherestr As String
str = "[Maps And Plans]" & "." & "[Map_ID]" & ", " & "[Maps And Plans]"
& "." & "[Titile]" & ", " & "[Maps And Plans]" & "." & "[Plan Map No]"
& ", "
sqlStr = ""
wherestr = "WHERE "

'ITERATE CONTROLS
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl = True Then

End If
ElseIf ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
wherestr = wherestr & "(((" & "[Maps and Plans]" & "." & "[" &
ctl.Name & "]" & ")" & "Like" & "'" & ctl.Value & "'" & ")" & ")"
End If
End If
Next ctl


'NO CONTROLS CHOSEN OR COMPLETED
If wherestr = "WHERE " Then
MsgBox "Please select a box", , "System Name"
wherestr = "WHERE"
Exit Sub
End If


'TRIM AND DROP TRAILING COMMA
'sqlStr = Trim(sqlStr)
'sqlStr = Left(sqlStr, Len(sqlStr) - 1)


'SHOW DATA
sqlStr = "SELECT " & "[Maps and Plans].*" & " FROM [Maps and Plans] " &
wherestr & ";"
CurrentDb.Querydefs("Maps and Plans Query").SQL = sqlStr

'REFRESH RESULTS
Me.[QrySubSearchBox].Form.Requery


And the record Source for the subform is Maps and Plans Query
Click to expand...
Click to expand...
 
Top