VBA / MS Access openform problem

I'm trying to open a form (frmMainActionLog) via an afterupdate event on a combobox listing investigators. I want to open it with a filter active on open, where the results are limited to the investigator in question.

Private Sub cboInvestigator_AfterUpdate()
    DoCmd.OpenForm "frmMainActionLog", , , "[Investigator] = '" & [cboInvestigator] & "'"
End Sub

Open in new window


In the data, "Investigator" is the name of a field, and cboInvestigator is the name of the combobox on the first form.

It's opening the form, but with no results when there should be.

Any pointers please guys?

LVL 2
RossAsked:
Who is Participating?
 
Nick67Commented:
You name it :)

Constant                     Control
 
acBoundObjectFrame   Bound object frame  
acCheckBox                 Check box  
acComboBox                Combo box  
acCommandButton      Command button  
acCustomControl         ActiveX (custom) control  
acImage                      Image  
acLabel                        Label  
acLine                          Line  
acListBox                     List box  
acObjectFrame            Unbound object frame or chart  
acOptionButton           Option button  
acOptionGroup            Option group  
acPage                        Page  
acPageBreak               Page break  
acRectangle                 Rectangle  
acSubform                   Subform/subreport  
acTabCtl                      Tab  
acTextBox                   Text box  
acToggleButton           Toggle button

Another area where this can be nice is on bound forms that you don't want the user to change values accidentally as they are browsing

Private Sub cmdChange_Click()
Dim ctl As Control
Dim myboolean As Boolean

If cmdChange.Caption = "Change details" Then
    myboolean = False
    cmdChange.Caption = "EDITING"
Else
    myboolean = True
    cmdChange.Caption = "Change details"
End If

For Each ctl In Me.Controls
    If (ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox) = True Then
        ctl.Locked = myboolean
    End If
Next ctl
End Sub
 
0
 
cas1Commented:
looks good so far. I think the problem is somewhere else. If you open the form frmMainActionLog directly from the database window, does it display any data?

Andy
0
 
VTKeganCommented:
Private Sub cboInvestigator_AfterUpdate()
    DoCmd.OpenForm "frmMainActionLog", , , "[Investigator] = '" & Me.cboInvestigator & "'"
End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If the combo multicolumn? If so, you might need to refer to the correct column:

me.yourcombo.Column(1)

Columns are zero-based, so Column(1) refers to the 2nd column, Column(2) refers to the 3rd column and so on.
0
 
RossAuthor Commented:
Cas1 - yes it does. All the data is there as I would expect, and the results are "unfiltered"

VTKegan - that "works" (ie, doesn't error) but has the same output - no results.

Any more ideas?
0
 
RossAuthor Commented:
LSM thats the ticket - well spotted! :D

0
 
RossAuthor Commented:
I'm trying to now transfer that idea over to a filter string (so I can use several combo boxes combined) but struggling.

Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click

Dim strSQL As String, intCounter As Integer
     'Build SQL String
For intCounter = 1 To 3

    If Me("Filter" & intCounter) <> "" Then
    
        If intCounter > 2 Then
        strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & CInt(Me("Filter" & intCounter)) & " And "
        
        Else
        
        strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
        
        End If
    
    End If

Next
     
     If strSQL <> "" Then
        'Strip Last " And "
        strSQL = Left(strSQL, (Len(strSQL) - 5))
        'Set the Filter property
        'Me.Filter = strSQL
          DoCmd.OpenForm "frmMainActionLog", , , strSQL
        Me.FilterOn = True
     Else
        Me.FilterOn = False
     End If

Exit_cmdFilter_Click:
    Exit Sub

Err_cmdFilter_Click:
    Resume Exit_cmdFilter_Click
    
End Sub

Open in new window

0
 
RossAuthor Commented:
The combo boxes are of course named filter1, filter2 etc.
0
 
Nick67Commented:
So, you have three combo boxes-- a bit poorly named as Filter1, Filter2, and Filter3
--You may want to think about that, as Filter is in use in VBA and 1,2 and 3 tell you nothing.  Difficult debugging awaits.
How about cboInvestigatorFilter, etc --

Now you'd like to filter based on these combo boxes.
Where will the event fire from?
Are all three necessary?
Will they all reset when you change one?
Looking at your logic, I take it the first box is a string variable, while the other two are integer.
Are any/all of them ever going to have a null value?

Lots to think about.

This makes no sense
<          DoCmd.OpenForm "frmMainActionLog", , , strSQL
        Me.FilterOn = True>
The form is either opening and you're are building a filter for doing that

OR

You're are building a filter for the form that contains this code.
Which?

Now, some clear code
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String

Dim myField1 as string
Dim myField2 as string
Dim myField3 as string

myField1 = me.Filter1.tag 'not that I like that name
myField2 = me.Filter2.tag
myField3 = me.Filter3.tag

strSQL1 = myField1 & " = " & chr(34) & me.filter1.value & chr(34) 'depends of course if the combo boxes bound value is what you want 
strSQL2 = myField2 & " = " me.filter2.value 'same caveat
strSQL3 = myField3 & " = " me.filter3.value 'same caveat

strSQL = strSQL1 & " AND " & strSQL2 & " AND " & strSQL3

DoCmd.OpenForm "frmMainActionLog", , , strSQL

'there's no null handling in here at all
'I'm not sure where you are going to fire this from
'And you'd better check what the bound values of all those combo boxes are and match them to the tags

Open in new window

0
 
RossAuthor Commented:
Nick - thanks for your input.

I had to adjust your code as it was missing some "&"'s in the strSQL lines, but even after that it's still giving an error.

Please remember I don't just want to combine all the comboboxes, I might only want to choose one of them for the filter too - I think this is part of the problem.

The error I'm getting when running it is: "Run-time error '3075' " "Syntax error (missing operator) in query expression 'Investigator = "20" AND  AND '.

I've spaced it above exactly as is. I presumed its failing because I have nothing selected in the second combo box, but regardless of the selections (or lack of) in my three comboboxes this doesn't work.

Any more ideas?

Cheers!

0
 
Nick67Commented:
I posed many questions to you.
You haven't answered any of them.
I am not surprised it didn't work in the first go.
<'there's no null handling in here at all>
And you passed in two null values.
BANG! was to be expected.

If, at first you don't succeed, try, try again :)

1.  What are you REALLY going to name your three combo boxes?
2.  What are the bound columns for these boxes
    a. you do know how to discern that?  If not, ask
3.  Are you filtering the form the combo boxes are on, or creating a filter to open a new form?
4.  Are the fields bund to the combo boxes present in the record source of the form being filtered?
5.  Given that you have three combo boxes, where will this event fire from?  the afterupdate event of one of them seems silly. A command button?
6.  Given that you have tried an event with two of the three as null, I take it any of the three are optional?  Or is just investigator mandatory?
7.  How will you handle clearing the boxes when adjusting your filter? Another command button?
0
 
RossAuthor Commented:
1.  What are you REALLY going to name your three combo boxes?
Can be anything - they were named Filter1-3 so I could iterate through them with the "For intCounter = 1 To 3" loop in my original attempt.

2.  What are the bound columns for these boxes
    a. you do know how to discern that?  If not, ask
The comboboxes are not bound (the form isn't the one doing the filtering - consider this a menu) - they do however obviously pull their source from another table.

3.  Are you filtering the form the combo boxes are on, or creating a filter to open a new form?
Creating a filter to open a new form. I have a working solution once the form is open.

4.  Are the fields bund to the combo boxes present in the record source of the form being filtered?
The comboboxes aren't bound, as I explained. But yes, the fields have the same names and attributes in the form being filtered.

5.  Given that you have three combo boxes, where will this event fire from?  the afterupdate event of one of them seems silly. A command button?
Yes - from a command button.

6.  Given that you have tried an event with two of the three as null, I take it any of the three are optional?  Or is just investigator mandatory?
Any of the three are optional - only one must be filled in (obviously).

7.  How will you handle clearing the boxes when adjusting your filter? Another command button?
Yes - there is another button to clear the filter.

Thanks
0
 
Nick67Commented:
Sigh.

I grasp that these combo boxes are not bound to a control source.
That would change the data in a table.
<they do however obviously pull their source from another table>
Yes!  Which Table? and which field is the bound column? And what is the name of that field?  And what is it's data type?
x3 please, one for each combo box
Bound ColumnHere, this unbound combo box is only returning a single field, and is bound to column 1, which is [Client Name]
--which , if I had it to do over again would be ClientName, but I digress slightly--
 Other exampleThis unbound combo box has a query as its row source and is bound to column 1
It however returns 2 column, the first, bound column is not displayed to the user
 Third exampleIt displays text to the user, but its bound column, which is the value it takes when a selection is made, is actually a number, Long data type.
These things matter greatly in what you are trying to do.

As @LSMConsulting divined in post 35240279, your tags are reflecting what the combo box shows the user, and probably not its bound column.
To make this work, I need to know the details of the bound columns of your combo boxes.

Naming conventions are important
http://www.mvps.org/access/tencommandments.htm
http://support.microsoft.com/kb/173738

I leave bound controls with the names that Access gives them (typically the bound field)
I give all unbound controls a name based on Hungarian notation and what their purpose is.
They then cluster together in Intellisense
I know that they are unbound.
By looking at their name, I can recall their purpose.

Sometimes, I don't come back to a form for years.
Good use of naming conventions makes the relearning process easier
:)
0
 
RossAuthor Commented:
Nick, whilst I appreciate you're trying to help, some of your posts come across as quite belittling... It might be the joy that is the expressionless internet, but that's how I'm interpreting some of your comments.

I've actually resolved this issue now, but am grateful for your attempt of assistance.
0
 
Nick67Commented:
They are not meant to bebeliitling, and I am sorry if you took them that way.
You are using the tag property, which is quite sophisticated.
You are then NOT using a naming convention and did not seem to understand the basics of how combo boxes work.
Which are basic fundamentals.

That is confusing to someone trying to help you, to say the least.
It makes it difficult to know what skill set you possess.

I would have liked to sewn everything together for you in a well-documented piece of code that would have used the names of the controls you were using.
But you didn't provide those for me.
Building a string with variables requires knowing if they are string, date or number.
Strings require bracketing with "" or better Chr(34), dates need to be bracketed with ##.
You didn't provide those either.

Our failure to understand one another led to this question not really being useful to anyone googling afterward.
That is unfortunate, because that is really the benefit of EE, that workable solutions exist for those who come later.
I have a form that builds a dynamic filter based on six different combo boxes.
I know the mechanics and pitfalls of getting what you wanted done to work.
It just didn't come together :(

Sorry man.
Glad you got it solved.
Post your working code for posterity, and give the points to @LSMConsulting.
He spotted that what you were doing with combo boxes is what was making things wonky.

Nick67
0
 
RossAuthor Commented:
Thanks Nick. I've been working with Access for a little while, but am still getting to grips with some areas I've never had exposure to.

The tag feature I was attempting to use so I could iterate through the filters programatically (FilterX & FilterX etc) - thats actually how I did it in the end. The code may not be pretty, but it does work lol.

Sincerely, I'm grateful for your help we just didn't "click" thats all!

Here's where I'm at with that:

Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click

Dim strSQL As String, intCounter As Integer
     'Build SQL String
For intCounter = 1 To 3

    If Me("Filter" & intCounter) <> "" Then
    
        If intCounter > 2 Then
        strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & CInt(Me("Filter" & intCounter)) & " And "
        Else
        
        strSQL = strSQL & "[tblFailures]![" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
        
        End If
    
    End If

Next
     If strSQL <> "" Then
        'Strip Last " And "
        strSQL = Left(strSQL, (Len(strSQL) - 5))
        'Set the Filter property
        DoCmd.OpenForm "frmMainActionLog", , , strSQL
        Me.Filter = strSQL
        'Debug.Print strSQL
        Me.FilterOn = True
     Else
        Me.FilterOn = False
     End If

Exit_cmdFilter_Click:
    Exit Sub

Err_cmdFilter_Click:
    Resume Exit_cmdFilter_Click

Open in new window


(I've left in that debug line in case it falls over later!)
0
 
Nick67Commented:
Control iteration can be handled much more elegantly with collections
This also handles an indefinite number of control --you don't have to count them.
It's also more efficient with machine resources, although these days you won't see much difference.

The following code will generically handle any number of combo boxes, named cbo..., in the detail section of a form, and build a formatted SQL string.
It will ignore combo boxes that are null.
It will handle string, date and numeric data.
It assumes the combo box bound column is column 1.
It fetches the appropriate field name to make the SQL work
Uncomment the msgbox near the end for testing the code
'assume DAO 3.6
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim ctl As Control

Set db = CurrentDb

'iterate through controls in the details section
For Each ctl In Me.Detail.Controls
    If ctl.ControlType = acComboBox And ctl.Name Like "cbo*" And IsNull(ctl.Value) = False Then 'combo box, named right, not null
        Set rs = db.OpenRecordset(ctl.RowSource, dbOpenDynaset, dbSeeChanges) 'pull the combo boxes rowsource
        Select Case True 'assume bound column is column 1--fields start numbering at zero
            Case rs.Fields(0).Type = dbText 'is the bound field text type data?
                If Nz(strSQL, "") = "" Then 'is this the first entry?
                    strSQL = rs.Fields(0).Name & " = " & Chr(34) & ctl.Value & Chr(34) 'bracket with quotes
                Else
                    strSQL = strSQL & " AND " & rs.Fields(0).Name & " = " & Chr(34) & ctl.Value & Chr(34) 'add to existing string, bracket with quotes
                End If
                rs.close
            Case rs.Fields(0).Type = dbDate 'is it date
                If Nz(strSQL, "") = "" Then 'is this the first entry?
                    strSQL = rs.Fields(0).Name & " = #" & ctl.Value & "#" 'bracket with #
                Else
                    strSQL = strSQL & " AND " & rs.Fields(0).Name & " = #" & ctl.Value & "#" 'add to existing string, bracket with #
                End If
                rs.close
            Case Else 'is it something that can be treated numerically
                If Nz(strSQL, "") = "" Then 'is this the first entry?
                    strSQL = rs.Fields(0).Name & " = " & ctl.Value ' add it
                Else
                    strSQL = strSQL & " AND " & rs.Fields(0).Name & " = " & ctl.Value 'add to existing string
                End If
                rs.close
        End Select
    End If
Next ctl

'msgbox strSQL

MsgBox Nz(strSQL, "no choices")

If strSQL <> "" Then
   DoCmd.OpenForm "frmMainActionLog", , , strSQL
   Me.Filter = strSQL
   Me.FilterOn = True
Else
   Me.FilterOn = False
End If

Open in new window

0
 
RossAuthor Commented:
Nick - can that also handle tickboxes? Looks very through, and something I'll look to use in the future - thank you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.