Insert values into Table

G

Guest

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
 
6

'69 Camaro

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.
 
G

Guest

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....
 
G

Guest

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
 
6

'69 Camaro

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top