My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Is there a better way to insert form data to a table?
|
|
 Jan 18 2005, 12:47 PM
Post
#1
|
|
|
New Member Posts: 18 From: Houston, TX  |
I have a form with unbound text boxes that make up an "estimate sheet" for users to generate a quote for our customers. There are eleven rows, some will contain data, some will not, depending on customer requirements, etc.. I would like to insert the values from the form into a table after the user is finished. Here is the code I am using:
CODE Private Sub cmdSaveQuote_Click() Dim strSQL01 As String Dim strSQL02 As String Dim strSQL03 As String Dim strSQL04 As String Dim strSQL05 As String Dim strSQL06 As String Dim strSQL07 As String Dim strSQL08 As String Dim strSQL09 As String Dim strSQL10 As String Dim strSQL11 As String If [txtQuant01] >= 1 Then strSQL01 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtDayRate & "', '" & Me.txtQuant01 & "', '" _ & Me.txtPrice01 & "', '" & Me.txtDisc01 & "';" CurrentDb.Execute strSQL01 DoEvents Else End If If [txtQuant02] >= 1 Then strSQL02 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtManlift & "', '" & Me.txtQuant02 & "', '" _ & Me.txtPrice02 & "', '" & Me.txtDisc02 & "';" CurrentDb.Execute strSQL02 DoEvents Else End If If [txtQuant03] >= 1 Then strSQL03 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtNewRll & "', '" & Me.txtQuant03 & "', '" _ & Me.txtPrice03 & "', '" & Me.txtDisc03 & "';" CurrentDb.Execute strSQL03 DoEvents Else End If If [txtQuant04] >= 1 Then strSQL04 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtTradeRll & "', '" & Me.txtQuant04 & "', '" _ & Me.txtPrice04 & "', '" & Me.txtDisc04 & "';" CurrentDb.Execute strSQL04 DoEvents Else End If If [txtQuant05] >= 1 Then strSQL05 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtRecertRll & "', '" & Me.txtQuant05 & "', '" _ & Me.txtPrice05 & "', '" & Me.txtDisc05 & "';" CurrentDb.Execute strSQL05 DoEvents Else End If If [txtQuant06] >= 1 Then strSQL06 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtPerDiem & "', '" & Me.txtQuant06 & "', '" _ & Me.txtPrice06 & "', '" & Me.txtDisc06 & "';" CurrentDb.Execute strSQL06 DoEvents Else End If If [txtQuant07] >= 1 Then strSQL07 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtFlight & "', '" & Me.txtQuant07 & "', '" _ & Me.txtPrice07 & "', '" & Me.txtDisc07 & "';" CurrentDb.Execute strSQL07 DoEvents Else End If If [txtQuant08] >= 1 Then strSQL08 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtRentalCar & "', '" & Me.txtQuant08 & "', '" _ & Me.txtPrice08 & "', '" & Me.txtDisc08 & "';" CurrentDb.Execute strSQL08 DoEvents Else End If If [txtQuant09] >= 1 Then strSQL09 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtMileage & "', '" & Me.txtQuant09 & "', '" _ & Me.txtPrice09 & "', '" & Me.txtDisc09 & "';" CurrentDb.Execute strSQL09 DoEvents Else End If If [txtQuant10] >= 1 Then strSQL10 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtFreight & "', '" & Me.txtQuant10 & "', '" _ & Me.txtPrice10 & "', '" & Me.txtDisc10 & "';" CurrentDb.Execute strSQL10 DoEvents Else End If If [txtQuant11] >= 1 Then strSQL11 = "INSERT INTO tblRecertDetails (strQuoteNumber, strDescription, intQuantity, curPrice, intDiscount) " _ & " SELECT '" & Me.txtQuoteNumber & "', '" & Me.txtOther & "', '" & Me.txtQuant11 & "', '" _ & Me.txtPrice11 & "', '" & Me.txtDisc11 & "';" CurrentDb.Execute strSQL11 DoEvents Else End If End Sub The code works, but is there a better way of doing this? Thanks, Brian Clayton |
 |
 
|
|
Jan 20 2005, 12:12 AM
Post
#2
|
|
|
Microsoft Corp UtterAccess VIP Posts: 1,839 From: Bellevue, WA |
Hi Brian,
Yes, this can be done using a For loop and a Recordet since your controls are all named similarly. Do a For loop from 1 to 11, and use the Controls collection on the form to get each text box. From there, check the value and add a new record accordingly. Something like: CODE Dim c As Control ' use a DAO.Recordset if you have a reference to DAO ' and adjust the code accordingly Dim rs as ADODB.Recordset ' open the table rs.Open "tblRecertDetails", CurrentProject.Connection, adOpenDynamic, adLockOptimistic For i = 1 to 11 set c = Me.Controls("txtQuant" & i) if c.value >= 1 then ' add a new record rs.AddNew rs.fields("strQuoteNumber")=Me.txtQuoteNumber rs.fields("strDescription")=me.txtother rs.fields(" intQuantity")=c.value ' since c is the quantity control rs.fields("curPrice")=me.controls("txtPrice" & i).value rs.fields(" intDiscount")=me.controls("txtDisc" & i).value rs.Update end if Next ' cleanup, etc... Hope this helps, |
|
|
|
|
Feb 2 2005, 03:16 PM
Post
#3
|
|
|
New Member Posts: 18 From: Houston, TX |
I knew there had to be an easier way. I will give it a try.
Thanks, Brian Clayton |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 18th June 2013 - 10:21 PM |