By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,080 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,080 IT Pros & Developers. It's quick & easy.

ComboBox for finding a record

P: 135
Expand|Select|Wrap|Line Numbers
  1. '''''''''''''''''''''''''''''''''''''''''''
  2. ' The AfterUpdate event procedure used in '
  3. ' the Find Customer combo box search.     '
  4. '''''''''''''''''''''''''''''''''''''''''''
  6.     'Moves to Customer Name text box and
  7.     'finds the record of whatever name
  8.     'is selected in the combo box
  9.     DoCmd.ShowAllRecords
  10.     Me.OrderDate.SetFocus
  11.     DoCmd.FindRecord Me.Combo74
  13.     'Set value of combo box equal to an empty string
  14.     Me!Combo74.Value = ""
I am using this code that I got form (probably on here) somewhere to make my ComboBox look up records by date. The problem comes in when I have multiple records for the same date. The combobox shows the date as many times as a record was entered on that date. Example: shows the date 5/20/2009 five diff times. When I select one of the 5/20/2009 dates to bring up the respective goes to the first record it can find with that matching date. Naturally, this is what the code is telling it to do after all. So what can I do to create a Combo that can be used to lookup records by date? How can I make the combo just show the date 5/20/2009 once, and then when selected filter the records so only those records with the matching date are shown? But then how would I unfilter the records if I want to back to viewing all the Records?
May 21 '09 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,287
Expand|Select|Wrap|Line Numbers
  1. Combo74_AfterUpdate()
  2. If Combo74.ListIndex >= 0 Then
  3.   Me.Filter = "DateField = #" & Combo74 & "#"
  4.   Me.FilterOn = True
  5. End If
  7. ClearFilterButton_Click()
  8.   Me.FilterOn = False
May 21 '09 #2

P: 135
Thank you very much.
May 21 '09 #3

Expert Mod 15k+
P: 31,709
Check out Literal DateTimes and Their Delimiters (#) for an explanation of why using the default display of a date is not always a good idea when using within SQL.
May 21 '09 #4

P: 135
This works great to filter my records by the date selected in the combo box. My combo box still shows multiple of the same date. I know each date represents a record created on that date, but is there any way to just show a duplicated date only once? I don't need every record represented for that date since I am going to select the date and filter by it.
May 21 '09 #5

Expert 100+
P: 1,287
Use the DISTINCT keyword in the SELECT statement for your combo box source.
May 21 '09 #6

Expert Mod 15k+
P: 31,709
You could also consider using a GROUP BY clause.

For your specified requirement though, the DISTINCT predicate would be preferable.
May 21 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.