 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / December 2006Insert values into Table
in order to export in a format acceptable by QuickBooks I need to plug values in txtBoxs of Form into specific fields in a fixed 5 record QBExport table made exclusively for this purpose: 1 Header1a Header1b Header1c Header1d 2 Header2a Header2b Header2c Header2d 3 Header3a txtBox1 (blank) txtBox2 4 Header4a (blank) (blank) Field3 5 Header5a Hopefully these columns will line up when y'all view it......All the "header" values are fixed data already manually put into the table to meet the requirements of QuickBooks. The txtBox values are the values needed to be inserted into this table. (I threw the blank fields in there too just for completeness as there are blank fields in the table which is ok...) Haven't alot of experience with Recordset insert...am reading but not quite got the handle on this syntax...is Access2002 ....would welcome a sample of what vb code to put onClick event of button in this form that will insert the txtBox values into table...much thanks  SignatureNTC Hi. > Hopefully these columns will line up They did, but you forgot to list the column names and their data types. We can guess that the first column is probably a numeric data type, and the second through the fifth columns are Text data types. The first and second column _may_ be required columns, but the third through fifth aren't required. For this example, I'm going to assume the first column is a Long data type. > Haven't alot of experience with Recordset insert Nor should you! You should either be using bound forms or using SQL. Only as a last resort should you use a Recordset for data input, updates, or deletions. > would welcome a sample of > what vb code to put onClick event of button CurrentDb().Execute "UPDATE TableName " & _ "SET Col3 = '" & Me!txtBox1.Value & "', Col5 = '" & Me!txtBox2.Value & "' " & _ "WHERE Col1 = 3;", dbFailOnError HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > in order to export in a format acceptable by QuickBooks I need to plug values > in txtBoxs of Form into specific fields in a fixed 5 record QBExport table [quoted text clipped - 20 lines] > what vb code to put onClick event of button in this form that will insert the > txtBox values into table...much thanks hey gracias....that outlines it for me and I should be able to ramp up ....will play with it over the next day...thanks again.... SignatureNTC > Hi. > [quoted text clipped - 50 lines] > > what vb code to put onClick event of button in this form that will insert the > > txtBox values into table...much thanks You're welcome. Good luck with it. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > hey gracias....that outlines it for me and I should be able to ramp up > ....will play with it over the next day...thanks again.... [quoted text clipped - 60 lines] >> > the >> > txtBox values into table...much thanks hmmmm getting a syntax error CurrentDb().Execute "UPDATE QBTerms" & "SET date = " '&Me!Shipvia.Value&'"" & "WHERE record = 4;", dbFailOnError Have stripped it down to attempt only one value insert.....am fuzzy on your triple quotation marks....possibly that is it SignatureNTC > You're welcome. Good luck with it. > [quoted text clipped - 69 lines] > >> > the > >> > txtBox values into table...much thanks Hi. > hmmmm getting a syntax error Actually, you have 10 errors, possibly more if the Date column is a Date data type. New at this, huh? ;-) It's hard to see the single quotes next to the double quotes, and the browser wrapping the text lines around improperly doesn't help any. Before I get started on fixing this, let me tell you some of the rules of building a text string in code, and let me ask you a question as well. One cannot use Date, or any other Reserved word, as an identifier (table name, column name, object name, procedure name, et cetera), because it will introduce bugs into the program. So change the column name Date to something like ShipDate. Is this column a date data type or a text string? The syntax I give you depends on your answer. When building a string, you're concatenating strings together, so you must place a space, an ampersand, and another space between each text string (begins and ends with double quotes) or variable. For example, if the text box control TableName has CarLot and the text box control NumCars has 72, then you can use: sSQL = "UPDATE " & Me!TableName.Value & " SET NumCars = " & Me!NumCars.Value This will place the string, "UPDATE CarLot SET NumCars = 72" into the variable sSQL. Notice that there's also a space after UPDATE and before the TableName value, because we don't want manywordsrunningtogether after concatenation because the computer can't parse the tokens (words) in the string out of the mess. Please reply back with the data types of each of your columns that will be used in the SQL statement. Oh, in case you're interested, your code produced this text string: "UPDATE QBTermsSET date = " You may ask why QBTerms and SET are run together. It's because you told the computer to concatenate the strings that way. The computer does exactly what you tell it to do. You may also wonder why the string is cut off after the space following the equals sign. The single quote (if it's not within a text string) means "everything to the right of this mark is a comment." That's what your single quote did, it commented out a runtogetherAmpersandAndControlNameAndTheRest: '&Me!Shipvia.Value&'"" HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > hmmmm getting a syntax error > [quoted text clipped - 92 lines] >> >> > the >> >> > txtBox values into table...much thanks Try: CurrentDb().Execute "UPDATE QBTerms SET [date] = '" & _ Me!Shipvia.Value & _ "' WHERE record = 4;", dbFailOnError HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > Hi. > [quoted text clipped - 149 lines] >>> >> > the >>> >> > txtBox values into table...much thanks |
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.