Strange behavior in combo field data

[AA Arens]

Hi I do have a products table and products-parts table in my Access
2003 database and log all services into a form.

I do have at least the following two combo boxes on my form:

- Choose Product where as the Row Source (See properties):

SELECT tblProducts.Pro ductName, tblProducts.Pro ductName FROM
tblProducts ORDER BY ProductName;

- Choose Part where as the Row Source (See properties):

SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts WHERE
(((tblParts.Pro ductName)=Forms !Calls.ProductN ame)) ORDER BY
tblParts.PartNa me;

After I selected Product and part they are both recorded in the table
representing the Form information.

After I go to anothER record in my form and go back to the first, the
Part combo field is empty and I choose items who dows not represent the
items belong to the choosen product. I guess because Access still has
the Part in its memory belonging to the choosen Product before.

The Table is still ok,no loss of data.

How to solve this problem, because I should also be able to change the
part, while I keep the Product, even when I just come from another
record?
Find below also the VB code of the Two combo boxes.

Private Sub cmbProductName_ AfterUpdate()
Me.Refresh
Me.cmbPartName. SetFocus
End Sub

Private Sub cmbProductName_ Enter()
Me!cmbProductNa me.Value = cmbProductName. ItemData(0)
Me!cmbProductNa me.Dropdown
End Sub

Private Sub cmbPartName_Aft erUpdate()
Me.Description. SetFocus
End Sub

Private Sub cmbPartName_Ent er()
Me!cmbPartName. Value = cmbPartName.Ite mData(0)
Me!cmbPartName. Dropdown
End Sub
Bart

[kingston via AccessMonster.com]

Check the control source of the two combo boxes. It sounds like the first
one is bound and the second is unbound. Or are you trying to set the default
value of the controls (and they are both supposed to be unbound)? Obviously,
there is an additional Me.Refresh in the Product AfterUpdate procedure, but
you probably have a reason for that. HTH

AA Arens wrote:

>Hi I do have a products table and products-parts table in my Access
2003 database and log all services into a form.

I do have at least the following two combo boxes on my form:

- Choose Product where as the Row Source (See properties):

SELECT tblProducts.Pro ductName, tblProducts.Pro ductName FROM
tblProducts ORDER BY ProductName;

- Choose Part where as the Row Source (See properties):

SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts WHERE
(((tblParts.Pr oductName)=Form s!Calls.Product Name)) ORDER BY
tblParts.PartN ame;

After I selected Product and part they are both recorded in the table
representing the Form information.

After I go to anothER record in my form and go back to the first, the
Part combo field is empty and I choose items who dows not represent the
items belong to the choosen product. I guess because Access still has
the Part in its memory belonging to the choosen Product before.

The Table is still ok,no loss of data.

How to solve this problem, because I should also be able to change the
part, while I keep the Product, even when I just come from another
record?

Find below also the VB code of the Two combo boxes.

Private Sub cmbProductName_ AfterUpdate()
Me.Refresh
Me.cmbPartName. SetFocus
End Sub

Private Sub cmbProductName_ Enter()
Me!cmbProductNa me.Value = cmbProductName. ItemData(0)
Me!cmbProductNa me.Dropdown
End Sub

Private Sub cmbPartName_Aft erUpdate()
Me.Description. SetFocus
End Sub

Private Sub cmbPartName_Ent er()
Me!cmbPartName. Value = cmbPartName.Ite mData(0)
Me!cmbPartName. Dropdown
End Sub

Bart

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200611/1

[AA Arens]

The default Value fields are empty.

What I discover is:

1. Value are stored in the table.

2. (at first record)
If I choose a Product via the combo, the part list is refreshed so its
combo displays parts belong to the product according to another table
which stores the product-parts combinations.

3. (to second record)
But when I go to another record (where another product is set by the
combo box) and I directly choose another part in the parts combo, it
displays the part list from the product I choosed in the former record.
So these parts does not belong to the product from the present record
since this product is different.

4. (I go back to 1st and then back again to 2nd record)
If I go back to the first record, the part is still displayd, but when
I go back to the secornd record, the field is empty, but the value it
should display is stored in the table.

5. So, I would lke to have the following sutation: The combo lists
always the value of the product they belong to, even when the product
value was set long before.

PS. About bounding: How can I see a field is properly bounded. Even
when it is bounded it might still not work.

Bart


kingston via AccessMonster.c om wrote:

Check the control source of the two combo boxes. It sounds like the first
one is bound and the second is unbound. Or are you trying to set the default
value of the controls (and they are both supposed to be unbound)? Obviously,
there is an additional Me.Refresh in the Product AfterUpdate procedure, but
you probably have a reason for that. HTH

AA Arens wrote:

Hi I do have a products table and products-parts table in my Access
2003 database and log all services into a form.

I do have at least the following two combo boxes on my form:

- Choose Product where as the Row Source (See properties):

SELECT tblProducts.Pro ductName, tblProducts.Pro ductName FROM
tblProducts ORDER BY ProductName;

- Choose Part where as the Row Source (See properties):

SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts WHERE
(((tblParts.Pro ductName)=Forms !Calls.ProductN ame)) ORDER BY
tblParts.PartNa me;

After I selected Product and part they are both recorded in the table
representing the Form information.

After I go to anothER record in my form and go back to the first, the
Part combo field is empty and I choose items who dows not represent the
items belong to the choosen product. I guess because Access still has
the Part in its memory belonging to the choosen Product before.

The Table is still ok,no loss of data.

How to solve this problem, because I should also be able to change the
part, while I keep the Product, even when I just come from another
record?

Find below also the VB code of the Two combo boxes.

Private Sub cmbProductName_ AfterUpdate()
Me.Refresh
Me.cmbPartName. SetFocus
End Sub

Private Sub cmbProductName_ Enter()
Me!cmbProductNa me.Value = cmbProductName. ItemData(0)
Me!cmbProductNa me.Dropdown
End Sub

Private Sub cmbPartName_Aft erUpdate()
Me.Description. SetFocus
End Sub

Private Sub cmbPartName_Ent er()
Me!cmbPartName. Value = cmbPartName.Ite mData(0)
Me!cmbPartName. Dropdown
End Sub

Bart

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200611/1

[kingston via AccessMonster.com]

You have to run the code that builds the part list combo everytime there is a
different product. IOW, switching to a different record may or may not
change the product. You have to check for that and rebuild the second combo
box. Typically, this is done via the form's OnCurrent event. So, you can
add the code from your first combo box to this event, or you can make the
procedure for the first combo box Public and call it from the form's
OnCurrent event.

I no longer think that you have an unbound control. In fact, you verified
this by checking that the values were being stored in the table. You can
also look at the control's Control Source property to see if the field name
is present.

AA Arens wrote:

>The default Value fields are empty.

What I discover is:

1. Value are stored in the table.

2. (at first record)
If I choose a Product via the combo, the part list is refreshed so its
combo displays parts belong to the product according to another table
which stores the product-parts combinations.

3. (to second record)
But when I go to another record (where another product is set by the
combo box) and I directly choose another part in the parts combo, it
displays the part list from the product I choosed in the former record.
So these parts does not belong to the product from the present record
since this product is different.

4. (I go back to 1st and then back again to 2nd record)
If I go back to the first record, the part is still displayd, but when
I go back to the secornd record, the field is empty, but the value it
should display is stored in the table.

5. So, I would lke to have the following sutation: The combo lists
always the value of the product they belong to, even when the product
value was set long before.

PS. About bounding: How can I see a field is properly bounded. Even
when it is bounded it might still not work.

Bart

>Check the control source of the two combo boxes. It sounds like the first
one is bound and the second is unbound. Or are you trying to set the default

[quoted text clipped - 58 lines]

>Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200611/1

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200611/1

[AA Arens]

The field name is present in the Cpontrol Source.

If I move the Me!cmbProductNa me.Value = cmbProductName. ItemData(0)
command to Current in Form then it only displays the first value in the
Product table.
Instead of that, if I remove the Refresh command in
Productname-afterupdate, then I am only able to choose the last values
from the part combo I obtained via the Row Sourse "Select.... .." (see
top)

There might be a solution when I ad a commend that reads the stored
value from the table and besed on than the 2nd combo should give the
right list.
How to read the value and where to put it?

Basically, all choosed value of Parts are stored, either from the right
part list or a false part list (list not belonging to the stored
product).
The value in the field just become blank (but still stored) in the
value does not belong to the last created list (from Select row sourse)
created from a choosen product from another reord.
Bart

On Nov 17, 1:47 am, "kingston via AccessMonster.c om" <u27511@uwe>
wrote:

You have to run the code that builds the part list combo everytime there is a
different product. IOW, switching to a different record may or may not
change the product. You have to check for that and rebuild the second combo
box. Typically, this is done via the form's OnCurrent event. So, you can
add the code from your first combo box to this event, or you can make the
procedure for the first combo box Public and call it from the form's
OnCurrent event.

I no longer think that you have an unbound control. In fact, you verified
this by checking that the values were being stored in the table. You can
also look at the control's Control Source property to see if the field name
is present.

AAArenswrote:

The default Value fields are empty.

What I discover is:

1. Value are stored in the table.

2. (at first record)
If I choose a Product via the combo, the part list is refreshed so its
combo displays parts belong to the product according to another table
which stores the product-parts combinations.

3. (to second record)
But when I go to another record (where another product is set by the
combo box) and I directly choose another part in the parts combo, it
displays the part list from the product I choosed in the former record.
So these parts does not belong to the product from the present record
since this product is different.

4. (I go back to 1st and then back again to 2nd record)
If I go back to the first record, the part is still displayd, but when
I go back to the secornd record, the field is empty, but the value it
should display is stored in the table.

5. So, I would lke to have the following sutation: The combo lists
always the value of the product they belong to, even when the product
value was set long before.

PS. About bounding: How can I see a field is properly bounded. Even
when it is bounded it might still not work.

Bart

Check the control source of the two combo boxes. It sounds like the first
one is bound and the second is unbound. Or are you trying to set the default

[quoted text clipped - 58 lines]

Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...cess/2006...--

Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2006...

[kingston via AccessMonster.com]

You shouldn't refresh the Product combobox unless a new product is created.
So set its RowSource at form design and leave it alone. Using the form's
OnCurrent event, set the Part combobox's RowSource and requery the combobox:

cboRS = "SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts WHERE
(((tblParts.Pro ductName)='" & Forms!Calls.Pro ductName & "'")) ORDER BY
tblParts.PartNa me;"
Me.ComboPart.Ro wSource = cboRS
Me.ComboPart.Re query

Do you see how the row source string changes when Forms!Calls.Pro ductName is
different? You're passing it as a variable, not a single value now. This
should also be attached to the Product combobox's AfterUpdate event.
AA Arens wrote:

>The field name is present in the Cpontrol Source.

If I move the Me!cmbProductNa me.Value = cmbProductName. ItemData(0)
command to Current in Form then it only displays the first value in the
Product table.
Instead of that, if I remove the Refresh command in
Productname-afterupdate, then I am only able to choose the last values
from the part combo I obtained via the Row Sourse "Select.... .." (see
top)

There might be a solution when I ad a commend that reads the stored
value from the table and besed on than the 2nd combo should give the
right list.
How to read the value and where to put it?

Basically, all choosed value of Parts are stored, either from the right
part list or a false part list (list not belonging to the stored
product).
The value in the field just become blank (but still stored) in the
value does not belong to the last created list (from Select row sourse)
created from a choosen product from another reord.

Bart

On Nov 17, 1:47 am, "kingston via AccessMonster.c om" <u27511@uwe>
wrote:

>You have to run the code that builds the part list combo everytime there is a
different product. IOW, switching to a different record may or may not

[quoted text clipped - 48 lines]

>>http://www.accessmonster.com/Uwe/For...cess/2006...--

Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2006...

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200611/1

[AA Arens]

I could not solve it but I understand the system.
1. I added also in the "Current" this code, right?

cboRSProduct = "SELECT tblProducts.Pro ductName, tblProducts.Pro ductName
FROM tblProducts ORDER BY ProductName;"
Me.cmbProductNa me.RowSource = cboRSProduct
Me.cmbProductNa me.Requery

The Source code that now apprears in the "Properties " is the same
2. If I copy/paste the cboRS = "Select.... . (etc.) I get the error
"Expected End Of Statement". The Help sectrion could not help me. When
I change the "'" (= " ' ") into "'"" (= " " ") then I get (Where Assist
is a product name):

SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts WHERE
(((tblParts.Pro ductName)='Base Station'")) ORDER BY tblParts.PartNa me;

I understand the purpose. How ever, the combo of Parts stays empty.
3. You told me about the attachement to the Product AfterUpdate event,
what should be attached? I guess only:
cboRS = "SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts
WHERE (((tblParts.Pro ductName)='" & Forms!Calls.Pro ductName & "'""))
ORDER BY tblParts.PartNa me;"

But Parts combo stays empty.

Bart



On Nov 18, 6:44 pm, "kingston via AccessMonster.c om" <u27511@uwe>
wrote:

You shouldn't refresh the Product combobox unless a new product is created.
So set its RowSource at form design and leave it alone. Using the form's
OnCurrent event, set the Part combobox's RowSource and requery the combobox:

cboRS = "SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts WHERE
(((tblParts.Pro ductName)='" & Forms!Calls.Pro ductName & "'")) ORDER BY
tblParts.PartNa me;"
Me.ComboPart.Ro wSource = cboRS
Me.ComboPart.Re query

Do you see how the row source string changes when Forms!Calls.Pro ductName is
different? You're passing it as a variable, not a single value now. This
should also be attached to the Product combobox's AfterUpdate event.

AAArenswrote:

The field name is present in the Cpontrol Source.

If I move the Me!cmbProductNa me.Value = cmbProductName. ItemData(0)
command to Current in Form then it only displays the first value in the
Product table.
Instead of that, if I remove the Refresh command in
Productname-afterupdate, then I am only able to choose the last values
from the part combo I obtained via the Row Sourse "Select.... .." (see
top)

There might be a solution when I ad a commend that reads the stored
value from the table and besed on than the 2nd combo should give the
right list.
How to read the value and where to put it?

Basically, all choosed value of Parts are stored, either from the right
part list or a false part list (list not belonging to the stored
product).
The value in the field just become blank (but still stored) in the
value does not belong to the last created list (from Select row sourse)
created from a choosen product from another reord.

Bart

On Nov 17, 1:47 am, "kingston via AccessMonster.c om" <u27511@uwe>
wrote:

You have to run the code that builds the part list combo everytime there is a
different product. IOW, switching to a different record may or may not

[quoted text clipped - 48 lines]

>http://www.accessmonster.com/Uwe/For...ms-access/2006...
Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2006...--

Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2006...

[kingston via AccessMonster.com]

1. No, set the Product combobox during form design (Properties) and that's
it. I don't think you want ProductName twice.

2. My code will not work because I don't know your exact form name, control
name, etc. I'm giving you only an idea of what the code should look like.
In the cboRS string, you need to replace ComboProduct with the actual control
name. This forum breaks up the code: everything from cboRS to ;" should be
on one long line. I also took out the second tblParts.PartNa me that you had
in the beginning of the SELECT statement.

cboRS = "SELECT tblParts.PartNa me FROM tblParts WHERE (tblParts.
ProductName='" & Me.ComboProduct &"') ORDER BY tblParts.PartNa me;"
Me.ComboPart.Ro wSource = cboRS
Me.ComboPart.Re query

3. The code in part 2 should be attached to the form's OnCurrent event and
the Product combobox's AfterUpdate event. So whenever the record is changed,
the parts combobox will be re-evaluated, and whenever the Product combobox is
updated, the parts combobox will be re-evaluated.

4. If you want the Product combobox re-evaluated (e.g. when you add a new
product), you can add similar code to the event that updates the Product
table.

AA Arens wrote:

>I could not solve it but I understand the system.

1. I added also in the "Current" this code, right?

cboRSProduct = "SELECT tblProducts.Pro ductName, tblProducts.Pro ductName
FROM tblProducts ORDER BY ProductName;"
Me.cmbProductN ame.RowSource = cboRSProduct
Me.cmbProductN ame.Requery

The Source code that now apprears in the "Properties " is the same

2. If I copy/paste the cboRS = "Select.... . (etc.) I get the error
"Expected End Of Statement". The Help sectrion could not help me. When
I change the "'" (= " ' ") into "'"" (= " " ") then I get (Where Assist
is a product name):

SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts WHERE
(((tblParts.Pr oductName)='Bas e Station'")) ORDER BY tblParts.PartNa me;

I understand the purpose. How ever, the combo of Parts stays empty.

3. You told me about the attachement to the Product AfterUpdate event,
what should be attached? I guess only:
cboRS = "SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts
WHERE (((tblParts.Pro ductName)='" & Forms!Calls.Pro ductName & "'""))
ORDER BY tblParts.PartNa me;"

But Parts combo stays empty.

Bart

On Nov 18, 6:44 pm, "kingston via AccessMonster.c om" <u27511@uwe>
wrote:

>You shouldn't refresh the Product combobox unless a new product is created.
So set its RowSource at form design and leave it alone. Using the form's

[quoted text clipped - 43 lines]

>Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2006...--

Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2006...

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200611/1

[AA Arens]

Thanks Kingston, it works!

(I indeed changed the combo names to my situation)
I only put back the tblParts.PartNa me from your first solution,
otherwise I got empty combo for Parts (it drops down and seems to have
contents, but tekst was invisible).

Very elegant solution.

Bart
On Nov 18, 11:35 pm, "kingston via AccessMonster.c om" <u27511@uwe>
wrote:

1. No, set the Product combobox during form design (Properties) and that's
it. I don't think you want ProductName twice.

2. My code will not work because I don't know your exact form name, control
name, etc. I'm giving you only an idea of what the code should look like.
In the cboRS string, you need to replace ComboProduct with the actual control
name. This forum breaks up the code: everything from cboRS to ;" should be
on one long line. I also took out the second tblParts.PartNa me that you had
in the beginning of the SELECT statement.

cboRS = "SELECT tblParts.PartNa me FROM tblParts WHERE (tblParts.
ProductName='" & Me.ComboProduct &"') ORDER BY tblParts.PartNa me;"
Me.ComboPart.Ro wSource = cboRS
Me.ComboPart.Re query

3. The code in part 2 should be attached to the form's OnCurrent event and
the Product combobox's AfterUpdate event. So whenever the record is changed,
the parts combobox will be re-evaluated, and whenever the Product combobox is
updated, the parts combobox will be re-evaluated.

4. If you want the Product combobox re-evaluated (e.g. when you add a new
product), you can add similar code to the event that updates the Product
table.

AAArenswrote:

I could not solve it but I understand the system.

1. I added also in the "Current" this code, right?

cboRSProduct = "SELECT tblProducts.Pro ductName, tblProducts.Pro ductName
FROM tblProducts ORDER BY ProductName;"
Me.cmbProductNa me.RowSource = cboRSProduct
Me.cmbProductNa me.Requery

The Source code that now apprears in the "Properties " is the same

2. If I copy/paste the cboRS = "Select.... . (etc.) I get the error
"Expected End Of Statement". The Help sectrion could not help me. When
I change the "'" (= " ' ") into "'"" (= " " ") then I get (Where Assist
is a product name):

SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts WHERE
(((tblParts.Pro ductName)='Base Station'")) ORDER BY tblParts.PartNa me;

I understand the purpose. How ever, the combo of Parts stays empty.

3. You told me about the attachement to the Product AfterUpdate event,
what should be attached? I guess only:
cboRS = "SELECT tblParts.PartNa me, tblParts.PartNa me FROM tblParts
WHERE (((tblParts.Pro ductName)='" & Forms!Calls.Pro ductName & "'""))
ORDER BY tblParts.PartNa me;"

But Parts combo stays empty.

Bart

On Nov 18, 6:44 pm, "kingston via AccessMonster.c om" <u27511@uwe>
wrote:

You shouldn't refresh the Product combobox unless a new product is created.
So set its RowSource at form design and leave it alone. Using the form's

[quoted text clipped - 43 lines]

Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2006...
Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2006...--

Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2006...