Writing to a table with code

[Todd]

I'm a new access user, so bear with me please.

I need to learn how to write code to enter data into a
table.

I'd like to be able to do the following things:

a) find a particular record and update a particular field
in that record

b) create a new record in a table and write to particular
fields in that new record

Ultimately what I'm doing is trying to make a forecast
table that is based on certain variables. So what I'll do
in practice is change the variables in a form or table,
and then use code to perform calculations based on the
variables to update the forecast table. I think by
learning how to do the two things above, I should be able
to do what I'm trying to do.

Thanks.

 

[Tim Ferguson]

a) find a particular record and update a particular field
in that record

UPDATE MyTable
SET MyField = MyField * 1.175;

b) create a new record in a table and write to particular
fields in that new record

INSERT INTO MyTable (MyKey, MyField, MyDate)
VALUES ("ABC", 34.67, #2003-11-30#);

Ultimately what I'm doing is trying to make a forecast
table that is based on certain variables. So what I'll do
in practice is change the variables in a form or table,
and then use code to perform calculations based on the
variables to update the forecast table. I think by
learning how to do the two things above, I should be able
to do what I'm trying to do.

The obvious way is to create the SQL strings and execute them

' note how to get the quoting right inside the string: use
' Chr$(34) to quote text values, and # characters for
' dates: numbers go in straight
'
strSQL = "INSERT INTO MyTable (MyKey, MyField, MyDate) " & _
"VALUES (" & _
Chr$(34) & Me!txtNewKey & Chr$(34) & ", " & _
Format$(Me!txtMyField.Value, "0.00") & ", " & _
Format$(Me!txtMyDate.Value, "\#yyyy\-mm\-dd\#") & _
");"


' remove this when you know it's working
MsgBox strSQL, "Check this out"


' and run it: db should be already Set to CurrentDB()
db.Execute strSQL, dbFailOnError


There is a slightly smarter and quicker way to do the same thing by
creating parameter queries and executing them instead, but you should get
the picture. If you have to use ADO, it's slightly different but only in
detail.

Hope that helps


Tim F

 

[Marty]

First let me start by saying, as an experienced
participant of this forum, chances are this query would
generally not have received a reply because you have not
asked a question per say. Let me re-phrase what you've
said. "I have this thing and I want to do some stuff with
some criteria from an unknown source, can anyone help me?"

The best way to use this forum is to start writing code
and when you run into a problem ask a question about the
specific problem you are having.

The reason I am replying to this is because I remember
when I was in your shoes. It was hard and it took a long
time and a lot of hours, hours heck days, to work through
the simplest problems. The method that worked best for me
is books, help from forums like this, and most of all
macros converted to VBA. Create a Macro of something you
want to do, save it, then close it. Click on TOOLS >
Macros > Convert to VB. This will open a VB code window
for you to analyse. I'm sorry I don't have anything more
specific for you. With hard work and perseverance I'm
sure you'll achieve your gols.

Good luck. Please stay in touch with any specific help
you may need along the way.

Marty