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

[Jamey]

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.Field1) 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.

[Wayne Gillespie]

On 21 Jan 2006 20:20:12 -0800, "Jamey" <ca*********@yahoo.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.Field1) 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.Requery
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

[Jamey]

Good point. That also cuts down the number of times that the combo gets
requeried.

[Wayne Gillespie]

On 21 Jan 2006 19:29:43 -0800, "DSR" <dr*******@hotmail.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.RowSource=strSQL
Me.MyCombo.Requery

End Sub
Sub MyCombo_Exit()
Dim strSQL as String

strSQL = "SELECT EmpNo, EmployeeName FROM tblEmployees;
Me.MyCombo.RowSource=strSQL
Me.MyCombo.Requery

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

[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.

[Jamey]

What I understand you to say is this:

Forms!Form1.RecordSource = Table1
Forms!Form1!Subform1.Form.RecordSource = Table2
Forms!Form1!Subform1.Form.LinkChildFields = IDfield
Forms!Form1!Subform1.Form.LinkMasterFields = IDfield

Forms!Form1!Subform1.Form!Combo1.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?

[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.

[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

[Jamey]

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.Form!cboCredProc.RowSource= _
"SELECT tblThird.ProcName " & _
"FROM tblThird " & _
"LEFT JOIN " & _
"(SELECT tblSurProc.SurIDNum, & _
"tblSurProc.CredProc " & _
"FROM tblSurProc " & _
"WHERE (((tblSurProc.SurIDNum)=" & _
Me!txtSuIDNum.Value & "))) " & _
"AS qryJoinder " & _
"ON tblThird.ProcName = " & _
"qryJoinder.CredProc " & _
"WHERE (((qryJoinder.CredProc) Is Null));"

Me!frmSurProc.Form!CredProc.Requery

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.SuIDNum and
tblSurProc.SurIDNum 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.

[Bob Quintal]

"DSR" <dr*******@hotmail.com> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

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).
Pretty normal.

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).
So you want the combo to lookup all the procedures except ones
already assigned to the child records having their foreign key
equal to the primary key of the parent form,

Have I understood correctly? If so, your combobox rowsource
would be:
SELECT AllProcAvail.ProcName from AllProcAvail
WHERE ProcName NOT IN (SELECT CredProc from tblSurProc WHERE
SurIDNum = forms!mainform!surIDNum);

* I don't know the name of your form so I called it mainform.
Change as required.

Once that's there you need to requery the combobox when the
mainform's record changes, so in the OnCurrent event put
me!subform.form!comboboxname.requery
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.

--
Bob Quintal

PA is y I've altered my email address.

[DSR]

I have it working now. I want to thank everyone that responded; each of
you helped tremendously.