Requery not updating form

[JOD59]

I have a DAO database that has a search built in SQL that creates a query with the search results each time its run. To activate the search you click a command button. The rest of the form has text boxes to display the results. The problem that I'm having is that when I run the search, the text boxes don't up date with the search information until I close the form, and reopen it. Is there a way of refreshing the information each time the search is run. I have tried requery, recalc, refresh, repaint etc. with no luck. Thanks for any help

 

[PWise]

i imagin that you have something like this
set rst = mydb.openrecordset ("select....from xxx")
what you have to do now is
me.textname = rst!name
me.textdate = rst!date

 

[JOD59]

pwise, I'm not sure what you mean, I'm new to all this. Here is the code I used for the search. Everything works fine except it will not requery the form unless I close and reopen it. Thanks



Private Sub Search_Data_Click()
' Pointer to error handler
On Error GoTo Search_Data_Click_err
' Declare variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strZone As String
Dim strScrap As String
Dim strTag As String
Dim strBldg As String
Dim strContact As String
Dim strDesc As String
Dim strSQL As String
Dim stDocName As String
Dim stRepName As String
Dim frmMain As Form

Set frmMain = Forms!FormZoneAll


' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryLookUp") Then
Set qdf = db.CreateQueryDef("qryLookUp")
Else
Set qdf = db.QueryDefs("qryLookUp")
End If
' Get the values from the combo boxes
If IsNull(Me.cboZone.Value) Then
strZone = " Like '*' "
Else
strZone = "=" & Me.cboZone.Value & " "
End If
If IsNull(Me.cboScrap.Value) Then
strScrap = " Like '*' "
Else
strScrap = "='" & Me.cboScrap.Value & "' "
End If
If IsNull(Me.cboBldg.Value) Then
strBldg = " Like '*' "
Else
strBldg = "='" & Me.cboBldg.Value & "' "
End If
If IsNull(Me.cboContact.Value) Then
strContact = " Like '*' "
Else
strContact = "='" & Me.cboContact.Value & "' "
End If
If IsNull(Me.cboDesc.Value) Then
strDesc = " Like '*' "
Else
strDesc = "='" & Me.cboDesc.Value & " '"
End If
If IsNull(Me.cboTag.Value) Then
strDesc = " Like '*' "
Else
strDesc = "='" & Me.cboTag.Value & " '"
End If

strSQL = "SELECT [ID], [Contact], [Item], [BusinessModel], [equip],[Zone].Tag_Red_Yellow, [KeeporScrap], " & _
"[EquipID], [Asset], [Description], [Manufacturer], [Model], [CLBldg], [CLRoom], [FLBldg]," & _
"[FLRoom], [Zone]" & _
"FROM [Zone] " & _
"WHERE [Zone].Zone" & strZone & _
"And [Zone].KeeporScrap" & strScrap & _
"And [Zone].[Tag_Red_Yellow]" & strTag & _
"And [Zone].CLBldg" & strBldg & _
"And [Zone].Contact" & strContact & _
"And [Zone].Description" & strDesc & _
"ORDER BY [Zone].Zone;"



' Pass the SQL string to the query


qdf.SQL = strSQL



' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryLookUp") = acObjStateOpen Then
DoCmd.Close acQuery, "qryLookUp"
End If
' Open the report
stDocName = "qryLookUp"
stRepName = "SHIREall"

If IsNull(Me.RepType.Value) Then
DoCmd.OpenReport stRepName, acViewPreview
Else
If Me.RepType.Value = "Access" Then
DoCmd.OpenReport stRepName, acViewPreview
Else
If Me.RepType.Value = "Excell" Then

DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, "C:\Test.xls", True

End If
End If
End If



Search_Data_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
Search_Data_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume Search_Data_Click_exit
frmMain.Recordset.Requery


End Sub

 

[PWise]

what would you want to requery on the form?
do you want that the results of the query should show on the form ?

 

[JOD59]

Yes, thats what I would like. The way the query is set up is each search rewrites the query that is linked to the form. Thanks

 

[traingamer]

Try Me.Requery rather than frmMain.Recordset.Requery.



Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill