Combo Boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know this has to be easy....

Visual Aid: www.gotmerchant.com/dtnew.gif

Customers is main table
Got a form for CallNotes

On CallNotes Form, I want two combos to do this. First one selects the
department - PullCallNotesDep. The second combo will display the results of
the first (PullCallNotesSubject).

PullCallNotesDept lists the depts like Sales, Billing, etc.
It has a relationship to PullCallNotesSubject as you can see.

PullCallNotesDept selection is stored in Department in CallNotes
PullCallNotesSubject selection is store in Subject in Call Notes

It seems it would be so simple, like a query or two, but have been cracking
at my brain for a while now & figured one of the intelligent people on this
forum could easily show me the light.

Curtis
 
Curtis said:
I know this has to be easy....

Visual Aid: www.gotmerchant.com/dtnew.gif

Customers is main table
Got a form for CallNotes

On CallNotes Form, I want two combos to do this. First one selects the
department - PullCallNotesDep. The second combo will display the results of
the first (PullCallNotesSubject).

PullCallNotesDept lists the depts like Sales, Billing, etc.
It has a relationship to PullCallNotesSubject as you can see.

PullCallNotesDept selection is stored in Department in CallNotes
PullCallNotesSubject selection is store in Subject in Call Notes

It seems it would be so simple, like a query or two, but have been cracking
at my brain for a while now & figured one of the intelligent people on this
forum could easily show me the light.

Curtis
Click to expand...

-----------------------------------
When you say: "The second combo will display the results of
the first" do you mean that 2nd combo will only show subjects that
pertain to the selected department? If so, filter the PullCallNotesID
column in the rowsource for the Subject combo box by the results of the
combo for Department. Type:

[forms]![YourFormame]![DepartmentComboName]

as the criteria in that column. Then only subjects that pertain to
that department will be available for selection. As long as the
PullCallNotesDepartmentID is what is stored in the Department combo
box, this will work.

hth- Betsy
 
Curtis,

Your first combo box should have the following properties:
ControlSource Department (in CallNotes)
ColumnCount 2
BoundColumn 1
RowSource SELECT PullCallNotesDept.PullCallNotesDeptID,
PullCallNotesDept.CallNotesDept FROM PullCallNotesDept ORDER BY
PullCallNotesDept.CallNotesDept;
ColumnWidths 0";x" (where x is wide enough to display the longest dept
name)

The ID will be stored because the Boundcolumn is 1. Because the first
column width is 0", however, the ID will not display. Rather the more
meaningful department will.

The second combo box will be filtered by the selection in the first (see
additional help on the forum under the subject "Cascading Combo Boxes). It
should have the same properties except for:

ControlSource Subject (CallNotes)
RowSource SELECT PullCallNotesSubject.PullCallNotesSubjectID,
PullCallNotesSubject.Subject WHERE PullCallNotesSubject.PullCallNotesDeptID =
[NameofYourFirstComboBox]

In the AfterUpdate event of the first combo box, requery the second combo to
update the filtering:

Me![YourSecondCombo].Requery

Hope that helps.
Sprinks
 
Ok, I tried exactly this:

ColumnCount - 2
BoundColumn - 1
Column Heads - No
Column Widths - 1.5"

RowSource Type - blank. I tried select table/query, but gave error saying
characters found after end of SQL statement. I take it out, no errors, but
blank data too!

Row Source - SELECT PullCallNotesDept.PullCallNotesDeptID,
PullCallNotesDept.CallNotesDept FROM PullCallNotesDept ORDER BY
PullCallNotesDept.CallNotesDept;ColumnWidths 0";2"

Nothing shows up (with rowsource type blank) and stopped, haven't tried the
rest of what you said.

Thanks
Curtis
Your first combo box should have the following properties:
ControlSource Department (in CallNotes)
ColumnCount 2
BoundColumn 1
RowSource SELECT PullCallNotesDept.PullCallNotesDeptID,
PullCallNotesDept.CallNotesDept FROM PullCallNotesDept ORDER BY
PullCallNotesDept.CallNotesDept;
ColumnWidths 0";x" (where x is wide enough to display the longest dept
name)

The ID will be stored because the Boundcolumn is 1. Because the first
column width is 0", however, the ID will not display. Rather the more
meaningful department will.

The second combo box will be filtered by the selection in the first (see
additional help on the forum under the subject "Cascading Combo Boxes). It
should have the same properties except for:

ControlSource Subject (CallNotes)
RowSource SELECT PullCallNotesSubject.PullCallNotesSubjectID,
PullCallNotesSubject.Subject WHERE PullCallNotesSubject.PullCallNotesDeptID =
[NameofYourFirstComboBox]

In the AfterUpdate event of the first combo box, requery the second combo to
update the filtering:

Me![YourSecondCombo].Requery

Hope that helps.
Sprinks

Curtis Stevens said:
I know this has to be easy....

Visual Aid: www.gotmerchant.com/dtnew.gif

Customers is main table
Got a form for CallNotes

On CallNotes Form, I want two combos to do this. First one selects the
department - PullCallNotesDep. The second combo will display the results of
the first (PullCallNotesSubject).

PullCallNotesDept lists the depts like Sales, Billing, etc.
It has a relationship to PullCallNotesSubject as you can see.

PullCallNotesDept selection is stored in Department in CallNotes
PullCallNotesSubject selection is store in Subject in Call Notes

It seems it would be so simple, like a query or two, but have been cracking
at my brain for a while now & figured one of the intelligent people on this
forum could easily show me the light.

Curtis
Click to expand...
Click to expand...
 
Ok, one idiot moment a week, guess I used it up in this one! Didn't snap
that was the next line of something to do..... Columnwidths is the field
name, not part of row source code.

I did do the second part and getting syntax error (missing operator) in
query expression "pullcallNOtesSubject.Subject WHERE
PullCallNotesSubject.PullCallNotesDeptID = Department'.

I will keep trying to figure out, but not working right now and have the
afterupdate in place as well.
Your first combo box should have the following properties:
ControlSource Department (in CallNotes)
ColumnCount 2
BoundColumn 1
RowSource SELECT PullCallNotesDept.PullCallNotesDeptID,
PullCallNotesDept.CallNotesDept FROM PullCallNotesDept ORDER BY
PullCallNotesDept.CallNotesDept;
ColumnWidths 0";x" (where x is wide enough to display the longest dept
name)

The ID will be stored because the Boundcolumn is 1. Because the first
column width is 0", however, the ID will not display. Rather the more
meaningful department will.

The second combo box will be filtered by the selection in the first (see
additional help on the forum under the subject "Cascading Combo Boxes). It
should have the same properties except for:

ControlSource Subject (CallNotes)
RowSource SELECT PullCallNotesSubject.PullCallNotesSubjectID,
PullCallNotesSubject.Subject WHERE PullCallNotesSubject.PullCallNotesDeptID =
[NameofYourFirstComboBox]

In the AfterUpdate event of the first combo box, requery the second combo to
update the filtering:

Me![YourSecondCombo].Requery

Hope that helps.
Sprinks

Curtis Stevens said:
I know this has to be easy....

Visual Aid: www.gotmerchant.com/dtnew.gif

Customers is main table
Got a form for CallNotes

On CallNotes Form, I want two combos to do this. First one selects the
department - PullCallNotesDep. The second combo will display the results of
the first (PullCallNotesSubject).

PullCallNotesDept lists the depts like Sales, Billing, etc.
It has a relationship to PullCallNotesSubject as you can see.

PullCallNotesDept selection is stored in Department in CallNotes
PullCallNotesSubject selection is store in Subject in Call Notes

It seems it would be so simple, like a query or two, but have been cracking
at my brain for a while now & figured one of the intelligent people on this
forum could easily show me the light.

Curtis
Click to expand...
Click to expand...
Click to expand...
 
I did do the second part and getting syntax error (missing operator) in
query expression "pullcallNOtesSubject.Subject WHERE
PullCallNotesSubject.PullCallNotesDeptID = Department'.
Click to expand...

Please post the COMPLETE SQL statement. There's got to be a SELECT in
there at the beginning...

John W. Vinson[MVP]
 
Please post the COMPLETE SQL statement. There's got to be a SELECT in
there at the beginning...
Click to expand...


Sprinks code didn't work, but I found this to work, used the builder....

SELECT PullCallNotesSubject.PullCallNotesSubjectID,
PullCallNotesSubject.Subject, PullCallNotesSubject.PullCallNotesDeptID FROM
PullCallNotesSubject WHERE
(((PullCallNotesSubject.PullCallNotesDeptID)=[Department])) ORDER BY
PullCallNotesSubject.Subject;

Works like a charm!!!

Curtis
 
Curtis,

Glad you got it to work. I believe your first attempt didn't work because
you didn't have brackets around Department to indicate that it was a control
on your form.

Sprinks

Curtis Stevens said:
Please post the COMPLETE SQL statement. There's got to be a SELECT in
there at the beginning...
Click to expand...


Sprinks code didn't work, but I found this to work, used the builder....

SELECT PullCallNotesSubject.PullCallNotesSubjectID,
PullCallNotesSubject.Subject, PullCallNotesSubject.PullCallNotesDeptID FROM
PullCallNotesSubject WHERE
(((PullCallNotesSubject.PullCallNotesDeptID)=[Department])) ORDER BY
PullCallNotesSubject.Subject;

Works like a charm!!!

Curtis
Click to expand...
 
Back
Top