List 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.