List Box

[Marshall Barton]

So I have scrapped the form completly and started afresh.

All is now working apart from one strange thing which happens. Let me
explain.....

When I us the Combo (Supplier) and select the supplier and then use the
Combo (Product) to select the product it diplays the correct products from
the selected supplier.
If I then select a diferent supplier for the next record on the datasheet
the previous records product goes blank, I then go on to select the product
forthe new record which
again offers the correct products for the chosen supplier but the previous
records product remains blank, why is this?
If I then for the next record select the same supplier as the first the
product in the first record reappears but the second record which has a
different supplier name disappears and so on.

Arrgghhh, so it was a corrupted form. Well, stuff happens
:-\

What you are seeing now is the consequence of using a
dependent combo box on a continuous or datasheet form. The
reason is simply that there is only one RowSource property
and it is used to display all the rows. Unfortunately, the
workaround is sort of messy.

First, modify the form's record source query to join the
products table to your form's table on the ProductID field
and add the ProductName field to the field list.

Then add a text box to the form's detail section and bind it
to the product name field. This text box should display the
correct product name on every row in the form.

Now, to keep user actions from using the text box, add a
line of code to the text box's GotFocus event procedure:
Me.CBOProduct.SetFocus

When you get all that working, size and position the text
box on top of the text portion of the combo box.

 

[SG]

Marshall,

So I If I add the Product Names Field my forms list and Create the text box
with it source being the product name you are correct it does display the
correct products BUT the Runtime Error Reappears exactly the same message as
before!!

If I then remove the product Name field from the tables source query the
error goes??!!

Any Ideas?

 

[Marshall Barton]

I am out of ideas on that problem. Because what you are
trying to do is pretty common, it sure looks to me like
there is a corruption problem. Recreating the form helped
some of it, but apparently did not completely resolve all of
it.

It's possible that creating a new, blank mdb, setting all
its database properties as needed (especially
NameAutoCorrect) and then importing everything except the
corrupt form will leave the corruption behind. It's not
guaranteed, but worth a try. I think decompiling after
making a backup copy of your file and before doing the
import improves the chances of a clean result.

 

[SG]

Marshall,

I have tried doing what you have suggested but without any success.
Would you know of anyone who would be able to take a look at this database
for me?

Once again thank you for all of your help!

S

I am out of ideas on that problem. Because what you are
trying to do is pretty common, it sure looks to me like
there is a corruption problem. Recreating the form helped
some of it, but apparently did not completely resolve all of
it.

It's possible that creating a new, blank mdb, setting all
its database properties as needed (especially
NameAutoCorrect) and then importing everything except the
corrupt form will leave the corruption behind. It's not
guaranteed, but worth a try. I think decompiling after
making a backup copy of your file and before doing the
import improves the chances of a clean result.
--
Marsh
MVP [MS Access]

So I If I add the Product Names Field my forms list and Create the text
box
with it source being the product name you are correct it does display the
correct products BUT the Runtime Error Reappears exactly the same message
as
before!!

If I then remove the product Name field from the tables source query the
error goes??!!


"Marshall Barton" wrote

 

[SG]

Marshall,

As one last ditch thought!

I changed the datasheet to a continue form. I have then created a text box
with a control source of productID which is the foriegn key from
tblProducts. If I then Select a supplier and then a product this displays ok
if I then select another supplier the text box with the control source of
productID show the correct product ID's for the previous entry. Can I then
change the primary key to be the product as apposed to having a productID
and then this will sho the product name instead of the ID???

The other issue is that the product description and serial number display
seem to alternate dependant on the supplier I choose, is there anyway we can
stop this occuring...??

Any suggestions help and advice would be gratefuly recieved before I pull
the remaining hair I have out!!

Kind Regards

S

I am out of ideas on that problem. Because what you are
trying to do is pretty common, it sure looks to me like
there is a corruption problem. Recreating the form helped
some of it, but apparently did not completely resolve all of
it.

It's possible that creating a new, blank mdb, setting all
its database properties as needed (especially
NameAutoCorrect) and then importing everything except the
corrupt form will leave the corruption behind. It's not
guaranteed, but worth a try. I think decompiling after
making a backup copy of your file and before doing the
import improves the chances of a clean result.
--
Marsh
MVP [MS Access]

So I If I add the Product Names Field my forms list and Create the text
box
with it source being the product name you are correct it does display the
correct products BUT the Runtime Error Reappears exactly the same message
as
before!!

If I then remove the product Name field from the tables source query the
error goes??!!


"Marshall Barton" wrote

 

[Marshall Barton]

As one last ditch thought!

I changed the datasheet to a continue form. I have then created a text box
with a control source of productID which is the foriegn key from
tblProducts. If I then Select a supplier and then a product this displays ok
if I then select another supplier the text box with the control source of
productID show the correct product ID's for the previous entry. Can I then
change the primary key to be the product as apposed to having a productID
and then this will sho the product name instead of the ID???

The other issue is that the product description and serial number display
seem to alternate dependant on the supplier I choose, is there anyway we can
stop this occuring...??

Do NOT change any primary keys! Your idea would work for
the immediate problem of having the combo box display
correctly on all rows, but you will run into an entirely
different set if issues if you ever need to change a product
name.

There seems to be more than a small communications gap here.
Somehow I thought you had been using a continuous form all
along. A lot of what I told you to do was based on that and
wouldn't make sense on a datasheet form. I guess I am
wondering what you thought I was talking about, I sure don't
understand what you were describing if you were looking at a
datasheet form???

The form's record source query was supposed to include the
product name field from the products table and the text box
should be bound to this field. If your record source does
not join to the products table and pick up the product name
field, then only rows with the same supplier as the current
record can display the selected product name.

I feel like we're starting over from somewhere near where
this exercise began.

What does the record source query look like now?
What code do you have at this point?

 

[SG]

Marshall,

I thought I mention the for setup anyway here is what I have...

I have a MainForm which has Tab Control On this I have a form called
frmreferb and within this I have a datasheet called
frmReferbDetailsSubformNew.

This is the record source of the frmReferbDetailsSubformNew Datasheet..

SELECT tblReferbDetails.ReferbDetailID, tblReferbDetails.ReferbID,
tblReferbDetails.Supplier, tblReferbDetails.ProductID,
tblReferbDetails.Quantity, tblReferbDetails.PricePerUnit
FROM tblReferbDetails;



I have the following controls on the datasheet...

Supplier Combo Box

Control Source = Supplier
Row Source = SELECT tblSuppliers.SupplierID, tblSuppliers.SupplierName
FROM tblSuppliers;
Bound Column 1
Column Count 2
Size 0;

After Update Event code...

Private Sub Supplier_AfterUpdate()
Me.ProductID = Null
Me.ProductID.Requery
End Sub

_______________________________________________________________________

PorductID Combo Box

Control Source = ProductID
Row Source = SELECT tblProducts.ProductID, tblProducts.ProductName,
tblProducts.ProductDescription, tblProducts.SerialNumber,
tblProducts.UnitPrice, tblProducts.SupplierID
FROM tblProducts
WHERE
(((tblProducts.SupplierID)=[Forms]![frmRoomMain]![frmReferb].[Form]![frmReferbDetailsSubformNew].[Form]![Supplier]));
Bound Column = 1
Coulmn Count = 6
Column Widths = 3cm;5cm;0cm;0cm;0cm;0cm;0cm

No Code
________________________________________________________________
Text Box Called CBOProductDesription
Control Source =ProductID.Column(2)

__________________________________________________________

Text Box called SerialNumber
Control Source =ProductID.Column(1)

__________________________________________________________

Text Box Called Quantity
Control Source= Quantity

_________________________________________________

Text Box Called PricePerUnit
Control Source = PricePerUnit

This is all I have on the datasheet..

Kind Regards & Fingers Crossed

 

[Marshall Barton]

I have a MainForm which has Tab Control On this I have a form called
frmreferb and within this I have a datasheet called
frmReferbDetailsSubformNew.

This is the record source of the frmReferbDetailsSubformNew Datasheet..

SELECT tblReferbDetails.ReferbDetailID, tblReferbDetails.ReferbID,
tblReferbDetails.Supplier, tblReferbDetails.ProductID,
tblReferbDetails.Quantity, tblReferbDetails.PricePerUnit
FROM tblReferbDetails;

I have the following controls on the datasheet...

Supplier Combo Box

Control Source = Supplier
Row Source = SELECT tblSuppliers.SupplierID, tblSuppliers.SupplierName
FROM tblSuppliers;
Bound Column 1
Column Count 2
Size 0;

After Update Event code...

Private Sub Supplier_AfterUpdate()
Me.ProductID = Null
Me.ProductID.Requery
End Sub
_______________________________________________________________________

PorductID Combo Box

Control Source = ProductID
Row Source = SELECT tblProducts.ProductID, tblProducts.ProductName,
tblProducts.ProductDescription, tblProducts.SerialNumber,
tblProducts.UnitPrice, tblProducts.SupplierID
FROM tblProducts
WHERE
(((tblProducts.SupplierID)=[Forms]![frmRoomMain]![frmReferb].[Form]![frmReferbDetailsSubformNew].[Form]![Supplier]));
Bound Column = 1
Coulmn Count = 6
Column Widths = 3cm;5cm;0cm;0cm;0cm;0cm;0cm

No Code
________________________________________________________________
Text Box Called CBOProductDesription
Control Source =ProductID.Column(2)
__________________________________________________________

Text Box called SerialNumber
Control Source =ProductID.Column(1)
__________________________________________________________

Text Box Called Quantity
Control Source= Quantity
_________________________________________________

Text Box Called PricePerUnit
Control Source = PricePerUnit

This is all I have on the datasheet..

And the problems are:
1) the product combo box does not display the product name
2) the product description and serial number text boxes
display the same thing on every row
3) when you navigate to another record the product drop list
still shows the products from the last selected suppier
4) If you fix 1), then the product combo box has the same
problem as 2)
Right?

You can fix 1) by changing the ProductID combo box's
Column Widths to 0cm;5cm;0cm;0cm;0cm;0cm;0cm

You can fix 3) by adding a line of code to the subform's
Current event:
Me.ProductID.Requery

I already described how to fix 4) but it has no hope of
working on a datasheet form. At one point you said you
tried using a continuous form, but I lost track of what did
and did not work in that experiment. You will have to live
with both 2) and 4) unless you use a continuous form.

If you do change it to a continuous form, then you can solve
2) by adding the description and serial fields to the form's
record source along with the product name field and binding
the text boxes to those fields. Both of these text boxes
should be locked and disabled.

 

[SG]

Marshall,

Just a quick word to say thankyou for all of your help! Finally I managed to
get this sorted out.

Kind Regards

S

I have a MainForm which has Tab Control On this I have a form called
frmreferb and within this I have a datasheet called
frmReferbDetailsSubformNew.

This is the record source of the frmReferbDetailsSubformNew Datasheet..

SELECT tblReferbDetails.ReferbDetailID, tblReferbDetails.ReferbID,
tblReferbDetails.Supplier, tblReferbDetails.ProductID,
tblReferbDetails.Quantity, tblReferbDetails.PricePerUnit
FROM tblReferbDetails;

I have the following controls on the datasheet...

Supplier Combo Box

Control Source = Supplier
Row Source = SELECT tblSuppliers.SupplierID, tblSuppliers.SupplierName
FROM tblSuppliers;
Bound Column 1
Column Count 2
Size 0;

After Update Event code...

Private Sub Supplier_AfterUpdate()
Me.ProductID = Null
Me.ProductID.Requery
End Sub
_______________________________________________________________________

PorductID Combo Box

Control Source = ProductID
Row Source = SELECT tblProducts.ProductID, tblProducts.ProductName,
tblProducts.ProductDescription, tblProducts.SerialNumber,
tblProducts.UnitPrice, tblProducts.SupplierID
FROM tblProducts
WHERE
(((tblProducts.SupplierID)=[Forms]![frmRoomMain]![frmReferb].[Form]![frmReferbDetailsSubformNew].[Form]![Supplier]));
Bound Column = 1
Coulmn Count = 6
Column Widths = 3cm;5cm;0cm;0cm;0cm;0cm;0cm

No Code
________________________________________________________________
Text Box Called CBOProductDesription
Control Source =ProductID.Column(2)
__________________________________________________________

Text Box called SerialNumber
Control Source =ProductID.Column(1)
__________________________________________________________

Text Box Called Quantity
Control Source= Quantity
_________________________________________________

Text Box Called PricePerUnit
Control Source = PricePerUnit

This is all I have on the datasheet..

And the problems are:
1) the product combo box does not display the product name
2) the product description and serial number text boxes
display the same thing on every row
3) when you navigate to another record the product drop list
still shows the products from the last selected suppier
4) If you fix 1), then the product combo box has the same
problem as 2)
Right?

You can fix 1) by changing the ProductID combo box's
Column Widths to 0cm;5cm;0cm;0cm;0cm;0cm;0cm

You can fix 3) by adding a line of code to the subform's
Current event:
Me.ProductID.Requery

I already described how to fix 4) but it has no hope of
working on a datasheet form. At one point you said you
tried using a continuous form, but I lost track of what did
and did not work in that experiment. You will have to live
with both 2) and 4) unless you use a continuous form.

If you do change it to a continuous form, then you can solve
2) by adding the description and serial fields to the form's
record source along with the product name field and binding
the text boxes to those fields. Both of these text boxes
should be locked and disabled.