Clear Form Fields and ReQuery

[Barne6ae]

I currently have Multiple Field Search Query that feeds from a main form. Query Result are dependent upon what is entered into the main form

Multiple Field Search Query SQL

SELECT tblMainTable.ItemID, tblMainTable.FiscalYear, tblMainTable.StoreName, tblMainTable.Item, tbleMainTable.ItemName
WHERE (((tblMainTable.FiscalYear)=[Forms]![frmMainForm]![txtFiscalYear]) AND ((tblMainTable.StoreName)=[Forms]![frmMainForm]!)) 

On the Main Form (frmMainForm) is a Multiple Field Seach Form pulling data from the multiple field search queary. I have a Sub Form (subfrmMainForm) that shows the results of the query.

Using the following VBA codes for command button upon click (Query and Clear)

Private Sub cmdbtnQuery_Click()
' Query Command Button 
Me.subformStoreItemSearch.Requery
End Sub
 
Private Sub cmdbtnClear_Click()
'When button is clicked clears the field on the main form
Me.txtFiscalYear = ""
Me.cboStoreName = ""
Me.cboItem = ""
Me.txtItemName = ""
Me.cboStoreLocation = ""
Me.txtState = ""
Me.txtCity = ""
 
'Requery subform
Me.SubfromStoreItemSearch.Requery
Me.subformStoreItemSearch.SetFocus
DoCmd.GoToRecord , , acLast
Me.txtFicalYear.SetFocus
 
End Sub

The Query Command Button works and the user can manually change the info on the form and hit the command button Query to requery the subform. However; when the user hits the Clear Command Button (to auto clear all the fields) the query button does not work anymore. I have to close out the form and to reopen it for the Query Button to work again.

[jforbes]

What do you mean it doesn't work anymore? Are you getting an error? Is the Form going Blank? Does everything seem to work OK, but the filtering is not returning any results?

It would be helpful to see the SQL of the Query used for subformStoreItemSearch.

Lastly, if I were to guess at a solution, I would try the following change (this is purely a guess):

Me.txtFiscalYear = year(now())
Me.cboStoreName = "*"
Me.cboItem = "*"
Me.txtItemName = "*"
Me.cboStoreLocation = "*"
Me.txtState = "*"
Me.txtCity = "*"

[ADezii]

1. Unless I am mistaken, the Data Source for your Sub-Form is
   

   SELECT tblMainTable.ItemID, tblMainTable.FiscalYear, tblMainTable.StoreName, tblMainTable.Item, tbleMainTable.ItemName
   WHERE (((tblMainTable.FiscalYear)=[Forms]![frmMainForm]![txtFiscalYear]) AND ((tblMainTable.StoreName)=[Forms]![frmMainForm]!))

2. Once you clear the Search Fields on the Main Form, the Query will return No Records since both Criteria have not been satisfied.
   

   tblMainTable.FiscalYear=[Forms]![frmMainForm]![txtFiscalYear] AND tblMainTable.StoreName=[Forms]![frmMainForm]![txtStoreName]

3. The Sub-Form, being based on this Query, will be Empty.
4. P.S. - ![txtStoreName] was omitted from the Query SQL.

[Barne6ae]

JForbes and Adezii

The data source is a Query (qryMainTableInput) pulling data tblMainTable based on what is inputted in a Form (frmMainFormInput)

Query SQL below

SELECT tblMainTable.ItemID, tblMainTable.FiscalYear, tblMainTable.StoreName, tblMainTable.Item, tblMainTable.ItemDescription, tblMainTable.AuthQty, tblMainTable.GEOLOC, tblMainTable.GEOLOCName
FROM tblMainTable
WHERE (((tblMainTable.FiscalYear)=[Forms]![frmMainFormInput]![txtFiscalYear]) AND ((tblMainTable.StoreName)=[Forms]![frmMainFormInput]![cboStoreName]) AND ((tblMainTable.Item)=[Forms]![frmMainFormInput]![cboItem])) OR (((tblMainTable.FiscalYear)=[Forms]![frmMainFormInput]![txtFiscalYear]) AND ((tblMainTable.StoreName)=[Forms]![frmMainFormInput]![cboStoreName]) AND (([Forms]![frmMainFormInput]![cboItem]) Is Null)) OR (((tblMainTable.FiscalYear)=[Forms]![frmMainFormInput]![txtFiscalYear]) AND ((tblMainTable.Item)=[Forms]![frmMainFormInput]![cboItem]) AND (([Forms]![frmMainFormInput]![cboStoringName]) Is Null)) OR (((tblMainTable.FiscalYear)=[Forms]![frmMainFormInput]![txtFiscalYear]) AND (([Forms]![frmMainFormInput]![cboItem]) Is Null) AND (([Forms]![frmMainFormInput]![cboStoringName]) Is Null));
 

The Subform (SubfromStoreItemSearch) for frmMainFormInput is based off of the Query Results (SQL above) based on the inputs of unbound text/combo boxes in frmMainFormInput.

Created a Query button on frmMainFormInput to requery SubfromStoreItemSearch.

Private Sub cmdbtnQuery_Click()
' Query Command Button 
Me.subformStoreItemSearch.Requery
End Sub
 

This Command button works with no issue, user is allowed to change the unbound text/combo boxes manually and requery the subform to show the results in the subform. However; when I inserted the below code for a Clear the fields in the frmMainFormInput so the user do not have to manually clear the text and combo boxes in frmMainFromInput and reset for a new query

Private Sub cmdbtnClear_Click()
'When button is clicked clears the field on the main form
Me.txtFiscalYear = ""
Me.cboStoreName = ""
Me.cboItem = ""
Me.txtItemName = ""
Me.cboStoreLocation = ""
Me.txtState = ""
Me.txtCity = ""
 
'Requery subform
Me.SubfromStoreItemSearch.Requery
Me.subformStoreItemSearch.SetFocus
DoCmd.GoToRecord , , acLast
Me.txtFicalYear.SetFocus
 
End Sub

Clear command button works, however; when user types in new info to Query (by Fiscal Year, Store Name, Item) and the user hits the Query Command Button the subform remains blank. But; when I go into the actual query (qryMainTableInput) the results show up. Or If the user exits out of the frmMainFromInput and re-opens the form the user can use the query button to populate the subform results. (until the clear command is executed).

Is their something wrong w/ the Clear Command Button VBA where it the form is not resetting for the user to query. Should I just delete the Clear Command Button and have the User just manually clear the form to query new info.

Hope this is clear as mud

Thanks

[jforbes]

ADezii may have anther option, but based on your Query, I would try this code for your Clear Button:

Private Sub cmdbtnClear_Click()
 'When button is clicked clears the field on the main form
 Me.txtFiscalYear.Value = Null
 Me.cboStoreName.Value = Null
 Me.cboItem.Value = Null
 Me.txtItemName.Value = Null
 Me.cboStoreLocation.Value = Null
 Me.txtState.Value = Null
 Me.txtCity.Value = Null
 ...
 End Sub

[Barne6ae]

JFordes, Thanks for the reply/input and it finally is working.