Combo problem

[Rick B]

Your combo box should not dsiplay deleted items. What is the source of your
combo-box? If it is pulling from a table or query, then it should only
display options that exist in that table or query.

 

[Derek Brown]

Hi all

Another problem I have with combo boxes and MSAccess wizard produced code is
that when selecting (accidentaly) an item from the combo that has been
previously deleted I get an error Run Time Error 13 Type missmatch. How do
you test for deleted items?

 

[Derek Brown]

Hi Rick

The form is based on a customer table. The combo gets its list from the
same table as the form. So when I delete a record it apears in the combo.
Any Ideas

 

[Rick B]

Are you sure? If you delete it from the table IT CAN'T pull to the
drop-down.

I don't think the combo-box's source is what you think it is.

Go into design view and tell us what is in the combo-box's "ROW SOURCE TYPE"
and "ROW SOURCE".

 

[Rick B]

What is the SQL statement being used in the combo-box?

Wha tis the record source for the form?

 

[Derek Brown]

Hi

Sorry trying to simplyfy question is sometimes risky.

The row sorce is actualy SQL code for the combo but still getting its data
from the same table as the form. Please also bear in mind that the combo was
produced using the access wizard and I simply select find a record based on
this form option which surely must take it's data from the form table to do
this.

Thank you for responce

Hope this helps.

 

[Derek Brown]

Hi
Combo Row Source :
SELECT SchoolsQuery.Company, SchoolsQuery.Name, SchoolsQuery.Status,
SchoolsQuery.[Account Code], SchoolsQuery.CorS FROM SchoolsQuery WHERE
(((SchoolsQuery.CorS)<>7)) ORDER BY SchoolsQuery.Company;

Form record source: SchoolsQuery

 

[Rick B]

Based on that I don't see any way that you could pull records in your combo
box that did not exist in SchoolsQuery. As a matter of fact, you should
only see records where CorS is not "7".

The form, on the other hand, should pull all records (including those where
CorS is "7".


--
Rick B

Hi
Combo Row Source :
SELECT SchoolsQuery.Company, SchoolsQuery.Name, SchoolsQuery.Status,
SchoolsQuery.[Account Code], SchoolsQuery.CorS FROM SchoolsQuery WHERE
(((SchoolsQuery.CorS)<>7)) ORDER BY SchoolsQuery.Company;

Form record source: SchoolsQuery

What is the SQL statement being used in the combo-box?

Wha tis the record source for the form?

--
Rick B



combo
was based
on to
do source
of

 

[tina]

PMFJI.
Derek, do you mean that when you open the form, and then delete a record,
that record remains in the combo box list - and causes the error if you
accidentally select it?

if yes, then try setting a macro or VBA code on the combobox's Enter event,
to Requery the combobox. that way the droplist records will always be
"current" to what's in the query's underlying table.

hth

Based on that I don't see any way that you could pull records in your combo
box that did not exist in SchoolsQuery. As a matter of fact, you should
only see records where CorS is not "7".

The form, on the other hand, should pull all records (including those where
CorS is "7".


--
Rick B

Hi
Combo Row Source :
SELECT SchoolsQuery.Company, SchoolsQuery.Name, SchoolsQuery.Status,
SchoolsQuery.[Account Code], SchoolsQuery.CorS FROM SchoolsQuery WHERE
(((SchoolsQuery.CorS)<>7)) ORDER BY SchoolsQuery.Company;

Form record source: SchoolsQuery

What is the SQL statement being used in the combo-box?

Wha tis the record source for the form?

--
Rick B



Hi

Sorry trying to simplyfy question is sometimes risky.

The row sorce is actualy SQL code for the combo but still getting its
data
from the same table as the form. Please also bear in mind that the combo
was
produced using the access wizard and I simply select find a record based
on
this form option which surely must take it's data from the form table to
do
this.

Thank you for responce

Hope this helps.

"Rick B" <Anonymous> wrote in message
Are you sure? If you delete it from the table IT CAN'T pull to the
drop-down.

I don't think the combo-box's source is what you think it is.

Go into design view and tell us what is in the combo-box's "ROW SOURCE
TYPE"
and "ROW SOURCE".

--
Rick B



Hi Rick

The form is based on a customer table. The combo gets its list from
the
same table as the form. So when I delete a record it apears in the
combo.
Any Ideas

"Rick B" <Anonymous> wrote in message
Your combo box should not dsiplay deleted items. What is the source
of
your
combo-box? If it is pulling from a table or query, then it should
only
display options that exist in that table or query.



--
Rick B



Hi all

Another problem I have with combo boxes and MSAccess wizard
produced
code
is
that when selecting (accidentaly) an item from the combo that has
been
previously deleted I get an error Run Time Error 13 Type missmatch.
How
do
you test for deleted items?

 

[Derek Brown]

Thanks Tina



The CorS (Customer or Supplier) is a little eronious; I have an archive
system that selects out from the combo any record that has been Archived.



The problem appears to be as you suspects. To delete or remove items from
the combo I have always requeried all of the comboboxes on a form this seems
to update information in most cases but only updates the combi with the fact
that a record has been deleted. I have even tried:

DoCmd.RunCommand acCmdSaveRecord
DBEngine.Idle dbRefreshCache
DoEvents

But it still does not remove deleted stuff.

I tried:

Me.Requery in the on enter Event

which does not work in fact it disables the combobox altogether however when
I use:

MyComboboxName.Requery

in the on enter Event all works perfect.



Thanks to you and Rick



Ps why does Me.requery not work?

PMFJI.
Derek, do you mean that when you open the form, and then delete a record,
that record remains in the combo box list - and causes the error if you
accidentally select it?

if yes, then try setting a macro or VBA code on the combobox's Enter
event,
to Requery the combobox. that way the droplist records will always be
"current" to what's in the query's underlying table.

hth

Based on that I don't see any way that you could pull records in your combo
box that did not exist in SchoolsQuery. As a matter of fact, you should
only see records where CorS is not "7".

The form, on the other hand, should pull all records (including those where
CorS is "7".


--
Rick B

Hi
Combo Row Source :
SELECT SchoolsQuery.Company, SchoolsQuery.Name, SchoolsQuery.Status,
SchoolsQuery.[Account Code], SchoolsQuery.CorS FROM SchoolsQuery WHERE
(((SchoolsQuery.CorS)<>7)) ORDER BY SchoolsQuery.Company;

Form record source: SchoolsQuery

"Rick B" <Anonymous> wrote in message
What is the SQL statement being used in the combo-box?

Wha tis the record source for the form?

--
Rick B



Hi

Sorry trying to simplyfy question is sometimes risky.

The row sorce is actualy SQL code for the combo but still getting
its
data
from the same table as the form. Please also bear in mind that the combo
was
produced using the access wizard and I simply select find a record based
on
this form option which surely must take it's data from the form
table to
do
this.

Thank you for responce

Hope this helps.

"Rick B" <Anonymous> wrote in message
Are you sure? If you delete it from the table IT CAN'T pull to
the
drop-down.

I don't think the combo-box's source is what you think it is.

Go into design view and tell us what is in the combo-box's "ROW SOURCE
TYPE"
and "ROW SOURCE".

--
Rick B



Hi Rick

The form is based on a customer table. The combo gets its list from
the
same table as the form. So when I delete a record it apears in
the
combo.
Any Ideas

"Rick B" <Anonymous> wrote in message
Your combo box should not dsiplay deleted items. What is the source
of
your
combo-box? If it is pulling from a table or query, then it should
only
display options that exist in that table or query.



--
Rick B



Hi all

Another problem I have with combo boxes and MSAccess wizard
produced
code
is
that when selecting (accidentaly) an item from the combo that has
been
previously deleted I get an error Run Time Error 13 Type missmatch.
How
do
you test for deleted items?

 

[tina]

Ps why does Me.requery not work?

in a form's VBA module, "Me" refers to the form object. so in the combobox's
Enter event, "Me.Requery" tells Access to requery the form's RecordSource,
*not* the combobox's RowSource. using
Me!ComboboxName.Requery
requeries the combobox's RowSource only, which is what you wanted in this
case. glad it worked for you.

Thanks Tina



The CorS (Customer or Supplier) is a little eronious; I have an archive
system that selects out from the combo any record that has been Archived.



The problem appears to be as you suspects. To delete or remove items from
the combo I have always requeried all of the comboboxes on a form this seems
to update information in most cases but only updates the combi with the fact
that a record has been deleted. I have even tried:

DoCmd.RunCommand acCmdSaveRecord
DBEngine.Idle dbRefreshCache
DoEvents

But it still does not remove deleted stuff.

I tried:

Me.Requery in the on enter Event

which does not work in fact it disables the combobox altogether however when
I use:

MyComboboxName.Requery

in the on enter Event all works perfect.



Thanks to you and Rick



Ps why does Me.requery not work?

PMFJI.
Derek, do you mean that when you open the form, and then delete a record,
that record remains in the combo box list - and causes the error if you
accidentally select it?

if yes, then try setting a macro or VBA code on the combobox's Enter
event,
to Requery the combobox. that way the droplist records will always be
"current" to what's in the query's underlying table.

hth

Based on that I don't see any way that you could pull records in your combo
box that did not exist in SchoolsQuery. As a matter of fact, you should
only see records where CorS is not "7".

The form, on the other hand, should pull all records (including those where
CorS is "7".


--
Rick B



Hi
Combo Row Source :
SELECT SchoolsQuery.Company, SchoolsQuery.Name, SchoolsQuery.Status,
SchoolsQuery.[Account Code], SchoolsQuery.CorS FROM SchoolsQuery WHERE
(((SchoolsQuery.CorS)<>7)) ORDER BY SchoolsQuery.Company;

Form record source: SchoolsQuery

"Rick B" <Anonymous> wrote in message
What is the SQL statement being used in the combo-box?

Wha tis the record source for the form?

--
Rick B



Hi

Sorry trying to simplyfy question is sometimes risky.

The row sorce is actualy SQL code for the combo but still getting
its
data
from the same table as the form. Please also bear in mind that the
combo
was
produced using the access wizard and I simply select find a record
based
on
this form option which surely must take it's data from the form
table
to
do
this.

Thank you for responce

Hope this helps.

"Rick B" <Anonymous> wrote in message
Are you sure? If you delete it from the table IT CAN'T pull to
the
drop-down.

I don't think the combo-box's source is what you think it is.

Go into design view and tell us what is in the combo-box's "ROW
SOURCE
TYPE"
and "ROW SOURCE".

--
Rick B



Hi Rick

The form is based on a customer table. The combo gets its list from
the
same table as the form. So when I delete a record it apears in
the
combo.
Any Ideas

"Rick B" <Anonymous> wrote in message
Your combo box should not dsiplay deleted items. What is the
source
of
your
combo-box? If it is pulling from a table or query, then it should
only
display options that exist in that table or query.



--
Rick B



Hi all

Another problem I have with combo boxes and MSAccess wizard
produced
code
is
that when selecting (accidentaly) an item from the combo

that
has

been
previously deleted I get an error Run Time Error 13 Type
missmatch.
How
do
you test for deleted items?