Combo boxes...

[thewabit via AccessMonster.com]

First of all..thank you guys for taking the time to help us newbies...it is
really appreciated!

I have 2 combo boxes, "CmtCode" and ""JsComment". I would like to have
JsComment to be populated with specific items depending on what is selected
in CmtCode. Both use a query in the rowsource to get the data but I cant get
the JsComment box to change when an item is selected in CmtCode.

I can see for example where comment code 1 has about 3 items to it in the
table. there are 2 tables involved and the common column is named "code".

I have tried to follow other threads and figure this out but answers with
(yourformname) for example, I just can't translate it to my name so thats why
I included actual names. Please respond using those.

Thanks!

 

[Arvin Meyer [MVP]]

I have a sample, using list boxes:

http://www.accessmvp.com/Arvin/Combo.zip

 

[John W. Vinson]

First of all..thank you guys for taking the time to help us newbies...it is
really appreciated!

I have 2 combo boxes, "CmtCode" and ""JsComment". I would like to have
JsComment to be populated with specific items depending on what is selected
in CmtCode. Both use a query in the rowsource to get the data but I cant get
the JsComment box to change when an item is selected in CmtCode.

I can see for example where comment code 1 has about 3 items to it in the
table. there are 2 tables involved and the common column is named "code".

I have tried to follow other threads and figure this out but answers with
(yourformname) for example, I just can't translate it to my name so thats why
I included actual names. Please respond using those.

Thanks!

I'm *guessing* here... but try opening the form in design view. Select the
CmtCode combo box and view its Properties. Find the "After update" property on
the Events tab. Click the ... icon by it and choose Code Builder.

Access will open the VBA window with two lines in it:

Private Sub CmtCode_AfterUpdate()
End Sub

Edit this to read

Private Sub CmtCode_AfterUpdate()
Me!JsComment.Requery
End Sub


This assumes that JsComment has a Row Source query referencing
[Forms]![YourFormName]![CmtCode] as a criterion. If it doesn't, post back with
more details (e.g. the SQL view of both combo's query).

 

[thewabit via AccessMonster.com]

I see what you mean...your assumptions were not present.

I did have the name wrong...they are "CmntAreaCode" and "obcomment". When
"CmntAreaCode" is changed I would like the corresponding items to be
available in "obcomment"

Here are the 2 querys in the rowsource for "CmntAreaCode" and "obcomment"
respectively:

SELECT tblCmntAreas.CmntAreaCode AS Code, tblCmntAreas.CmntAreaName AS [Comnt
Area] FROM tblCmntAreas;

SELECT Comments, CmntAreaCode, CommentID FROM tblComments WHERE
CmntAreaCode=3 ORDER BY tblComments.CmntAreaCode;

First of all..thank you guys for taking the time to help us newbies...it is
really appreciated!

[quoted text clipped - 12 lines]

Thanks!

I'm *guessing* here... but try opening the form in design view. Select the
CmtCode combo box and view its Properties. Find the "After update" property on
the Events tab. Click the ... icon by it and choose Code Builder.

Access will open the VBA window with two lines in it:

Private Sub CmtCode_AfterUpdate()
End Sub

Edit this to read

Private Sub CmtCode_AfterUpdate()
Me!JsComment.Requery
End Sub

This assumes that JsComment has a Row Source query referencing
[Forms]![YourFormName]![CmtCode] as a criterion. If it doesn't, post back with
more details (e.g. the SQL view of both combo's query).

 

[thewabit via AccessMonster.com]

I have been playing with this some more. I am not sure which query field of
ObComment the [forms]! criterie should go. I added the one you suggested and
the ObComment combobox is now blank. BTW..I did add the requery to the
CmntAreaCode afterupdate.

Still not working.

Thanks!

I see what you mean...your assumptions were not present.

I did have the name wrong...they are "CmntAreaCode" and "obcomment". When
"CmntAreaCode" is changed I would like the corresponding items to be
available in "obcomment"

Here are the 2 querys in the rowsource for "CmntAreaCode" and "obcomment"
respectively:

SELECT tblCmntAreas.CmntAreaCode AS Code, tblCmntAreas.CmntAreaName AS [Comnt
Area] FROM tblCmntAreas;

SELECT Comments, CmntAreaCode, CommentID FROM tblComments WHERE
CmntAreaCode=3 ORDER BY tblComments.CmntAreaCode;
[quoted text clipped - 20 lines]

[Forms]![YourFormName]![CmtCode] as a criterion. If it doesn't, post back with
more details (e.g. the SQL view of both combo's query).

 

[John W. Vinson]

I have been playing with this some more. I am not sure which query field of
ObComment the [forms]! criterie should go. I added the one you suggested and
the ObComment combobox is now blank. BTW..I did add the requery to the
CmntAreaCode afterupdate.

Well, if *you* don't know which field you're searching, I certainly can't tell
- you have the database available and I don't!

Perhaps you could post the current SQL of both queries, and indicate the names
and datatypes of the relevant fields in the two tables.

 

[thewabit via AccessMonster.com]

Here are the 2 querys in the rowsource for "CmntAreaCode" and "obcomment"
respectively:

SELECT tblCmntAreas.CmntAreaCode AS Code, tblCmntAreas.CmntAreaName AS [Comnt
Area] FROM tblCmntAreas;

SELECT Comments, CmntAreaCode, CommentID FROM tblComments WHERE
CmntAreaCode=3 ORDER BY tblComments.CmntAreaCode;

Thetwo tables are called "tblCmntAreas" and "tblComments".

"tblCmntAreas" has 3 columns:
ID
CmntAreaCode
CmntAreaName

"tblComments has many columns but the relevant ones are:
CommentID
Comments
CmntAreaCode

Thanks John!

I have been playing with this some more. I am not sure which query field of
ObComment the [forms]! criterie should go. I added the one you suggested and
the ObComment combobox is now blank. BTW..I did add the requery to the
CmntAreaCode afterupdate.

Well, if *you* don't know which field you're searching, I certainly can't tell
- you have the database available and I don't!

Perhaps you could post the current SQL of both queries, and indicate the names
and datatypes of the relevant fields in the two tables.

 

[John W. Vinson]

Here are the 2 querys in the rowsource for "CmntAreaCode" and "obcomment"
respectively:

SELECT tblCmntAreas.CmntAreaCode AS Code, tblCmntAreas.CmntAreaName AS [Comnt
Area] FROM tblCmntAreas;

SELECT Comments, CmntAreaCode, CommentID FROM tblComments WHERE
CmntAreaCode=3 ORDER BY tblComments.CmntAreaCode;

Thetwo tables are called "tblCmntAreas" and "tblComments".

"tblCmntAreas" has 3 columns:
ID
CmntAreaCode
CmntAreaName

"tblComments has many columns but the relevant ones are:
CommentID
Comments
CmntAreaCode

Ok now I'm officially really confused.

ASSUMING (and you know what THAT means) that you want the second combo box to
be filtered by the choice in the first combo box, rather than displaying only
comments for CmntAreaCode 3; and assuming that CmntAreaCode is a Long Integer
or Autonumber field, try changing the RowSource for obcomment to

SELECT Comments, CmntAreaCode, CommentID FROM tblComments WHERE
CmntAreaCode=[Forms]![YourFormName]![CmntAreaCode] ORDER BY
tblComments.CmntAreaCode;

You will want to change the Order By clause to sort by some other field which
will present the comments in some reasonable order - as it is, the sort will
do absolutely nothing since all the comments will have the same value of
CmntAreaCode.

 

[thewabit via AccessMonster.com]

I just want to make sure..

the [YourFormName] should be substituted with my form name like so
[frmobservation]...correct?

Here are the 2 querys in the rowsource for "CmntAreaCode" and "obcomment"
respectively:

[quoted text clipped - 16 lines]

Comments
CmntAreaCode

Ok now I'm officially really confused.

ASSUMING (and you know what THAT means) that you want the second combo box to
be filtered by the choice in the first combo box, rather than displaying only
comments for CmntAreaCode 3; and assuming that CmntAreaCode is a Long Integer
or Autonumber field, try changing the RowSource for obcomment to

SELECT Comments, CmntAreaCode, CommentID FROM tblComments WHERE
CmntAreaCode=[Forms]![YourFormName]![CmntAreaCode] ORDER BY
tblComments.CmntAreaCode;

You will want to change the Order By clause to sort by some other field which
will present the comments in some reasonable order - as it is, the sort will
do absolutely nothing since all the comments will have the same value of
CmntAreaCode.

 

[thewabit via AccessMonster.com]

And for some reason...that "3" got put into the criteria...I know that
doesn't belong....sorry for the confusion.

I just want to make sure..

the [YourFormName] should be substituted with my form name like so
[frmobservation]...correct?
[quoted text clipped - 17 lines]

do absolutely nothing since all the comments will have the same value of
CmntAreaCode.

 

[John W. Vinson]

I just want to make sure..

the [YourFormName] should be substituted with my form name like so
[frmobservation]...correct?

Exactly - you hadn't posted the formname so I used a substitute.

Does it work? Did you try it?

 

[thewabit via AccessMonster.com]

I am just now starting on it again...will post results soon...thanks!

I just want to make sure..

the [YourFormName] should be substituted with my form name like so
[frmobservation]...correct?

Exactly - you hadn't posted the formname so I used a substitute.

Does it work? Did you try it?

 

[thewabit via AccessMonster.com]

It didn't seem to work.

It doesn't matter what I select in CmntAreaCode, there are no selections
listed in obcomments.

I just want to make sure..

the [YourFormName] should be substituted with my form name like so
[frmobservation]...correct?

Exactly - you hadn't posted the formname so I used a substitute.

Does it work? Did you try it?

 

[thewabit via AccessMonster.com]

How do I know if it is a long integer or auto numbering?

I am just now starting on it again...will post results soon...thanks!
[quoted text clipped - 4 lines]

Does it work? Did you try it?

 

[John W. Vinson]

It didn't seem to work.

It doesn't matter what I select in CmntAreaCode, there are no selections
listed in obcomments.

Please post the following:

current SQL of the two RowSources
ControlSource, ColumnCount, ColumnWidths, and Bound Column of both combos

It may be that the obcomments combo box simply isn't showing the desired
value.

Question: is Comment a Memo field? If so it won't work in a combo box.

 

[John W. Vinson]

How do I know if it is a long integer or auto numbering?

Open the table in design view. Look at the datatype of the field. It might be
Number (with Long Integer in the field properties on the lower left), or it
might be Autonumber, or it might be something else.

 

[thewabit via AccessMonster.com]

Here are the 2 querys in the rowsource for "CmntAreaCode" and "obcomment"
respectively:

SELECT tblCmntAreas.CmntAreaCode, tblCmntAreas.CmntAreaName FROM
tblCmntAreas;

SELECT Comments, CmntAreaCode, CommentID FROM tblComments WHERE
CmntAreaCode=Forms!frmobservations!CmntAreaCode ORDER BY tblComments.
CmntAreaCode;

When I typed in the SQL you suggested on obcomment and saved it, the brackets
fell away...is that normal?

CmntAreaCode Box:
ControlSource = CmntAreaCode
ColumnCount = 2
Column Width = 0.5";2"
Bound Column = 1

ObComment Box:
ControlSource = ObComment
ColumnCount = 3
Column Width = 10";0";0"
Bound Column = 1

Finally, if you mean the comments field in the table "tblcomments"...it is a
text field.

Also, I currently do not have any requery language in the afterupdate.

 

[thewabit via AccessMonster.com]

Both table are Long Integers

Open the table in design view. Look at the datatype of the field. It might be
Number (with Long Integer in the field properties on the lower left), or it
might be Autonumber, or it might be something else.

 

[John W. Vinson]

Also, I currently do not have any requery language in the afterupdate.

That's why it's not working. The second combo box has no way to know what
you've selected in the first one.

 

[thewabit via AccessMonster.com]

I will add it back and see what happens. Does all the other stuff look normal?