If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
List Box
SG wrote:
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 procedu 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. -- Marsh MVP [MS Access] |
#22
|
|||
|
|||
List Box
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" wrote in message ... SG wrote: 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 procedu 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. -- Marsh MVP [MS Access] |
#23
|
|||
|
|||
List Box
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] SG wrote: 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 wrote: 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 procedu 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. |
#24
|
|||
|
|||
List Box
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 "Marshall Barton" wrote in message ... 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] SG wrote: 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 wrote: 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 procedu 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. |
#25
|
|||
|
|||
List Box
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 "Marshall Barton" wrote in message ... 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] SG wrote: 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 wrote: 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 procedu 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. |
#26
|
|||
|
|||
List Box
SG wrote:
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? -- Marsh MVP [MS Access] |
#27
|
|||
|
|||
List Box
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" wrote in message ... SG wrote: 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? -- Marsh MVP [MS Access] |
#28
|
|||
|
|||
List Box
SG wrote:
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 a 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. -- Marsh MVP [MS Access] |
#29
|
|||
|
|||
List Box
Marshall,
Just a quick word to say thankyou for all of your help! Finally I managed to get this sorted out. Kind Regards S "Marshall Barton" wrote in message ... SG wrote: 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 a 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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|