Creating a record in a table using code after updating a form 3

[GoinDeep]

I have limited VB Knowledge, but I know enough about it to figure most things out when pointed in the right direction. Here's what I would like to do:

I have a form that after the form is updated, "After_Update Event" I would presume, I would like to create a record in a table that is related to this form, then have code enter certain things in the tables fields.

What this table is, is a log of activity for this order, so as soon as the order is created I want to send a record to the log saying "This order was created on this date and time by so and so..." Here's the specifics:

My Form is "frm_OrderEntry"

The table to create the record in is: "tbl_Activities"

The Fields are:
"OrderID" (This is the unique number that relates it to the Order in the table, and it is taken from a field on the "frm_OrderEntry" called "txt_OrderID")

"ActivityDate" (Now)
"ActivityTime" (Now)
"EmployeeID" (taken from a field on the "frm_OrderEntry" called "txt_EmployeeID")

If somebody knows a forum where I can read this or anything that might help with this specific situation would be great.

 

[PHV]

Why TWO fields (ActivityDate,ActivityTime) for a SINGLE value (Now()) ?
A starting point:

DoCmd.RunSQL "INSERT INTO tbl_Activities (OrderID,ActivityDateTime,EmployeeID)" _
 & " VALUES (" & Me!txt_OrderID & "," _
 & "#" & Format(Now, "yyyy-mm-dd hh:nn:ss") & "#," _
 & "'" & Me!txt_EmployeeID & "')"

If EmployeeID is defined as numeric in tbl_Activities then get rid of the single quotes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GoinDeep]

Only because that table already has date and time as 2 fields.

 

[GoinDeep]

Ok, I am getting s "Syntex Error in INSERT INTO statement"

Does it look right?

DoCmd.RunSQL "INSERT INTO tbl_Activities(OrderID, ActivityDate, ActivityTime, EmployeeID" _
& " VALUES (" & Me!txtOrderID & "," _
& "#" & Format(Now, "yyyy-mm-dd") & "#," _
& "#" & Format(Now, "hh:nn") & "#," _
& "'" & Me!txtEmployeeID & "')

 

[maxhugen]

On first glance, you look to be missing a closing parenthesis in: "INSERT INTO tbl_Activities(OrderID, ActivityDate, ActivityTime, EmployeeID"

Should be:

"INSERT INTO tbl_Activities(OrderID, ActivityDate, ActivityTime, EmployeeID[red])[/red]"

Max Hugen
Australia

 

[GoinDeep]

Boom...that was it. Now I actually forgot to add one more item...that is "Activity Code" which is "WE" which is "Work Order Entered". I added it as follows, but I am getting the old "Missing Operator Error", and SQL is foreign to me. It is just a text value.

DoCmd.RunSQL "INSERT INTO tbl_Activities(OrderID, ActivityDate, ActivityTime, ActivityCode, EmployeeID)" _
& " VALUES (" & Me!txtOrderID & "," _
& "#" & Format(Now, "yyyy-mm-dd") & "#," _
& "#" & Format(Now, "hh:nn") & "#," _
& "WE" _
& "'" & Me!txtEmployeeID & "')

 

[PHV]

& "[!]'[/!]WE[!]'[/!]" _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GoinDeep]

There it is...thanks so much.

 

[GoinDeep]

We'll, I guess one more thing...I do I trap and disgard the "About to append" warning?

 

[PHV]

DoCmd.SetWarnings False
DoCmd.RunSQL "..."
DoCmd.SetWarnings True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GoinDeep]

Great.

 

[Melagan]

This was answered in another thread, but just to remain consistent:

CurrentDb.Execute [i]Query as String[/i]
[COLOR=green]'No need to worry about Action Query Warnings[/color]

..is equivilent to:

DoCmd.SetWarnings False
DoCmd.RunSQL [i]SQL Statement[/i]
DoCmd.SetWarnings True

~Melagan
______
"It's never too late to become what you might have been.