 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2007Adding multiple records at once..Is it possible?
My subform updates a table set up to track commodity increases. Each Vendor has a commodityID that helps to group vendors that sell like commodities together. Currently my main form has three cascading boxes (2 combo, one list): cboBaseComm, cboSecComm, and lstVendorID The subform is linked to the main form by VendorID, VendorName, and SecCommID and once the user selects a Vendor from the list box these fields populate the subform of the new record. Then the user can enter the estimated increase %,and date of increase, and reason. So far all of this works as I intended. But, most of the time...the increase %, date, and reason, for each vendor in the list box are the exact same (because the list box only shows vendors with the same CommodityID...ie:if steel goes up 3%, then all the vendors with the CommodityID of steel will go up 3%, on the same date, for the same reason) Currently the user has to select each vendor in the list box individually to create the new records for each of them. Is there a way to be able to tell the form that I want a new record created for all vendors with X CommID and each new record have X % change, on X date, for X reason? And, still have the option to select the vendors individually if by chance the data is different on that specific CommID. I realize I may have to do some backtracking to make this change...but I think in the end it will be worth it. Thanks Diane You can do it in 2 ways: 1. Create a recordset in code, and loop through it adding the new records using the AddNew method. 2. Use an append query. The second is fastest and can be run manually from a query, as well as from code.  SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > My subform updates a table set up to track commodity increases. Each > Vendor [quoted text clipped - 31 lines] > Thanks > Diane Thanks for the quick response. Unfortunately it is a little beyond my experience.... Would you mind giving me a few more details. While I understand what and how to use an append query on a basic level, I have never used one in a form. I think I need to use the query builder on the forms recordsouce to make it an append query but my problem is that the mainforms controls are unbound...and the subform is bound to the table I want to update. Do I need to change the controls on the mainform to bound controls of a new query...or am I way off base? > You can do it in 2 ways: > [quoted text clipped - 40 lines] > > Thanks > > Diane From your original post, you mentioned that the subform is linked to the main form by VendorID. That would not be possible with an unbound main form. You could, however, fill a bound subform with data filtered by the VendorID on a mainform. The easiest way for an inexperienced person to use an append query in code is to first build the append query that way you want. Then just open it in code: Sub cmdMyButton_Click() DoCmd.OpenQuery "YourQueryNameHere" End Sub There are much more sophisticated ways, but I think that's the easiest. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > Thanks for the quick response. Unfortunately it is a little beyond my > experience.... [quoted text clipped - 61 lines] >> > Thanks >> > Diane > you mentioned that the subform is linked to the > main form by VendorID. That would not be possible with an unbound > main form. Howso? If there's a control on the main form called VendorID then you can use it as the link. Links are not limited to fields in a parent form's recordsource -- links can be any valid expression. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ Thanks for bearing with me. I could be getting the terminology mixed up. Let me try to clarify. The recordsource on my main form is blank (I assume that meant the main form is unbound). The combo boxes row sources are my lookup tables (they are each using a different table). I linked the subform to the combo boxes manually using the using the Link Child and Master Feilds in the properties of the subform. I am going to mess with Arvins suggestion for a while and see what I can get to work. Thanks again Diane > > you mentioned that the subform is linked to the > > main form by VendorID. That would not be possible with an unbound [quoted text clipped - 3 lines] > you can use it as the link. Links are not limited to fields in a > parent form's recordsource -- links can be any valid expression. >> you mentioned that the subform is linked to the >> main form by VendorID. That would not be possible with an unbound [quoted text clipped - 3 lines] > you can use it as the link. Links are not limited to fields in a > parent form's recordsource -- links can be any valid expression. Linking in the sense of a 1 to many relationship (or even a 1 to 1 relationship) is not possible because there's nothing on the 1 side. If the combo boxes and list box are used to filter the subform's recordsource with a Where clause, that's not a link. In fact, I just tried to create the link in the Access 97 test database I use and it wouldn't do it saying that you cannot build the link. Symantics are not important though. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com >>> you mentioned that the subform is linked to the >>> main form by VendorID. That would not be possible with an [quoted text clipped - 7 lines] > relationship) is not possible because there's nothing on the 1 > side. That it's unbound does *not* mean there's nothing to link to -- all you need is a control that has a value. > If the > combo boxes and list box are used to filter the subform's > recordsource with a Where clause, that's not a link. In fact, I > just tried to create the link in the Access 97 test database I use > and it wouldn't do it saying that you cannot build the link. > Symantics are not important though. My understanding was that the combo box was used as the parent value, so that when you chose a value from the combo box, the subform would display the matching values. This is a perfectly valid approach and does not require a recordsource in the parent form, or assigning the recordsource of the child form. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ >My understanding was that the combo box was used as the parent > value, so that when you chose a value from the combo box, the > subform would display the matching values. This is a perfectly valid > approach and does not require a recordsource in the parent form, or > assigning the recordsource of the child form. David The above is exactly how I have it set up. And it works fine if I only want to add a single record at a time. I am still struggling with making it add multiple record though. I know that at some point I am going to need to use an append query, but I still don't understand exactly how. The table I am adding records has an autonumber PK that I need to be created, along with the other fields I mentioned earlier. And If I already have records in the table from a previous date under a specific vendor name and/or Commodity code, I do not want to change those; I want to create a new record all together with a new PK #. Could one of you give me a little more detail on what needs to be done to make this happen? Thanks, Diane >>My understanding was that the combo box was used as the parent >> value, so that when you chose a value from the combo box, the [quoted text clipped - 4 lines] > The above is exactly how I have it set up. And it works fine if I > only want to add a single record at a time. I have no idea why it shouldn't work for multiple records. > I am still struggling with making it add multiple record though. > I know that at some point I am going to need to use an append > query, I don't see why. With a parent link on the combo box, which would provide the PK of the parent record, you should be able to add records to the subform, and the foreign key value would be populated automatically, without any need for an append query -- you'd just have to make sure your subform allowed record additions. > but I still > don't understand exactly how. The table I am adding records has > an autonumber PK that I need to be created, along with the other > fields I mentioned earlier. When you add a record, the Autonumber field of the child table will be populated automatically. And the foreign key field (which is *not* an Autonumber, but a long integer) will be populated automatically when you add a record in the subform because you've properly linked the parent and child form on the combo box that provides the PK value of the parent record. > And If I already have records in the table from a > previous date under a specific vendor name and/or Commodity code, > I do not want to change those; I want to create a new record all > together with a new PK #. Something is missing from your description of the situation. I see no reason why you shouldn't simply be able to click the asterisk button in the child form and start entering data in the new record. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ I am not expaining this clearly. I apologize. Let me try to clarify: The combo boxes in my main form cascade. Base Comm, Sec Comm, Vendor Name (and vendorID). The user chooses a Base Comm, then a Sec Comm (from a list filtered off cboBaseComm) then a Vendor (from a list filtered off cboSecComm). It is not until a Vendor name (and ID) is chosen that the record in the subform is added. This new record added includes the three fields in the main form that were selected, automatically creates the PK (autonumber), and has blank fields for % change, effectdate, and explaination. This is okay...but most often the user has to duplicate the same information for each of the vendors listed in the main form combo box. (ie: All vendors that sell steel increase at the same time, on the same date, the same percent) What a really want to happen is this. Once the user selcts a Base and Sec comm code, they can input data into %change, effectdate, and explaination fields in the main form (i think). Then, this new data will be added, along with the BaseCode, SecCodes, to a new record for each of the Vendors listed in the Vendor combo box (filtered off the cboSecCode). For example, all at once, I want to be able to add five records that are identical with exception to the PK and VendorID and Vendor Name feilds. I know that I am close, but I am just not sure what steps I am missing. Thanks again for your help, Diane > >>My understanding was that the combo box was used as the parent > >> value, so that when you chose a value from the combo box, the [quoted text clipped - 37 lines] > no reason why you shouldn't simply be able to click the asterisk > button in the child form and start entering data in the new record. > I am not expaining this clearly. I apologize. Let me try to > clarify: [quoted text clipped - 6 lines] > cboSecComm). It is not until a Vendor name (and ID) is chosen > that the record in the subform is added. So far I see nothing that contradicts the model in my head that drove my responses. > This new record added includes the three fields in the main form > that were selected, Why would you need that? If the combos are based on each other, surely the last, the Vendor, implies the other two, and storing the values from those two is un-normalized duplication of data. > automatically creates the PK (autonumber), and has blank fields > for % change, effectdate, and explaination. You're saying that the AfterUpdate event of the Vendor combo runs code that adds a new record in the subform? OK. > This is okay...but most often the user has to duplicate the same > information for each of the vendors listed in the main form combo > box. (ie: All vendors that sell steel increase at the same time, > on the same date, the same percent) How about a multiselect listbox instead of a combo box, then? You could have the AfterUpdate of the previous combo box fire code that selects all the items in the listbox, and the user could then deselect any that don't apply. Then you'd have a command button to add the records, which would work by walking through the listbox and checking the .Selected property. Or maybe there's a Selected collection -- I forget. But that would collect the VendorIDs and you could use that as criteria for an append query based on your Vendor table, filtered on the list of VendorIDs collected by walking through the selected items in the listbox. > What a really want to happen is this. Once the user selcts a Base > and Sec comm code, they can input data into %change, effectdate, > and explaination fields in the main form (i think). Then, this > new data will be added, along with the BaseCode, SecCodes, to a > new record for each of the Vendors listed in the Vendor combo box > (filtered off the cboSecCode). Then adding to what I said above, add unbound fields for the three values you need in each record and then click the button that does the append. You'd then requery the subform to display the matching records, which would no longer be linked on VendorID, but on the first two fields you selected (and you'd have to link to the values in the appropriate tables from the vendor table in your recordsource for your subform).. > For example, all at > once, I want to be able to add five records that are identical > with exception to the PK and VendorID and Vendor Name feilds. Don't even worry about the child table's PK, since that is going to be populated when the records are appended to the table. All you need to know is the value of the VendorIDs. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > Why would you need that? If the combos are based on each other, > surely the last, the Vendor, implies the other two, and storing the > values from those two is un-normalized duplication of data. Thank you...you are correct...I don't know how I overlooked that... > How about a multiselect listbox instead of a combo box, then? You > could have the AfterUpdate of the previous combo box fire code that [quoted text clipped - 6 lines] > table, filtered on the list of VendorIDs collected by walking > through the selected items in the listbox. > Then adding to what I said above, add unbound fields for the three > values you need in each record and then click the button that does [quoted text clipped - 3 lines] > in the appropriate tables from the vendor table in your recordsource > for your subform).. > Don't even worry about the child table's PK, since that is going to > be populated when the records are appended to the table. All you > need to know is the value of the VendorIDs. Thank you... this is where I was headed but I was stuck on what to do next. This will give me a lot to try out (as I am still learning VBA). Hopefully I won't have too many more questions before I get it up and running. Thanks again DIane > this is where I was headed but I was stuck on what to do next. > This will give me a lot to try out (as I am still learning VBA). > Hopefully I won't have too many more questions before I get it up > and running. Basically, the concept is quite simple 1. you need to collect several values that all records will have. 2. you need to select the multiple vendors that these records will apply to. So, you need an unbound form where you put in the values for 1) and some method of choosing multiple vendors. I suggested a multiselect listbox, but getting the data out of that is fairly complicated (I always have to look it up myself!). Another alternative would be to use a combo box and an unbound text box. Have the combo box's AfterUpdate event append the VendorID to the unbound textbox: Me!txtVendorList = (Me!VendorList + " OR ") & Me!cmbVendors You'll end up with a list of VendorIDs like this: 1 OR 2 OR 3 OR 4 And then you can use Application.BuildCriteria to convert that to a format for use in a WHERE clause: strWhere = "WHERE " _ & Application.BuildCriteria("tblVendors.VendorID", _ dbLong, Me!txtVendorIDs) Application.BuildCriteria converts "1 OR 2 OR 3 OR 4" into: tblVendors.VendorID=1 Or tblVendors.VendorID=2 Or tblVendors.VendorID=3 Or tblVendors.VendorID=4 Then you can use that like this: INSERT INTO tblChild ( VendorID, Field1, Field2, Field3 ) SELECT tblVendors.VendorID, "Value1", "Value2", "Value3" FROM tblVendors WHERE tblVendors.VendorID=1 Or tblVendors.VendorID=2 Or tblVendors.VendorID=3 Or tblVendors.VendorID=4 The "Value1", "Value2" will be the data you put into your unbound text fields on the parent form, and Field1, Field2, etc. are the fields you want those data inserted into. "tblChild" is the table you're inserting the data into. Now, to display these records, you'll need to figure out the appropriate recordsource. But maybe the above can get you started. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.