Combobox displays data in drop down list, but after updated the box is empty ??

[Marc]

Dear All,

I have two cascating comboboxes that wok fine, nevertheless I notice an odd
behaviour.
In the second cmb, after having choosen the interested datum, the box
appears empty.......and if I open the Form, containing the various fields,
the 2 comboboxes are empty.
The queer is that in the tables data are present.

Am I wronging something or this is due to an Access 2003 bug?

Any answer is appreciated and welcomed.

Thanks in advance,
Marc

 

[tina]

my guess is that your form is set to Continuous Forms view or Datasheet
view. when you change the available selections in a droplist, as in bound
cascading comboboxes, and the list in the *current* record does not include
values that were selected in other visible records, then the bound combobox
control in those records will *appear* blank. but no, the data has not
disappeared from the underlying table.

in Datasheet view, you have to put up with it. the most you can do is to add
the table, that sources the combobox droplist, to the form's RecordSource.
use a LEFT JOIN *from* the form's data table *to* the new table; that's a
reversal of the primary/foreign key link in the Relationships window, of
course, but it's necessary. then add a new control to the form, locked so
the user can't try to change values in it, and set its' ControlSource to the
appropriate field from the new table in the form's RecordSource. in
Datasheet view, you'll now have an additional column showing the value
chosen in the combobox, even though the combobox itself may appear blank.

in Continuous Forms view, you can get around this visual inconvenience by
putting a textbox control on top of the combobox control, leaving only the
"down arrow" of the combobox showing. set the textbox's Tabstop property to
No and lock it to prevent editing, same as above. set its' ControlSource to
the appropriate field from the new table in the form's RecordSource. so the
user will *see* the textbox control for every record, but will actually tab
into the combobox control when editing a record.

hth

 

[Marc]

my guess is that your form is set to Continuous Forms view or Datasheet
view. when you change the available selections in a droplist, as in bound
cascading comboboxes, and the list in the *current* record does not
include
values that were selected in other visible records, then the bound
combobox
control in those records will *appear* blank. but no, the data has not
disappeared from the underlying table.

in Datasheet view, you have to put up with it. the most you can do is to
add
the table, that sources the combobox droplist, to the form's RecordSource.
use a LEFT JOIN *from* the form's data table *to* the new table; that's a
reversal of the primary/foreign key link in the Relationships window, of
course, but it's necessary. then add a new control to the form, locked so
the user can't try to change values in it, and set its' ControlSource to
the
appropriate field from the new table in the form's RecordSource. in
Datasheet view, you'll now have an additional column showing the value
chosen in the combobox, even though the combobox itself may appear blank.

in Continuous Forms view, you can get around this visual inconvenience by
putting a textbox control on top of the combobox control, leaving only the
"down arrow" of the combobox showing. set the textbox's Tabstop property
to
No and lock it to prevent editing, same as above. set its' ControlSource
to
the appropriate field from the new table in the form's RecordSource. so
the
user will *see* the textbox control for every record, but will actually
tab
into the combobox control when editing a record.

hth

Thank you Tina for your answer,
.....but I have to say that I am not an advanced Access user.

The two cascating comboboxes are unbounded in a DataEntry Form in a single
view.

I have 4 Tables linked each other with a relationship 1 to many.

A DataEntry Form has fields for the 3rd table (excursions and invoices
details) in
the same form there is a subform for the 4th table (persons and fee), now I
have put two unbounded cascating comboboxes named Category (linked to 1st
table already filled in advance) and Customer (linked 2nd table already
filled in advance).

I do not want to have a one big table only, with double data, for this
reason the Category and Customer tables are stores with unique data only,
where I choose/filter by mean two unbounded cmbs the related data for the
excursions.

I hope this clues are clear, otherwise thanks the same for your help!!

Kind regards
Marc

 

[tina]

okay. your cascading comboboxes are on a form bound to table3, but the
combobox controls themselves are unbound, and their RowSources are table1
and table2, respectively. when you open a form, it is normal for any unbound
control to be "blank", unless you have a DefaultValue set for the control,
or set a value in the control, usually in the form's Load event.

once the form is open, any value that you enter in an unbound control will
normally remain as you move from record to record in the form - unless you
change or remove it with VBA code or a macro, usually in the form's Current
event or perhaps another control's AfterUpdate event.

sorry to be dense, but i'm not understanding what behavior you're *wanting
or expecting* to see, and what behavior you are actually seeing. will you
explain further, please?

hth

 

[Marc]

okay. your cascading comboboxes are on a form bound to table3, but the
combobox controls themselves are unbound, and their RowSources are table1
and table2, respectively. when you open a form, it is normal for any
unbound
control to be "blank", unless you have a DefaultValue set for the control,
or set a value in the control, usually in the form's Load event.

once the form is open, any value that you enter in an unbound control will
normally remain as you move from record to record in the form - unless you
change or remove it with VBA code or a macro, usually in the form's
Current
event or perhaps another control's AfterUpdate event.

sorry to be dense, but i'm not understanding what behavior you're *wanting
or expecting* to see, and what behavior you are actually seeing. will you
explain further, please?

hth

Hi Tina, thanks again for your answer!
I didn't want to write a long and boring post, so I have been concise.

From your answer I understand a little more, now I'll try to be more clear.
So.....I have 4 tables linked among them with a relationship 1 to many


*TblCategory*
IdCategory
Category

*TblCustomer*
IdCategoria
Customer
Address1
Address2
Address3
Country
Tel
Ecc.

*TblExcursions*
IdExcursion
DateExcursion
N°People
Note
N°Invoice
AmountInvoice
Paid (Yes/No)
PaimentDate
IdCustomer

*TblEmployee*
IdEmployees
Staff
Fee
IdExcursion

The DataEntry Excursions has fields related the excursions and invoice
(TblExcursions) and a subform in a spreadsheet view, related Staff and Fee
(TblEmployee)

When the user (I am doing all that for a friend of mine, who uses excel)
records the excursion, first she opens the Customer Form, checks if the
customer is already present (TblCustomer), if so, then she opens the
DataEntry Excursions Form, in which there are two unbounded comboboxes, the
first is Category (Schools, Factories, Travel Agencies and so on) with these
properties:

DataTab=> RowSource (from inner query)
SELECT TblCategory.IDCategory, TblCategory.Category FROM
TblCategory;

EventTab=>AfterUpdate:
Private Sub Category_AfterUpdate()
Me!Customer.Requery
End Sub


the second unbounded combobox is Customer, which properties are as follows:

DataTab=>RowSource (from inner query)
SELECT TblCustomer.IdCustomer, TblCustomer.Customer, TblCustomer.Address1,
TblCustomer.Address2, TblCustomer.Address3, TblCustomer.Country,
TblCustomer.IdCategory FROM TblCustomer WHERE
(((TblCustomer.IdCategory)=Forms!frmDataEntry!Category)) ORDER BY
TblCustomer.Customer;


EventTab=>AfterUpdate:
Private Sub Customer_AfterUpdate()
Customer = Customer.Column(1)
End Sub

= = = = = = =
Here is the problem:

When the user selects the category, the cmb works fine!
When the user selects the filtered customer in the dropdown list in the
second cmb, the customers are displayed, but not set in the combobox and the
combobox remains empty.

Futhermore:
From another form, named FindNotSettledInvoice, with a double click I can
open the related record in the DataEntryExcursions, all the values are shown
in the fields exept the two unbounded comboboxes, which are empty.

I have been working from a week in solving this problem, but I don't want to
give up. Maybe I am close the solution and I don't know that......so I need
the help of experienced Access users.

I hope, I have been clear, if not ask again.

Kind regards,
Marc

 

[tina]

comments inline.

From your answer I understand a little more, now I'll try to be more clear.
So.....I have 4 tables linked among them with a relationship 1 to many


*TblCategory*
IdCategory
Category

*TblCustomer*
IdCategoria
Customer
Address1
Address2
Address3
Country
Tel
Ecc.

*TblExcursions*
IdExcursion
DateExcursion
N°People
Note
N°Invoice
AmountInvoice
Paid (Yes/No)
PaimentDate
IdCustomer

*TblEmployee*
IdEmployees
Staff
Fee
IdExcursion

The DataEntry Excursions has fields related the excursions and invoice
(TblExcursions) and a subform in a spreadsheet view, related Staff and Fee
(TblEmployee)

When the user (I am doing all that for a friend of mine, who uses excel)
records the excursion, first she opens the Customer Form, checks if the
customer is already present (TblCustomer), if so, then she opens the
DataEntry Excursions Form, in which there are two unbounded comboboxes, the
first is Category (Schools, Factories, Travel Agencies and so on) with these
properties:

DataTab=> RowSource (from inner query)
SELECT TblCategory.IDCategory, TblCategory.Category FROM
TblCategory;

EventTab=>AfterUpdate:
Private Sub Category_AfterUpdate()
Me!Customer.Requery
End Sub


the second unbounded combobox is Customer, which properties are as follows:

DataTab=>RowSource (from inner query)
SELECT TblCustomer.IdCustomer, TblCustomer.Customer, TblCustomer.Address1,
TblCustomer.Address2, TblCustomer.Address3, TblCustomer.Country,
TblCustomer.IdCategory FROM TblCustomer WHERE
(((TblCustomer.IdCategory)=Forms!frmDataEntry!Category)) ORDER BY
TblCustomer.Customer;


EventTab=>AfterUpdate:
Private Sub Customer_AfterUpdate()
Customer = Customer.Column(1)
End Sub

= = = = = = =
Here is the problem:

When the user selects the category, the cmb works fine!

okay, so selecting a category filters the droplist in combobox Customer, as
expected.

When the user selects the filtered customer in the dropdown list in the
second cmb, the customers are displayed, but not set in the combobox and the
combobox remains empty.

what is the primary key field of TblCustomer? and what is the foreign key
field that links each customer record to a specific category in TblCategory?
and finally, what is the BoundColumn of the Customer combobox control?

 

[Marc]

[...]

what is the primary key field of TblCustomer? and what is the foreign key
field that links each customer record to a specific category in
TblCategory?
and finally, what is the BoundColumn of the Customer combobox control?

Sorry Tina, in the previous post I forgot to write the Customer Primary Key,
but it is present (IdCustomer)

*TblCustomer*
IdCustomer = Counter (Primary Key)
IdCategory = Numeric
Customer = Text
Address1 = Text
Address2 = Text
Address3 = Text
Country = Text
Tel = Text
Ecc. = Text

Please, what do you mean when you say foreign key, is it IdCategory?

When I open the Category Table, in the left side there is a + sign, if I
click the + sign another table appears (Customer Table). At the left side of
the Customer Table is always present a + sign which contains the Excursion
Table, and so for the Employee Table.

It's like a big box, which inside has another middle size box, which inside
has another little box.... and so on. Ralationship 1 to many.

The BoundColumn of the Customer combobox control is the 1

DataTab=>RowSource (from inner query)
IdCustomer (column 0)
Customer (column 1)
Address1 (column 2)
Address2 (column 3)
Address3 (column 4)
Country (column 5)
IdCategory (column 6) [Forms]![frmDataEntry]![Category]

SELECT TblCustomer.IdCustomer, TblCustomer.Customer, TblCustomer.Address1,
TblCustomer.Address2, TblCustomer.Address3, TblCustomer.Country,
TblCustomer.IdCategory FROM TblCustomer WHERE
(((TblCustomer.IdCategory)=Forms!frmDataEntry!Category)) ORDER BY
TblCustomer.Customer;


Is it right?

 

[tina]

comments inline.

[...]

what is the primary key field of TblCustomer? and what is the foreign key
field that links each customer record to a specific category in
TblCategory?
and finally, what is the BoundColumn of the Customer combobox control?

Sorry Tina, in the previous post I forgot to write the Customer Primary Key,
but it is present (IdCustomer)

okay, good.

*TblCustomer*
IdCustomer = Counter (Primary Key)
IdCategory = Numeric
Customer = Text
Address1 = Text
Address2 = Text
Address3 = Text
Country = Text
Tel = Text
Ecc. = Text

Please, what do you mean when you say foreign key, is it IdCategory?

yes, it is. you need to read up on relational design principles, hon.
everything you're working with here is based on those principles; this will
all make a lot more sense to you if you understand them. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

When I open the Category Table, in the left side there is a + sign, if I
click the + sign another table appears (Customer Table). At the left side of
the Customer Table is always present a + sign which contains the Excursion
Table, and so for the Employee Table.

okay. open TblCategory in Design view. open the Properties box and set the
Subdatasheet Name property to [None]. you should do this for every table in
the database. for more information, see
http://home.att.net/~california.db/tips.html#aTip6.

It's like a big box, which inside has another middle size box, which inside
has another little box.... and so on. Ralationship 1 to many.

The BoundColumn of the Customer combobox control is the 1

DataTab=>RowSource (from inner query)
IdCustomer (column 0)
Customer (column 1)
Address1 (column 2)
Address2 (column 3)
Address3 (column 4)
Country (column 5)
IdCategory (column 6) [Forms]![frmDataEntry]![Category]

SELECT TblCustomer.IdCustomer, TblCustomer.Customer, TblCustomer.Address1,
TblCustomer.Address2, TblCustomer.Address3, TblCustomer.Country,
TblCustomer.IdCategory FROM TblCustomer WHERE
(((TblCustomer.IdCategory)=Forms!frmDataEntry!Category)) ORDER BY
TblCustomer.Customer;

okay. the BoundColumn property is set to 1, which is the default setting.
that's good. but, you need to understand that the "1" refers to the *first
column*, NOT to the index value of the column. in other words, the bound
column is IdCustomer, which is Column(0). don't change it - that's what it
should be. now, let's go back to the event procedure you posted previously
for the Customer combobox control:

EventTab=>AfterUpdate:
Private Sub Customer_AfterUpdate()
Customer = Customer.Column(1)
End Sub

here's your problem. after you choose a customer from the droplist, the code
is trying to change the value of the Customer control from the IdCustomer
value you selected, to the value in the *2nd column of the combobox, which
is the Customer field* (and again, i assume that field holds a customer
name). first, you can't do that, and second, you don't want to. you need the
value in the Customer combobox control to be the IdCustomer value. if you
want to *see* the customer name, rather than the IdCustomer value, after
selecting a customer from the droplist, then set the ColumnWidth of the
first column to zero (0). the column will be hidden, but Access can still
"see" the value in it. recommend you read up on all the properties specific
to combobox controls, so you'll understand better how they work. the easy
way to do this is open the form in Design view, and click on a combobox
control; in the Properties box, click on a property "line" that you want to
read about, and press F1. Access Help will automatically open to that topic.

hth

 

[Marc]

[...]
Comment inline

yes, it is. you need to read up on relational design principles, hon.
everything you're working with here is based on those principles; this
will
all make a lot more sense to you if you understand them. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

Thank you Tina for giving me refences, in order to fill my lacks.

okay. the BoundColumn property is set to 1, which is the default setting.
that's good. but, you need to understand that the "1" refers to the *first
column*, NOT to the index value of the column. in other words, the bound
column is IdCustomer, which is Column(0). don't change it - that's what it
should be.

Ok, I understood!

now, let's go back to the event procedure you posted previously

for the Customer combobox control:

EventTab=>AfterUpdate:
Private Sub Customer_AfterUpdate()
Customer = Customer.Column(1)
End Sub

here's your problem. after you choose a customer from the droplist, the
code
is trying to change the value of the Customer control from the IdCustomer
value you selected, to the value in the *2nd column of the combobox, which
is the Customer field* (and again, i assume that field holds a customer
name).

Yes

first, you can't do that, and second, you don't want to. you need the

value in the Customer combobox control to be the IdCustomer value. if you
want to *see* the customer name, rather than the IdCustomer value, after
selecting a customer from the droplist, then set the ColumnWidth of the
first column to zero (0). the column will be hidden, but Access can still
"see" the value in it.

But, if I change the VB routine in:

Private Sub Customer_AfterUpdate()
Customer = IdCustomer.Column(0)
End Sub

I have a Debug Message Error

recommend you read up on all the properties specific

to combobox controls, so you'll understand better how they work. the easy
way to do this is open the form in Design view, and click on a combobox
control; in the Properties box, click on a property "line" that you want
to
read about, and press F1. Access Help will automatically open to that
topic.

Please. give more time in reading the Access Help, since somenime it's not
so user friendly. Now I have to go to work. I think if nothing happens, I
can do that this evening.
Thanks again for your answers.
Kind regards
Marc

 

[Marc]

[...]

Please. give more time in reading the Access Help, since somenime it's not
so user friendly. Now I have to go to work. I think if nothing happens, I
can do that this evening.
Thanks again for your answers.
Kind regards
Marc

Hi Tina, *I made a step aheah!!*

I changed/set the BoundColumn to 2 (Customer in Row Source) and the second
combobox works perfectly!!!!
You are Great!!

Nertheless, if I reopen the frmDataEntry the two unbounded combobox are
still empty.....
Now I have to read the Access inline Help (F1), but please do not leave
alone....you have been the only one who helped me. If you can, let me know
something about this trick.

THANK YOU AGAIN and see you soon!!!

Warmest regards
Marc

 

[tina]

[...]
Comment inline

yes, it is. you need to read up on relational design principles, hon.
everything you're working with here is based on those principles; this
will
all make a lot more sense to you if you understand them. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

Thank you Tina for giving me refences, in order to fill my lacks.

okay. the BoundColumn property is set to 1, which is the default setting.
that's good. but, you need to understand that the "1" refers to the *first
column*, NOT to the index value of the column. in other words, the bound
column is IdCustomer, which is Column(0). don't change it - that's what it
should be.

Ok, I understood!

now, let's go back to the event procedure you posted previously

for the Customer combobox control:

EventTab=>AfterUpdate:
Private Sub Customer_AfterUpdate()
Customer = Customer.Column(1)
End Sub

here's your problem. after you choose a customer from the droplist, the
code
is trying to change the value of the Customer control from the IdCustomer
value you selected, to the value in the *2nd column of the combobox, which
is the Customer field* (and again, i assume that field holds a customer
name).

Yes

first, you can't do that, and second, you don't want to. you need the

value in the Customer combobox control to be the IdCustomer value. if you
want to *see* the customer name, rather than the IdCustomer value, after
selecting a customer from the droplist, then set the ColumnWidth of the
first column to zero (0). the column will be hidden, but Access can still
"see" the value in it.

But, if I change the VB routine in:

Private Sub Customer_AfterUpdate()
Customer = IdCustomer.Column(0)
End Sub

why, oh why, oh why, are you trying to set the value of the Customer
combobox control, after you just selected a value from the droplist? please
explain what you're trying to accomplish here, because it frankly doesn't
make any sense.

 

[tina]

comments inline.

[...]

Please. give more time in reading the Access Help, since somenime it's not
so user friendly. Now I have to go to work. I think if nothing happens, I
can do that this evening.
Thanks again for your answers.
Kind regards
Marc

Hi Tina, *I made a step aheah!!*

I changed/set the BoundColumn to 2 (Customer in Row Source) and the second
combobox works perfectly!!!!

well, it's showing you the customer name, hon, once you choose it. and if
that's all you want, is to see that name - though i can't imagine why - then
no problem. but if you want to actually use the "identity" of that customer
record - in other words, the primary key - to do something in the data entry
form, then you should keep the bound column as 1, and hide it, like i told
you before.

and please see my comments in response to your previous post.

Nertheless, if I reopen the frmDataEntry the two unbounded combobox are
still empty.....

yes, they're unbound controls. so when you open the form, they will always
be empty - unless you write code to set their values, or set a default value
for each one in its' DefaultValue property. just what is it you were
expecting, or hoping, to see in those controls when you open the form?

Now I have to read the Access inline Help (F1), but please do not leave
alone....you have been the only one who helped me. If you can, let me know
something about this trick.

i'm not sure what your question is here. i already told you how to see a
relevant Help topic using F1; do you have a question about my directions?

 

[Marc]

[...]

why, oh why, oh why, are you trying to set the value of the Customer
combobox control, after you just selected a value from the droplist?
please
explain what you're trying to accomplish here, because it frankly doesn't
make any sense.

I am making R&D (research & development)

 

[Marc]

[...]
Comment inline

well, it's showing you the customer name, hon, once you choose it. and if
that's all you want, is to see that name - though i can't imagine why -
then
no problem. but if you want to actually use the "identity" of that
customer
record - in other words, the primary key - to do something in the data
entry
form, then you should keep the bound column as 1, and hide it, like i told
you before.

and please see my comments in response to your previous post.

I read it carefully, and my boundcolumn was set to 1; the doptlist has 6
columns (IdCustomer = 0cm; Customer = 5cm; Address1 = 5cm; and so on...)
but it doesn't work, unless I change the boundcolumn to 2 (Customer)

yes, they're unbound controls. so when you open the form, they will always
be empty - unless you write code to set their values, or set a default
value
for each one in its' DefaultValue property. just what is it you were
expecting, or hoping, to see in those controls when you open the form?

Ahh.... unbound controls. Yes, when I re-open a record, I expect/hope to see
the
data set previously.........but I don't want to have double controls in
every tables....Access is a related database....in your experience what do
you suggest me.

i'm not sure what your question is here. i already told you how to see a
relevant Help topic using F1; do you have a question about my directions?

I am not a child anymore, and I have nobody who makes my household chores,
shopping and duties..... and I want to help my friends and have a little
freetime for myself in recharging my batteries. And the Inline Help (F1),
sometime is a little boring and not user friendly.....but if, at the moment,
is the only solution, I'll do that!

Anyway, THANK YOU again Tina for your help!
Best wishes

Marc

 

[tina]

when you say "it doesn't work", i assume you mean the code in the
AfterUpdate event procedure. here's my question and your response, from the
other arm of this thread (which i won't go back to again).

okay, well, that answer is not specific or useful in any way.

[...]

why, oh why, oh why, are you trying to set the value of the Customer
combobox control, after you just selected a value from the droplist?
please
explain what you're trying to accomplish here, because it frankly doesn't
make any sense.

I am making R&D (research & development)

that answer is not specific, and gives me absolutely no useful information
to try to help you further. and i have to say that i've never, in 5+ years
in these newsgroups, had an op complain that reading "boring" Help files
takes time better used in other pursuits. your priorities are your business,
of course, but since i've been spending *my* personal time trying my best to
help you (and i have a job and a personal life, too), i'm not impressed with
your commitment to doing the work - often long, hard, tedious work - it
takes to learn to use Access to produce quality applications. i'm done with
this thread. good luck with your project.

[...]
Comment inline

well, it's showing you the customer name, hon, once you choose it. and if
that's all you want, is to see that name - though i can't imagine why -
then
no problem. but if you want to actually use the "identity" of that
customer
record - in other words, the primary key - to do something in the data
entry
form, then you should keep the bound column as 1, and hide it, like i told
you before.

and please see my comments in response to your previous post.

I read it carefully, and my boundcolumn was set to 1; the doptlist has 6
columns (IdCustomer = 0cm; Customer = 5cm; Address1 = 5cm; and so on...)
but it doesn't work, unless I change the boundcolumn to 2 (Customer)

yes, they're unbound controls. so when you open the form, they will always
be empty - unless you write code to set their values, or set a default
value
for each one in its' DefaultValue property. just what is it you were
expecting, or hoping, to see in those controls when you open the form?

Ahh.... unbound controls. Yes, when I re-open a record, I expect/hope to see
the
data set previously.........but I don't want to have double controls in
every tables....Access is a related database....in your experience what do
you suggest me.

i'm not sure what your question is here. i already told you how to see a
relevant Help topic using F1; do you have a question about my

directions?

I am not a child anymore, and I have nobody who makes my household chores,
shopping and duties..... and I want to help my friends and have a little
freetime for myself in recharging my batteries. And the Inline Help (F1),
sometime is a little boring and not user friendly.....but if, at the moment,
is the only solution, I'll do that!

Anyway, THANK YOU again Tina for your help!
Best wishes

Marc