Combo Box data to Table Problem

[Konchetta via AccessMonster.com]

Greetings All,
This website has been so helpful in my designing the database for my Internal
Audit Department but I have one problem that has been driving me crazy for
the past 2 weeks and I now want to seek your assistance for I haven't seen
this questioned in any other forums.

I have 6 combo boxes on my Form-Audit Receipt Input Form whose record source
is Table-Audit Information. I have the Form setup to for the user to choose
the Sub-Grantee from Combo Box 1 and once chosen Combo Box 2 and Combo Box 3 -
6 will populate the Control Number and Programs associated with the Sub-
Grantee chosen because of the coding I have listed below.

=[Sub-Grantee Name1].Column(1)
=[Sub-Grantee Name1].Column(2) and so on for each Program

I finally realized that although the data is showing on my form, it is not
populating to my table to be included on my reports. WHY OH WHY???? I
believe my Combo Boxes 2 and 3-6 should really be List Boxes but I had a bit
of a problem with trying to show the chosen fields on the Form.

Background of the Program Combo Boxes: I have four Combo boxes-Program #1,
Program #2, Program #3 and Program #4. The Sub-Grantee can be associated
with up to four programs so I broke them down like this instead of doing a
list box that gave me a problem but now I can't see my data in the tables.
Please HELP!!!! I have to present this on next week to my IT department.

Your assistance is GREATLY appreciated.

Konchetta(BRAIN FREEZE)

 

[SusanV]

Hi Konchetta,

The combobox should have your lookups in the rowsource property, and be
bound to the field in the table you wish to populate in the Control Source
property, granted that you would be adding/changing a single value to the
field. If I understand you correctly, you want each combobox to add to the
previous data in the field, correct?

If so, you *could* use the after_update event on each combobox to append
that detail to the field by leaving the combobox UNBOUND, then code to
concatenate. However, this is probably a bad idea, because if a user then
changes their mind on a choice your code would simply append the new option
rather than changing the previous entries.

You probably ought to change your form to use a listbox instead, unless you
actually have individual fields in the table for each combobox.

Or better yet, create a new table for the sub-grantee's choices, related to
the main table via the grantee's id (one-to-many, with many being the new
table). Then add a subform for the new tables records, linked via the
grantee's id, continuous form, with a combobox on each row bound to the
choice field in Control Source, with the lookup table as the Row Source.

 

[Klatuu]

If you are using Cascading Combo boxes like you are and those Combos are
bound controls, you will experience this kind of problem. The way I handle
this is to make my combos unbound so it will not affect what is in the record
and create a bound text control that is invisible to actually carry the data
to and from the table.
Just have the After Update event of the combo populate the corresponding
text box. You can also keep the value in the combos aligned with the
invisible text boxes using the form's Current event.

If Not Me.NewRecord Then
Me.cboProgram1 = Me.txtProgram1
Me.cboProgram2 = Me.txtProgram3
...
Me.cboProgram6 = Me.txtProgram6
End If

 

[Konchetta via AccessMonster.com]

Thank you so much Susan for even responding to my question. I will try to do
this and see what happens.
Konchetta

Hi Konchetta,

The combobox should have your lookups in the rowsource property, and be
bound to the field in the table you wish to populate in the Control Source
property, granted that you would be adding/changing a single value to the
field. If I understand you correctly, you want each combobox to add to the
previous data in the field, correct?

If so, you *could* use the after_update event on each combobox to append
that detail to the field by leaving the combobox UNBOUND, then code to
concatenate. However, this is probably a bad idea, because if a user then
changes their mind on a choice your code would simply append the new option
rather than changing the previous entries.

You probably ought to change your form to use a listbox instead, unless you
actually have individual fields in the table for each combobox.

Or better yet, create a new table for the sub-grantee's choices, related to
the main table via the grantee's id (one-to-many, with many being the new
table). Then add a subform for the new tables records, linked via the
grantee's id, continuous form, with a combobox on each row bound to the
choice field in Control Source, with the lookup table as the Row Source.

Greetings All,
This website has been so helpful in my designing the database for my

[quoted text clipped - 31 lines]

Konchetta(BRAIN FREEZE)

 

[Konchetta via AccessMonster.com]

Thanks to you as well Klattu. I will try this. Sounds like the same as Susan
as stating. Thanks so much for helping me out. If I don't get it, it's
because of my understanding. Will let you know. You guys are AWESOME!!!

Konchetta.

If you are using Cascading Combo boxes like you are and those Combos are
bound controls, you will experience this kind of problem. The way I handle
this is to make my combos unbound so it will not affect what is in the record
and create a bound text control that is invisible to actually carry the data
to and from the table.
Just have the After Update event of the combo populate the corresponding
text box. You can also keep the value in the combos aligned with the
invisible text boxes using the form's Current event.

If Not Me.NewRecord Then
Me.cboProgram1 = Me.txtProgram1
Me.cboProgram2 = Me.txtProgram3
...
Me.cboProgram6 = Me.txtProgram6
End If

Greetings All,
This website has been so helpful in my designing the database for my Internal

[quoted text clipped - 25 lines]

Konchetta(BRAIN FREEZE)

 

[SusanV]

Believe me, I get as much more than I give - great place to learn!

Thanks to you as well Klattu. I will try this. Sounds like the same as
Susan
as stating. Thanks so much for helping me out. If I don't get it, it's
because of my understanding. Will let you know. You guys are AWESOME!!!

Konchetta.

If you are using Cascading Combo boxes like you are and those Combos are
bound controls, you will experience this kind of problem. The way I
handle
this is to make my combos unbound so it will not affect what is in the
record
and create a bound text control that is invisible to actually carry the
data
to and from the table.
Just have the After Update event of the combo populate the corresponding
text box. You can also keep the value in the combos aligned with the
invisible text boxes using the form's Current event.

If Not Me.NewRecord Then
Me.cboProgram1 = Me.txtProgram1
Me.cboProgram2 = Me.txtProgram3
...
Me.cboProgram6 = Me.txtProgram6
End If

Greetings All,
This website has been so helpful in my designing the database for my
Internal

[quoted text clipped - 25 lines]

Konchetta(BRAIN FREEZE)

 

[Klatuu]

Amen

Believe me, I get as much more than I give - great place to learn!

Thanks to you as well Klattu. I will try this. Sounds like the same as
Susan
as stating. Thanks so much for helping me out. If I don't get it, it's
because of my understanding. Will let you know. You guys are AWESOME!!!

Konchetta.

If you are using Cascading Combo boxes like you are and those Combos are
bound controls, you will experience this kind of problem. The way I
handle
this is to make my combos unbound so it will not affect what is in the
record
and create a bound text control that is invisible to actually carry the
data
to and from the table.
Just have the After Update event of the combo populate the corresponding
text box. You can also keep the value in the combos aligned with the
invisible text boxes using the form's Current event.

If Not Me.NewRecord Then
Me.cboProgram1 = Me.txtProgram1
Me.cboProgram2 = Me.txtProgram3
...
Me.cboProgram6 = Me.txtProgram6
End If

Greetings All,
This website has been so helpful in my designing the database for my
Internal
[quoted text clipped - 25 lines]

Konchetta(BRAIN FREEZE)

 

[Konchetta via AccessMonster.com]

Hello Klatuu,
I think I see where you are going with this. I had to sit and THINK thru my
brain freeze. But I have one question for you in reference to the coding. I
have set up the invisible text boxes on the form and made my combo boxes
unbound. The coding you gave, should "Me" be the name of my Form and cbo and
txt be the names of my combo and text boxes like this?

If Not Audit Receipt Input Form.NewRecord Then
Audit Receipt Input Form.Program #1 = Audit Receipt Input Form.Program #1
....
or do I have to put the cbo and txt in front of the name of the boxes? I
don't know that part of the coding. I always see Me but not sure what it is
used for. Sorry to be illiterate in this matter.
Thanks again for your help. I feel relieved that I have an idea of where I
am going with this.

Konchetta

Amen

Believe me, I get as much more than I give - great place to learn!

[quoted text clipped - 30 lines]

 

[SusanV]

Me refers to the current form or report. So if you are on the form called
"MainForm", and want to reference the control "label0" on "MainForm" you
simply say Me.label0.
Also, if your form names have spaces, you need to enclose them in [square
brackets]. Using the # sign in field names is not a good idea - if you ever
wanted to port this to ASP or move it to a different database, you could run
into problems.
--
hth,
SusanV

Hello Klatuu,
I think I see where you are going with this. I had to sit and THINK thru
my
brain freeze. But I have one question for you in reference to the coding.
I
have set up the invisible text boxes on the form and made my combo boxes
unbound. The coding you gave, should "Me" be the name of my Form and cbo
and
txt be the names of my combo and text boxes like this?

If Not Audit Receipt Input Form.NewRecord Then
Audit Receipt Input Form.Program #1 = Audit Receipt Input Form.Program
#1
...
or do I have to put the cbo and txt in front of the name of the boxes? I
don't know that part of the coding. I always see Me but not sure what it
is
used for. Sorry to be illiterate in this matter.
Thanks again for your help. I feel relieved that I have an idea of where I
am going with this.

Konchetta

Amen

Believe me, I get as much more than I give - great place to learn!

[quoted text clipped - 30 lines]

Konchetta(BRAIN FREEZE)

 

[Klatuu]

In addition to Susan's answer, the txt and cbo are only naming conventions I
use to identify the object type in my code so I know what I am dealing with.
If you are on a sub form and want to address an object on the main form it is:
Me.Parent.txtSomething

Hello Klatuu,
I think I see where you are going with this. I had to sit and THINK thru my
brain freeze. But I have one question for you in reference to the coding. I
have set up the invisible text boxes on the form and made my combo boxes
unbound. The coding you gave, should "Me" be the name of my Form and cbo and
txt be the names of my combo and text boxes like this?

If Not Audit Receipt Input Form.NewRecord Then
Audit Receipt Input Form.Program #1 = Audit Receipt Input Form.Program #1
....
or do I have to put the cbo and txt in front of the name of the boxes? I
don't know that part of the coding. I always see Me but not sure what it is
used for. Sorry to be illiterate in this matter.
Thanks again for your help. I feel relieved that I have an idea of where I
am going with this.

Konchetta

Amen

Believe me, I get as much more than I give - great place to learn!

[quoted text clipped - 30 lines]

Konchetta(BRAIN FREEZE)

 

[Konchetta via AccessMonster.com]

You two are my Angels from HEAVEN!!!! Thanks.

Me refers to the current form or report. So if you are on the form called
"MainForm", and want to reference the control "label0" on "MainForm" you
simply say Me.label0.
Also, if your form names have spaces, you need to enclose them in [square
brackets]. Using the # sign in field names is not a good idea - if you ever
wanted to port this to ASP or move it to a different database, you could run
into problems.

Hello Klatuu,
I think I see where you are going with this. I had to sit and THINK thru

[quoted text clipped - 26 lines]

 

[Konchetta via AccessMonster.com]

I will also change the "#" in my field names.

Me refers to the current form or report. So if you are on the form called
"MainForm", and want to reference the control "label0" on "MainForm" you
simply say Me.label0.
Also, if your form names have spaces, you need to enclose them in [square
brackets]. Using the # sign in field names is not a good idea - if you ever
wanted to port this to ASP or move it to a different database, you could run
into problems.

Hello Klatuu,
I think I see where you are going with this. I had to sit and THINK thru

[quoted text clipped - 26 lines]

 

[SusanV]

Don't thank us - just help someone else when you can

;-D

You two are my Angels from HEAVEN!!!! Thanks.

Me refers to the current form or report. So if you are on the form called
"MainForm", and want to reference the control "label0" on "MainForm" you
simply say Me.label0.
Also, if your form names have spaces, you need to enclose them in [square
brackets]. Using the # sign in field names is not a good idea - if you
ever
wanted to port this to ASP or move it to a different database, you could
run
into problems.

Hello Klatuu,
I think I see where you are going with this. I had to sit and THINK thru

[quoted text clipped - 26 lines]

Konchetta(BRAIN FREEZE)