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 |
#1
|
|||
|
|||
2 tables, 1 query, 1 form, 1 subform
I am new to Access. Using Office Access 2003.
table1 = contractors information (name, address, etc.) table2 = order info with autonumber field (part 1, part 2, order date, etc.) query1= join by contractor number [enter contractor number] form1= contractors information displayed, but cannot edit or select field (zero data entry allowed), plus autonumber field subform1=order information, plus autonumber field So far this works, but has a few issues. The table2 (orders) has 3 rows. When I am using the form it correctly displays one of the selected contractor and contractors order information. However, when I click the next button (on bottom toolbar) the order information does not change to show the different information for each order. It is a different record number, the order fields keep showing the last rows saved information for that field. Also the autonumber in the subform changes, but the autonumber in the form does not, it shows the last row saved autonumber. When I click the new record button it does clear the order fields information, and when I type any information in a field the autonumber does increment to the correct number. Is there a way to make the bottom toolbar have the new record button added to it, so the users can advance through the orders and then add? Any clues? -- Michael Horne |
#2
|
|||
|
|||
2 tables, 1 query, 1 form, 1 subform
Is [enter contractor number] the name of a field?
I don't understand your issue but do you have anything in the Link Master/Child properties of the subform control? This should be the field names that are used to join the tables. BTW: Do you really have fields for Part 1 and Part 2? How many part fields do you have? Do you understand this might not be normalized? -- Duane Hookom Microsoft Access MVP "Michael Horne" wrote: I am new to Access. Using Office Access 2003. table1 = contractors information (name, address, etc.) table2 = order info with autonumber field (part 1, part 2, order date, etc.) query1= join by contractor number [enter contractor number] form1= contractors information displayed, but cannot edit or select field (zero data entry allowed), plus autonumber field subform1=order information, plus autonumber field So far this works, but has a few issues. The table2 (orders) has 3 rows. When I am using the form it correctly displays one of the selected contractor and contractors order information. However, when I click the next button (on bottom toolbar) the order information does not change to show the different information for each order. It is a different record number, the order fields keep showing the last rows saved information for that field. Also the autonumber in the subform changes, but the autonumber in the form does not, it shows the last row saved autonumber. When I click the new record button it does clear the order fields information, and when I type any information in a field the autonumber does increment to the correct number. Is there a way to make the bottom toolbar have the new record button added to it, so the users can advance through the orders and then add? Any clues? -- Michael Horne |
#3
|
|||
|
|||
2 tables, 1 query, 1 form, 1 subform
Hi, thanks for helping me. I tried to be clear, but I have only been using
Access for a few months now. Both table1 and table2 have a field which stores the number assigned to each contractor. Table1 field is called Nbr_Contract. Table2 field is called ID_CNTRCT. Query1 joins the two tables on these contractor number fields. In the query for Nbr_Contract I have selected Total= Group By, and Show is checked, and Criteria= [enter contract #]. [enter contract #] pops up a window when the form is run, forcing the user to input a contract to return results. I am so new I have no idea where to find the Link Master/Child properties of the subform control, or what that is exactly. Can you tell me where I need to go? I have one big issue and two small issues. Big issue= When user starts the form. If contract number has multiple rows listed in the orders table, then when the user clicks the next row button in the form, the values shown on the screen do not change (They should change to show the different values based upon each order placed). Small issue1a= On form. Order# (this is an autonumber in the orders table). When user starts the form. If contract number has multiple rows listed in the orders table, then when the user clicks the next row button in the form, the value shown on the screen does NOT change (This should change to show the different order #'s based upon each order placed). Small issue 1b= On Subform. Order# (this is an autonumber in the orders table). When user starts the form. If contract number has multiple rows listed in the orders table, then when the user clicks the next row button in the form, the value shown on the screen DOES change (This shows the correct autonumber value of each order placed). I think the flow of the form is better if the order# shows up in the form not the subform. Why is the behavior so different? Small issue2= When user starts the form, the bottom contains the next record button, end button, and begin button. Why does it not contain a button to create a new record? The is one I turned on but it sits on the toolbar row on top. Haha. No I really do not have fields for Part1 and Part2. I clearly did not word that well. The orders table allows each order to have 15 items in it, so there are 15 dropdown boxes in the form. Click on any of the 15 boxes and it contains a list of parts, which only 1 item can be selected. I hope I have been more clear, and less murky. Any help is greatly appreciated!! -- Michael Horne "Duane Hookom" wrote: Is [enter contractor number] the name of a field? I don't understand your issue but do you have anything in the Link Master/Child properties of the subform control? This should be the field names that are used to join the tables. BTW: Do you really have fields for Part 1 and Part 2? How many part fields do you have? Do you understand this might not be normalized? -- Duane Hookom Microsoft Access MVP "Michael Horne" wrote: I am new to Access. Using Office Access 2003. table1 = contractors information (name, address, etc.) table2 = order info with autonumber field (part 1, part 2, order date, etc.) query1= join by contractor number [enter contractor number] form1= contractors information displayed, but cannot edit or select field (zero data entry allowed), plus autonumber field subform1=order information, plus autonumber field So far this works, but has a few issues. The table2 (orders) has 3 rows. When I am using the form it correctly displays one of the selected contractor and contractors order information. However, when I click the next button (on bottom toolbar) the order information does not change to show the different information for each order. It is a different record number, the order fields keep showing the last rows saved information for that field. Also the autonumber in the subform changes, but the autonumber in the form does not, it shows the last row saved autonumber. When I click the new record button it does clear the order fields information, and when I type any information in a field the autonumber does increment to the correct number. Is there a way to make the bottom toolbar have the new record button added to it, so the users can advance through the orders and then add? Any clues? -- Michael Horne |
#4
|
|||
|
|||
2 tables, 1 query, 1 form, 1 subform
"The orders table allows each order to have 15 items in it"... I would not
put any more work into this until after you normalized the table structure. Each Item should create its own record in a related table. Take a look at the Northwind sample database. It has a fairly good structure for managing customers, orders, order details, etc. Also, do a web search on "Data normalization" to find out what's wrong with your current table structure. -- Duane Hookom Microsoft Access MVP "Michael Horne" wrote: Hi, thanks for helping me. I tried to be clear, but I have only been using Access for a few months now. Both table1 and table2 have a field which stores the number assigned to each contractor. Table1 field is called Nbr_Contract. Table2 field is called ID_CNTRCT. Query1 joins the two tables on these contractor number fields. In the query for Nbr_Contract I have selected Total= Group By, and Show is checked, and Criteria= [enter contract #]. [enter contract #] pops up a window when the form is run, forcing the user to input a contract to return results. I am so new I have no idea where to find the Link Master/Child properties of the subform control, or what that is exactly. Can you tell me where I need to go? I have one big issue and two small issues. Big issue= When user starts the form. If contract number has multiple rows listed in the orders table, then when the user clicks the next row button in the form, the values shown on the screen do not change (They should change to show the different values based upon each order placed). Small issue1a= On form. Order# (this is an autonumber in the orders table). When user starts the form. If contract number has multiple rows listed in the orders table, then when the user clicks the next row button in the form, the value shown on the screen does NOT change (This should change to show the different order #'s based upon each order placed). Small issue 1b= On Subform. Order# (this is an autonumber in the orders table). When user starts the form. If contract number has multiple rows listed in the orders table, then when the user clicks the next row button in the form, the value shown on the screen DOES change (This shows the correct autonumber value of each order placed). I think the flow of the form is better if the order# shows up in the form not the subform. Why is the behavior so different? Small issue2= When user starts the form, the bottom contains the next record button, end button, and begin button. Why does it not contain a button to create a new record? The is one I turned on but it sits on the toolbar row on top. Haha. No I really do not have fields for Part1 and Part2. I clearly did not word that well. The orders table allows each order to have 15 items in it, so there are 15 dropdown boxes in the form. Click on any of the 15 boxes and it contains a list of parts, which only 1 item can be selected. I hope I have been more clear, and less murky. Any help is greatly appreciated!! -- Michael Horne "Duane Hookom" wrote: Is [enter contractor number] the name of a field? I don't understand your issue but do you have anything in the Link Master/Child properties of the subform control? This should be the field names that are used to join the tables. BTW: Do you really have fields for Part 1 and Part 2? How many part fields do you have? Do you understand this might not be normalized? -- Duane Hookom Microsoft Access MVP "Michael Horne" wrote: I am new to Access. Using Office Access 2003. table1 = contractors information (name, address, etc.) table2 = order info with autonumber field (part 1, part 2, order date, etc.) query1= join by contractor number [enter contractor number] form1= contractors information displayed, but cannot edit or select field (zero data entry allowed), plus autonumber field subform1=order information, plus autonumber field So far this works, but has a few issues. The table2 (orders) has 3 rows. When I am using the form it correctly displays one of the selected contractor and contractors order information. However, when I click the next button (on bottom toolbar) the order information does not change to show the different information for each order. It is a different record number, the order fields keep showing the last rows saved information for that field. Also the autonumber in the subform changes, but the autonumber in the form does not, it shows the last row saved autonumber. When I click the new record button it does clear the order fields information, and when I type any information in a field the autonumber does increment to the correct number. Is there a way to make the bottom toolbar have the new record button added to it, so the users can advance through the orders and then add? Any clues? -- Michael Horne |
#5
|
|||
|
|||
2 tables, 1 query, 1 form, 1 subform
Duane,
I still am a bit fuzzy on normalization. I would not know how to rate what normal form that I am currently at. Do you have a simplified definition that you could share with me? I have looked at Northwind sample database and I think that mine is pretty close to it as is. My orders table is about equal to Northwinds. My customer table is about equal to Northwinds. My products table is about equal to Northwinds. I do not have an equivalent table to Northwinds Order Details. So if I make a Order Details table and no longer store the results the purchase in the orders table will that fix my Big Issue? -- Michael Horne "Duane Hookom" wrote: "The orders table allows each order to have 15 items in it"... I would not put any more work into this until after you normalized the table structure. Each Item should create its own record in a related table. Take a look at the Northwind sample database. It has a fairly good structure for managing customers, orders, order details, etc. Also, do a web search on "Data normalization" to find out what's wrong with your current table structure. -- Duane Hookom Microsoft Access MVP "Michael Horne" wrote: Hi, thanks for helping me. I tried to be clear, but I have only been using Access for a few months now. Both table1 and table2 have a field which stores the number assigned to each contractor. Table1 field is called Nbr_Contract. Table2 field is called ID_CNTRCT. Query1 joins the two tables on these contractor number fields. In the query for Nbr_Contract I have selected Total= Group By, and Show is checked, and Criteria= [enter contract #]. [enter contract #] pops up a window when the form is run, forcing the user to input a contract to return results. I am so new I have no idea where to find the Link Master/Child properties of the subform control, or what that is exactly. Can you tell me where I need to go? I have one big issue and two small issues. Big issue= When user starts the form. If contract number has multiple rows listed in the orders table, then when the user clicks the next row button in the form, the values shown on the screen do not change (They should change to show the different values based upon each order placed). Small issue1a= On form. Order# (this is an autonumber in the orders table). When user starts the form. If contract number has multiple rows listed in the orders table, then when the user clicks the next row button in the form, the value shown on the screen does NOT change (This should change to show the different order #'s based upon each order placed). Small issue 1b= On Subform. Order# (this is an autonumber in the orders table). When user starts the form. If contract number has multiple rows listed in the orders table, then when the user clicks the next row button in the form, the value shown on the screen DOES change (This shows the correct autonumber value of each order placed). I think the flow of the form is better if the order# shows up in the form not the subform. Why is the behavior so different? Small issue2= When user starts the form, the bottom contains the next record button, end button, and begin button. Why does it not contain a button to create a new record? The is one I turned on but it sits on the toolbar row on top. Haha. No I really do not have fields for Part1 and Part2. I clearly did not word that well. The orders table allows each order to have 15 items in it, so there are 15 dropdown boxes in the form. Click on any of the 15 boxes and it contains a list of parts, which only 1 item can be selected. I hope I have been more clear, and less murky. Any help is greatly appreciated!! -- Michael Horne "Duane Hookom" wrote: Is [enter contractor number] the name of a field? I don't understand your issue but do you have anything in the Link Master/Child properties of the subform control? This should be the field names that are used to join the tables. BTW: Do you really have fields for Part 1 and Part 2? How many part fields do you have? Do you understand this might not be normalized? -- Duane Hookom Microsoft Access MVP "Michael Horne" wrote: I am new to Access. Using Office Access 2003. table1 = contractors information (name, address, etc.) table2 = order info with autonumber field (part 1, part 2, order date, etc.) query1= join by contractor number [enter contractor number] form1= contractors information displayed, but cannot edit or select field (zero data entry allowed), plus autonumber field subform1=order information, plus autonumber field So far this works, but has a few issues. The table2 (orders) has 3 rows. When I am using the form it correctly displays one of the selected contractor and contractors order information. However, when I click the next button (on bottom toolbar) the order information does not change to show the different information for each order. It is a different record number, the order fields keep showing the last rows saved information for that field. Also the autonumber in the subform changes, but the autonumber in the form does not, it shows the last row saved autonumber. When I click the new record button it does clear the order fields information, and when I type any information in a field the autonumber does increment to the correct number. Is there a way to make the bottom toolbar have the new record button added to it, so the users can advance through the orders and then add? Any clues? -- Michael Horne |
#6
|
|||
|
|||
2 tables, 1 query, 1 form, 1 subform
Michael Horne wrote:
Duane, I still am a bit fuzzy on normalization. I would not know how to rate what normal form that I am currently at. Do you have a simplified definition that you could share with me? I have looked at Northwind sample database and I think that mine is pretty close to it as is. My orders table is about equal to Northwinds. My customer table is about equal to Northwinds. My products table is about equal to Northwinds. I do not have an equivalent table to Northwinds Order Details. So if I make a Order Details table and no longer store the results the purchase in the orders table will that fix my Big Issue? Try this sample video lesson: http://www.lynda.com/home/Player.aspx?lpk4=13150 or this: http://www.lynda.com/home/Player.aspx?lpk4=31001 or this: http://office.microsoft.com/training...RP061494301033 (you'll need to correct the line-break). There are some very formal descriptions of Normalisation ("normal forms") but it's really something most easily grasped from examples - I hope the above will help. There are probably countless more videos on UTube! Phil, London |
#7
|
|||
|
|||
2 tables, 1 query, 1 form, 1 subform
Philip,
Thanks for the information. My work is blocking the first two links (lynda.com) so I will try those at home. -- Michael Horne "Philip Herlihy" wrote: Michael Horne wrote: Duane, I still am a bit fuzzy on normalization. I would not know how to rate what normal form that I am currently at. Do you have a simplified definition that you could share with me? I have looked at Northwind sample database and I think that mine is pretty close to it as is. My orders table is about equal to Northwinds. My customer table is about equal to Northwinds. My products table is about equal to Northwinds. I do not have an equivalent table to Northwinds Order Details. So if I make a Order Details table and no longer store the results the purchase in the orders table will that fix my Big Issue? Try this sample video lesson: http://www.lynda.com/home/Player.aspx?lpk4=13150 or this: http://www.lynda.com/home/Player.aspx?lpk4=31001 or this: http://office.microsoft.com/training...RP061494301033 (you'll need to correct the line-break). There are some very formal descriptions of Normalisation ("normal forms") but it's really something most easily grasped from examples - I hope the above will help. There are probably countless more videos on UTube! Phil, London |
Thread Tools | |
Display Modes | |
|
|