Refresh Subform with results

  • Thread starter Thread starter Woodies_46
  • Start date Start date
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
 
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?
 
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
 
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.
 
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...
 
Back
Top