 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / February 2008Code to Loop a Paste Append x times
Hello - I have 2 linked tables - Table "A" is a product , and table "B" generates a serial number (AutoNumber field) for each individual existence of that product. For example, I have receive 20 new items of MyProduct. I would like to be able to generate the next 20 serial numbers for MyProduct by entering a starter record in table "B" then running some code that creates "x" additional records. The user would enter the "X" value in a field that the code should refer to for the count of the number of new entries in table "B". Basically all I need some "loop" code to be able to tell it how many times to paste append the starter record. Thanks sandy Sandy You've described a "how" -- how you want to accomplish something. Doing the way you've decided will result in "X" new records. Now how 'bout the "why" -- as in "why do you feel you need X new records in tblB? What will having those records allow you to do? Regards Jeff Boyce Microsoft Office/Access MVP > Hello - > [quoted text clipped - 15 lines] > Thanks > sandy Hello Jeff - The Products in my table are populated circuit boards that my company builds to test our RF Chip. Each board has a particular layout (PCB) and set of components on it. We build 20 or 30 of each type of board and assign serial numbers to those boards. Each board undergoes a series of tests and we report on the test results which are tracked by serial number. The boards are sometimes changed a bit and the best results go forward. So when I build 20 boards, I want to generate 20 new serial numbers for them in one step and start the tracking process. I have been working directly in the tables but I need to get the Engineers to manage this process themselves. Fool proof is the word of the day! Hope that helps! s- > Sandy > [quoted text clipped - 28 lines] > > Thanks > > sandy Thanks for the clarification, Sandy. It looks like John has provided a solution that works for you. Regards Jeff Boyce Microsoft Office/Access MVP > Hello Jeff - > [quoted text clipped - 59 lines] >> > Thanks >> > sandy >For example, I have receive 20 new items of MyProduct. I would like to be >able to generate the next 20 serial numbers for MyProduct by entering a >starter record in table "B" then running some code that creates "x" >additional records. The user would enter the "X" value in a field that the >code should refer to for the count of the number of new entries in table "B". You can do this conveniently using a handy little auxiliary table. Create a table named Num with one Long Integer field N - fill it (manually, or using Fill... Series in Excel and copy and paste, or in code) with values from 0 through the most items you'll ever need. Be generous, it's a tiny table even with 10000 records. Fill your items table using an Append query based on Num; use a criterion on N of < [Forms]![yourform]![txtX] where txtX is the name of the unbound textbox in which the user specifies the number of records desired. You can specify the productID and other fields either from the form or from a join to the products table. John W. Vinson [MVP] Hello John - This is perfect and simple. Thank you very much regards sandy > >For example, I have receive 20 new items of MyProduct. I would like to be > >able to generate the next 20 serial numbers for MyProduct by entering a [quoted text clipped - 18 lines] > > John W. Vinson [MVP] Hi John - I have set up the form for the data that needs to be used for the appended records. I have created the t_Num table with numbers to 500 My query is as follows: INSERT INTO BoardTraveller ( BoardID, FabDate ) SELECT BoardTraveller.BoardID, BoardTraveller.FabDate FROM BoardTraveller, t_Num WHERE (((BoardTraveller.BoardID)=[forms]![f_LogABuild].[BoardID_DD]) AND ((BoardTraveller.FabDate)=[forms]![f_LogABuild].[FabDate_DD]) AND ((t_Num.[Num])=[forms]![f_LogABuild].[BuildNum_DD])); The part I don't understand is how to get this query to append a specific number of records based on the value in [forms]![f_LogABuild].[BuildNum_DD] Currently the query results in "You are about to append 0 rows..." Of course the t_Num is not doing any good at this point, I have just added it to the query... Thanks for your help! sandy > >For example, I have receive 20 new items of MyProduct. I would like to be > >able to generate the next 20 serial numbers for MyProduct by entering a [quoted text clipped - 18 lines] > > John W. Vinson [MVP] >The part I don't understand is how to get this query to append a specific >number of records based on the value in [forms]![f_LogABuild].[BuildNum_DD] [quoted text clipped - 3 lines] >Of course the t_Num is not doing any good at this point, I have just added >it to the query... Try one tiny change: rather than an = use a <= (less than or equals) operator: INSERT INTO BoardTraveller ( BoardID, FabDate ) SELECT BoardTraveller.BoardID, BoardTraveller.FabDate FROM BoardTraveller, t_Num WHERE (((BoardTraveller.BoardID)=[forms]![f_LogABuild]![BoardID_DD]) AND ((BoardTraveller.FabDate)=[forms]![f_LogABuild]![FabDate_DD]) AND ((t_Num.[Num])<=[forms]![f_LogABuild]![BuildNum_DD])); Assuming that the textbox BuildNum_DD contains (say) 25, you should get 25 rows in the resulting query, with t_Num values from 1 to 25. John W. Vinson [MVP] Hmm - still no records - How is t_num associated with the BoardTraveller table? should it be linked to the Autonumber field or some such? My Query does not have the AutoNumber [BrdSN] field in it as the data is automatically generated and not appended. I am still missing something... thanks! s > >The part I don't understand is how to get this query to append a specific > >number of records based on the value in [forms]![f_LogABuild].[BuildNum_DD] [quoted text clipped - 17 lines] > > John W. Vinson [MVP] >Hmm - still no records - > >How is t_num associated with the BoardTraveller table? should it be linked >to the Autonumber field or some such? It's not. It's a Cartesian Join - joining each selected record in the Source table with *EVERY SINGLE RECORD* in t_num. >My Query does not have the AutoNumber [BrdSN] field in it as the data is >automatically generated and not appended. The problem is that this is set up to pull a single existing record *FROM* BoardTraveller - SELECT BoardTraveller.BoardID etc. - and generate BuildNum_DD copies of that record. If BoardID is an Autonumber field you will get *nothing* inserted, since it would try to create that many copies, all with the exact same Board_ID - and since it's a primary key, you can only get one. If you want *new* BoardID values, and you're not taking anything from any existing record in BoardTraveller, just keep BoardTraveller as the target table (the INSERT INTO) and only select from t_num: INSERT INTO BoardTraveller (FabDate) SELECT [forms]![f_LogABuild]![FabDate_DD] FROM t_Num WHERE ((t_Num.[Num])<=[forms]![f_LogABuild]![BuildNum_DD])); This will create BuildNum_DD copies of a record, autonumbering the BoardID and storing the same FabDate in each record. The sequential number N will be lost (and I don't see where you would be putting it anyway). John W. Vinson [MVP] Thanks - this worked perfectly! > >Hmm - still no records - > > [quoted text clipped - 27 lines] > > John W. Vinson [MVP] John, I have the same problem, but considerably less Access skill. Could you "dumb this down" a bit for me? I have a table Num with one field (long int) with rows 1 to 20. I have a table Products with 5 fields of various definitions (an autonum primary key) I have a form MakeProducts with no data source and 6 unbound objects - 5 for the Products table fields and one for the Num field. I have a button on this form "Make New Products" and I have a feeling I should be putting the code into the Make_New_Products_Click() routine. After this I get confused... Is there a way to do this with an appen query in the access GUI, or do I have to modify the VB code? Thanks, Emily > >For example, I have receive 20 new items of MyProduct. I would like to be > >able to generate the next 20 serial numbers for MyProduct by entering a [quoted text clipped - 18 lines] > > John W. Vinson [MVP] >John, > I have the same problem, but considerably less Access skill. Could you [quoted text clipped - 11 lines] >Is there a way to do this with an appen query in the access GUI, or do I >have to modify the VB code? You won't need to use the controls on this form at all. For that matter I don't see what function you're visualizing this form as playing! Could you explain what you want this button to do? Are you trying to "clone" one product into 1 or 5 or 20 identical products, or change the value of one or more fields, or what?  SignatureJohn W. Vinson [MVP] John, I am trying to "clone" one product into 1 or 5 or 20 identical products... In a nutshell, I need a way for untrained access users to enter information about a product lot(once) and state how many of this product is being created. I then need to populate a table with all of the fields about this product this many times. When the product is created, all instances are exactly the same. Later, each product undergoes individual changes that will affect all of the fields under consideration. So on day one I will have N duplicate rows, but on day 35 I will have N unique rows... (I'm pretty sure I normalized the design correctly, I know it seems an odd thing to do in a RDB) Any help would be great - thanks! > >John, > > I have the same problem, but considerably less Access skill. Could you [quoted text clipped - 18 lines] > one product into 1 or 5 or 20 identical products, or change the value of one > or more fields, or what? >John, > [quoted text clipped - 10 lines] >will have N unique rows... (I'm pretty sure I normalized the design >correctly, I know it seems an odd thing to do in a RDB) ok... just wanted to be sure I understood! I actually have code that does just this in my animal shelter application: when somebody brings in a litter of six kittens they can enter the data for the first one, and type 6 in an unbound textbox. The code makes five more copies of that animal's data. Your code would run an Append query such as INSERT INTO yourtable SELECT [Forms]![Yourform]![txtThis], [Forms]![Yourform]![txtThat], [Forms]![yourform]![txtTheOther] FROM Num WHERE N < [Forms]![yourform]![txtN]; This will generate as many replicates of the data shown on the form (in the textboxes txtThis, txtThat and txtTheOther) as specified in the unbound textbox txtN. You'll want to check for reasonable (>0, < 20 say) values in txtN before running the query. The data textboxes can be bound to an existng record or unbound for free entry of data, whichever works better for you. SignatureJohn W. Vinson [MVP] John, Thanks for the code snippet! Unfortunately I am still having problems... I created a button on my form, and in the onclick event property clicked on event procedure to get to Visual Basic. Here is how I worked the code in: Private Sub Create_Lot_Click() INSERT INTO [Wafers in FAB] SELECT [Forms]![Wafer Starts]![FLN] FROM N WHERE N < [Forms]![Wafer Starts]![N]; End Sub I am getting a compile error: Expected: end of statement (after I type the INSERT INTO line). I tried naming my table something without spaces and using that table name. It's almost like my tables aren't in scope in this method... My forms appear in the VB properties list with no problem. My second question is: Why am I selecting the fields from N? should't I be selecting them from a data source for the form or something? Thanks, I appreciate your patience with my inexperience in Access and VB! Emily > >John, > > [quoted text clipped - 29 lines] > txtN before running the query. The data textboxes can be bound to an existng > record or unbound for free entry of data, whichever works better for you. >John, > [quoted text clipped - 16 lines] >that table name. It's almost like my tables aren't in scope in this method... >My forms appear in the VB properties list with no problem. The problem is that I posted SQL code to be put into a Query... and you put it into a VBA module. SQL is one language, VBA a very different language - and neither makes sense in the other's context! Copy that text into the SQL window of a new query. Save it as qryClone. In your VBA for the Click event put Private Sub Create_Lot_Click() Dim db As DAO.Database Set db = CurrentDb On Error GoTo Proc_Error db.Execute "qryClone", dbFailOnError Proc_Exit: Exit Sub Proc_Error: Msgbox "Error " & Err.Num & " in Create_Lot_Click" & vbCrLf & Err.Description Resume Proc_Exit End Sub (Added some error trapping code just in case). >My second question is: Why am I selecting the fields from N? should't I be >selecting them from a data source for the form or something? Ummm... What I suggested will in fact insert them from the form directly. That's what you asked for. Your example will insert only one field - FLN; I don't know if other fields are needed. Don't insert anything into [Wafers in FAP]'s Autonumber field, it will increment automatically. You're basing the append query on N purely to get N records. That's the only role that N plays. SignatureJohn W. Vinson [MVP] |
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
|
©2008 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.