473,687 Members | 3,586 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Populate combobox

DSR
Help Please... I would like to populate a combo box on a form with a
query that compares data from two tables. Any record that is unique in
table1 should continue to populate my combobox. The instant the record
appears also in table2 it should no longer be listed in my combo box.
This is not a key field in either of the tables. Basically what I am
trying to do is prevent the user from entering duplicate values even
though it is not a key field. I am trying to do this by limiting their
choices in the combo box. There is a legitimate reason for neither
field being a key.

Thanks for your help

Jan 22 '06 #1
11 12303
You can do that by inserting this SQL as the combo box's RowSource
value.

SELECT Tabl1.Field1
FROM Table1 LEFT JOIN Table2
ON Table1.Field1 = Table2.Field1
WHERE (((Table2.Field 1) Is Null));

You'll also need to set the combo box's RowSourceType to Table/Query.

If you want to update the RowSource as you add new values to the bound
columns from Table2 you'll need to add an AfterUpdate event for the
applicable control to call the Requery method for that control.

Jan 22 '06 #2
On 21 Jan 2006 20:20:12 -0800, "Jamey" <ca*********@ya hoo.com> wrote:
You can do that by inserting this SQL as the combo box's RowSource
value.

SELECT Tabl1.Field1
FROM Table1 LEFT JOIN Table2
ON Table1.Field1 = Table2.Field1
WHERE (((Table2.Field 1) Is Null));

You'll also need to set the combo box's RowSourceType to Table/Query.

If you want to update the RowSource as you add new values to the bound
columns from Table2 you'll need to add an AfterUpdate event for the
applicable control to call the Requery method for that control.


In this situation I normally requery the combo in the Enter event.

Sub MyCombo_Enter()
Me.MyCombo.Requ ery
End Sub

This ensures the the combo's rowsource is always current without the need to rely on other controls.

Wayne Gillespie
Gosford NSW Australia
Jan 22 '06 #3
Good point. That also cuts down the number of times that the combo gets
requeried.

Jan 22 '06 #4
On 21 Jan 2006 19:29:43 -0800, "DSR" <dr*******@hotm ail.com> wrote:
Help Please... I would like to populate a combo box on a form with a
query that compares data from two tables. Any record that is unique in
table1 should continue to populate my combobox. The instant the record
appears also in table2 it should no longer be listed in my combo box.
This is not a key field in either of the tables. Basically what I am
trying to do is prevent the user from entering duplicate values even
though it is not a key field. I am trying to do this by limiting their
choices in the combo box. There is a legitimate reason for neither
field being a key.

Thanks for your help


In addition to Jamey's suggestion.

A gotcha you should be aware of, depending on how your design is setup. Filtering a combo in this manner can lead to
existing valid data not being displayed if it no longer meets the criteria of the rowsource SQL/query.

As an example.

I have an existing app with a combo used to define who raised a certain report.
The client requested that the drop down list of the combo include only current employees.
This is easily achieved by adding a criteria to the RowSouce SQL, however it also meant that employees who had left the
company but who had previously raised reports, were no longer being displayed. (The combo appeared blank even though
they had a valid entry)

The way around this is to change the RowSource on entering and exiting the combo -

Sub MyCombo_Enter()
Dim strSQL as String

strSQL = "SELECT EmpNo, EmployeeName FROM tblEmployees WHERE [LeftCompany]=False;
Me.MyCombo.RowS ource=strSQL
Me.MyCombo.Requ ery

End Sub
Sub MyCombo_Exit()
Dim strSQL as String

strSQL = "SELECT EmpNo, EmployeeName FROM tblEmployees;
Me.MyCombo.RowS ource=strSQL
Me.MyCombo.Requ ery

End Sub

Existing data is always displayed correctly as the RowSource returns all records, however new entries (or edits) are
filtered to allow selection of only records which a valid at the time of editing.

This may not apply to your situation, but it is useful to be aware of.
Wayne Gillespie
Gosford NSW Australia
Jan 22 '06 #5
DSR
Many Thanks, but as in most cases, I forgot to give you all the
details. Your SQL works fine, however what I didn't mention is that the
comboBox needs to represent the current recordset on a subform. Another
words, even though the field from tbl2 isn't unique it needs to reflect
all the records from that table that have a key field called IDfield in
table2. Currently it is removing from the comboBox any record in the
field on tbl2 and not just the ones that are associated with the
current record in the subForm. I also have a Control (textbox) on the
subform that displays the IDfield ID Number of the record set that I
want filtered from my ComboBox. So Sorry for not being more specific
and I really appreciate your help on this.

Jan 22 '06 #6
What I understand you to say is this:

Forms!Form1.Rec ordSource = Table1
Forms!Form1!Sub form1.Form.Reco rdSource = Table2
Forms!Form1!Sub form1.Form.Link ChildFields = IDfield
Forms!Form1!Sub form1.Form.Link MasterFields = IDfield

Forms!Form1!Sub form1.Form!Comb o1.RowSource = strSQL (as above)

So, the pertinent questions are:

Are the form and subform joined by a LinkMaster and LinkChild field, as
above?
Is the combo we're talking about here on the subform or on the main
form?

Jan 22 '06 #7
DSR
The main form is based on tblSuInfo. This form displays, in textboxes,
field data called SuLastName, SuFirstName, and SuIDNum. The Subform is
based on tblSurProc. It is linked to the main table, on the many side
of one to many, by the field called SurIDNum (AutoNumber in the parent
table and number in the child table). The ComboBox , which is on the
subform, is there to offer choices to the user to add to the CredProc
field on the child table (tblSurProc). There is a third table in the
database that holds a field called AllProcAvail. I want my comboBox to
reflect all Records from the third table's field (ProcName) but only
those that don't already appear in the field (CredProc) in
tblSurProc, but this must only reflect the current recordset based on
SurIDNum from the main table (tblSurInfo). The third table doesn't
need a relationship to the others since it just holds All Proc Names.
If a new Proc name is added to this field it must be reflected in my
comboBox. This whole scenario should allow for a different record set
based on which current SurIDNum I am viewing on my main form.

I hope this is clear enough and I really do appreciate your help.

Jan 22 '06 #8
DSR
Thanks Wayne -- I will look into this more closely once I get the
comboBox to populate with the data that I am looking for.
Thanks again

Jan 22 '06 #9
Alright. It ain't easy, but this is one way to skin your cat:

With the forms setup as mentioned above, you'll need to set the Combo
named cboCredProc (those not yet selected from the table containing
ProcName, which this code refers to as tblThird) RowSource in the
Current event for the main form, as such (watch for word wrap):

Private Sub Form_Current()

Me!frmSurProc.F orm!cboCredProc .RowSource= _
"SELECT tblThird.ProcNa me " & _
"FROM tblThird " & _
"LEFT JOIN " & _
"(SELECT tblSurProc.SurI DNum, & _
"tblSurProc.Cre dProc " & _
"FROM tblSurProc " & _
"WHERE (((tblSurProc.S urIDNum)=" & _
Me!txtSuIDNum.V alue & "))) " & _
"AS qryJoinder " & _
"ON tblThird.ProcNa me = " & _
"qryJoinder.Cre dProc " & _
"WHERE (((qryJoinder.C redProc) Is Null));"

Me!frmSurProc.F orm!CredProc.Re query

End Sub

Notice that txtSuIDNum is the text box on the main form containing the
SuIDNum value from the tblSuInfo form.

Next you'll need to requery the combo as well in the subform's Current
event:

Private Sub Form_Current()
Me.cboCredProc. Requery
End Sub

Now, that said, here's a couple suggestions:

1. Any fields involved in an entity relationship should probably have
the same names and exactly the same data type. tblSuInfo.SuIDN um and
tblSurProc.SurI DNum are examples of these. As the complexity of the
project grows, so does the haze of deciphering which fields are
related, so standardize that naming.

2. It appears that your third table should be related to tblSurProc in
a 1:M relationship between ProcName and CredProc. Again, you should
probably standardize the name. This should probably be set to Cascade
Update and Delete so that if any of your ProcName fields change, you
won't run into orphaned records in tblSurProc and your data integrity
will remain intact.

Jan 22 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3247
by: Jassim Rahma | last post by:
Hi, what is the best way to populate comboBox from database.. the reason I am asking is that i have more than 8 comboBoxes in one form and each should retrieve data from a table and I don't want that to slow the form show. can you please submit a sample code...
0
2133
by: ROO | last post by:
Hi Everyone, I have a database table that have 4 field( C1, C2, M1, M2) on my form i have two combo box ComboC and ComboM C1 C2 M1 M2 1 Sales 1 Monthly Terms 1 Sales 2 Annual Terms
1
2209
by: Primera | last post by:
I'm trying to populate a ComboBox on Form.load with a list of the OU's pulled from AD. Here is the code that does not work: Dim rootdir As New System.DirectoryServices.DirectoryEntry _ ("LDAP://dc=corp,dc=abc,dc=com") Dim rootsearch As New System.DirectoryServices.DirectorySearcher(rootdir) Dim result As System.DirectoryServices.SearchResult rootsearch.SearchScope = DirectoryServices.SearchScope.Subtree rootsearch.Filter =...
16
10519
by: agrawal.solutions | last post by:
Hello Friends I am asking a very silly question but i dont find any solution fo this.. I am selectiong a recordset and want to populate a combobox where id would be inviseble and the content would. I am getting the recordset and the no of record but then i am unble to populate the combobox. I have already tried all the function starting form for each x in .... and while...wend and do....loop
0
4003
by: vnaz235 | last post by:
Hi, guys, 1. Can anybody help me to populate ComboBox on VB6 form with a) text strings from the range on the already existing Excel sheet (e.g., Workbook("Test1").Sheets("Sheet1").Range("P12:P72")) and another ComboBox with b) names of visible Excel sheets in the same workbook? 2. How to pass selected items from ComboBox1 into Workbook("Test1").Sheets("Sheet1").Range("A1") and from ComboBox2 into...
1
5999
by: freekedoutfish | last post by:
Hi. New member here Im sat at work, pounding my head off the desk because this tiny bit of simple code refuses to work. The sub is intended to pull data from the "companyname" column in the "cmSelectAllCompanyNames" table, and put all the company names it finds into the "cbFilterCompanyName" combo box. Now the confusing aspect is this: It works!!!
1
12710
by: =?Utf-8?B?SmVzcGVyLCBEZW5tYXJr?= | last post by:
Hi, I've a column in a dataGridView set as combobox. I can't find any example showing how to populate the list in the combobox. Does anyone have an example showing me this. Regards Jesper.
4
5708
by: gobblegob | last post by:
hi guys, I would like to populate a combobox list with the contents of a folder which contains text files , which will later be opened. Thanks in advanced for any help :) Gobble.
1
2285
by: =?Utf-8?B?Q2hyaXM=?= | last post by:
This actually stemmed from anther post I created. But, I have two combo boxes. ComboBox1 is populated by a list of Systems (DataSet from Oracle Query). Now, I want to take Combobox2, and when the data is changed on Combox1, I want to populate data depending on the selection in Combox1. So, ComboBox1 lists "OracleDB123". I want ComboBox2 to list all accounts on that system. That information is stored in say the Accounts
0
8527
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9070
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8947
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8781
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6453
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5806
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2964
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2214
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1948
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.