Noob question on how to use an insert query in a command button

[Glen]

Hello all.

Normally i would do this type of project in PHP and SQL but the boss
has requested that I make this in something that he's familiar with,
namely access.

What i'm trying to do is create a time sheet entry system. What i have
done is created a DB that has 4 tables in it. A user table, project
table, rank table, and a hours table. The DB itself I can use the
queries and reports on till i'm blue in the face, what i'm trying to
do is create a form that will allow other users (that arent allowed to
see the raw data) a way of submitting time to the hours table based on
a multi level query.

So in short i'm trying to do an INSERT INTO query through this VB stuff
and I have no clue how to do it. I found some information on the web,
I also bought a book in hopes of finding out how to handle this and
both have proven useless thus far.

this is what I have

Private Sub SubmitBtn_Click()
On Error GoTo Submit_err

Dim dbLocation 'Location of our Access database file
Dim sSQL 'Our SQL query
dbLocation = "//Divot/public/common/timecard/tim.mdb"
Set objADO = CreateObject("ADODB.Connection") 'Create an ADO connection
to a database

objADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dbLocation 'Open the database
sSQL = "INSERT INTO Hours ( UserID, ProjectID, Date, Hours ) VALUES('"
& & "' , '" &  & "')" 'Set our SQL query
objADO.Execute (sSQL)  'Execute this SQL query

'Cleanup
objADO.Close
Set objADO = Nothing

Submit_err:
MsgBox Err.Description
Resume Submit_err
End Sub

Keep in mind that some of this i grabbed from the web. and other
pieces from the book. What do you need from me and what can i provide
that might help to know what i'm trying to accomplish?

 

[Douglas J. Steele]

A couple of things.

First, Date is a reserved word, and shouldn't be used for your own purposes.
If you can't rename the field, at least put square brackets around it to
reduce the likelihood of problems.

Second, you have to actually provide the values you want to insert:

sSQL = "INSERT INTO Hours ( UserID, ProjectID, [Date], Hours ) " & _
"VALUES('" & variable1 & "', '" & variable 2 & "' , '" & _
Format(variable3, "\#mm\/dd\/yyyy\#") & ", " & _
Format(variable4, "\#hh\:nn\:ss\#") & ")"

Note that date values must be delimited with # characteres, and need to be
in mm/dd/yyyy format (regardless of what your Short Date format have been
set to through Regional Settings) (Okay, this isn't strictly true. You can
get away with any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The
point is, if you're using dd/mm/yyyy, it won't work properly for the first
12 days of each month.)

I'm assuming above that UserID and ProjectID are both text fields. If
they're numeric, lose the single quotes.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello all.

Normally i would do this type of project in PHP and SQL but the boss
has requested that I make this in something that he's familiar with,
namely access.

What i'm trying to do is create a time sheet entry system. What i have
done is created a DB that has 4 tables in it. A user table, project
table, rank table, and a hours table. The DB itself I can use the
queries and reports on till i'm blue in the face, what i'm trying to
do is create a form that will allow other users (that arent allowed to
see the raw data) a way of submitting time to the hours table based on
a multi level query.

So in short i'm trying to do an INSERT INTO query through this VB stuff
and I have no clue how to do it. I found some information on the web,
I also bought a book in hopes of finding out how to handle this and
both have proven useless thus far.

this is what I have

Private Sub SubmitBtn_Click()
On Error GoTo Submit_err

Dim dbLocation 'Location of our Access database file
Dim sSQL 'Our SQL query
dbLocation = "//Divot/public/common/timecard/tim.mdb"
Set objADO = CreateObject("ADODB.Connection") 'Create an ADO connection
to a database

objADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dbLocation 'Open the database
sSQL = "INSERT INTO Hours ( UserID, ProjectID, Date, Hours ) VALUES('"
& & "' , '" &  & "')" 'Set our SQL query
objADO.Execute (sSQL)  'Execute this SQL query

'Cleanup
objADO.Close
Set objADO = Nothing

Submit_err:
MsgBox Err.Description
Resume Submit_err
End Sub

Keep in mind that some of this i grabbed from the web. and other
pieces from the book. What do you need from me and what can i provide
that might help to know what i'm trying to accomplish?