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 |
#11
|
|||
|
|||
subform issue
Me too! However, now I have a new issue (of course). When one enters a new
order using the form we've been working on, the data populates a couple of tables. In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field that has it identify the city code based on the customer name in the customer table (they're related tables) but it's still not updating. Can the table be designed to enter the info on its own? Thanks again for any help. "tina" wrote: you're welcome, glad it worked for you! "Patttt" wrote in message news Hi Tina, I just wanted to let you know that your expertise was a terrific help! I fixed the problem - everything is running smoothly. You're the best! Pat "tina" wrote: well, in the subform, i'd get rid of the two-table query. bind the subform to tblOrderDetails, or to a query that includes only tblOrderDetails. make sure the subform is linked to the mainform, by setting the LinkChildFields property to OrderID (which refers to the OrderID foreign key field in tblOrderDetails) and setting the LinkMasterFields property to OrderID (which refers to the OrderID primary key field in tblOrders). if you want to see/choose products by name, in the OrderDetails subform, then add a combobox control in the subform, with the ControlSource set to the ProductID field in tblOrderDetails, and the RowSource set to a SQL statement based on tblProducts, something like SELECT ProductID, ProductName FROM tblProducts ORDER BY ProductName; set the combobox control's BoundColumn to 1 (the default), the ColumnCount to 2, and the ColumnWidths to 0";2" so the ProductID will be correctly stored in the ProductID field in tblOrderDetails, but the combobox control in the subform with *display* the product name, not the id. hth "Patttt" wrote in message ... The subform is based on a query that does contain my data when I open the query. It's based on 2 tables that have primary key/ foreign key relationships. The query contains a field called "product id" that is the primary key one of the tables. However, an early designer added some code that replaces the product id number with the name of the product (another table that is also related). I had it working once in the subform, but it only displayed the product id code - not the product name. Now, I don't have any entries in the subform at all. To answer your questions: 1. the mainform is bound to the Orders table 2. Order Id is the primary key of Orders 3. The subform is bound to a query based on Orders and Order Details tables. the tables are related on the Order ID field. 4. Order ID from Order is part of the query (not displayed in the subform). Order ID is part of the main form and displayed on the main form. Thanks so much for your help! For some reason, the query DOES display the records, but the subform doesn't AND we can't add anything new via the query or the subform. Pat "tina" wrote: you need to go back and look at the tables/relationships. what table is bound to the mainform? what is that table's primary key field? what table is bound to the subform? does that table contain a foreign key field that links back to the primary key field of the first table? if yes, is that foreign key field included in the RecordSource of the subform? is the primary key field of the first table included in the RecordSource of the mainform? hth "Patttt" wrote in message ... nope....that didn't fix it. I did notice, though, that I have an error message on the status bar that says "join key of table Order Details not in record set" when I try to add a new record. I don't have any idea where to go to fix it! "Patttt" wrote: "Allow Additions" says yes, although "Data Entry" says no. I'll try changing that one. thanks! "Brian" wrote: Check your subform's AllowAdditions property. Did it get set to False somehow? "Patttt" wrote: Is there a reason why a subform won't let you enter new data? I can enter new data in the main form section just fine. The subform does have the dropdown list that displays the correct choices, but when I click on one of them, nothing is entered in the field. Essentially, the subform only displays the existing data but won't let you enter new records. The tables ARE related, so it's not a relationship issue. any thoughts? Thanks for your help! Pat |
#12
|
|||
|
|||
subform issue
okay, well, a couple things. first of all
In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field let's stop here. if you are saying that you have a Lookup field in tblOrders - in the table itself, not a combobox control in a form - then i'd recommend you stop immediately and get rid of that Lookup in the table. back up your database (or at least back up tblOrders); then open tblOrders in Design view, go to the city code field, click the Lookup tab in Field Properties, and change the Display Control setting to TextBox. save and close the table. Lookup fields in tables are bad news; for more information, see http://www.mvps.org/access/lookupfields.htm. second, i assume that each order in tblOrders is linked to a specific customer. and if i understood you correctly, each customer is linked to a specific city code. if that's correct, what is your business reason for storing a city code in each order record, when orders and city codes are already indirectly linked via customers? hth "Patttt" wrote in message ... Me too! However, now I have a new issue (of course). When one enters a new order using the form we've been working on, the data populates a couple of tables. In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field that has it identify the city code based on the customer name in the customer table (they're related tables) but it's still not updating. Can the table be designed to enter the info on its own? Thanks again for any help. "tina" wrote: you're welcome, glad it worked for you! "Patttt" wrote in message news Hi Tina, I just wanted to let you know that your expertise was a terrific help! I fixed the problem - everything is running smoothly. You're the best! Pat "tina" wrote: well, in the subform, i'd get rid of the two-table query. bind the subform to tblOrderDetails, or to a query that includes only tblOrderDetails. make sure the subform is linked to the mainform, by setting the LinkChildFields property to OrderID (which refers to the OrderID foreign key field in tblOrderDetails) and setting the LinkMasterFields property to OrderID (which refers to the OrderID primary key field in tblOrders). if you want to see/choose products by name, in the OrderDetails subform, then add a combobox control in the subform, with the ControlSource set to the ProductID field in tblOrderDetails, and the RowSource set to a SQL statement based on tblProducts, something like SELECT ProductID, ProductName FROM tblProducts ORDER BY ProductName; set the combobox control's BoundColumn to 1 (the default), the ColumnCount to 2, and the ColumnWidths to 0";2" so the ProductID will be correctly stored in the ProductID field in tblOrderDetails, but the combobox control in the subform with *display* the product name, not the id. hth "Patttt" wrote in message ... The subform is based on a query that does contain my data when I open the query. It's based on 2 tables that have primary key/ foreign key relationships. The query contains a field called "product id" that is the primary key one of the tables. However, an early designer added some code that replaces the product id number with the name of the product (another table that is also related). I had it working once in the subform, but it only displayed the product id code - not the product name. Now, I don't have any entries in the subform at all. To answer your questions: 1. the mainform is bound to the Orders table 2. Order Id is the primary key of Orders 3. The subform is bound to a query based on Orders and Order Details tables. the tables are related on the Order ID field. 4. Order ID from Order is part of the query (not displayed in the subform). Order ID is part of the main form and displayed on the main form. Thanks so much for your help! For some reason, the query DOES display the records, but the subform doesn't AND we can't add anything new via the query or the subform. Pat "tina" wrote: you need to go back and look at the tables/relationships. what table is bound to the mainform? what is that table's primary key field? what table is bound to the subform? does that table contain a foreign key field that links back to the primary key field of the first table? if yes, is that foreign key field included in the RecordSource of the subform? is the primary key field of the first table included in the RecordSource of the mainform? hth "Patttt" wrote in message ... nope....that didn't fix it. I did notice, though, that I have an error message on the status bar that says "join key of table Order Details not in record set" when I try to add a new record. I don't have any idea where to go to fix it! "Patttt" wrote: "Allow Additions" says yes, although "Data Entry" says no. I'll try changing that one. thanks! "Brian" wrote: Check your subform's AllowAdditions property. Did it get set to False somehow? "Patttt" wrote: Is there a reason why a subform won't let you enter new data? I can enter new data in the main form section just fine. The subform does have the dropdown list that displays the correct choices, but when I click on one of them, nothing is entered in the field. Essentially, the subform only displays the existing data but won't let you enter new records. The tables ARE related, so it's not a relationship issue. any thoughts? Thanks for your help! Pat |
#13
|
|||
|
|||
subform issue
yes, I'd already learned about the lookup field the hard way. It's now a
text box. The man who owns the database is a friend of my husband who owns a brewery. He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. I've already created a query for him that tells him how much he sold by city codes using the dates he choses based on the relationship between the Orders and Customers tables. Since I did the original work for him, he's now coming up with a lot of "issues" that used to work and now don't. Every time I think I'm finished, he comes up with something new.......... It's been so long since I've worked much with Access (last certification was XP) that I can't even remember if it's possible to have a field automatically add data in a table like this. I'm more than happy if you tell me it can't be done....... Thanks again for your help! Pat "tina" wrote: okay, well, a couple things. first of all In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field let's stop here. if you are saying that you have a Lookup field in tblOrders - in the table itself, not a combobox control in a form - then i'd recommend you stop immediately and get rid of that Lookup in the table. back up your database (or at least back up tblOrders); then open tblOrders in Design view, go to the city code field, click the Lookup tab in Field Properties, and change the Display Control setting to TextBox. save and close the table. Lookup fields in tables are bad news; for more information, see http://www.mvps.org/access/lookupfields.htm. second, i assume that each order in tblOrders is linked to a specific customer. and if i understood you correctly, each customer is linked to a specific city code. if that's correct, what is your business reason for storing a city code in each order record, when orders and city codes are already indirectly linked via customers? hth "Patttt" wrote in message ... Me too! However, now I have a new issue (of course). When one enters a new order using the form we've been working on, the data populates a couple of tables. In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field that has it identify the city code based on the customer name in the customer table (they're related tables) but it's still not updating. Can the table be designed to enter the info on its own? Thanks again for any help. "tina" wrote: you're welcome, glad it worked for you! "Patttt" wrote in message news Hi Tina, I just wanted to let you know that your expertise was a terrific help! I fixed the problem - everything is running smoothly. You're the best! Pat "tina" wrote: well, in the subform, i'd get rid of the two-table query. bind the subform to tblOrderDetails, or to a query that includes only tblOrderDetails. make sure the subform is linked to the mainform, by setting the LinkChildFields property to OrderID (which refers to the OrderID foreign key field in tblOrderDetails) and setting the LinkMasterFields property to OrderID (which refers to the OrderID primary key field in tblOrders). if you want to see/choose products by name, in the OrderDetails subform, then add a combobox control in the subform, with the ControlSource set to the ProductID field in tblOrderDetails, and the RowSource set to a SQL statement based on tblProducts, something like SELECT ProductID, ProductName FROM tblProducts ORDER BY ProductName; set the combobox control's BoundColumn to 1 (the default), the ColumnCount to 2, and the ColumnWidths to 0";2" so the ProductID will be correctly stored in the ProductID field in tblOrderDetails, but the combobox control in the subform with *display* the product name, not the id. hth "Patttt" wrote in message ... The subform is based on a query that does contain my data when I open the query. It's based on 2 tables that have primary key/ foreign key relationships. The query contains a field called "product id" that is the primary key one of the tables. However, an early designer added some code that replaces the product id number with the name of the product (another table that is also related). I had it working once in the subform, but it only displayed the product id code - not the product name. Now, I don't have any entries in the subform at all. To answer your questions: 1. the mainform is bound to the Orders table 2. Order Id is the primary key of Orders 3. The subform is bound to a query based on Orders and Order Details tables. the tables are related on the Order ID field. 4. Order ID from Order is part of the query (not displayed in the subform). Order ID is part of the main form and displayed on the main form. Thanks so much for your help! For some reason, the query DOES display the records, but the subform doesn't AND we can't add anything new via the query or the subform. Pat "tina" wrote: you need to go back and look at the tables/relationships. what table is bound to the mainform? what is that table's primary key field? what table is bound to the subform? does that table contain a foreign key field that links back to the primary key field of the first table? if yes, is that foreign key field included in the RecordSource of the subform? is the primary key field of the first table included in the RecordSource of the mainform? hth "Patttt" wrote in message ... nope....that didn't fix it. I did notice, though, that I have an error message on the status bar that says "join key of table Order Details not in record set" when I try to add a new record. I don't have any idea where to go to fix it! "Patttt" wrote: "Allow Additions" says yes, although "Data Entry" says no. I'll try changing that one. thanks! "Brian" wrote: Check your subform's AllowAdditions property. Did it get set to False somehow? "Patttt" wrote: Is there a reason why a subform won't let you enter new data? I can enter new data in the main form section just fine. The subform does have the dropdown list that displays the correct choices, but when I click on one of them, nothing is entered in the field. Essentially, the subform only displays the existing data but won't let you enter new records. The tables ARE related, so it's not a relationship issue. any thoughts? Thanks for your help! Pat |
#14
|
|||
|
|||
subform issue
good job re the demise of the Lookup field. and if that's the worst thing
you ever have to learn "the hard way" in Access, then you'll have a fantastically better track record than i have! He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. hmm, okay. normalization is king, *but* in the real world there are sometimes sound business reasons for breaking those rules. (note: the fact that the client "wants a field to show up in x table", is not a sound business reason. you can't expect your client - in this case, a friend - to know beans about relational design principles, so s/he doesn't get to call the shots in structural design. no more than you would tell a builder how to build the foundation of your new house - unless you're an experienced builder yourself!) in this case, i can see a business reason for putting the code in the Orders table: a customer may move, but that should not affect reporting of *past* orders data, only open, unshipped orders and future orders. especially from the tax angle - nothing worse than being audited going back three years, and the taxes don't add up because some customers have moved in the interim - ouch! as for automatically entering the city code in each new order record, shouldn't be hard. there are a number of ways to set it up; exactly how you do it depends on how the orders data entry form is set up. when entering a new order in the form, do you use a combobox control to choose the customer? if so, you should be able to include the city code field in the combobox's RowSource. you can hide the column (set its' ColumnWidth to zero) so it doesn't show in the droplist, but the city code values will still be available to the form. you could then add code to the combobox control's AfterUpdate event, something along the lines of Me!CityCodeField = Me!ComboboxControl.Column(x) replace "x" with the index number of the column that has the city code. remember that combobox columns are zero-based, so the first column (left to right) is (0), the second column is (1), etc. if that's not how your orders data entry form is set up, you'll need to describe it in some detail before i can make other suggestions. hth "Patttt" wrote in message ... yes, I'd already learned about the lookup field the hard way. It's now a text box. The man who owns the database is a friend of my husband who owns a brewery. He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. I've already created a query for him that tells him how much he sold by city codes using the dates he choses based on the relationship between the Orders and Customers tables. Since I did the original work for him, he's now coming up with a lot of "issues" that used to work and now don't. Every time I think I'm finished, he comes up with something new.......... It's been so long since I've worked much with Access (last certification was XP) that I can't even remember if it's possible to have a field automatically add data in a table like this. I'm more than happy if you tell me it can't be done....... Thanks again for your help! Pat "tina" wrote: okay, well, a couple things. first of all In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field let's stop here. if you are saying that you have a Lookup field in tblOrders - in the table itself, not a combobox control in a form - then i'd recommend you stop immediately and get rid of that Lookup in the table. back up your database (or at least back up tblOrders); then open tblOrders in Design view, go to the city code field, click the Lookup tab in Field Properties, and change the Display Control setting to TextBox. save and close the table. Lookup fields in tables are bad news; for more information, see http://www.mvps.org/access/lookupfields.htm. second, i assume that each order in tblOrders is linked to a specific customer. and if i understood you correctly, each customer is linked to a specific city code. if that's correct, what is your business reason for storing a city code in each order record, when orders and city codes are already indirectly linked via customers? hth "Patttt" wrote in message ... Me too! However, now I have a new issue (of course). When one enters a new order using the form we've been working on, the data populates a couple of tables. In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field that has it identify the city code based on the customer name in the customer table (they're related tables) but it's still not updating. Can the table be designed to enter the info on its own? Thanks again for any help. "tina" wrote: you're welcome, glad it worked for you! "Patttt" wrote in message news Hi Tina, I just wanted to let you know that your expertise was a terrific help! I fixed the problem - everything is running smoothly. You're the best! Pat "tina" wrote: well, in the subform, i'd get rid of the two-table query. bind the subform to tblOrderDetails, or to a query that includes only tblOrderDetails. make sure the subform is linked to the mainform, by setting the LinkChildFields property to OrderID (which refers to the OrderID foreign key field in tblOrderDetails) and setting the LinkMasterFields property to OrderID (which refers to the OrderID primary key field in tblOrders). if you want to see/choose products by name, in the OrderDetails subform, then add a combobox control in the subform, with the ControlSource set to the ProductID field in tblOrderDetails, and the RowSource set to a SQL statement based on tblProducts, something like SELECT ProductID, ProductName FROM tblProducts ORDER BY ProductName; set the combobox control's BoundColumn to 1 (the default), the ColumnCount to 2, and the ColumnWidths to 0";2" so the ProductID will be correctly stored in the ProductID field in tblOrderDetails, but the combobox control in the subform with *display* the product name, not the id. hth "Patttt" wrote in message ... The subform is based on a query that does contain my data when I open the query. It's based on 2 tables that have primary key/ foreign key relationships. The query contains a field called "product id" that is the primary key one of the tables. However, an early designer added some code that replaces the product id number with the name of the product (another table that is also related). I had it working once in the subform, but it only displayed the product id code - not the product name. Now, I don't have any entries in the subform at all. To answer your questions: 1. the mainform is bound to the Orders table 2. Order Id is the primary key of Orders 3. The subform is bound to a query based on Orders and Order Details tables. the tables are related on the Order ID field. 4. Order ID from Order is part of the query (not displayed in the subform). Order ID is part of the main form and displayed on the main form. Thanks so much for your help! For some reason, the query DOES display the records, but the subform doesn't AND we can't add anything new via the query or the subform. Pat "tina" wrote: you need to go back and look at the tables/relationships. what table is bound to the mainform? what is that table's primary key field? what table is bound to the subform? does that table contain a foreign key field that links back to the primary key field of the first table? if yes, is that foreign key field included in the RecordSource of the subform? is the primary key field of the first table included in the RecordSource of the mainform? hth "Patttt" wrote in message ... nope....that didn't fix it. I did notice, though, that I have an error message on the status bar that says "join key of table Order Details not in record set" when I try to add a new record. I don't have any idea where to go to fix it! "Patttt" wrote: "Allow Additions" says yes, although "Data Entry" says no. I'll try changing that one. thanks! "Brian" wrote: Check your subform's AllowAdditions property. Did it get set to False somehow? "Patttt" wrote: Is there a reason why a subform won't let you enter new data? I can enter new data in the main form section just fine. The subform does have the dropdown list that displays the correct choices, but when I click on one of them, nothing is entered in the field. Essentially, the subform only displays the existing data but won't let you enter new records. The tables ARE related, so it's not a relationship issue. any thoughts? Thanks for your help! Pat |
#15
|
|||
|
|||
subform issue
Ha! No - it's certainly not the worst -- just the latest
I entered your line of code in the AfterUpdate Event box and did a test entry, but it didn't update the city code in the orders table. The customer combo box already has a row source that says "SELECT DISTINCTROW Customers.* FROM Customers ORDER BY Customers.CompanyName; ". should I put the city code in the row source also? I didn't do that. The owner said he started out with a template that he downloaded from the microsoft site, if that helps any (it didn't for me - but you might be more familiar with what's available). Once he started playing with the template, his troubles started. He's not opposed to deleting the city code from the orders table, but you make an excellent point about saving data in case of tax audits, so I guess I'll keep trying to get this working! Thanks for hanging in with me on this. Pat "tina" wrote: good job re the demise of the Lookup field. and if that's the worst thing you ever have to learn "the hard way" in Access, then you'll have a fantastically better track record than i have! He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. hmm, okay. normalization is king, *but* in the real world there are sometimes sound business reasons for breaking those rules. (note: the fact that the client "wants a field to show up in x table", is not a sound business reason. you can't expect your client - in this case, a friend - to know beans about relational design principles, so s/he doesn't get to call the shots in structural design. no more than you would tell a builder how to build the foundation of your new house - unless you're an experienced builder yourself!) in this case, i can see a business reason for putting the code in the Orders table: a customer may move, but that should not affect reporting of *past* orders data, only open, unshipped orders and future orders. especially from the tax angle - nothing worse than being audited going back three years, and the taxes don't add up because some customers have moved in the interim - ouch! as for automatically entering the city code in each new order record, shouldn't be hard. there are a number of ways to set it up; exactly how you do it depends on how the orders data entry form is set up. when entering a new order in the form, do you use a combobox control to choose the customer? if so, you should be able to include the city code field in the combobox's RowSource. you can hide the column (set its' ColumnWidth to zero) so it doesn't show in the droplist, but the city code values will still be available to the form. you could then add code to the combobox control's AfterUpdate event, something along the lines of Me!CityCodeField = Me!ComboboxControl.Column(x) replace "x" with the index number of the column that has the city code. remember that combobox columns are zero-based, so the first column (left to right) is (0), the second column is (1), etc. if that's not how your orders data entry form is set up, you'll need to describe it in some detail before i can make other suggestions. hth "Patttt" wrote in message ... yes, I'd already learned about the lookup field the hard way. It's now a text box. The man who owns the database is a friend of my husband who owns a brewery. He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. I've already created a query for him that tells him how much he sold by city codes using the dates he choses based on the relationship between the Orders and Customers tables. Since I did the original work for him, he's now coming up with a lot of "issues" that used to work and now don't. Every time I think I'm finished, he comes up with something new.......... It's been so long since I've worked much with Access (last certification was XP) that I can't even remember if it's possible to have a field automatically add data in a table like this. I'm more than happy if you tell me it can't be done....... Thanks again for your help! Pat "tina" wrote: okay, well, a couple things. first of all In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field let's stop here. if you are saying that you have a Lookup field in tblOrders - in the table itself, not a combobox control in a form - then i'd recommend you stop immediately and get rid of that Lookup in the table. back up your database (or at least back up tblOrders); then open tblOrders in Design view, go to the city code field, click the Lookup tab in Field Properties, and change the Display Control setting to TextBox. save and close the table. Lookup fields in tables are bad news; for more information, see http://www.mvps.org/access/lookupfields.htm. second, i assume that each order in tblOrders is linked to a specific customer. and if i understood you correctly, each customer is linked to a specific city code. if that's correct, what is your business reason for storing a city code in each order record, when orders and city codes are already indirectly linked via customers? hth "Patttt" wrote in message ... Me too! However, now I have a new issue (of course). When one enters a new order using the form we've been working on, the data populates a couple of tables. In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field that has it identify the city code based on the customer name in the customer table (they're related tables) but it's still not updating. Can the table be designed to enter the info on its own? Thanks again for any help. "tina" wrote: you're welcome, glad it worked for you! "Patttt" wrote in message news Hi Tina, I just wanted to let you know that your expertise was a terrific help! I fixed the problem - everything is running smoothly. You're the best! Pat "tina" wrote: well, in the subform, i'd get rid of the two-table query. bind the subform to tblOrderDetails, or to a query that includes only tblOrderDetails. make sure the subform is linked to the mainform, by setting the LinkChildFields property to OrderID (which refers to the OrderID foreign key field in tblOrderDetails) and setting the LinkMasterFields property to OrderID (which refers to the OrderID primary key field in tblOrders). if you want to see/choose products by name, in the OrderDetails subform, then add a combobox control in the subform, with the ControlSource set to the ProductID field in tblOrderDetails, and the RowSource set to a SQL statement based on tblProducts, something like SELECT ProductID, ProductName FROM tblProducts ORDER BY ProductName; set the combobox control's BoundColumn to 1 (the default), the ColumnCount to 2, and the ColumnWidths to 0";2" so the ProductID will be correctly stored in the ProductID field in tblOrderDetails, but the combobox control in the subform with *display* the product name, not the id. hth "Patttt" wrote in message ... The subform is based on a query that does contain my data when I open the query. It's based on 2 tables that have primary key/ foreign key relationships. The query contains a field called "product id" that is the primary key one of the tables. However, an early designer added some code that replaces the product id number with the name of the product (another table that is also related). I had it working once in the subform, but it only displayed the product id code - not the product name. Now, I don't have any entries in the subform at all. To answer your questions: 1. the mainform is bound to the Orders table 2. Order Id is the primary key of Orders 3. The subform is bound to a query based on Orders and Order Details tables. the tables are related on the Order ID field. 4. Order ID from Order is part of the query (not displayed in the subform). Order ID is part of the main form and displayed on the main form. Thanks so much for your help! For some reason, the query DOES display the records, but the subform doesn't AND we can't add anything new via the query or the subform. Pat "tina" wrote: you need to go back and look at the tables/relationships. what table is bound to the mainform? what is that table's primary key field? what table is bound to the subform? does that table contain a foreign key field that links back to the primary key field of the first table? if yes, is that foreign key field included in the RecordSource of the subform? is the primary key field of the first table included in the RecordSource of the mainform? hth "Patttt" wrote in message ... nope....that didn't fix it. I did notice, though, that I have an error message on the status bar that says "join key of table Order Details not in record set" when I try to add a new record. I don't have any idea where to go to fix it! "Patttt" wrote: "Allow Additions" says yes, although "Data Entry" says no. I'll try changing that one. thanks! "Brian" wrote: Check your subform's AllowAdditions property. Did it get set to False somehow? "Patttt" wrote: Is there a reason why a subform won't let you enter new data? I can enter new data in the main form section just fine. The subform does have the |
#16
|
|||
|
|||
subform issue
comments inline.
"Patttt" wrote in message ... Ha! No - it's certainly not the worst -- just the latest well, you're in good company, if that's any comfort! I entered your line of code in the AfterUpdate Event box and did a test entry, but it didn't update the city code in the orders table. The customer combo box already has a row source that says "SELECT DISTINCTROW Customers.* FROM Customers ORDER BY Customers.CompanyName; ". should I put the city code in the row source also? I didn't do that. well, yes. you can't set the value of the field in the Orders table, using code that refers to the combobox control, unless the value is available in the combobox's RowSource. add the city code field to the RowSource, set the ColumnWidth for that column to zero, and refer to that column's Index value in the code i posted. The owner said he started out with a template that he downloaded from the microsoft site, if that helps any (it didn't for me - but you might be more familiar with what's available). Once he started playing with the template, his troubles started. He's not opposed to deleting the city code from the orders table, but you make an excellent point about saving data in case of tax audits, so I guess I'll keep trying to get this working! Thanks for hanging in with me on this. Pat you're welcome. we'll get it figured out, don't worry. "tina" wrote: good job re the demise of the Lookup field. and if that's the worst thing you ever have to learn "the hard way" in Access, then you'll have a fantastically better track record than i have! He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. hmm, okay. normalization is king, *but* in the real world there are sometimes sound business reasons for breaking those rules. (note: the fact that the client "wants a field to show up in x table", is not a sound business reason. you can't expect your client - in this case, a friend - to know beans about relational design principles, so s/he doesn't get to call the shots in structural design. no more than you would tell a builder how to build the foundation of your new house - unless you're an experienced builder yourself!) in this case, i can see a business reason for putting the code in the Orders table: a customer may move, but that should not affect reporting of *past* orders data, only open, unshipped orders and future orders. especially from the tax angle - nothing worse than being audited going back three years, and the taxes don't add up because some customers have moved in the interim - ouch! as for automatically entering the city code in each new order record, shouldn't be hard. there are a number of ways to set it up; exactly how you do it depends on how the orders data entry form is set up. when entering a new order in the form, do you use a combobox control to choose the customer? if so, you should be able to include the city code field in the combobox's RowSource. you can hide the column (set its' ColumnWidth to zero) so it doesn't show in the droplist, but the city code values will still be available to the form. you could then add code to the combobox control's AfterUpdate event, something along the lines of Me!CityCodeField = Me!ComboboxControl.Column(x) replace "x" with the index number of the column that has the city code. remember that combobox columns are zero-based, so the first column (left to right) is (0), the second column is (1), etc. if that's not how your orders data entry form is set up, you'll need to describe it in some detail before i can make other suggestions. hth "Patttt" wrote in message ... yes, I'd already learned about the lookup field the hard way. It's now a text box. The man who owns the database is a friend of my husband who owns a brewery. He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. I've already created a query for him that tells him how much he sold by city codes using the dates he choses based on the relationship between the Orders and Customers tables. Since I did the original work for him, he's now coming up with a lot of "issues" that used to work and now don't. Every time I think I'm finished, he comes up with something new.......... It's been so long since I've worked much with Access (last certification was XP) that I can't even remember if it's possible to have a field automatically add data in a table like this. I'm more than happy if you tell me it can't be done....... Thanks again for your help! Pat "tina" wrote: okay, well, a couple things. first of all In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field let's stop here. if you are saying that you have a Lookup field in tblOrders - in the table itself, not a combobox control in a form - then i'd recommend you stop immediately and get rid of that Lookup in the table. back up your database (or at least back up tblOrders); then open tblOrders in Design view, go to the city code field, click the Lookup tab in Field Properties, and change the Display Control setting to TextBox. save and close the table. Lookup fields in tables are bad news; for more information, see http://www.mvps.org/access/lookupfields.htm. second, i assume that each order in tblOrders is linked to a specific customer. and if i understood you correctly, each customer is linked to a specific city code. if that's correct, what is your business reason for storing a city code in each order record, when orders and city codes are already indirectly linked via customers? hth "Patttt" wrote in message ... Me too! However, now I have a new issue (of course). When one enters a new order using the form we've been working on, the data populates a couple of tables. In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field that has it identify the city code based on the customer name in the customer table (they're related tables) but it's still not updating. Can the table be designed to enter the info on its own? Thanks again for any help. "tina" wrote: you're welcome, glad it worked for you! "Patttt" wrote in message news Hi Tina, I just wanted to let you know that your expertise was a terrific help! I fixed the problem - everything is running smoothly. You're the best! Pat "tina" wrote: well, in the subform, i'd get rid of the two-table query. bind the subform to tblOrderDetails, or to a query that includes only tblOrderDetails. make sure the subform is linked to the mainform, by setting the LinkChildFields property to OrderID (which refers to the OrderID foreign key field in tblOrderDetails) and setting the LinkMasterFields property to OrderID (which refers to the OrderID primary key field in tblOrders). if you want to see/choose products by name, in the OrderDetails subform, then add a combobox control in the subform, with the ControlSource set to the ProductID field in tblOrderDetails, and the RowSource set to a SQL statement based on tblProducts, something like SELECT ProductID, ProductName FROM tblProducts ORDER BY ProductName; set the combobox control's BoundColumn to 1 (the default), the ColumnCount to 2, and the ColumnWidths to 0";2" so the ProductID will be correctly stored in the ProductID field in tblOrderDetails, but the combobox control in the subform with *display* the product name, not the id. hth "Patttt" wrote in message ... The subform is based on a query that does contain my data when I open the query. It's based on 2 tables that have primary key/ foreign key relationships. The query contains a field called "product id" that is the primary key one of the tables. However, an early designer added some code that replaces the product id number with the name of the product (another table that is also related). I had it working once in the subform, but it only displayed the product id code - not the product name. Now, I don't have any entries in the subform at all. To answer your questions: 1. the mainform is bound to the Orders table 2. Order Id is the primary key of Orders 3. The subform is bound to a query based on Orders and Order Details tables. the tables are related on the Order ID field. 4. Order ID from Order is part of the query (not displayed in the subform). Order ID is part of the main form and displayed on the main form. Thanks so much for your help! For some reason, the query DOES display the records, but the subform doesn't AND we can't add anything new via the query or the subform. Pat "tina" wrote: you need to go back and look at the tables/relationships. what table is bound to the mainform? what is that table's primary key field? what table is bound to the subform? does that table contain a foreign key field that links back to the primary key field of the first table? if yes, is that foreign key field included in the RecordSource of the subform? is the primary key field of the first table included in the RecordSource of the mainform? hth "Patttt" wrote in message ... nope....that didn't fix it. I did notice, though, that I have an error message on the status bar that says "join key of table Order Details not in record set" when I try to add a new record. I don't have any idea where to go to fix it! "Patttt" wrote: "Allow Additions" says yes, although "Data Entry" says no. I'll try changing that one. thanks! "Brian" wrote: Check your subform's AllowAdditions property. Did it get set to False somehow? "Patttt" wrote: Is there a reason why a subform won't let you enter new data? I can enter new data in the main form section just fine. The subform does have the |
#17
|
|||
|
|||
subform issue
What is Me! ? Is it a function?
Anyway, here's what I've done -- First, I added ";Customers.CSitus" to the end of the row source on the Customer name field. When I switched to the form view, the customer name had disappeared. Obviously, THAT wasn't the right answer. I remved the text I added to the row source property and it restored the customer name. So, I decided to add a combo box for the CSitus code and set its width to zero. I used the combo box wizard to create the object and link it to the customer table. I entered your "Me!CSitusfield=Me!comboboxControl.column5" in the after update event property but got an error message ("Microsoft can't find the object Me!CSitus.....") when I attempted to enter a new record. I think I'm on the right path but am having problems with the syntax. The form is based on the Orders Table, and I'm using the Customer Table to locate the CSitus code (the city code). Am I getting close? thanks, Pat "tina" wrote: comments inline. "Patttt" wrote in message ... Ha! No - it's certainly not the worst -- just the latest well, you're in good company, if that's any comfort! I entered your line of code in the AfterUpdate Event box and did a test entry, but it didn't update the city code in the orders table. The customer combo box already has a row source that says "SELECT DISTINCTROW Customers.* FROM Customers ORDER BY Customers.CompanyName; ". should I put the city code in the row source also? I didn't do that. well, yes. you can't set the value of the field in the Orders table, using code that refers to the combobox control, unless the value is available in the combobox's RowSource. add the city code field to the RowSource, set the ColumnWidth for that column to zero, and refer to that column's Index value in the code i posted. The owner said he started out with a template that he downloaded from the microsoft site, if that helps any (it didn't for me - but you might be more familiar with what's available). Once he started playing with the template, his troubles started. He's not opposed to deleting the city code from the orders table, but you make an excellent point about saving data in case of tax audits, so I guess I'll keep trying to get this working! Thanks for hanging in with me on this. Pat you're welcome. we'll get it figured out, don't worry. "tina" wrote: good job re the demise of the Lookup field. and if that's the worst thing you ever have to learn "the hard way" in Access, then you'll have a fantastically better track record than i have! He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. hmm, okay. normalization is king, *but* in the real world there are sometimes sound business reasons for breaking those rules. (note: the fact that the client "wants a field to show up in x table", is not a sound business reason. you can't expect your client - in this case, a friend - to know beans about relational design principles, so s/he doesn't get to call the shots in structural design. no more than you would tell a builder how to build the foundation of your new house - unless you're an experienced builder yourself!) in this case, i can see a business reason for putting the code in the Orders table: a customer may move, but that should not affect reporting of *past* orders data, only open, unshipped orders and future orders. especially from the tax angle - nothing worse than being audited going back three years, and the taxes don't add up because some customers have moved in the interim - ouch! as for automatically entering the city code in each new order record, shouldn't be hard. there are a number of ways to set it up; exactly how you do it depends on how the orders data entry form is set up. when entering a new order in the form, do you use a combobox control to choose the customer? if so, you should be able to include the city code field in the combobox's RowSource. you can hide the column (set its' ColumnWidth to zero) so it doesn't show in the droplist, but the city code values will still be available to the form. you could then add code to the combobox control's AfterUpdate event, something along the lines of Me!CityCodeField = Me!ComboboxControl.Column(x) replace "x" with the index number of the column that has the city code. remember that combobox columns are zero-based, so the first column (left to right) is (0), the second column is (1), etc. if that's not how your orders data entry form is set up, you'll need to describe it in some detail before i can make other suggestions. hth "Patttt" wrote in message ... yes, I'd already learned about the lookup field the hard way. It's now a text box. The man who owns the database is a friend of my husband who owns a brewery. He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. I've already created a query for him that tells him how much he sold by city codes using the dates he choses based on the relationship between the Orders and Customers tables. Since I did the original work for him, he's now coming up with a lot of "issues" that used to work and now don't. Every time I think I'm finished, he comes up with something new.......... It's been so long since I've worked much with Access (last certification was XP) that I can't even remember if it's possible to have a field automatically add data in a table like this. I'm more than happy if you tell me it can't be done....... Thanks again for your help! Pat "tina" wrote: okay, well, a couple things. first of all In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field let's stop here. if you are saying that you have a Lookup field in tblOrders - in the table itself, not a combobox control in a form - then i'd recommend you stop immediately and get rid of that Lookup in the table. back up your database (or at least back up tblOrders); then open tblOrders in Design view, go to the city code field, click the Lookup tab in Field Properties, and change the Display Control setting to TextBox. save and close the table. Lookup fields in tables are bad news; for more information, see http://www.mvps.org/access/lookupfields.htm. second, i assume that each order in tblOrders is linked to a specific customer. and if i understood you correctly, each customer is linked to a specific city code. if that's correct, what is your business reason for storing a city code in each order record, when orders and city codes are already indirectly linked via customers? hth "Patttt" wrote in message ... Me too! However, now I have a new issue (of course). When one enters a new order using the form we've been working on, the data populates a couple of tables. In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field that has it identify the city code based on the customer name in the customer table (they're related tables) but it's still not updating. Can the table be designed to enter the info on its own? Thanks again for any help. "tina" wrote: you're welcome, glad it worked for you! "Patttt" wrote in message news Hi Tina, I just wanted to let you know that your expertise was a terrific help! I fixed the problem - everything is running smoothly. You're the best! Pat "tina" wrote: well, in the subform, i'd get rid of the two-table query. bind the subform to tblOrderDetails, or to a query that includes only tblOrderDetails. make sure the subform is linked to the mainform, by setting the LinkChildFields property to OrderID (which refers to the OrderID foreign key field in tblOrderDetails) and setting the LinkMasterFields property to OrderID (which refers to the OrderID primary key field in tblOrders). if you want to see/choose products by name, in the OrderDetails subform, then add a combobox control in the subform, with the ControlSource set to the ProductID field in tblOrderDetails, and the RowSource set to a SQL statement based on tblProducts, something like SELECT ProductID, ProductName FROM tblProducts ORDER BY ProductName; set the combobox control's BoundColumn to 1 (the default), the ColumnCount to 2, and the ColumnWidths to 0";2" so the ProductID will be correctly stored in the ProductID field in tblOrderDetails, but the combobox control in the subform with *display* the product name, not the id. hth "Patttt" wrote in message ... The subform is based on a query that does contain my data when I open the query. It's based on 2 tables that have primary key/ foreign key relationships. The query contains a field called "product id" that is the primary key one of the tables. However, an early designer added some code that replaces the product id number with the name of the product (another table that is also related). I had it working once in the subform, but it only displayed the product id code - not the product name. Now, I don't have any entries in the subform at all. To answer your questions: 1. the mainform is bound to the Orders table 2. Order Id is the primary key of Orders 3. The subform is bound to a query based on Orders and Order Details tables. the tables are related on the Order ID field. 4. Order ID from Order is part of the query (not displayed in the |
#18
|
|||
|
|||
subform issue
What is Me! ? Is it a function?
Me!SomeControlName is the VBA syntax to refer to a control in the form *that the VBA code is running in*. "Me" just replaces the full form reference, as Forms("NameOfForm")!SomeControlName think of it as a first-person kind of thing: when referring to yourself, or something related to you, you would say "me" or "my". if i'm referring to you, i'd say "Pat" or "Pat's". as for the city code issue - hang tough; we will get there, as i promised earlier. it really isn't hard, i just need a clearer picture of your setup, so i can give you clear instructions. so let's get back to basics a moment. post your customers table structure, like this: tblCustomers CustomerID (primary key) FirstName LastName CityCode (foreign key from tblCities) next, post your cities table, in the same format, like this: tblCities CityCode (primary key) CityName give me *real* information, though - the real table and field names, and the correct designations of the primary and foreign key fields. from there, i can tell you how to set RowSource of the customer combobox control on the data entry form, so you can use the code i posted before to get the value you need into the orders record. hth "Patttt" wrote in message ... What is Me! ? Is it a function? Anyway, here's what I've done -- First, I added ";Customers.CSitus" to the end of the row source on the Customer name field. When I switched to the form view, the customer name had disappeared. Obviously, THAT wasn't the right answer. I remved the text I added to the row source property and it restored the customer name. So, I decided to add a combo box for the CSitus code and set its width to zero. I used the combo box wizard to create the object and link it to the customer table. I entered your "Me!CSitusfield=Me!comboboxControl.column5" in the after update event property but got an error message ("Microsoft can't find the object Me!CSitus.....") when I attempted to enter a new record. I think I'm on the right path but am having problems with the syntax. The form is based on the Orders Table, and I'm using the Customer Table to locate the CSitus code (the city code). Am I getting close? thanks, Pat "tina" wrote: comments inline. "Patttt" wrote in message ... Ha! No - it's certainly not the worst -- just the latest well, you're in good company, if that's any comfort! I entered your line of code in the AfterUpdate Event box and did a test entry, but it didn't update the city code in the orders table. The customer combo box already has a row source that says "SELECT DISTINCTROW Customers.* FROM Customers ORDER BY Customers.CompanyName; ". should I put the city code in the row source also? I didn't do that. well, yes. you can't set the value of the field in the Orders table, using code that refers to the combobox control, unless the value is available in the combobox's RowSource. add the city code field to the RowSource, set the ColumnWidth for that column to zero, and refer to that column's Index value in the code i posted. The owner said he started out with a template that he downloaded from the microsoft site, if that helps any (it didn't for me - but you might be more familiar with what's available). Once he started playing with the template, his troubles started. He's not opposed to deleting the city code from the orders table, but you make an excellent point about saving data in case of tax audits, so I guess I'll keep trying to get this working! Thanks for hanging in with me on this. Pat you're welcome. we'll get it figured out, don't worry. "tina" wrote: good job re the demise of the Lookup field. and if that's the worst thing you ever have to learn "the hard way" in Access, then you'll have a fantastically better track record than i have! He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. hmm, okay. normalization is king, *but* in the real world there are sometimes sound business reasons for breaking those rules. (note: the fact that the client "wants a field to show up in x table", is not a sound business reason. you can't expect your client - in this case, a friend - to know beans about relational design principles, so s/he doesn't get to call the shots in structural design. no more than you would tell a builder how to build the foundation of your new house - unless you're an experienced builder yourself!) in this case, i can see a business reason for putting the code in the Orders table: a customer may move, but that should not affect reporting of *past* orders data, only open, unshipped orders and future orders. especially from the tax angle - nothing worse than being audited going back three years, and the taxes don't add up because some customers have moved in the interim - ouch! as for automatically entering the city code in each new order record, shouldn't be hard. there are a number of ways to set it up; exactly how you do it depends on how the orders data entry form is set up. when entering a new order in the form, do you use a combobox control to choose the customer? if so, you should be able to include the city code field in the combobox's RowSource. you can hide the column (set its' ColumnWidth to zero) so it doesn't show in the droplist, but the city code values will still be available to the form. you could then add code to the combobox control's AfterUpdate event, something along the lines of Me!CityCodeField = Me!ComboboxControl.Column(x) replace "x" with the index number of the column that has the city code. remember that combobox columns are zero-based, so the first column (left to right) is (0), the second column is (1), etc. if that's not how your orders data entry form is set up, you'll need to describe it in some detail before i can make other suggestions. hth "Patttt" wrote in message ... yes, I'd already learned about the lookup field the hard way. It's now a text box. The man who owns the database is a friend of my husband who owns a brewery. He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. I've already created a query for him that tells him how much he sold by city codes using the dates he choses based on the relationship between the Orders and Customers tables. Since I did the original work for him, he's now coming up with a lot of "issues" that used to work and now don't. Every time I think I'm finished, he comes up with something new.......... It's been so long since I've worked much with Access (last certification was XP) that I can't even remember if it's possible to have a field automatically add data in a table like this. I'm more than happy if you tell me it can't be done....... Thanks again for your help! Pat "tina" wrote: okay, well, a couple things. first of all In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field let's stop here. if you are saying that you have a Lookup field in tblOrders - in the table itself, not a combobox control in a form - then i'd recommend you stop immediately and get rid of that Lookup in the table. back up your database (or at least back up tblOrders); then open tblOrders in Design view, go to the city code field, click the Lookup tab in Field Properties, and change the Display Control setting to TextBox. save and close the table. Lookup fields in tables are bad news; for more information, see http://www.mvps.org/access/lookupfields.htm. second, i assume that each order in tblOrders is linked to a specific customer. and if i understood you correctly, each customer is linked to a specific city code. if that's correct, what is your business reason for storing a city code in each order record, when orders and city codes are already indirectly linked via customers? hth "Patttt" wrote in message ... Me too! However, now I have a new issue (of course). When one enters a new order using the form we've been working on, the data populates a couple of tables. In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field that has it identify the city code based on the customer name in the customer table (they're related tables) but it's still not updating. Can the table be designed to enter the info on its own? Thanks again for any help. "tina" wrote: you're welcome, glad it worked for you! "Patttt" wrote in message news Hi Tina, I just wanted to let you know that your expertise was a terrific help! I fixed the problem - everything is running smoothly. You're the best! Pat "tina" wrote: well, in the subform, i'd get rid of the two-table query. bind the subform to tblOrderDetails, or to a query that includes only tblOrderDetails. make sure the subform is linked to the mainform, by setting the LinkChildFields property to OrderID (which refers to the OrderID foreign key field in tblOrderDetails) and setting the LinkMasterFields property to OrderID (which refers to the OrderID primary key field in tblOrders). if you want to see/choose products by name, in the OrderDetails subform, then add a combobox control in the subform, with the ControlSource set to the ProductID field in tblOrderDetails, and the RowSource set to a SQL statement based on tblProducts, something like SELECT ProductID, ProductName FROM tblProducts ORDER BY ProductName; set the combobox control's BoundColumn to 1 (the default), the ColumnCount to 2, and the ColumnWidths to 0";2" so the ProductID will be correctly stored in the ProductID field in tblOrderDetails, but the combobox control in the subform with *display* the product name, not the id. hth "Patttt" wrote in message ... The subform is based on a query that does contain my data when I open the query. It's based on 2 tables that have primary key/ foreign key relationships. The query contains a field called "product id" that is the primary key one of the tables. However, an early designer added some code that replaces the product id number with the name of the product (another table that is also related). I had it working once in the subform, but it only displayed the product id code - not the product name. Now, I don't have any entries in the subform at all. To answer your questions: 1. the mainform is bound to the Orders table 2. Order Id is the primary key of Orders 3. The subform is bound to a query based on Orders and Order Details tables. the tables are related on the Order ID field. 4. Order ID from Order is part of the query (not displayed in the |
#19
|
|||
|
|||
subform issue
The cities code is part of the Customers table. Here's the structu
Customer ID - autonumber, primary key Company Name, text CSitus (this is the city code field), text First Name, text Last name, text Billing Address, text City, text State, text Zip, text Phone Number, text Notes, memo Here is the structure of the Orders table: OrderID, autonumber, primary key Customer ID, number Order Date, date/time Purchase Order Number, text CSitus, number Payment received, yes/no The form also uses the Order Details Table. Structu OrderDetail ID, autonumber, primary key Order ID, number Product ID, number (the table actually displays the name of the product rather than the numeric id number. This comes from the Product table. Quantity, number Unit Price, number Discout, number Here is the structure of the Products table: Product ID, autonumber, primary key Product Name, text CostofEach, currency Unit Size, number In Stock, yes/no These 4 tables are related on the redundant ID fields in each. The FORM called "Add an Order and Details" is based on the Orders table and includes all flelds in Orders table other than CSitus, which is the field he wants to populate when a new order is entered. The form also contains a subform that is now based on the Order Details table plus a calculated field that I added to provide an extended price for the order (quantity X unit price). Yes, I figured Me! is a function, but I couldn't find it in the functions lists. "tina" wrote: What is Me! ? Is it a function? Me!SomeControlName is the VBA syntax to refer to a control in the form *that the VBA code is running in*. "Me" just replaces the full form reference, as Forms("NameOfForm")!SomeControlName think of it as a first-person kind of thing: when referring to yourself, or something related to you, you would say "me" or "my". if i'm referring to you, i'd say "Pat" or "Pat's". as for the city code issue - hang tough; we will get there, as i promised earlier. it really isn't hard, i just need a clearer picture of your setup, so i can give you clear instructions. so let's get back to basics a moment. post your customers table structure, like this: tblCustomers CustomerID (primary key) FirstName LastName CityCode (foreign key from tblCities) next, post your cities table, in the same format, like this: tblCities CityCode (primary key) CityName give me *real* information, though - the real table and field names, and the correct designations of the primary and foreign key fields. from there, i can tell you how to set RowSource of the customer combobox control on the data entry form, so you can use the code i posted before to get the value you need into the orders record. hth "Patttt" wrote in message ... What is Me! ? Is it a function? Anyway, here's what I've done -- First, I added ";Customers.CSitus" to the end of the row source on the Customer name field. When I switched to the form view, the customer name had disappeared. Obviously, THAT wasn't the right answer. I remved the text I added to the row source property and it restored the customer name. So, I decided to add a combo box for the CSitus code and set its width to zero. I used the combo box wizard to create the object and link it to the customer table. I entered your "Me!CSitusfield=Me!comboboxControl.column5" in the after update event property but got an error message ("Microsoft can't find the object Me!CSitus.....") when I attempted to enter a new record. I think I'm on the right path but am having problems with the syntax. The form is based on the Orders Table, and I'm using the Customer Table to locate the CSitus code (the city code). Am I getting close? thanks, Pat "tina" wrote: comments inline. "Patttt" wrote in message ... Ha! No - it's certainly not the worst -- just the latest well, you're in good company, if that's any comfort! I entered your line of code in the AfterUpdate Event box and did a test entry, but it didn't update the city code in the orders table. The customer combo box already has a row source that says "SELECT DISTINCTROW Customers.* FROM Customers ORDER BY Customers.CompanyName; ". should I put the city code in the row source also? I didn't do that. well, yes. you can't set the value of the field in the Orders table, using code that refers to the combobox control, unless the value is available in the combobox's RowSource. add the city code field to the RowSource, set the ColumnWidth for that column to zero, and refer to that column's Index value in the code i posted. The owner said he started out with a template that he downloaded from the microsoft site, if that helps any (it didn't for me - but you might be more familiar with what's available). Once he started playing with the template, his troubles started. He's not opposed to deleting the city code from the orders table, but you make an excellent point about saving data in case of tax audits, so I guess I'll keep trying to get this working! Thanks for hanging in with me on this. Pat you're welcome. we'll get it figured out, don't worry. "tina" wrote: good job re the demise of the Lookup field. and if that's the worst thing you ever have to learn "the hard way" in Access, then you'll have a fantastically better track record than i have! He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. hmm, okay. normalization is king, *but* in the real world there are sometimes sound business reasons for breaking those rules. (note: the fact that the client "wants a field to show up in x table", is not a sound business reason. you can't expect your client - in this case, a friend - to know beans about relational design principles, so s/he doesn't get to call the shots in structural design. no more than you would tell a builder how to build the foundation of your new house - unless you're an experienced builder yourself!) in this case, i can see a business reason for putting the code in the Orders table: a customer may move, but that should not affect reporting of *past* orders data, only open, unshipped orders and future orders. especially from the tax angle - nothing worse than being audited going back three years, and the taxes don't add up because some customers have moved in the interim - ouch! as for automatically entering the city code in each new order record, shouldn't be hard. there are a number of ways to set it up; exactly how you do it depends on how the orders data entry form is set up. when entering a new order in the form, do you use a combobox control to choose the customer? if so, you should be able to include the city code field in the combobox's RowSource. you can hide the column (set its' ColumnWidth to zero) so it doesn't show in the droplist, but the city code values will still be available to the form. you could then add code to the combobox control's AfterUpdate event, something along the lines of Me!CityCodeField = Me!ComboboxControl.Column(x) replace "x" with the index number of the column that has the city code. remember that combobox columns are zero-based, so the first column (left to right) is (0), the second column is (1), etc. if that's not how your orders data entry form is set up, you'll need to describe it in some detail before i can make other suggestions. hth "Patttt" wrote in message ... yes, I'd already learned about the lookup field the hard way. It's now a text box. The man who owns the database is a friend of my husband who owns a brewery. He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. I've already created a query for him that tells him how much he sold by city codes using the dates he choses based on the relationship between the Orders and Customers tables. Since I did the original work for him, he's now coming up with a lot of "issues" that used to work and now don't. Every time I think I'm finished, he comes up with something new.......... It's been so long since I've worked much with Access (last certification was XP) that I can't even remember if it's possible to have a field automatically add data in a table like this. I'm more than happy if you tell me it can't be done....... Thanks again for your help! Pat "tina" wrote: okay, well, a couple things. first of all In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field let's stop here. if you are saying that you have a Lookup field in tblOrders - in the table itself, not a combobox control in a form - then i'd recommend you stop immediately and get rid of that Lookup in the table. back up your database (or at least back up tblOrders); then open tblOrders in Design view, go to the city code field, click the Lookup tab in Field Properties, and change the Display Control setting to TextBox. save and close the table. Lookup fields in tables are bad news; for more information, see http://www.mvps.org/access/lookupfields.htm. second, i assume that each order in tblOrders is linked to a specific customer. and if i understood you correctly, each customer is linked to a specific city code. if that's correct, what is your business reason for storing a city code in each order record, when orders and city codes are already indirectly linked via customers? hth "Patttt" wrote in message ... Me too! However, now I have a new issue (of course). When one enters a new order using the form we've been working on, the data populates a couple of tables. In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field that has it identify the city code based on the customer name in the customer table |
#20
|
|||
|
|||
subform issue
Yes, I figured Me! is a function, but I couldn't find it in the functions
lists. sorry i wasn't clear. NO, Me is *not* a function. it is an object reference in VBA syntax that refers to the "current" form (or report) that the code is running in. go back and read my previous explanation again and see if it makes more sense the 2nd time around. The form also uses the Order Details Table. Structu OrderDetail ID, autonumber, primary key Order ID, number Product ID, number (the table actually displays the name of the product rather than the numeric id number. This comes from the Product table. so ProductID is a Lookup field in tblOrderDetails. just like i said about the other one - *get rid of the Lookup field in the table*! These 4 tables are related on the redundant ID fields in each. The FORM called "Add an Order and Details" is based on the Orders table and includes all flelds in Orders table other than CSitus, which is the field he wants to populate when a new order is entered. okay, then. first, add field CSitus to the *RecordSource* of form "Add an Order and Details". if you're using a version of Access prior to A2000, it may be necessary to add a control to the form, with ControlSource of CSitus, so the system will recognize it. if you need to do that, just make sure you add the control to the Detail section of the form, and set its' Visible property to False. next, let's talk about the RowSource of the combobox control, in the form, that you use to choose a customer name for a new order record. (and forget about the subform, it's not relevant to the discussion.) i'm going to call the combobox "cboCustomer". you posted a RowSource for cboCustomer a few posts ago, as SELECT DISTINCTROW Customers.* FROM Customers ORDER BY Customers.CompanyName; first, you don't need all those fields - unless you're using the customer's address, etc, to help choose the right customer. next, why did you include DISTINCTROW in the query? please tell me you don't have the same customer listed more than once in tblCustomers! if you do, we need to stop here and have a separate discussion about table structure. if you don't, let's change the RowSource to only the fields you *need* to see in the combobox droplist when choosing a customer for an order record. for example: SELECT CustomerID, CompanyName, CSitus FROM Customers ORDER BY CompanyName; based on the above RowSource, set the following properties for cboCustomer as ColumnCount: 3 BoundColumn: 1 ColumnWidths: 0";2";0" ListWidth: 2.25" LimitToList: Yes in the combobox control's AfterUpdate event, add the following code, as Me!CSitus = Me!cboCustomer.Column(2) taking another look at the RowSource posted above, you'll see that CSitus is the third field listed. looking at the BoundColumn setting, you'll see that the value that will be stored in the control's ControlSource field is the first column - CustomerID. (the number in the BoundColumn property setting is *not* an index value, so the "zero-based" rule does not apply here. and yes, i know it's confusing.) looking at ColumnWidths property setting posted above, you'll see that the first and third column's widths are zero, so the customer id and city code will not be seen when the droplist is open - only the customer name. and finally, looking at the VBA code above, you'll see that the *index* value referred to is Column(2); remember that combobox column indexes are zero-based, first column is Column(0), second is Column(1), third is Column(2), etc. and btw, in case you're not already familiar with object-naming conventions, suggest you see http://home.att.net/~california.db/tips.html#aTip5. hth "Patttt" wrote in message news The cities code is part of the Customers table. Here's the structu Customer ID - autonumber, primary key Company Name, text CSitus (this is the city code field), text First Name, text Last name, text Billing Address, text City, text State, text Zip, text Phone Number, text Notes, memo Here is the structure of the Orders table: OrderID, autonumber, primary key Customer ID, number Order Date, date/time Purchase Order Number, text CSitus, number Payment received, yes/no The form also uses the Order Details Table. Structu OrderDetail ID, autonumber, primary key Order ID, number Product ID, number (the table actually displays the name of the product rather than the numeric id number. This comes from the Product table. Quantity, number Unit Price, number Discout, number Here is the structure of the Products table: Product ID, autonumber, primary key Product Name, text CostofEach, currency Unit Size, number In Stock, yes/no These 4 tables are related on the redundant ID fields in each. The FORM called "Add an Order and Details" is based on the Orders table and includes all flelds in Orders table other than CSitus, which is the field he wants to populate when a new order is entered. The form also contains a subform that is now based on the Order Details table plus a calculated field that I added to provide an extended price for the order (quantity X unit price). Yes, I figured Me! is a function, but I couldn't find it in the functions lists. "tina" wrote: What is Me! ? Is it a function? Me!SomeControlName is the VBA syntax to refer to a control in the form *that the VBA code is running in*. "Me" just replaces the full form reference, as Forms("NameOfForm")!SomeControlName think of it as a first-person kind of thing: when referring to yourself, or something related to you, you would say "me" or "my". if i'm referring to you, i'd say "Pat" or "Pat's". as for the city code issue - hang tough; we will get there, as i promised earlier. it really isn't hard, i just need a clearer picture of your setup, so i can give you clear instructions. so let's get back to basics a moment. post your customers table structure, like this: tblCustomers CustomerID (primary key) FirstName LastName CityCode (foreign key from tblCities) next, post your cities table, in the same format, like this: tblCities CityCode (primary key) CityName give me *real* information, though - the real table and field names, and the correct designations of the primary and foreign key fields. from there, i can tell you how to set RowSource of the customer combobox control on the data entry form, so you can use the code i posted before to get the value you need into the orders record. hth "Patttt" wrote in message ... What is Me! ? Is it a function? Anyway, here's what I've done -- First, I added ";Customers.CSitus" to the end of the row source on the Customer name field. When I switched to the form view, the customer name had disappeared. Obviously, THAT wasn't the right answer. I remved the text I added to the row source property and it restored the customer name. So, I decided to add a combo box for the CSitus code and set its width to zero. I used the combo box wizard to create the object and link it to the customer table. I entered your "Me!CSitusfield=Me!comboboxControl.column5" in the after update event property but got an error message ("Microsoft can't find the object Me!CSitus.....") when I attempted to enter a new record. I think I'm on the right path but am having problems with the syntax. The form is based on the Orders Table, and I'm using the Customer Table to locate the CSitus code (the city code). Am I getting close? thanks, Pat "tina" wrote: comments inline. "Patttt" wrote in message ... Ha! No - it's certainly not the worst -- just the latest well, you're in good company, if that's any comfort! I entered your line of code in the AfterUpdate Event box and did a test entry, but it didn't update the city code in the orders table. The customer combo box already has a row source that says "SELECT DISTINCTROW Customers.* FROM Customers ORDER BY Customers.CompanyName; ". should I put the city code in the row source also? I didn't do that. well, yes. you can't set the value of the field in the Orders table, using code that refers to the combobox control, unless the value is available in the combobox's RowSource. add the city code field to the RowSource, set the ColumnWidth for that column to zero, and refer to that column's Index value in the code i posted. The owner said he started out with a template that he downloaded from the microsoft site, if that helps any (it didn't for me - but you might be more familiar with what's available). Once he started playing with the template, his troubles started. He's not opposed to deleting the city code from the orders table, but you make an excellent point about saving data in case of tax audits, so I guess I'll keep trying to get this working! Thanks for hanging in with me on this. Pat you're welcome. we'll get it figured out, don't worry. "tina" wrote: good job re the demise of the Lookup field. and if that's the worst thing you ever have to learn "the hard way" in Access, then you'll have a fantastically better track record than i have! He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. hmm, okay. normalization is king, *but* in the real world there are sometimes sound business reasons for breaking those rules. (note: the fact that the client "wants a field to show up in x table", is not a sound business reason. you can't expect your client - in this case, a friend - to know beans about relational design principles, so s/he doesn't get to call the shots in structural design. no more than you would tell a builder how to build the foundation of your new house - unless you're an experienced builder yourself!) in this case, i can see a business reason for putting the code in the Orders table: a customer may move, but that should not affect reporting of *past* orders data, only open, unshipped orders and future orders. especially from the tax angle - nothing worse than being audited going back three years, and the taxes don't add up because some customers have moved in the interim - ouch! as for automatically entering the city code in each new order record, shouldn't be hard. there are a number of ways to set it up; exactly how you do it depends on how the orders data entry form is set up. when entering a new order in the form, do you use a combobox control to choose the customer? if so, you should be able to include the city code field in the combobox's RowSource. you can hide the column (set its' ColumnWidth to zero) so it doesn't show in the droplist, but the city code values will still be available to the form. you could then add code to the combobox control's AfterUpdate event, something along the lines of Me!CityCodeField = Me!ComboboxControl.Column(x) replace "x" with the index number of the column that has the city code. remember that combobox columns are zero-based, so the first column (left to right) is (0), the second column is (1), etc. if that's not how your orders data entry form is set up, you'll need to describe it in some detail before i can make other suggestions. hth "Patttt" wrote in message ... yes, I'd already learned about the lookup field the hard way. It's now a text box. The man who owns the database is a friend of my husband who owns a brewery. He has to pay taxes based on the locations of his customers, so he wants the location code to show up in the Orders table. I've already created a query for him that tells him how much he sold by city codes using the dates he choses based on the relationship between the Orders and Customers tables. Since I did the original work for him, he's now coming up with a lot of "issues" that used to work and now don't. Every time I think I'm finished, he comes up with something new.......... It's been so long since I've worked much with Access (last certification was XP) that I can't even remember if it's possible to have a field automatically add data in a table like this. I'm more than happy if you tell me it can't be done....... Thanks again for your help! Pat "tina" wrote: okay, well, a couple things. first of all In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field let's stop here. if you are saying that you have a Lookup field in tblOrders - in the table itself, not a combobox control in a form - then i'd recommend you stop immediately and get rid of that Lookup in the table. back up your database (or at least back up tblOrders); then open tblOrders in Design view, go to the city code field, click the Lookup tab in Field Properties, and change the Display Control setting to TextBox. save and close the table. Lookup fields in tables are bad news; for more information, see http://www.mvps.org/access/lookupfields.htm. second, i assume that each order in tblOrders is linked to a specific customer. and if i understood you correctly, each customer is linked to a specific city code. if that's correct, what is your business reason for storing a city code in each order record, when orders and city codes are already indirectly linked via customers? hth "Patttt" wrote in message ... Me too! However, now I have a new issue (of course). When one enters a new order using the form we've been working on, the data populates a couple of tables. In one of the tables (orders), there is a city code that is NOT part of the form but should automatically update when the new record is created. It's not updating that field. I made it a lookup field that has it identify the city code based on the customer name in the customer table |
|
Thread Tools | |
Display Modes | |
|
|