Clear and Requery combo boxes

G

Ginger

Good evening, All.

I am working on a database that I inherited and running
into problems with a deadline of tomorrow noon. I have
looked at this code many times and can't quite get it to
work correctly. I have worked with combo boxes and
requerying previously, but this is just not working. I
apologize for the lengthy post.

I have a popup form that allows a user to select from
either or both or none of the 2 combo boxes. The selections
are issue owner and project team. This form is then hidden.
The choices then generate a report based on the selection.

I need help with 3 things.

First, I need to know where to put the coding for clearing
the text boxes so that when the form is
open/showing/re-opened it does not show the previous
selection. It works fine the first time through. If I put
it in the lost focus, then the report has no data.

Second, now that I brought the database to another
computer, I'm getting the error message "Compile error,
can't find project or library.

Third, on re-querying the row source for the combo boxes,
first run through works fine, but when I try to make
another selection, it does not work.
Any and all help is greatly appreciated.

The code is as follows:

Private Sub cboIssueOwner_GotFocus()

On Error GoTo PROC_ERR

Dim strSQL As String

' Assign the cbo rowsource according to the users choices
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
strSQL = "SELECT DISTINCT tblMasterTable.IssueOwner,
" & _
"[tblUserList].[LastName] & ', ' & [tblUserList]." & _
"[FirstName] AS Name " & _
"FROM tblUserList INNER JOIN tblMasterTable ON " & _
"tblUserList.UserID = tblMasterTable.IssueOwner " & _
"WHERE ([tblMasterTable].[IssueStatus])<> 'Closed'
" & _
"ORDER BY [tblUserList].[LastName] & ', ' & " & _
"[tblUserList].[FirstName];"
End Select

' Assign the rowsource and requery the recordset
Me.cboIssueOwner.RowSource = strSQL
Me.cboIssueOwner.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, _
Title:="cboIssueOwner_GotFocus"
Resume PROC_EXIT

End Sub


Private Sub cboProjectTeam_GotFocus()
' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------

On Error GoTo PROC_ERR

Dim strSQL As String

' Assign the cbo rowsource according to the users choices
If IsNull(Me.cboIssueOwner.Value) Or Me.cboIssueOwner =
"" Then
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
' The user has left the IssueOwner field null, show
all ProjectTeams
strSQL = "SELECT DISTINCT
[tblMasterTable].[ProjectTeam] " & _
"FROM [tblMasterTable] " & _
"WHERE (([tblMasterTable].[ProjectTeam]) Like
'*') " & _
"AND ([tblMasterTable].[IssueStatus]<>'Closed')
" & _
"ORDER BY [tblMasterTable].[ProjectTeam];"

End Select
Else
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
' The user has filled in an IssueOwner, filter the
ProjectTeams
strSQL = "SELECT DISTINCT
[tblMasterTable].[ProjectTeam] " & _
"FROM [tblMasterTable] " & _
"WHERE
((([tblMasterTable].[IssueStatus])<>'Closed')" & _
" AND (([tblMasterTable].[IssueOwner])=[Forms]!" & _
"[frmRunIssuesByOwner]![cboIssueOwner])) " & _
"ORDER BY [tblMasterTable].[ProjectTeam];"

End Select
End If

' Assign the rowsource and requery the recordset
Me.cboProjectTeam.RowSource = strSQL
Me.cboProjectTeam.Requery

PROC_EXIT:

Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, _
Title:="cboProjectTeam_GotFocus"
Resume PROC_EXIT

End Sub

Private Sub cmdRunReport_Click()
' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------

On Error GoTo PROC_ERR

Dim strDocName As String
Dim strSQLWhere As String

' Assign which report to open based on which cmdButton
called this form
strDocName = Form_frmMainMenu.Tag

' Hide the selection form
Form_frmRunIssuesByOwner.Visible = False

' Validate the choices the user made
' Assign wildcards to search if the entries are blank
If IsNull(Me.cboIssueOwner) Or Me.cboIssueOwner = "" Then
Me.cboIssueOwner.Value = "Like " & Chr(34) & "*" & Chr(34)
Else
Me.cboIssueOwner.Value = "= " & Me.cboIssueOwner.Value
End If

If IsNull(Me.cboProjectTeam) Then
Me.cboProjectTeam.Value = "Like " & Chr(34) & "*" & Chr(34)
Else
Me.cboProjectTeam.Value = "= " & Chr(39) &
Me.cboProjectTeam.Value & Chr(39)
End If

' Assign the cbo values as variables in a where statement
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
strSQLWhere = "(([tblMasterTable].[IssueStatus])<>" &
Chr(34) & "Closed" & Chr(34) & ") AND " & _
"(([tblMasterTable].[ProjectTeam])" &
Me.cboProjectTeam.Value & ") AND " & _
"(([tblMasterTable].[IssueOwner])" &
Me.cboIssueOwner.Value & ")"

End Select

' Open the report
DoCmd.OpenReport strDocName, acPreview,
WhereCondition:=strSQLWhere

PROC_EXIT:
' Clear the selection forms fields
Me.cboIssueOwner.Value = ""
Me.cboProjectTeam.Value = ""
Exit Sub
 
N

Newbie

Q1 - I would put the code to clear the combo boxes after the code that calls
the report and before the code that makes form visible again.

Q2 - In a module goto Tools References and check that none of them have
MISSING - if they have then point it to the correct file.

Q3 - what doesn't work?

HTH
Ginger said:
Good evening, All.

I am working on a database that I inherited and running
into problems with a deadline of tomorrow noon. I have
looked at this code many times and can't quite get it to
work correctly. I have worked with combo boxes and
requerying previously, but this is just not working. I
apologize for the lengthy post.

I have a popup form that allows a user to select from
either or both or none of the 2 combo boxes. The selections
are issue owner and project team. This form is then hidden.
The choices then generate a report based on the selection.

I need help with 3 things.

First, I need to know where to put the coding for clearing
the text boxes so that when the form is
open/showing/re-opened it does not show the previous
selection. It works fine the first time through. If I put
it in the lost focus, then the report has no data.

Second, now that I brought the database to another
computer, I'm getting the error message "Compile error,
can't find project or library.

Third, on re-querying the row source for the combo boxes,
first run through works fine, but when I try to make
another selection, it does not work.
Any and all help is greatly appreciated.

The code is as follows:

Private Sub cboIssueOwner_GotFocus()

On Error GoTo PROC_ERR

Dim strSQL As String

' Assign the cbo rowsource according to the users choices
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
strSQL = "SELECT DISTINCT tblMasterTable.IssueOwner,
" & _
"[tblUserList].[LastName] & ', ' & [tblUserList]." & _
"[FirstName] AS Name " & _
"FROM tblUserList INNER JOIN tblMasterTable ON " & _
"tblUserList.UserID = tblMasterTable.IssueOwner " & _
"WHERE ([tblMasterTable].[IssueStatus])<> 'Closed'
" & _
"ORDER BY [tblUserList].[LastName] & ', ' & " & _
"[tblUserList].[FirstName];"
End Select

' Assign the rowsource and requery the recordset
Me.cboIssueOwner.RowSource = strSQL
Me.cboIssueOwner.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, _
Title:="cboIssueOwner_GotFocus"
Resume PROC_EXIT

End Sub


Private Sub cboProjectTeam_GotFocus()
' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------

On Error GoTo PROC_ERR

Dim strSQL As String

' Assign the cbo rowsource according to the users choices
If IsNull(Me.cboIssueOwner.Value) Or Me.cboIssueOwner =
"" Then
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
' The user has left the IssueOwner field null, show
all ProjectTeams
strSQL = "SELECT DISTINCT
[tblMasterTable].[ProjectTeam] " & _
"FROM [tblMasterTable] " & _
"WHERE (([tblMasterTable].[ProjectTeam]) Like
'*') " & _
"AND ([tblMasterTable].[IssueStatus]<>'Closed')
" & _
"ORDER BY [tblMasterTable].[ProjectTeam];"

End Select
Else
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
' The user has filled in an IssueOwner, filter the
ProjectTeams
strSQL = "SELECT DISTINCT
[tblMasterTable].[ProjectTeam] " & _
"FROM [tblMasterTable] " & _
"WHERE
((([tblMasterTable].[IssueStatus])<>'Closed')" & _
" AND (([tblMasterTable].[IssueOwner])=[Forms]!" & _
"[frmRunIssuesByOwner]![cboIssueOwner])) " & _
"ORDER BY [tblMasterTable].[ProjectTeam];"

End Select
End If

' Assign the rowsource and requery the recordset
Me.cboProjectTeam.RowSource = strSQL
Me.cboProjectTeam.Requery

PROC_EXIT:

Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, _
Title:="cboProjectTeam_GotFocus"
Resume PROC_EXIT

End Sub

Private Sub cmdRunReport_Click()
' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------

On Error GoTo PROC_ERR

Dim strDocName As String
Dim strSQLWhere As String

' Assign which report to open based on which cmdButton
called this form
strDocName = Form_frmMainMenu.Tag

' Hide the selection form
Form_frmRunIssuesByOwner.Visible = False

' Validate the choices the user made
' Assign wildcards to search if the entries are blank
If IsNull(Me.cboIssueOwner) Or Me.cboIssueOwner = "" Then
Me.cboIssueOwner.Value = "Like " & Chr(34) & "*" & Chr(34)
Else
Me.cboIssueOwner.Value = "= " & Me.cboIssueOwner.Value
End If

If IsNull(Me.cboProjectTeam) Then
Me.cboProjectTeam.Value = "Like " & Chr(34) & "*" & Chr(34)
Else
Me.cboProjectTeam.Value = "= " & Chr(39) &
Me.cboProjectTeam.Value & Chr(39)
End If

' Assign the cbo values as variables in a where statement
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
strSQLWhere = "(([tblMasterTable].[IssueStatus])<>" &
Chr(34) & "Closed" & Chr(34) & ") AND " & _
"(([tblMasterTable].[ProjectTeam])" &
Me.cboProjectTeam.Value & ") AND " & _
"(([tblMasterTable].[IssueOwner])" &
Me.cboIssueOwner.Value & ")"

End Select

' Open the report
DoCmd.OpenReport strDocName, acPreview,
WhereCondition:=strSQLWhere

PROC_EXIT:
' Clear the selection forms fields
Me.cboIssueOwner.Value = ""
Me.cboProjectTeam.Value = ""
Exit Sub
Click to expand...
 
G

Ginger

Thank you.
Q3 - when a selection is made for the project team, the
report shows the data. When I close the report and go back
to the popup form and try to select another project team,
all I get is either a blank or = in the value of the combo.
The combo box row source does not requery properly.

Ginger
-----Original Message-----
Q1 - I would put the code to clear the combo boxes after the code that calls
the report and before the code that makes form visible again.

Q2 - In a module goto Tools References and check that none of them have
MISSING - if they have then point it to the correct file.

Q3 - what doesn't work?

HTH
Good evening, All.

I am working on a database that I inherited and running
into problems with a deadline of tomorrow noon. I have
looked at this code many times and can't quite get it to
work correctly. I have worked with combo boxes and
requerying previously, but this is just not working. I
apologize for the lengthy post.

I have a popup form that allows a user to select from
either or both or none of the 2 combo boxes. The selections
are issue owner and project team. This form is then hidden.
The choices then generate a report based on the selection.

I need help with 3 things.

First, I need to know where to put the coding for clearing
the text boxes so that when the form is
open/showing/re-opened it does not show the previous
selection. It works fine the first time through. If I put
it in the lost focus, then the report has no data.

Second, now that I brought the database to another
computer, I'm getting the error message "Compile error,
can't find project or library.

Third, on re-querying the row source for the combo boxes,
first run through works fine, but when I try to make
another selection, it does not work.
Any and all help is greatly appreciated.

The code is as follows:

Private Sub cboIssueOwner_GotFocus()

On Error GoTo PROC_ERR

Dim strSQL As String

' Assign the cbo rowsource according to the users choices
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
strSQL = "SELECT DISTINCT tblMasterTable.IssueOwner,
" & _
"[tblUserList].[LastName] & ', ' & [tblUserList]." & _
"[FirstName] AS Name " & _
"FROM tblUserList INNER JOIN tblMasterTable ON " & _
"tblUserList.UserID = tblMasterTable.IssueOwner " & _
"WHERE ([tblMasterTable].[IssueStatus])<> 'Closed'
" & _
"ORDER BY [tblUserList].[LastName] & ', ' & " & _
"[tblUserList].[FirstName];"
End Select

' Assign the rowsource and requery the recordset
Me.cboIssueOwner.RowSource = strSQL
Me.cboIssueOwner.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, _
Title:="cboIssueOwner_GotFocus"
Resume PROC_EXIT

End Sub


Private Sub cboProjectTeam_GotFocus()
' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------

On Error GoTo PROC_ERR

Dim strSQL As String

' Assign the cbo rowsource according to the users choices
If IsNull(Me.cboIssueOwner.Value) Or Me.cboIssueOwner =
"" Then
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
' The user has left the IssueOwner field null, show
all ProjectTeams
strSQL = "SELECT DISTINCT
[tblMasterTable].[ProjectTeam] " & _
"FROM [tblMasterTable] " & _
"WHERE (([tblMasterTable].[ProjectTeam]) Like
'*') " & _
"AND ([tblMasterTable].[IssueStatus]<>'Closed')
" & _
"ORDER BY [tblMasterTable].[ProjectTeam];"

End Select
Else
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
' The user has filled in an IssueOwner, filter the
ProjectTeams
strSQL = "SELECT DISTINCT
[tblMasterTable].[ProjectTeam] " & _
"FROM [tblMasterTable] " & _
"WHERE
((([tblMasterTable].[IssueStatus])<>'Closed')" & _
" AND (([tblMasterTable].[IssueOwner])=[Forms]!" & _
"[frmRunIssuesByOwner]![cboIssueOwner])) " & _
"ORDER BY [tblMasterTable].[ProjectTeam];"

End Select
End If

' Assign the rowsource and requery the recordset
Me.cboProjectTeam.RowSource = strSQL
Me.cboProjectTeam.Requery

PROC_EXIT:

Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, _
Title:="cboProjectTeam_GotFocus"
Resume PROC_EXIT

End Sub

Private Sub cmdRunReport_Click()
' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------

On Error GoTo PROC_ERR

Dim strDocName As String
Dim strSQLWhere As String

' Assign which report to open based on which cmdButton
called this form
strDocName = Form_frmMainMenu.Tag

' Hide the selection form
Form_frmRunIssuesByOwner.Visible = False

' Validate the choices the user made
' Assign wildcards to search if the entries are blank
If IsNull(Me.cboIssueOwner) Or Me.cboIssueOwner = "" Then
Me.cboIssueOwner.Value = "Like " & Chr(34) & "*" & Chr(34)
Else
Me.cboIssueOwner.Value = "= " & Me.cboIssueOwner.Value
End If

If IsNull(Me.cboProjectTeam) Then
Me.cboProjectTeam.Value = "Like " & Chr(34) & "*" & Chr(34)
Else
Me.cboProjectTeam.Value = "= " & Chr(39) &
Me.cboProjectTeam.Value & Chr(39)
End If

' Assign the cbo values as variables in a where statement
Select Case Form_frmMainMenu.Tag
Case Is = "rptIssuesbyOwner"
strSQLWhere = "(([tblMasterTable].[IssueStatus])<>" &
Chr(34) & "Closed" & Chr(34) & ") AND " & _
"(([tblMasterTable].[ProjectTeam])" &
Me.cboProjectTeam.Value & ") AND " & _
"(([tblMasterTable].[IssueOwner])" &
Me.cboIssueOwner.Value & ")"

End Select

' Open the report
DoCmd.OpenReport strDocName, acPreview,
WhereCondition:=strSQLWhere

PROC_EXIT:
' Clear the selection forms fields
Me.cboIssueOwner.Value = ""
Me.cboProjectTeam.Value = ""
Exit Sub
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo box help 2
Question for John Vinson 5
UserForm and ordering variables 3
sorting the combo box after requery. 1
How to recognize shading and border formatting in the following code? 9

Top