• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

How do I modify an Access Combo Box to show the next selection than the line above?

Example Access Combo BoxI have a subform with a combo box that lists the names of the rescued horses in alphabetical order.  I want the first line to work like it works now, starting with names beginning with the letter "A".  Let say I pick "Baby Girl."  When I go to the next line's combo box, after I click on the combo box'es down arrow, I want the combo box to start with the NEXT horse's name, "Baby Ray", as shown in the attached graphic.  That way I don't have to scroll through the "A" again (but I could if I need to).  Any ideas?
0
bobbat
Asked:
bobbat
  • 10
  • 7
  • 6
  • +2
2 Solutions
 
mbizupCommented:
This can't be done with a combo box.

You can approximate it, I think, using a textbox, a command button, and a listbox all set up to *appear* like a combo box.   You would need VBA behind the command button to show/hide the listbox when needed and to highlight the 'next item' in the listbox.
0
 
als315Commented:
Try this sample. As mbizup stated, it is impossible with pure combobox.
DBHorses.accdb
0
 
hnasrCommented:
Try this: for 3 combo boxes, expand for extra.
Table a(aID as string), if integer remove single quotes from code bellow.
Private Sub cboAid1_AfterUpdate()
    Dim sql As String
    sql = cboAid1.RowSource
    sql = Replace(sql, "From a", "From a WHERE aID > '" & cboAid1 & "'")
    cboAid2.RowSource = sql
    cboAid2.Requery
End Sub

Private Sub cboAid2_AfterUpdate()
    Dim sql As String
    sql = cboAid1.RowSource
    sql = Replace(sql, "From a", "From a WHERE aID > '" & cboAid2 & "'")
    cboAid3.RowSource = sql
    cboAid3.Requery
End Sub

Open in new window

filter-next-selection.accdb
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanMIS LiasonCommented:
For me, I am just curious:

< Let say I pick "Baby Girl."  When I go to the next line's combo box, after I click on the combo box'es down arrow, I want the combo box to start with the NEXT horse's name, "Baby Ray", as shown in the attached graphic. >

...how are you sure that you will always want the next Name alphabetically?
Why not: "Baby Girl.", then "Secretariat", ..Then "Citation"?

...again, just curious here, ...or perhaps I am just not understanding something...

;-)

Jeff
0
 
bobbatAuthor Commented:
Combo Box Example #2Jeff, I may or may not want to choose the next name in alphabetical order.  All I want to do is have a shorter list of names presented so I do not have to search through the whole list again.  A better example would be if the first horse was "Theodora (Dora)" in the first line.  The next horse that the donor wants to allocate money to is "Willow.”  Thus the combo box would "index" or "move to" or "automatically scroll to" (not sure of the technical term if, one exists, but it is as if the Row Source of the Combo Box changes) the horse after Willow which would be "Tiny Belle" and the horse that I need to choose would be "Willow", the second to the last name in the list.  Some donors can become quite specific and specify a dozen horses with each getting something different.  Rescues are under a lot of scrutiny these days and the donor's receipt needs to reflect their wishes and we need to spend their money the way they want it spent.

Thanks for asking for the clarification and I hope you have an idea.
http://bhfer.org/
0
 
bobbatAuthor Commented:
I forgot to add that I enter the horses names alphabetically (the names are rarely consecutive)since most donors make their lists that way too.
0
 
als315Commented:
@bobbat: have you tested my sample and sample from hnasr?
0
 
bobbatAuthor Commented:
I will tonight, sorry for the delay but we had five adopted horses unexpectedly returned to us and turning them around for re-adoption has taken all my attention.
0
 
bobbatAuthor Commented:
Error MessageHi Alex,  I get the attached error in MS Access 2007 Service Pack 3.  What can I do to correct this error?
0
 
als315Commented:
Sorry, old version was uploaded (you can delete whole sub with error). Test this one.
DBHorses.accdb
0
 
bobbatAuthor Commented:
Error Message in Combo BoxYour example works great!  In my database I get the error message shown in the graphic.  Any ideas?

Query 1 Reads: SELECT tblHorses.lngHorsesID, tblHorses.strHorseBarnName
FROM tblHorses
ORDER BY tblHorses.strHorseBarnName;

Query 2 Reads: SELECT TOP 1 Max(tblHorses.strHorseBarnName) AS MaxOfstrHorseBarnName
FROM tblDonationsDetails INNER JOIN tblHorses ON tblDonationsDetails.lngHorsesID = tblHorses.lngHorsesID
ORDER BY Max(tblHorses.strHorseBarnName) DESC;
0
 
als315Commented:
If you open your Query2ForDonationDetailsForm, you will see correct field for name: MaxOfstrHorseBarnName, so you should change code to:
Me.cboHorsesID = DLookup("[IngHorsesID]",   "Query1ForDonationDetailsForm",   "[strHorseBarnName] > " & chr(34) & DLookup("[MaxOfstrHorseBarnName]", "Query2ForDonationDetailsForm")  & chr(34))

Open in new window

0
 
bobbatAuthor Commented:
Example Query 2 and My Query 2Alex,  the RowSource to my Horse ID Combo Box (cboHorsesID) is:
SELECT tblHorses.lngHorsesID, tblHorses.strHorseBarnName
FROM tblHorses
ORDER BY tblHorses.strHorseBarnName;

The OnGotFocus Code is:
    If Me.NewRecord Then
       
    'New Als315, 8/28/12:
        Me.cboHorsesID = DLookup("[lngHorsesID]", "Query1ForDonationDetailsForm", "[strHorseBarnName] > " _
        & Chr(34) & DLookup("[MaxOfstrHorseBarnName]", "Query2ForDonationDetailsForm") & Chr(34))
       
    End If

But the combo box shows all the horses like before.  Any ideas?
0
 
als315Commented:
I think in Query2ForDonationDetailsForm you should limit list to some of IDs (lngDonationsID or lngEventsID). Will be good if you upload sample DB with this form and related table with sample data
0
 
hnasrCommented:
No feedback on my comment http:#38308866
0
 
bobbatAuthor Commented:
Will load sample DB today.
0
 
bobbatAuthor Commented:
Hi Alex, sample database attached.
DB-Horse-1-for-EE.accdb
0
 
hnasrCommented:
Try this to see if I understand the problem.

Table tblDonationsDetails modified:
Added field strHorseBarnName selected from Combo34, field lngHorsesID is filled from Combo34 column(1) in after update event

Any clarifications?
DB-Horse-2-for-EE.accdb
0
 
als315Commented:
@hnasr: Interesting solution, but I don't like idea to store names in table and you will have problems if form will be reopened or you will change some position in the middle of list.
May be some other event should be used.

Test sample.
DB-Horse-3-for-EE.accdb
0
 
hnasrCommented:
@ als315:
The values in the table are just a way to help the OP continue his work until a better solution is found. It is not used for any further processing because ID is stored.  If you go to table and change name value the ID will stay the same.
I  wanted OP comments for me to better understand his problem.
In current version if you go back end edit the first record, you notice that the current value not displayed in combo.
I don't have to assume things.

Does he need to re-edit the previous values?
0
 
hnasrCommented:
@ bobbat:

Try this version. It does not use an extra field in table, instead it uses a query "idName"
Combo Box in form header.
Move record selector to relevant record, and select from combo box.

Code used in Combo34_AfterUpdate and Form_Current events.

Feedback!
DB-Horse-4-for-EE-.accdb
0
 
bobbatAuthor Commented:
hnasr: Your DB-Horse-2-for-EE works OK but a horse's name has changed.  It is rare, but it has happened.  It may not matter since I use the horse's ID number when I use Queries.
Alex's other comments apply but perhaps I can live with those comments.  

Alex: Your DB Horse-3-For-EE works OK.  

It is rare, but sometimes I have to re-edit the horse.

hnasr: Your DB-Horse-4-for-EE works OK but has an awkward feel to it.  I can live with it but my wife does not like it and I am trying to get her or our Treasurer (another lady) to start using the database.

Suggestions for the next step?
0
 
hnasrCommented:
bobbat:
"hnasr: Your DB-Horse-2-for-EE works OK but a horse's name has changed.  It is rare, but it has happened.  It may not matter since I use the horse's ID number when I use Queries."

You are right,  and although the name in the details table is not used in queries but it shows a name in the table which may be needed to change.

"hnasr: Your DB-Horse-4-for-EE works OK but has an awkward feel to it.  I can live with it but my wife does not like it and I am trying to get her or our Treasurer (another lady) to start using the database."

They are right, but they need to push work ahead until a better solution is achieved.

Hope they will be happy with this version: DB-Horse-5-for-EE

Run the form "frmDonationDetailsTest", a pop up form "selectName" appears.
Move pop up to a suitable place on screen.
Select from combo, and watch records added to form.
Use scroll wheel to show records if not visible.
DB-Horse-5-for-EE.accdb
0
 
bobbatAuthor Commented:
Thanks guys for your help.  The horses thank you too!
http://bhfer.wordpress.com/
0
 
hnasrCommented:
Welcome!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 7
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now