 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / March 2006How Do I Extract Data from my Form to load new table records?
I am VERY new to this, so sorry for the dumb question. I have the best MS Access books you can by, and they all take for granted you know how to do this, so gloss over it. I have the need to create skeleton our outline records in a table. Assume the destination table is called tbl_Checklist. I have create a form from which I allow three fields to be input (unbound text boxes). I then hit a button I created call Generate. The intent is that this then executes code to: 1) Open a "template table with a number of predefined "standard" rows 2) Read these rows one at a time 3) Take data from these rows PLUS the three fields from the unbound text boxes 4) Write this data out to my empty tbl_Checklist table. The template table data looks like this: CKL_ItemNo (numeric) CKL_Description (text) CKL_Complexity (Numeric) 100.10 Description 1 1 100.20 Description 2 2 100.50 Description 3 1 ...etc When I am done, I want my Tbl_Checklist to look something like: Formfield 1 (text) Formfield2 (txt) Formfield3 (txt) CKL_ItemNo (numeric) CKL_Description (text) CKL_Complexity (Numeric) or FF1 FF2 FF3 100.10 Description 1 1 FF1 FF2 FF3 100.20 Description 2 2 FF1 FF2 FF3 100.50 Description 3 1 ....etc Basically, I am creating a standard set of skeleton data. If my Template file has 10 records in it, then every time I execute this, I would be adding all the data from the template file, plus the same three input fields, to each of the 10 records, and thus, loading 10 records every time I hit the generate button. Any thought? I am basically ok with the coding part, but cannot figure out how to reference the form fields, in my code. ANy good coding examples of what is probably hand extremely simply form-to-code process? Thanks I whipped up a couple of tables, a form and some code... hope this is what you are looking for.. :) 'watch for line wrap '*****beg code*************** Option Compare Database Option Explicit Private Sub Generate_Click() Dim db As Database Dim strSQL As String Dim i As Long Set db = CurrentDb ' these are the fields in the destination table -> tbl_Checklist strSQL = "INSERT INTO tbl_Checklist ( CKL_ItemNo, CKL_Description, CKL_Complexity, FF1, FF2, FF3 )" 'this is temp table rows . change tbl_Template to the name of your table strSQL = strSQL & " SELECT tbl_Template.CKL_ItemNo, tbl_Template.CKL_Description, tbl_Template.CKL_Complexity, '" ' these are the controls on the form ' change "Form5" to the name of your form ' change tbF1, tbF2, tbF3to the names of your controls strSQL = strSQL & [Forms]![Form5]![tbF1] & "' AS F1, '" strSQL = strSQL & [Forms]![Form5]![tbF2] & "' AS F2, '" strSQL = strSQL & [Forms]![Form5]![tbF3] & "' AS F3" 'change tbl_Template to the name of your temp table strSQL = strSQL & " FROM tbl_Template;" db.Execute strSQL, dbFailOnError ' the next two lines can be deleted if you want i = db.RecordsAffected MsgBox i & " records (rows) were added" End Sub '*****end code*************** If I didn't comment well enough, post back. HTH  SignatureSteve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) > I am VERY new to this, so sorry for the dumb question. I have the best MS > Access books you can by, and they all take for granted you know how to do [quoted text clipped - 39 lines] > > Thanks Wow...I am blown away. As I look through it, it logically seems to make sense. I will give it a try and get back to you. THANKS SO MUCH! Patk > I whipped up a couple of tables, a form and some code... > hope this is what you are looking for.. :) [quoted text clipped - 85 lines] > > > > Thanks Steve: I have adapted your code (Hopefully, correctly) as follows, but am getting an error on execution Run-time error '3061' Too few parameters. Expected 3. Here is the code, as it stands: Option Compare Database Option Explicit 'this is the name of the form control to execute Private Sub Command4_Click() Dim db As Database Dim strSQL As String Dim i As Long Set db = CurrentDb ' these are the fields in the destination table -> tbl_Checklist ' in your initial code, you had the Form fields last....I wanted them to be first (prepend) so I put the FF fields first. strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number, RF_Descriptor, RF_Multiplier )" 'this is temp table rows . change tbl_Template to the name of your table 'I am simply using the name tbl_Template...good as any table, and this matches DB. strSQL = strSQL & " SELECT tbl_Template.CKL_ItemNo, tbl_Template.CKL_Description, tbl_Template.CKL_Complexity, '" ' these are the controls on the form ' change "Form5" to the name of your form ' change tbF1, tbF2, tbF3to the names of your controls ' I made these changes, however, in your code, you had the fields as simply F1, F2, F3. I assume you meant FF1, FF2, and FF3. Let me know if not correct. strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "' AS FF1, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] & "' AS FF2, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity] & "' AS FF3" ' I tried adding an additional single quote before FF3 in the above line, but that resulted in a different error (run time error 3067: query input must contain at least one table or query). 'change tbl_Template to the name of your temp table strSQL = strSQL & " FROM tbl_Template;" db.Execute strSQL, dbFailOnError ' the next two lines can be deleted if you want i = db.RecordsAffected MsgBox i & " records (rows) were added" End Sub What did I miss, or did I transpose something in building the sql string? Thanks! I think this is darn close.....and I REALLY appreciate your taking the time. Pat > Wow...I am blown away. As I look through it, it logically seems to make > sense. I will give it a try and get back to you. THANKS SO MUCH! [quoted text clipped - 90 lines] > > > > > > Thanks Sorry....found an obvious error..did not have the tbl_template fields properly named. I still get the 3067 error, but here is the current code: Option Compare Database Option Explicit Private Sub Command4_Click() Dim db As Database Dim strSQL As String Dim i As Long Set db = CurrentDb ' these are the fields in the destination table -> tbl_Checklist strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number, RF_Descriptor, RF_Multiplier )" 'this is temp table rows . change tbl_Template to the name of your table strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number, tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '" ' these are the controls on the form ' change "Form5" to the name of your form ' change tbF1, tbF2, tbF3to the names of your controls strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "' AS FF1, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] & "' AS FF2, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity] & "' AS FF3'" 'change tbl_Template to the name of your temp table strSQL = strSQL & " FROM tbl_Template;" db.Execute strSQL, dbFailOnError ' the next two lines can be deleted if you want i = db.RecordsAffected MsgBox i & " records (rows) were added" End Sub Pat, It looks like there is an extra (') at the end of one line Expanded, the line looks like & " ' AS FF3 ' " It should be & "' AS FF3 " (expanded & " ' AS FF3 " ) See if that fixes the error For debugging I use: '-------SNIP----------- 'change tbl_Template to the name of your temp table strSQL = strSQL & " FROM tbl_Template;" MsgBox strSQL '<< FOR DEBUGGING 'db.Execute strSQL, dbFailOnError ' < comment out so it doesn't run ' the next two lines can be deleted if you want '-------SNIP----------- SignatureSteve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) > Sorry....found an obvious error..did not have the tbl_template fields > properly named. I still get the 3067 error, but here is the current code: [quoted text clipped - 37 lines] > MsgBox i & " records (rows) were added" > End Sub I am trying to capture that message window as exactly as possible (after eliminating the extra single quote). Here is the strSQL field displayed in the MsgBox: INSERT INTO tbl_CKLT (FF1, FF2, FF3, RF_Outline_Number, RF_Descriptor, RF_Multiplier ) Select tbl_Template.RF_Outline_Number, tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, 'Test' AS FF1, 'Assisted Customer Support (CP)' AS FF2, '2' as FF3 FROM tbl_Template; The form input values look right. If I do execute the db.Execute, i get this error: Run-time error '3127': The INSERT INTO statement contains the following unknown field name: 'FF1'. Make sure you have typed the name correctly, and try the operation again. Here is the current code: Option Compare Database Option Explicit Private Sub Command4_Click() Dim db As Database Dim strSQL As String Dim i As Long Set db = CurrentDb ' these are the fields in the destination table -> tbl_Checklist strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number, RF_Descriptor, RF_Multiplier )" 'this is temp table rows . change tbl_Template to the name of your table strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number, tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '" ' these are the controls on the form ' change "Form5" to the name of your form ' change tbF1, tbF2, tbF3to the names of your controls strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "' AS FF1, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] & "' AS FF2, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity] & "' AS FF3" 'change tbl_Template to the name of your temp table strSQL = strSQL & " FROM tbl_Template;" MsgBox strSQL db.Execute strSQL, dbFailOnError ' the next two lines can be deleted if you want i = db.RecordsAffected MsgBox i & " records (rows) were added" End Sub Hope I got that right > Pat, > [quoted text clipped - 61 lines] > > MsgBox i & " records (rows) were added" > > End Sub Pat, I see the problem...... you changed theorder of the fields in the INSERT clause!!! This is the way I wrote it (old field names): \/ \/ strSQL = "INSERT INTO tbl_Checklist ( CKL_ItemNo, CKL_Description, CKL_Complexity, FF1, FF2, FF3 )" This is what you changed it to: \/ \/ \/ strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number, RF_Descriptor, RF_Multiplier )" The order of the fields in the INSERT part *MUST* be in the same order as the data being inserted. change this line: strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number, RF_Descriptor, RF_Multiplier )" to: strSQL = "INSERT INTO tbl_CKLT ( RF_Outline_Number, > RF_Descriptor, RF_Multiplier, FF1, FF2, FF3)" See if that makes a difference! :) SignatureSteve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) > I am trying to capture that message window as exactly as possible (after > eliminating the extra single quote). Here is the strSQL field displayed in [quoted text clipped - 116 lines] > > > MsgBox i & " records (rows) were added" > > > End Sub Sorry..I did that because the order of the data and how it existed in the output table. Maybe I have a very wrong basic assumption (think so): Do the fields FF1, FF2, and FF3 have to be defined fields in the output table? IF so, then I need to rename them to match. Also, the RF fields are coming from the template file, but going INTO the tbl_CKLT...do I have to move the data incoming from the template file into fields named same as in tbl_CKLT? I guess that is my disconnect...how does it know that I want to put data from, say the template file, into another field in the CKLT table field name? That is where I am lost. Patk > Pat, > [quoted text clipped - 144 lines] > > > > MsgBox i & " records (rows) were added" > > > > End Sub > output table. Maybe I have a very wrong basic assumption (think so): Do the > fields FF1, FF2, and FF3 have to be defined fields in the output table? YES > so, then I need to rename them to match. Also, the RF fields are coming from > the template file, but going INTO the tbl_CKLT...do I have to move the data > incoming from the template file into fields named same as in tbl_CKLT? NO Change the FF1 - 3 in the following line to the field names in tbl_CKLT strSQL = "INSERT INTO tbl_CKLT (RF_Outline_Number, RF_Descriptor, RF_Multiplier, FF1, FF2, FF3)" and the code should run. (it did in my test mdb <g>) ~~~~ From Help (such as it is ): " INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase] SELECT [source.]field1[, field2[, ...] FROM tableexpression You can also use INSERT INTO to append a set of records from another table or query by using the SELECT ... FROM clause as shown above in the multiple-record append query syntax. In this case, the SELECT clause specifies the fields to append to the specified target table. target = The name of the table or query to append records *to*. field1, field2 = Names of the fields to append data to. (They have to match the *names* of the fields in target, but not the order.) source = The name of the table or query to copy records *from*. (They have to match the *order and datatypes* of the fields in target, but not names.) tableexpression = The name of the table or tables (query) from which records are inserted. value1, value2 = The values to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in quotation marks (' '). (And must have the same number of fields as the number of values) So, what is the name of the table where you want to append the records? destination table = tbl_CKLT What are the names of the fields in the destination table? RF_Outline_Number, RF_Descriptor, RF_Multiplier, FF1, \ FF2, you need to rename these if they are not in tbl_CKLT FF3 / What is the source table name? source table = tbl_Template What are the names of the fields in the source table? RF_Outline_Number, RF_Descriptor, RF_Multiplier [Forms]![frm_GenerateAssetChecklist]![inputAsset] AS FF1, [Forms]![frm_GenerateAssetChecklist]![inputPlatform] AS FF2, [Forms]![frm_GenerateAssetChecklist]![inputComplexity] AS FF3 ~~~~ -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) > Sorry..I did that because the order of the data and how it existed in the > output table. Maybe I have a very wrong basic assumption (think so): Do the [quoted text clipped - 90 lines] > > > > > > Hope I got that right I think I am really close. That last post cleared up a lot of things for me, I think. I am now down to the point that I am getting an error on the db.execute "data type mismatch in criteria expression.: I think that may be due to either the fact that the inputComplexity value is coming in as a text field (I think this because of the single quotes) and maybe i just need to remove the single quotes...I will try that. Or, the boolean field I am trying to initialize to FALSE (or NO...tried both) is not correct. Initially, I just want to set CKLT_Complete to NO (false) in every row, so there is nothing to accept from the form or the DB, to fill this out. HEre is the current code (gosh...I HAVE to be close???): Private Sub Command4_Click() Dim db As Database Dim strSQL As String Dim i As Long Dim booValue As Boolean Set db = CurrentDb booValue = False ' these are the fields in the destination table -> tbl_Checklist strSQL = "INSERT INTO tbl_CKLT ( CKLT_Item_Num, CKLT_Description, CKLT_Multiplier, CKLT_Asset, CKLT_Platform, CKLT_Complexity, CKLT_Complete )" 'this is temp table rows . change tbl_Template to the name of your table strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number, tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '" ' these are the controls on the form ' change "Form5" to the name of your form ' change tbF1, tbF2, tbF3to the names of your controls MsgBox [Forms]![frm_GenerateAssetChecklist]![inputAsset] MsgBox [Forms]![frm_GenerateAssetChecklist]![inputPlatform] MsgBox [Forms]![frm_GenerateAssetChecklist]![inputComplexity] strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "' AS CKLT_Asset, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] & "' AS CKLT_Platform, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity] & "' AS CKLT_Complexity, '" strSQL = strSQL & "NO" & "' AS CKLT_Complete " 'change tbl_Template to the name of your temp table strSQL = strSQL & " FROM tbl_Template;" MsgBox strSQL db.Execute strSQL, dbFailOnError ' the next two lines can be deleted if you want i = db.RecordsAffected MsgBox i & " records (rows) were added" End Sub By jove, I got it. THANKS A MILLION, STEVE! I owe you big time. Mismatch issue was the yes/no field and the way I was inserting that value into the sql string. Here is the final code: Private Sub Command4_Click() Dim db As Database Dim strSQL As String Dim i As Long Dim booValue As Boolean Set db = CurrentDb booValue = False ' these are the fields in the destination table -> tbl_CKLT strSQL = "INSERT INTO tbl_CKLT ( CKLT_Item_Num, CKLT_Description, CKLT_Multiplier, CKLT_Asset, CKLT_Platform, CKLT_Complexity, CKLT_Complete )" 'this is temp table rows. strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number, tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "' AS CKLT_Asset, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] & "' AS CKLT_Platform, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity] & "' AS CKLT_Complexity, " strSQL = strSQL & False & " AS CKLT_Complete " strSQL = strSQL & " FROM tbl_Template;" 'MsgBox strSQL db.Execute strSQL, dbFailOnError ' Output information about the numbers of rows added. i = db.RecordsAffected MsgBox i & " records (rows) were added" End Sub Again, my sincere thanks...this will help solve a process that will need to be done many times. By the way, the template file could contain hundreds of times, so entering these values into the DB every time a new asset is introduced would have been painful, at best (even if we just did excel imports...which was the original plan). This is SOOO much slicker! Patk Wonderful. IMPORTANT!!! You should put code at the beginning to check if the unbound controls are NULL - the code *will* bomb if any of the controls do not have an entry. I'm not sure if it will bomb if any of the fields in the template table are NULL - you'll have to try it. Since you defined a variable booValue as Boolean, then set it to FALSE, I was going to suggest using \/ strSQL = strSQL & booValue & " AS CKLT_Complete " If you stay with using FALSE in the line (not wrong, just another way), then you should delete the booValue lines - don't want extra code laying around to confuse yourself later. Since we are talking about "other ways", you could have run the "INSERT INTO" statement without the data from the 3 unbound (plus the boolean field), then run 4 "UPDATE" queries to fill in the 4 fields. But as long as it is working ....... :D SignatureSteve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) > By jove, I got it. THANKS A MILLION, STEVE! I owe you big time. Mismatch > issue was the yes/no field and the way I was inserting that value into the [quoted text clipped - 41 lines] > > Patk For some reason, my browser went flaky when I typed a response to your last message, so apologize if this is redundant. I have, indeed, made the change relate to the booValue and it is working fine. My next challenge is to test to make sure, before I even load these records, if records for this asset do not already exist. Right now, I can add the same asset name, multiple times (and have done so during my testing), but I do not want that to happen in real life. Anyway, thanks again for all your help in getting me over this hump. You are the best...patk > Wonderful. > [quoted text clipped - 63 lines] > > > > Patk > relate to the booValue and it is working fine. My next challenge is to test > to make sure, before I even load these records, if records for this asset do > not already exist. Check out this page for SQL. Towards the bottom of the page, in the FAQ section, is an example of the EXISTS clause of the INSERT INOT statement. http://www.techonthenet.com/sql/insert.php Using the subquery will not enter duplicate records. No extra code! HTH SignatureSteve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) > For some reason, my browser went flaky when I typed a response to your last > message, so apologize if this is redundant. I have, indeed, made the change [quoted text clipped - 74 lines] > > > > > > Patk That did the trick! Thanks Steve. Here is the code as it now stands (and works!): Option Compare Database Option Explicit Private Sub Command4_Click() Dim db As Database Dim strSQL As String Dim i As Long Dim booValue As Boolean Dim strAsset As String Set db = CurrentDb booValue = False strAsset = [Forms]![frm_GenerateAssetChecklist]![inputAsset] 'MsgBox "Form: " & strAsset ' these are the fields in the destination table -> tbl_CKLT strSQL = "INSERT INTO tbl_CKLT ( CKLT_Item_Num, CKLT_Description, CKLT_Multiplier, CKLT_Asset, CKLT_Platform, CKLT_Complexity, CKLT_Complete )" 'this is temp table rows. strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number, tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "' AS CKLT_Asset, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] & "' AS CKLT_Platform, '" strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity] & "' AS CKLT_Complexity, " strSQL = strSQL & booValue & " AS CKLT_Complete " strSQL = strSQL & " FROM tbl_Template WHERE not exists (SELECT * from tbl_CKLT WHERE CKLT_Asset = '" & strAsset & "' ) ;" 'MsgBox strSQL db.Execute strSQL, dbFailOnError ' Output information about the numbers of rows added. i = db.RecordsAffected If i <> 0 Then MsgBox "A checklist of " & i & " records (rows) were added to the RMA database." Else MsgBox "Zero rows added. This might be due to the fact that the asset already exists in the checklist table." End If End Sub Excellent! I wasn't sure you were still watching this thread.... amazing how things fall together to get the results you want. :D -- Steve S. -------------------------------- "Veni, Vidi, Velcro" (I came, I saw, I stuck around.) > That did the trick! Thanks Steve. Here is the code as it now stands (and > works!): [quoted text clipped - 45 lines] > > End Sub |
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.