Allen Brown I need you -Combo Box Instructions

[Annelie]

What you are posting on your website is what I have been looking for as a
user very poor in knowledge of VBA
I followed your instructions for limiting a report to a date range and it
worked perfect.
I can now filter for dates in a report for dates using a formatted date.
You added to your instructions that it is now easy to add a combo box to
filter for more items.
My next step was to filter that form by other criteria, like employee name
or job number. I used your example and replaced customberID with EmployeeID.
Now I am trying to add the combobox to filter an employee name, replacing
your instructions for customerID with EmployeeID. All I keep getting is
error messages.
After many trial and errors, now I get an error on:
Dim rs As DAO.Recordset

I tried totally recreating your instructions from scratch, but I can't get
anywhere. Somehow, when I tried to create your combo with the using the same
name , just replacing customer with employee and going to the employee
table, it keeps opening the FrmWhatDate that I previously created which
pulls the info for dates from a query not the employee table.

Did thing change is since your instructions. I am now using Access 2003 (I
like it a lot, finally a backup feature) in 2002 format. Help, I am so close
to being able to filter reports by not only dates, but employee Name. Once I
master that I can filter anything.

This is obviously not an easy item, people in the newsgroups keep asking for
filtering reports by pulling info of combo boxes, but responses never
precise. Obviously, it is not an easy problem to solve. Your website
explains things for the not so experience user quite well. I can follow to a
certain extend how this is all supposed to work.
If you reply I post the codes.
Pleeeeeease ,
Annelie
For anyone who is interested the website address is:
http://users.bigpond.net.au/abrowne1/tips.html

 

[Larry Linson]

"Annelie" wrote

For anyone who is interested the website address is:
http://users.bigpond.net.au/abrowne1/tips.html

Allen Browne's new website address is http://allenbrowne.com/.

If the bigpond address isn't out of service now, it soon will be.

And, Annelie -- if you'd tell us _what_ error (number and descriptions) you
get on Dim rs As DAO.Recordset, someone might be able to assist.

Absent the specific error, I can say that the most common problem
encountered with such a Dim of a DAO Object is a Missing Reference to the
DAO Library. To deal with that, the thing to do would be to open a module,
and on the menu of the module window, Tools | References, and if Microsoft
DAO 3.6 Library is not checked, scroll down until you find it, click to
check it (and, as a safety measure) move it up until it is higher in the
list than the ADO reference.

Larry Linson
Microsoft Access MVP

 

[Annelie]

"Annelie" wrote


Allen Browne's new website address is http://allenbrowne.com/.

If the bigpond address isn't out of service now, it soon will be.

And, Annelie -- if you'd tell us _what_ error (number and descriptions) you
get on Dim rs As DAO.Recordset, someone might be able to assist.

Absent the specific error, I can say that the most common problem
encountered with such a Dim of a DAO Object is a Missing Reference to the
DAO Library. To deal with that, the thing to do would be to open a module,
and on the menu of the module window, Tools | References, and if Microsoft
DAO 3.6 Library is not checked, scroll down until you find it, click to
check it (and, as a safety measure) move it up until it is higher in the
list than the ADO reference.

Larry Linson
Microsoft Access MVP

 

[Annelie]

You were right, I did not load the library, thanks for describing how to
load it.
After getting rid of that error, it can't find my employee ID. I have a
combo box that works, except I do not know how to make the report choose the
name I select. Here is my combo:
Sub CboMoveTo_AfterUpdate()
DoCmd.GoToControl "EmployeeID"
DoCmd.FindRecord Me![cboMoveTo]
End Sub
-------------------------------------------------------
Using Allen Browne's method - (EmployeeID is a text field)
It bombs on rs.findfirst -- runtime error 3070
Sub CboMoveTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

 

[Annelie]

I got it working by changing the SocSecNo record source name to EmployeeID,
I had the label set to EmployeeID only.
Next questions: How do I combine Allen Browne's FrmWhatDates with the
cboMoveto?
Annelie

You were right, I did not load the library, thanks for describing how to
load it.
After getting rid of that error, it can't find my employee ID. I have a
combo box that works, except I do not know how to make the report choose the
name I select. Here is my combo:
Sub CboMoveTo_AfterUpdate()
DoCmd.GoToControl "EmployeeID"
DoCmd.FindRecord Me![cboMoveTo]
End Sub
-------------------------------------------------------
Using Allen Browne's method - (EmployeeID is a text field)
It bombs on rs.findfirst -- runtime error 3070
Sub CboMoveTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

"Annelie" wrote


Allen Browne's new website address is http://allenbrowne.com/.

If the bigpond address isn't out of service now, it soon will be.

And, Annelie -- if you'd tell us _what_ error (number and descriptions) you
get on Dim rs As DAO.Recordset, someone might be able to assist.

Absent the specific error, I can say that the most common problem
encountered with such a Dim of a DAO Object is a Missing Reference to the
DAO Library. To deal with that, the thing to do would be to open a module,
and on the menu of the module window, Tools | References, and if Microsoft
DAO 3.6 Library is not checked, scroll down until you find it, click to
check it (and, as a safety measure) move it up until it is higher in the
list than the ADO reference.

Larry Linson
Microsoft Access MVP