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.ProductName, tblProducts.ProductName FROM
tblProducts ORDER BY ProductName;

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

SELECT tblParts.PartName, tblParts.PartName FROM tblParts WHERE
(((tblParts.ProductName)=Forms!Calls.ProductName)) ORDER BY
tblParts.PartName;

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!cmbProductName.Value = cmbProductName.ItemData(0)
Me!cmbProductName.Dropdown
End Sub

Private Sub cmbPartName_AfterUpdate()
Me.Description.SetFocus
End Sub

Private Sub cmbPartName_Enter()
Me!cmbPartName.Value = cmbPartName.ItemData(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.ProductName, tblProducts.ProductName FROM
tblProducts ORDER BY ProductName;

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

SELECT tblParts.PartName, tblParts.PartName FROM tblParts WHERE
(((tblParts.ProductName)=Forms!Calls.ProductName) ) ORDER BY
tblParts.PartName;

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!cmbProductName.Value = cmbProductName.ItemData(0)
Me!cmbProductName.Dropdown
End Sub

Private Sub cmbPartName_AfterUpdate()
Me.Description.SetFocus
End Sub

Private Sub cmbPartName_Enter()
Me!cmbPartName.Value = cmbPartName.ItemData(0)
Me!cmbPartName.Dropdown
End Sub

Bart

--
Message posted via AccessMonster.com
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.com 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.ProductName, tblProducts.ProductName FROM
tblProducts ORDER BY ProductName;

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

SELECT tblParts.PartName, tblParts.PartName FROM tblParts WHERE
(((tblParts.ProductName)=Forms!Calls.ProductName)) ORDER BY
tblParts.PartName;

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!cmbProductName.Value = cmbProductName.ItemData(0)
Me!cmbProductName.Dropdown
End Sub

Private Sub cmbPartName_AfterUpdate()
Me.Description.SetFocus
End Sub

Private Sub cmbPartName_Enter()
Me!cmbPartName.Value = cmbPartName.ItemData(0)
Me!cmbPartName.Dropdown
End Sub

Bart

--
Message posted via AccessMonster.com
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.com
http://www.accessmonster.com/Uwe/For...ccess/200611/1

--
Message posted via AccessMonster.com
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!cmbProductName.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.com" <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.com
http://www.accessmonster.com/Uwe/For...cess/2006...--

Message posted via AccessMonster.comhttp://www.accessmonster.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.PartName, tblParts.PartName FROM tblParts WHERE
(((tblParts.ProductName)='" & Forms!Calls.ProductName & "'")) ORDER BY
tblParts.PartName;"
Me.ComboPart.RowSource = cboRS
Me.ComboPart.Requery

Do you see how the row source string changes when Forms!Calls.ProductName 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!cmbProductName.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.com" <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.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2006...

--
Message posted via AccessMonster.com
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.ProductName, tblProducts.ProductName
FROM tblProducts ORDER BY ProductName;"
Me.cmbProductName.RowSource = cboRSProduct
Me.cmbProductName.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.PartName, tblParts.PartName FROM tblParts WHERE
(((tblParts.ProductName)='Base Station'")) ORDER BY tblParts.PartName;

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.PartName, tblParts.PartName FROM tblParts
WHERE (((tblParts.ProductName)='" & Forms!Calls.ProductName & "'""))
ORDER BY tblParts.PartName;"

But Parts combo stays empty.

Bart



On Nov 18, 6:44 pm, "kingston via AccessMonster.com" <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.PartName, tblParts.PartName FROM tblParts WHERE
(((tblParts.ProductName)='" & Forms!Calls.ProductName & "'")) ORDER BY
tblParts.PartName;"
Me.ComboPart.RowSource = cboRS
Me.ComboPart.Requery

Do you see how the row source string changes when Forms!Calls.ProductName 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!cmbProductName.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.com" <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.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2006...--

Message posted via AccessMonster.comhttp://www.accessmonster.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.PartName that you had
in the beginning of the SELECT statement.

cboRS = "SELECT tblParts.PartName FROM tblParts WHERE (tblParts.
ProductName='" & Me.ComboProduct &"') ORDER BY tblParts.PartName;"
Me.ComboPart.RowSource = cboRS
Me.ComboPart.Requery

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.ProductName, tblProducts.ProductName
FROM tblProducts ORDER BY ProductName;"
Me.cmbProductName.RowSource = cboRSProduct
Me.cmbProductName.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.PartName, tblParts.PartName FROM tblParts WHERE
(((tblParts.ProductName)='Base Station'")) ORDER BY tblParts.PartName;

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.PartName, tblParts.PartName FROM tblParts
WHERE (((tblParts.ProductName)='" & Forms!Calls.ProductName & "'""))
ORDER BY tblParts.PartName;"

But Parts combo stays empty.

Bart

On Nov 18, 6:44 pm, "kingston via AccessMonster.com" <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.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2006...--

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2006...

--
Message posted via AccessMonster.com
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.PartName 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.com" <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.PartName that you had
in the beginning of the SELECT statement.

cboRS = "SELECT tblParts.PartName FROM tblParts WHERE (tblParts.
ProductName='" & Me.ComboProduct &"') ORDER BY tblParts.PartName;"
Me.ComboPart.RowSource = cboRS
Me.ComboPart.Requery

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.ProductName, tblProducts.ProductName
FROM tblProducts ORDER BY ProductName;"
Me.cmbProductName.RowSource = cboRSProduct
Me.cmbProductName.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.PartName, tblParts.PartName FROM tblParts WHERE
(((tblParts.ProductName)='Base Station'")) ORDER BY tblParts.PartName;

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.PartName, tblParts.PartName FROM tblParts
WHERE (((tblParts.ProductName)='" & Forms!Calls.ProductName & "'""))
ORDER BY tblParts.PartName;"

But Parts combo stays empty.

Bart

On Nov 18, 6:44 pm, "kingston via AccessMonster.com" <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.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2006...
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2006...--

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2006...