[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Combo Box updating record

Posted on 2011-10-06
Medium Priority
Last Modified: 2013-11-27
I am using a form to sort for results on a report. i.e, select a "department"

I have a bound Combo box on the form  that i use to select the criteria prior to running the report. i.e. Select Department 1 and only Department 1 items are displayed. Select Department 2 Department 2 is displayed.

This works fine.

When I close the form, the record prior to the selected record in the form is being updated with the last value in the combo box.

I DO NOT want to use this form to update any record, just select the criteria for the report.

I have tried to use an unbound combo box put it does not do the sorting in the report.

Any other options or code to prevent the Combo box from updating records?
Question by:SMP319
  • 5
  • 4
LVL 61

Expert Comment

ID: 36927216
You don't need to use bound controls for this.

If you have an unbound combo box for selecting departments, just use this code in a button click event:

Docmd.openreport "Myreport", acviewpreview,,"[Department] = '" & me.cbodepartment & "'"  '<--- for text dept


Docmd.openreport "Myreport", acviewpreview,,"[Department] = " & me.cbodepartment  '<--- for numeric dept
LVL 14

Assisted Solution

pteranodon72 earned 200 total points
ID: 36927295

You want to use an unbound combo so that it does not affect the data. The Control Source property (what gets stored) of the combo must be blank. However, the Row Source (what gets shown on the list)of the combo can refer to your list of Departments.

It sounds like the form you are working with allows entry of Employees?, including their Department field. You can keep one field named Department and have it bound to Department on the underlying table/query. Use this as a normal control to view/edit the Department assignment. The report filtering combo must be a separate combo that is unbound. You can call it cboFilterDepartment. Then, in the event that opens the report, use:

docmd.OpenReport "reportname", acViewPreview,,"Department='" & cboFilterDepartment & "'"

(assuming Department is a text field on the underlying table)



Author Comment

ID: 36930432
I create a unbound combo box on  the form which i use to select the division (Changed from Department as mentioned above) and a button which i then added the folowign code.

Private Sub Division_Click()
DoCmd.OpenReport "Project Tasks", acViewPreview, , "[Division] = '" & Me.cbodivision & "'" '<--- for text Division

End Sub

I get and error message
Compile Error:
Method or data member not found.
It references  .cbodivision  part of the code as the error

The Second Suggestion above opened the report but did not yield any data. It appears the filter part is not working

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 61

Expert Comment

ID: 36930455
<Method or data member not found.>

That means either that cboDivision is not the correct name of your combo box, or that the code is not on the same form as your combo box.

Double-check the Name property of your combo box, and ensure that you are running this code from a command button that resides on the same form as your combo box.
LVL 61

Expert Comment

ID: 36930479
Both of our posts are essetially the same thing so you should be getting identical results if they are both being implemented properly.

Regarding not finding any records, that means that the reports recordsource does not contain any records that match the criteria you are sullplying.  Does the Department field in your report's record source actually include any records for the value you are selecting in the combo box?

If you can post a copy of your database, it may be easier to help troubleshoot.

Author Comment

ID: 36930544
Here is my sample Database. the Form is the  "Division Selection Project report", Report is the "Project Tasks". I checked everything you had mentioned. the error is gone but now i get a report with no results.
Any thoughts?
LVL 61

Accepted Solution

mbizup earned 1800 total points
ID: 36930634
Okay - here's what is happening:

- Your combos rowsource is something along the lines of "SELECT ID, Division FROM ..."
- The actual value of the combo box if you don't specify a column is the ID, not the text Division field
- Your report's recordsource uses the text Division field, not its corresponding ID
- Because of that mis-match, no records are found

To resolve this, you can either:
- Modify your combo's rowsource to only include the division (not the ID) from your Division lookup table.


- Keep the combo's rowsource the same and change your code to the following, spcifying the column index:

DoCmd.OpenReport "Project Tasks", acViewPreview, , "[Division] = '" & Me.cbodivision.Column(1) & "'"

Note that Column Index is a zero-based count, meaning that the first column is column Zero, the second is column 1, etc.

Author Comment

ID: 36930721
Can your code be altered to bring the report up in the report view? it is opening in Print preview.

Author Comment

ID: 36930732
Answered my question . Changed "Preview" in the code to "Report". I am still learning :)
LVL 61

Expert Comment

ID: 36930743
Excellent - and leaving that parameter blank will send it directly to the default printer :)

DoCmd.OpenReport "Project Tasks", , , "[Division] = '" & Me.cbodivision.Column(1) & "'"


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question