Solved

VB - How to set unbound Combobox Item for two column variables

Posted on 2014-01-14
2
756 Views
Last Modified: 2014-01-14
Hello All,

I have a form which has several unbound comboboxes.

In the VB code I set the rowsource of the various comboboxes.

I also have a listbox of existing records where on that listbox's DblClickEvent I go to the Record and update the various unbound comboboxes with the info from that record.

However, one of the comboboxes could have multiple values if I use only one of the rowsource's fields to select by.  Is there a way to do it with two?

For Example.
One of my comboboxes is cboSelectCounty.
My Rowsource is set in the VB code. When the listbox is double clicked I have the code

Private Sub lstExistingRecs_DblClick(Cancel As Integer)
Dim b as long
b = Me.lstExistingRecs.Column(1) ' CountyCodeID

me.cboselectcounty = b
end sub

Everything above works fine as does what I want it to....

HOWEVER, for combobox [cboSelectBkNbrSubBkNbr] the rowsource is set as

"SELECT CountyCodeID, RecBkTypeID, RecBkNbrID, RecBkNbrSubID, BkNbrWhole, RefBk FROM qrySys_ValidRecBkTypeRecBkNbrBkNbrSubByCounty "

When I double click the Listbox I want the selection in [cboSelectBkNbrSubBkNbr] to be based on:
RecBkNbrID
AND
RecBkNbrSubID

I know I can do the RowSource "WHERE RecBkNbrID=x AND  RecBkNbrSubID=y"
but then the drop down list will only show that one particular line if the user needs to change it.

Is there a way to get that one field selected and showing in the combobox without limiting the list to just that one when I'm using 2 variable?
0
Comment
Question by:wlwebb
2 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39779901
You could modify the RowSource for the combo and concatenate those two fields into a single computed field and make that the first field in the combo box (bound column) and hide it.  something like:

SELECT [RecBnNbrID] & "|" & [RecBnNbrSubID] as Key, CountyCodeID,
RecBkTypeID, RecBkNbrID, RecBkNbrSubID, BkNbrWhole, RefBk
FROM qrySys_ValidRecBkTypeRecBkNbrBkNbrSubByCounty

Then, in the double click event, you would concatenate those values in your list, something like:

me.cboSelectBkNprBusBkNbr = Me.lstExistingRecs.Column(0) & "|" & me.lstExistingRecs.column(1)
0
 

Author Closing Comment

by:wlwebb
ID: 39779966
Uggh... missed that one........  Sometimes you just can't see the forest for all the trees right in front of you.........


Thank you for the Help!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now