Re: Clear and Requery combo boxes

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Newbie (noidea_at_nospam.com)
Date: 04/08/04 

Date: Thu, 8 Apr 2004 09:57:48 +0100

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" <anonymous@discussions.microsoft.com> wrote in message
news:1701301c41d25$c2104c60$a601280a@phx.gbl...
> 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

Quantcast