Create new record from code in Access 2000

  • Thread starter Thread starter Del
  • Start date Start date
I need to record several variables into a new record of a table. The table
is in the same database but not the Record Source for the form that the code
is associated with.

The problem I need help with is creating the new record. Once the record
exists I can put the data into it.
 
DoCmd.RunSQL command can be used to execute a SQL Statement. Since the SQL
Statement is comprised of text you can concateonate(sp) the statement as
neccessary to insert whatever values you need.

Such as

strSQL = "INSERT INTO tblTrailerActivityHeaders (
lngTrailerLoadLocation, txtTrailerLoadStatus, txtTrailerDispatchStatus,
dteTrailerLoadDate, txtTrailerDOTNumber, dblControlId ) SELECT " &
Me.pg2_cboTrailerLoadLocation & " AS Expr6, 'Loading' AS Expr5, 'Not Ready'
AS Expr4, #" & Date & "# AS Expr3, " & "'" & Me.pg1_lstSelectTrailer & "' AS
Expr2, " & dblControlValue & " AS Expr1;"

Or you can use DAO to insert the records directly as in...

Set db = dbEngine(0)(0)
Set rs = db.OpenRecordset("tblTrailerActivityDetailShows")
rs.AddNew
rs("lngTrailerActivityHeaderId") = lngExistingHeader
rs("txtShowNumber") = Me.pg2_cboSelectShow
varNewRecordId = rs("lngTrailerActivityDetailId")
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

(The code samples are working with two completely different tables and are
not examples of how to do the same task with the same table. But the
principle is the same.)
 
Thank you.

I am self-taught in VBA and I have many gaps in my knowledge. One of them
was just filled in by your code. I was not aware of 'AS Expr' before. As
you can imagine, it was limiting.

Thanks again,
 
1) Play around A LOT with the QUERY BUILDER
2) I always use the QUERY BUILDER to actually craft my SQL statements just
to save time. Its just a matter of displaying the SQL Statement and then copy
and past. *BUT* if you go the route of imbeding your SQL Statement in code,
it will not run as efficiency as a SQL statement saved as a QUERY. When
Access executes a QUERY, the JET Engine optimizes it figuring out how best to
execute it. When the SQL statement is in code, JET has to optimize it each
and every time you execute it. Depending on the circumstances, you may want
to have one SQL Statement saved as a query and another in code. If its saved
as a QUERY, then you get efficiency but at the risk that a user might tamper
with it (if you don't have security turned on). If its a SQL Statement, you
sacrifice efficiency for increasing the probability that it won't be tampered
with (especially if you create a .MDE file for the front end). Keep in mind
though that most of the time the efficiency factor won't be an issue given
the typical speed of laptops/desktops, the probability that you're NOT
executing a complicated query, and the probability that you're working with a
relatively small database.
 
Back
Top