Combo Box QUESTION

K

kealaz

Hello,

I have a table, with and within each record
FIELD 1
FIELD 2
FIELD 3

How do create a combo box in a form where Field 1, 2 and 3 are each
selections in that combo box and not on the same line as one selection. Such
that when you click on the little arrow, you see

FIELD 1
FIELD 2
FIELD 3

and NOT

FIELD1 | FIELD 2 | FIELD 3

Thank you for your help.
 
D

Dirk Goldgar

kealaz said:
Hello,

I have a table, with and within each record
FIELD 1
FIELD 2
FIELD 3

How do create a combo box in a form where Field 1, 2 and 3 are each
selections in that combo box and not on the same line as one selection.
Such
that when you click on the little arrow, you see

FIELD 1
FIELD 2
FIELD 3

and NOT

FIELD1 | FIELD 2 | FIELD 3

Thank you for your help.
Click to expand...


If I understand what you want, this can be achieved by setting the combo
box's RowSourceType property to "Field List".
 
K

kealaz

Thank you so much! Yes, that worked. I have another combo box on that same
form, with the following info.

SELECT table001.item1, table001.FIELD1, table001.FIELD2, table001.FIELD3
FROM table001;
column count: 4
column widths: 2"; 0"; 0"; 0"

And I want to take the fields from the value of that combo box. How do I
get those selections? The first combo box is coming up correctly. I just
don't know how to get the selections for the second combo box to come up.

Thanks!
 
D

Dirk Goldgar

kealaz said:
Thank you so much! Yes, that worked. I have another combo box on that
same
form, with the following info.

SELECT table001.item1, table001.FIELD1, table001.FIELD2, table001.FIELD3
FROM table001;
column count: 4
column widths: 2"; 0"; 0"; 0"

And I want to take the fields from the value of that combo box. How do I
get those selections? The first combo box is coming up correctly. I just
don't know how to get the selections for the second combo box to come up.
Click to expand...


Sorry, I don't understand you this time. Could you rephrase your question,
please? It might help if you gave some examples.
 
K

kealaz

Sorry, I didn't explain that very well. I was so excited that something
worked, that I didn't formulate my next question properly.

On my form, I have a combo box w/ the following properties.

Row Source Type: Table/Query
Row Source: SELECT table001.item1, table001.FIELD1, table001.FIELD2,
table001.FIELD3 FROM table001;
column count: 4
column widths: 2"; 0"; 0"; 0"

This combo box is working correctly and giving me the results I want. I
checked by giving the 2nd, 3rd and 4th columns width, and the results are
there and correct.

The next combo box is the one I am working on and asked about in my original
post. I want the values in column 3, 4 & 5 of the first combo box to be
selections in this second combo box, such that the choices are

[value of FIELD 1]
[value of FIELD 2]
[value of FIELD 3]


How do I make this happen? Would I use After Update on the first box? or
Row Source on the second combo box. Can my source be a control in the same
form? What is the code to make this work?


I hope this makes more sense. THANK YOU so much for your help with this.
 
K

kealaz

Sorry, I didn't explain that very well. I was so excited that something
worked, that I didn't formulate my next question properly.

On my form, I have a combo box w/ the following properties.

Row Source Type: Table/Query
Row Source: SELECT table001.item1, table001.FIELD1, table001.FIELD2,
table001.FIELD3 FROM table001;
column count: 4
column widths: 2"; 0"; 0"; 0"

This combo box is working correctly and giving me the results I want. I
checked by giving the 2nd, 3rd and 4th columns width, and the results are
there and correct.

The next combo box is the one I am working on and asked about in my original
post. I want the values in column 3, 4 & 5 of the first combo box to be
selections in this second combo box, such that the choices are

[value of FIELD 1]
[value of FIELD 2]
[value of FIELD 3]


How do I make this happen? Would I use After Update on the first box? or
Row Source on the second combo box. Can my source be a control in the same
form? What is the code to make this work?


I hope this makes more sense. THANK YOU so much for your help with this.
 
K

kealaz

Sorry, I didn't explain that very well. I was so excited that something
worked, that I didn't formulate my next question properly.

On my form, I have a combo box w/ the following properties.

Row Source Type: Table/Query
Row Source: SELECT table001.item1, table001.FIELD1, table001.FIELD2,
table001.FIELD3 FROM table001;
column count: 4
column widths: 2"; 0"; 0"; 0"

This combo box is working correctly and giving me the results I want. I
checked by giving the 2nd, 3rd and 4th columns width, and the results are
there and correct.

The next combo box is the one I am working on and asked about in my original
post. I want the values in column 3, 4 & 5 of the first combo box to be
selections in this second combo box, such that the choices are

[value of FIELD 1]
[value of FIELD 2]
[value of FIELD 3]


How do I make this happen? Would I use After Update on the first box? or
Row Source on the second combo box. Can my source be a control in the same
form? What is the code to make this work?


I hope this makes more sense. THANK YOU so much for your help with this.
 
K

kealaz

Sorry, I didn't explain that very well. I was so excited that something
worked, that I didn't formulate my next question properly.

On my form, I have a combo box w/ the following properties.

Row Source Type: Table/Query
Row Source: SELECT table001.item1, table001.FIELD1, table001.FIELD2,
table001.FIELD3 FROM table001;
column count: 4
column widths: 2"; 0"; 0"; 0"

This combo box is working correctly and giving me the results I want. I
checked by giving the 2nd, 3rd and 4th columns width, and the results are
there and correct.

The next combo box is the one I am working on and asked about in my original
post. I want the values in column 3, 4 & 5 of the first combo box to be
selections in this second combo box, such that the choices are

[value of FIELD 1]
[value of FIELD 2]
[value of FIELD 3]


How do I make this happen? Would I use After Update on the first box? or
Row Source on the second combo box. Can my source be a control in the same
form? What is the code to make this work?


I hope this makes more sense. THANK YOU so much for your help with this.
 
K

kealaz

Sorry, I didn't explain that very well. I was so excited that something
worked, that I didn't formulate my next question properly.

On my form, I have a combo box w/ the following properties.

Row Source Type: Table/Query
Row Source: SELECT table001.item1, table001.FIELD1, table001.FIELD2,
table001.FIELD3 FROM table001;
column count: 4
column widths: 2"; 0"; 0"; 0"

This combo box is working correctly and giving me the results I want. I
checked by giving the 2nd, 3rd and 4th columns width, and the results are
there and correct.

The next combo box is the one I am working on and asked about in my original
post. I want the values in column 3, 4 & 5 of the first combo box to be
selections in this second combo box, such that the choices are

[value of FIELD 1]
[value of FIELD 2]
[value of FIELD 3]


How do I make this happen? Would I use After Update on the first box? or
Row Source on the second combo box. Can my source be a control in the same
form? What is the code to make this work?


I hope this makes more sense. THANK YOU so much for your help with this.
 
K

kealaz

Sorry, I didn't explain that very well. I was so excited that something
worked, that I didn't formulate my next question properly.

On my form, I have a combo box w/ the following properties.

Row Source Type: Table/Query
Row Source: SELECT table001.item1, table001.FIELD1, table001.FIELD2,
table001.FIELD3 FROM table001;
column count: 4
column widths: 2"; 0"; 0"; 0"

This combo box is working correctly and giving me the results I want. I
checked by giving the 2nd, 3rd and 4th columns width, and the results are
there and correct.

The next combo box is the one I am working on and asked about in my original
post. I want the values in column 3, 4 & 5 of the first combo box to be
selections in this second combo box, such that the choices are

[value of FIELD 1]
[value of FIELD 2]
[value of FIELD 3]


How do I make this happen? Would I use After Update on the first box? or
Row Source on the second combo box. Can my source be a control in the same
form? What is the code to make this work?


I hope this makes more sense. THANK YOU so much for your help with this.
 
D

Dirk Goldgar

kealaz said:
Sorry, I didn't explain that very well. I was so excited that something
worked, that I didn't formulate my next question properly.

On my form, I have a combo box w/ the following properties.

Row Source Type: Table/Query
Row Source: SELECT table001.item1, table001.FIELD1, table001.FIELD2,
table001.FIELD3 FROM table001;
column count: 4
column widths: 2"; 0"; 0"; 0"

This combo box is working correctly and giving me the results I want. I
checked by giving the 2nd, 3rd and 4th columns width, and the results are
there and correct.

The next combo box is the one I am working on and asked about in my
original
post. I want the values in column 3, 4 & 5 of the first combo box to be
selections in this second combo box, such that the choices are

[value of FIELD 1]
[value of FIELD 2]
[value of FIELD 3]


How do I make this happen? Would I use After Update on the first box? or
Row Source on the second combo box. Can my source be a control in the
same
form? What is the code to make this work?
Click to expand...


Hmm ... It's an odd thing you're doing, and it may well be that you should
revisit your data design -- it's likely that your Table001 is not
normalized, and things would be easier for you with a different design.

That said, I think you could accomplish what you're currently asking by
using the AfterUpdate event of the first combo box to set the RowSource of
the second combo box, using a "Value List" RowSourceType. Something like
this:

'----- start of example code -----
Private Sub cboFirst_AfterUpdate()

Const Q As String = """"

With Me.cboFirst

If IsNull(.Value) Then
Me.cboSecond.RowSource = ""
Else

Me.cboSecond.RowSourceType = "Value List"
' NOTE: Don't necessarily need the above, if
' RowSourceType was set at design time.

Me.cboSecond.RowSource =
Q & .Column(1) & Q & ";" & _
Q & .Column(2) & Q & ";" & _
Q & .Column(3) & Q

End With

End Sub
'----- end of example code -----
 
K

kealaz

Dirk,

Thank you so much, you've been a huge help. I understand that this is
unusual, and you are correct, I'm sure I have problems with my initial
design. I'm trying to convert a database that was written in dbase IV about
20 years ago by someone else, to Access 2003. I wouldn't know where to start
to try and re-design the tables and not lose info.

The application of this little bit that I'm asking about is for a purchasing
program. I have a part and each part can have up to, but never any more than
3 manufacturers. Since there would not be more than 3 mfgs, I think I can
get it to work, if I can figure out a way to get the fields in that second
combo box to populate with columns 2, 3, and 4 from the first combo box.

I have entered the code you gave me in the After Update code area. Should
it work? Am I supposed to make any changes to the second combo box (the one
receiving the values)? Right now, it is...

unbound
Control Source: [blank]
Row Source Type: Value List
Row Source: [blank]

Thank you again for all of your help!
 
D

Dirk Goldgar

kealaz said:
Dirk,

Thank you so much, you've been a huge help. I understand that this is
unusual, and you are correct, I'm sure I have problems with my initial
design. I'm trying to convert a database that was written in dbase IV
about
20 years ago by someone else, to Access 2003. I wouldn't know where to
start
to try and re-design the tables and not lose info.

The application of this little bit that I'm asking about is for a
purchasing
program. I have a part and each part can have up to, but never any more
than
3 manufacturers. Since there would not be more than 3 mfgs, I think I can
get it to work, if I can figure out a way to get the fields in that second
combo box to populate with columns 2, 3, and 4 from the first combo box.

I have entered the code you gave me in the After Update code area. Should
it work? Am I supposed to make any changes to the second combo box (the
one
receiving the values)? Right now, it is...

unbound
Control Source: [blank]
Row Source Type: Value List
Row Source: [blank]
Click to expand...


I can't say whether the ControlSource of the second combo box should be
blank or not -- that depends on whether you intend for the selected value to
be stored directly in the form's underlying table or not. However, the rest
of that looks correct.

There was a small bug in the code I gave in my previous post -- I left out
an End If statement. Did you discover that? Other than that, it should
work -- in principle, of course, and after the names have been corrected.
Be aware that columns 2, 3, and 4 of a combo box will be known in code by
..Column(1), .Column(2), and .Column(3), because the .Column collection is
0-based.

Here's some revised code, based on the information you've given me in this
post:

'----- start of revisd code -----
Private Sub cboFirst_AfterUpdate()

Const Q As String = """"
Dim I As Long
Dim strRowSource As String
Dim strValue As String

With Me.cboFirst

If IsNull(.Value) Then
Me.cboSecond.RowSource = ""
Else

For I = 1 To 3

strValue = .Column(I) & vbNullString

If Len(strValue) > 0 Then
strRowSource = strRowSource & ";" & Q & strValue & Q
End If

Next I

Me.cboSecond.RowSource = Mid$(strRowSource, 2)

End If

End With

End Sub
'----- end of revised code -----

Be sure to correct the names "cboFirst" and "cboSecond" that I've used in
the above code. And note that this is for the AfterUpdate event of the
combo box, not the form itself.

If this doesn't work, it's possible that the cboSecond should be
representing numeric values, not strings. In that case, set the rowsource
without the quotes:

strRowSource = strRowSource & ";" & strValue

Also, if this doesn't seem to be working, give me the real names of the
controls and the data types of the fields involved, and maybe we can get
more specific.
 
K

kealaz

I have a form called frmPOTODO_process. The Record Source of this form is
tblPOTODO. From that table I retrieve...

PART_NO
DESCRIP
NAME
MANUF1
MANUF1_PN
MANUF2
MANUF2_PN
MANUF3
MANUF3_PN

What I ultimately need to do with this form is 1) choose a manuf and 2)
choose a vendor. thru these posts... I have been trying to tackle that first
goal. Choose a manuf. My first combo box has the following properties...

Name: PART_NO
Control Source: PART_NO
Row Source Type: Table/Query
Row Source: SELECT tblPOTODO.PART_NO, tblPOTODO.MANUF1, tblPOTODO.MANUF2,
tblPOTODO.MANUF3, FROM tblPOTODO;
Column Count: 4
Column Widths: 2";0";0";0"

This combo box is working correctly and giving me the results I want.

Next I have two text boxes [DESCRIP AND NAME]

Then my second combo box with the following info.:

Name: chooseMANUF
Control Source: [blank]
Row Source Type: [not sure what to put here... I've tried Table/Query, Value
List and Field List -- I think (from your previous posts to me, it should be
Value List)]
Row Source: [blank]

What I want the second combo box to have as it's values are the values of
MANUF1, MANUF2 AND MANUF3 for the part number displayed, which would corelate
to column 2, 3 and 4 of the first combo box (or column 1, 2 & 3 in the coding)
Then the user can choose, one of the manufacturers for that part. Once that
selection is made....

I was going to pass that information to a text box and then have another
combo box with selections available to choose a vendor. [I realize I haven't
gotten here yet, but I'm trying to give you a more complete picture so that
you can help me, and belive me I TRULY APPRECIATE ALL OF YOUR HELP!!!]

I have a table called tblMANUF with the following information:
MANUF
VENDORNAME
VENDORNO

I'm thinking (maybe incorrectly) that once I get the first two combo boxes
working, this third one will be easier, especially since the underlying
tables are closer to what you guys call "normalized".

Once the MANUF and VENDOR is chosen, then I need all the information to be
written to a table called tblBUY, where the info will wait until a purchase
order is cut for it.


**********************************

So far, I have tried....


< ----- begin code ----- >


Private Sub PART_NO_AfterUpdate()

Const Q As String = """"

With Me.PART_NO

If IsNull(.Value) Then
Me.chooseMANUF.RowSource = ""
Else

Me.chooseMANUF.RowSourceType = "Value List"

Me.chooseMANUF.RowSource = Q & .Column(1) & Q & ";" & _
Q & .Column(2) & Q & ";" & _
Q & .Column(3) & Q


End If

End With



End Sub

< ----- end code ----- >


I have also tried the following....


< ----- begin code ----- >

Private Sub PART_NO_AfterUpdate()

Const Q As String = """"
Dim I As Long
Dim strRowSource As String
Dim strValue As String

With Me.PART_NO

If IsNull(.Value) Then
Me.chooseMANUF.RowSource = ""
Else

For I = 1 To 3

strValue = .Column(I) & vbNullString

If Len(strValue) > 0 Then
strRowSource = strRowSource & ";" & Q & strValue & Q
End If

Next I

Me.chooseMANUF.RowSource = Mid$(strRowSource, 2)

End If

End With


End Sub

< ----- end code ----- >


......both with no luck on getting the second combo box to populate with the
values from the first combo box.



Again, thank you so much for your help. I really hope I can get this to go.
 
D

Dirk Goldgar

Thank you, that was a lot of useful information. I just set up a test table
and form with controls as you've described and code as in the second block
(which was the second version I posted). It worked just fine for me. Once
I chose a part, the manufacturers of that part were listed in the second
combo box's dropdown list. So I have to wonder if the code is being
executed at all.

What version of Access are you running? If it's Access 2007, you'll have to
put the database in a trusted location, or explicitly trust the database,
before code in it will run.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


kealaz said:
I have a form called frmPOTODO_process. The Record Source of this form is
tblPOTODO. From that table I retrieve...

PART_NO
DESCRIP
NAME
MANUF1
MANUF1_PN
MANUF2
MANUF2_PN
MANUF3
MANUF3_PN

What I ultimately need to do with this form is 1) choose a manuf and 2)
choose a vendor. thru these posts... I have been trying to tackle that
first
goal. Choose a manuf. My first combo box has the following properties...

Name: PART_NO
Control Source: PART_NO
Row Source Type: Table/Query
Row Source: SELECT tblPOTODO.PART_NO, tblPOTODO.MANUF1, tblPOTODO.MANUF2,
tblPOTODO.MANUF3, FROM tblPOTODO;
Column Count: 4
Column Widths: 2";0";0";0"

This combo box is working correctly and giving me the results I want.

Next I have two text boxes [DESCRIP AND NAME]

Then my second combo box with the following info.:

Name: chooseMANUF
Control Source: [blank]
Row Source Type: [not sure what to put here... I've tried Table/Query,
Value
List and Field List -- I think (from your previous posts to me, it should
be
Value List)]
Row Source: [blank]

What I want the second combo box to have as it's values are the values of
MANUF1, MANUF2 AND MANUF3 for the part number displayed, which would
corelate
to column 2, 3 and 4 of the first combo box (or column 1, 2 & 3 in the
coding)
Then the user can choose, one of the manufacturers for that part. Once
that
selection is made....

I was going to pass that information to a text box and then have another
combo box with selections available to choose a vendor. [I realize I
haven't
gotten here yet, but I'm trying to give you a more complete picture so
that
you can help me, and belive me I TRULY APPRECIATE ALL OF YOUR HELP!!!]

I have a table called tblMANUF with the following information:
MANUF
VENDORNAME
VENDORNO

I'm thinking (maybe incorrectly) that once I get the first two combo boxes
working, this third one will be easier, especially since the underlying
tables are closer to what you guys call "normalized".

Once the MANUF and VENDOR is chosen, then I need all the information to be
written to a table called tblBUY, where the info will wait until a
purchase
order is cut for it.


**********************************

So far, I have tried....


< ----- begin code ----- >


Private Sub PART_NO_AfterUpdate()

Const Q As String = """"

With Me.PART_NO

If IsNull(.Value) Then
Me.chooseMANUF.RowSource = ""
Else

Me.chooseMANUF.RowSourceType = "Value List"

Me.chooseMANUF.RowSource = Q & .Column(1) & Q & ";" & _
Q & .Column(2) & Q & ";" & _
Q & .Column(3) & Q


End If

End With



End Sub

< ----- end code ----- >


I have also tried the following....


< ----- begin code ----- >

Private Sub PART_NO_AfterUpdate()

Const Q As String = """"
Dim I As Long
Dim strRowSource As String
Dim strValue As String

With Me.PART_NO

If IsNull(.Value) Then
Me.chooseMANUF.RowSource = ""
Else

For I = 1 To 3

strValue = .Column(I) & vbNullString

If Len(strValue) > 0 Then
strRowSource = strRowSource & ";" & Q & strValue & Q
End If

Next I

Me.chooseMANUF.RowSource = Mid$(strRowSource, 2)

End If

End With


End Sub

< ----- end code ----- >


.....both with no luck on getting the second combo box to populate with
the
values from the first combo box.



Again, thank you so much for your help. I really hope I can get this to
go.
Click to expand...
 
K

kealaz

I'm running Access 2003. I will try it again. Is there a way I can test if
it's reading the code that would be more obvious. I do web design... and
sometimes if I want to see something I will make put in dummy info and make
it LARGE and RED so it stands out to test if the code is even getting
accepted. Once I know it's being red, I will do my more subtle changes. Is
there a correlation in the coding of these combo boxes?
 
D

Dirk Goldgar

kealaz said:
I'm running Access 2003. I will try it again. Is there a way I can test
if
it's reading the code that would be more obvious. I do web design... and
sometimes if I want to see something I will make put in dummy info and
make
it LARGE and RED so it stands out to test if the code is even getting
accepted. Once I know it's being red, I will do my more subtle changes.
Is
there a correlation in the coding of these combo boxes?
Click to expand...


Set a breakpoint in the code, then run the form, choose something in the
PART_NO combo, and see if the code execution stops at the breakpoint.

Even before doing that, check the Event tab of the property sheet of the
PART_NO combo, and make sure it says "[Event Procedure]" on the After Update
line.
 
K

kealaz

Hi Dirk,

It does say [Event Procedure]. And I have other After Update codes in other
parts of my database that have successfully been implemented.

I am definitely missing something. I have also set up a test table and
form, as you did, in a clean, new database (great idea!) and it's just not
working. I'm sure you've figured out by now that I have no clue... this is
my first time working w/ Access, or any database.... but I'm persistent. Do
you know what I might be missing? When I set it up, I made only the changes
that I outlined in my previous posts. Everything else would be whatever
Access has as default. I'm running MS Access 2003.

I don't know what the term "trust a database" would mean, or how to do that.
I am trying to figure out how to set a breakpoint.

Thank you for your time with this.
 
D

Dirk Goldgar

kealaz said:
Hi Dirk,

It does say [Event Procedure]. And I have other After Update codes in
other
parts of my database that have successfully been implemented.

I am definitely missing something. I have also set up a test table and
form, as you did, in a clean, new database (great idea!) and it's just
not
working. I'm sure you've figured out by now that I have no clue... this
is
my first time working w/ Access, or any database.... but I'm persistent.
Do
you know what I might be missing? When I set it up, I made only the
changes
that I outlined in my previous posts. Everything else would be whatever
Access has as default. I'm running MS Access 2003.

I don't know what the term "trust a database" would mean, or how to do
that.
Click to expand...

If you're using Access 2003 or earlier, you don't have to worry about it.
I am trying to figure out how to set a breakpoint.
Click to expand...

Open the form in design view. Open its code module in the VB editor.
Locate the PART_NO_AfterUpdate() procedure. Click on the vertical gray bar
on at the left edge of the module window, next to the line of code where you
want to set the breakpoint -- I suggest you use the Sub header line itself.
A big red-brown dot should appear where you clicked, indicating that a
breakpoint has been set.

Alternative to that, just click on the line of code, then press F9.

Once you've set a breakpoint, open the form in normal form view and select a
part in the combo box. If the code doesn't stop at the breakpoint, then the
code isn't being called. If it does, you can pressing F8 to step through it
line by line, allowing you to see the path the code takes and even inspect
the values of variables and controls using the facilities of the debugger.

If that doesn't lead you to an understanding of what's wrong, then ...
If you'd like to send me a cut-down copy of your database, containing only
the elements necessary to demonstrate the problem, compacted and then zipped
to less than 1MB in size (preferably much smaller) -- I'll have a look at
it, time permitting. You can send it to the address derived by removing NO
SPAM and ".invalid" from the reply address of this message. If that address
isn't visible to you, you can get my address from my web site, which is
listed in my sig. Do *not* post my real address in the newsgroup -- I don't
want to be buried in spam and viruses.
 
K

kealaz

Holy Guacamole!!! It's working, it's working, it's working!!! Thanks so
much. Okay, I knew it would be something 'dumb'... Because the part numbers
were "showing" in the first combo box, I wasn't SELECTING a part number...
but when I was following your troubleshooting tips, carefully, I went and
selected a different part number, just for the heck of it... and then the
second combo box populated. THANK YOU so much. Okay... it's working in that
test database. Now to get it to work in my real database. Thanks Dirk! :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo Box QUESTION 5
Union and group, then count 1
Hyperlink 1
Trouble with combo box 1
Combo box trouble 1
Combining firstname and lastname in combo box 6
Access 2003 Combo Box 4
Mult-Value Select returning ID# not text. 4

Top