Need to create a new record in a table using known values

[jason305]

I've got values in variables in my code. I want to append a record to a table using these values.

I'm not using any recordsets. This is all running in Access 2000 and I'm calling the tables directly.

Forgive me, I am new at this.

Thanks!

 

[LonnieJohnson]

You can use the VBA Docmd.RunSQL command. Basically it is the same as running an append query. To get the SQL for it you could put together an append query and copy the SQL. I put together an an example that I ran. I made a table called AppendingFromCode. It has three fields. Name1, Address1, Amount1. Below is the code that I used to append amounts to the table. I used input boxes to get the values. You can modify as needed.


Public Function Appending()

Dim strName As String
Dim strAddress As String
Dim dolAmount As Currency

strName = InputBox("Name&quot
strAddress = InputBox("Address&quot
dolAmount = InputBox("Amount&quot

DoCmd.RunSQL "INSERT INTO AppendingFromCode " _
& "( Name1, Address1, Amount1 ) " _
& "SELECT '" & strName & "' AS Expr1, '" _
& strAddress & "' AS Expr2, " _
& dolAmount & " AS Expr3;"


End Function

Good luck... B-)
ljprodev@yahoo.com
ProDev
MS Access Applications

 

[PaulMilnes]

Jason -

What you want to do is clearer in your code if you use a recordset. If you're OK with Lonnie's SQL, well and good, but if not, I can send you some typical code which will create the recordset and add a new record.

Best regards

Paul

 

[jason305]

Thanks for the help guys. Funny, I had actually thought of and implimented the SQL idea before I was able to get back to this. It worked great.

I've never been real sure about the recordset things. I'd be interested to learn though.

Jason