Query works but form does not display results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have designed a query that takes 3 parameters from three combo boxes on a
form. Any combination of the parameters or none is acceptable. when I run the
query with the form closed I get the parameter boxes and the proper results
dependent upon what data I supply. If I open the form and select my
parameters from the combo boxes only the first combo box works (changes the
results in the subform) however if I leave the form open and run the raw
query I get the right results. I have placed a button on the form that
executes the query and it also retrieves the right data but it opens a new
datasheet. In short I get the records I want but not where I want to see
them. I have includded the significant code and could use some help before I
pull out the last few hairs I have left.

Private Sub cboTypeSelect_AfterUpdate()
Me.frm_qryTypeSelect.Requery
End Sub

Private Sub cboGradeSelect_AfterUpdate()
Me.frm_qryTypeSelect.Requery
End Sub

Private Sub cboNameSelect_AfterUpdate()
Me.frm_qryTypeSelect.Requery
End Sub

Private Sub cmd_qryTypeSelect_Click()
On Error GoTo Err_cmd_qryTypeSelect_Click

Dim stDocName As String

stDocName = "qryProductSelect"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmd_qryTypeSelect_Click:
Exit Sub

Err_cmd_qryTypeSelect_Click:
MsgBox Err.Description
Resume Exit_cmd_qryTypeSelect_Click

End Sub

SELECT [Product Inventory].P_Type, [Product Inventory].P_Grade, [Product
Inventory].P_Name
FROM [Product Inventory]
WHERE ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND (([Product
Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect]) AND
(([Product Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect])) OR
((([Product Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect])
AND (([Product Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect])
AND (([Forms]![frmProductSelect]![cboTypeSelect]) Is Null)) OR ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND (([Product
Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect]) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null)) OR ((([Product
Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect]) AND
(([Forms]![frmProductSelect]![cboTypeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null)) OR ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND (([Product
Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect]) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null)) OR ((([Product
Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect]) AND
(([Forms]![frmProductSelect]![cboTypeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null)) OR ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null)) OR
((([Forms]![frmProductSelect]![cboTypeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null));
 
I've never found requerying in Access very easy or predictable - there's also
refresh or DoCmd.RunCommand acCmdRefresh.

Hang on, I think you need to requery the Form within the subform, not just
the subform object. Try this instead:
Me.frm_qryTypeSelect.Form.Requery



the alchemist said:
I have designed a query that takes 3 parameters from three combo boxes on a
form. Any combination of the parameters or none is acceptable. when I run the
query with the form closed I get the parameter boxes and the proper results
dependent upon what data I supply. If I open the form and select my
parameters from the combo boxes only the first combo box works (changes the
results in the subform) however if I leave the form open and run the raw
query I get the right results. I have placed a button on the form that
executes the query and it also retrieves the right data but it opens a new
datasheet. In short I get the records I want but not where I want to see
them. I have includded the significant code and could use some help before I
pull out the last few hairs I have left.

Private Sub cboTypeSelect_AfterUpdate()
Me.frm_qryTypeSelect.Requery
End Sub

Private Sub cboGradeSelect_AfterUpdate()
Me.frm_qryTypeSelect.Requery
End Sub

Private Sub cboNameSelect_AfterUpdate()
Me.frm_qryTypeSelect.Requery
End Sub

Private Sub cmd_qryTypeSelect_Click()
On Error GoTo Err_cmd_qryTypeSelect_Click

Dim stDocName As String

stDocName = "qryProductSelect"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmd_qryTypeSelect_Click:
Exit Sub

Err_cmd_qryTypeSelect_Click:
MsgBox Err.Description
Resume Exit_cmd_qryTypeSelect_Click

End Sub

SELECT [Product Inventory].P_Type, [Product Inventory].P_Grade, [Product
Inventory].P_Name
FROM [Product Inventory]
WHERE ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND (([Product
Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect]) AND
(([Product Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect])) OR
((([Product Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect])
AND (([Product Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect])
AND (([Forms]![frmProductSelect]![cboTypeSelect]) Is Null)) OR ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND (([Product
Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect]) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null)) OR ((([Product
Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect]) AND
(([Forms]![frmProductSelect]![cboTypeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null)) OR ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND (([Product
Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect]) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null)) OR ((([Product
Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect]) AND
(([Forms]![frmProductSelect]![cboTypeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null)) OR ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null)) OR
((([Forms]![frmProductSelect]![cboTypeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null));
Click to expand...
 
Hey Martin thanks

I figured it out (very late last night). It came down to a lot of ANDs and
ORs until I had all of the combinations covered without having one cancel out
another

Martin said:
I've never found requerying in Access very easy or predictable - there's also
refresh or DoCmd.RunCommand acCmdRefresh.

Hang on, I think you need to requery the Form within the subform, not just
the subform object. Try this instead:
Me.frm_qryTypeSelect.Form.Requery



the alchemist said:
I have designed a query that takes 3 parameters from three combo boxes on a
form. Any combination of the parameters or none is acceptable. when I run the
query with the form closed I get the parameter boxes and the proper results
dependent upon what data I supply. If I open the form and select my
parameters from the combo boxes only the first combo box works (changes the
results in the subform) however if I leave the form open and run the raw
query I get the right results. I have placed a button on the form that
executes the query and it also retrieves the right data but it opens a new
datasheet. In short I get the records I want but not where I want to see
them. I have includded the significant code and could use some help before I
pull out the last few hairs I have left.

Private Sub cboTypeSelect_AfterUpdate()
Me.frm_qryTypeSelect.Requery
End Sub

Private Sub cboGradeSelect_AfterUpdate()
Me.frm_qryTypeSelect.Requery
End Sub

Private Sub cboNameSelect_AfterUpdate()
Me.frm_qryTypeSelect.Requery
End Sub

Private Sub cmd_qryTypeSelect_Click()
On Error GoTo Err_cmd_qryTypeSelect_Click

Dim stDocName As String

stDocName = "qryProductSelect"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmd_qryTypeSelect_Click:
Exit Sub

Err_cmd_qryTypeSelect_Click:
MsgBox Err.Description
Resume Exit_cmd_qryTypeSelect_Click

End Sub

SELECT [Product Inventory].P_Type, [Product Inventory].P_Grade, [Product
Inventory].P_Name
FROM [Product Inventory]
WHERE ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND (([Product
Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect]) AND
(([Product Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect])) OR
((([Product Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect])
AND (([Product Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect])
AND (([Forms]![frmProductSelect]![cboTypeSelect]) Is Null)) OR ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND (([Product
Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect]) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null)) OR ((([Product
Inventory].P_Name)=[Forms]![frmProductSelect]![cboNameSelect]) AND
(([Forms]![frmProductSelect]![cboTypeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null)) OR ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND (([Product
Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect]) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null)) OR ((([Product
Inventory].P_Grade)=[Forms]![frmProductSelect]![cboGradeSelect]) AND
(([Forms]![frmProductSelect]![cboTypeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null)) OR ((([Product
Inventory].P_Type)=[Forms]![frmProductSelect]![cboTypeSelect]) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null)) OR
((([Forms]![frmProductSelect]![cboTypeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboGradeSelect]) Is Null) AND
(([Forms]![frmProductSelect]![cboNameSelect]) Is Null));
Click to expand...
Click to expand...
 
Back
Top