Adding records at run time 1

[solo7]

What's the best way add a record (with several fiels) to a table at run time. My module is creating the table ok, and deleting it when required - I just need a clue on populating the fields.

Steady ...

 

[RickSpr]

The "best" way depends on facts you haven't provided, such as where the new record's values will come.

Here are 2 alternatives for you to evaluate.

1. If the values come from constants and/or variables, you can use DoCmd.RunSQL to execute a SQL INSERT statement. You build up the SQL statement by concatenating string representations of the values to be inserted. Example:
Dim strSQL As String
strSQL = "INSERT INTO MyTable (Field1, Field2, Field3) " _
& " VALUES(" & Value1 & "," & Value2 & "," & Value3 _
& ""
DoCmd.RunSQL strSQL

2. Alternatively, you can use DAO to insert records in code. Example:
Dim db As Database, rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Your Talbe Name&quot
rst.AddNew
rst!Field1 = Value 1
rst!Field2 = Value 2
rst!Field3 = Value 3
...
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing Rick Sprague

 

[solo7]

Cheers Rick,
I was having problems with the Dim As Database, my code just doesn't give me the option to select
As Database so i was looking for a SQL solution which I think you have given me. I'll play around with what you've said and hopefully I won't be back (on this subject). Thanks

Steady ...

 

[solo7]

Rick, - or anyone
The SQL sequence works fine with the numerical values I have, but I just get a input box - like the ones on parameter queries - when I introduce Text vales.

This works fine:
strSQL = "INSERT INTO TblCalender (year,month) "_ & "VALUES (" & VarYear & "," & RepMonth & ""

as VarYear and RepMonth are numerical

This asks for input:
strSQL = "INSERT INTO TblCalender_ (year,month,day,daynme) " & "VALUES (" & VarYear & "," &_ RepMonth & "," & RepDays & "," & RepToday & ""

where RepMonth and RepToday are Text

All data is present in the right places when i use the breakpoint. It seems that "VALUES" doesn't work for Text ??

any ideas ??

Steady ...

 

[KarMac21]

Steady,

For text values you have to put in single quotes right before the first pair of double quotes before the string variable and again after the second pair of double right after that same string variable:

ex. strSQL = "INSERT INTO TblCalender_ (year,month,day,daynme) " & "VALUES (" & VarYear & "," &_ RepMonth & ", '" & RepDays & "' , '" & RepToday & "');"

Hope this helps,
Shane

 

[RickSpr]

In case you want to do the DAO code again some day, the reason you're not getting Database in the list of types is that you have to add a reference to the DAO Object Library. Do this by opening a code window, then select Tools>References... from the menu. Scroll until you find DAO 3.6 (or 3.5) Object Library, check the check box, and click OK.

Any time you know an object exists, but it doesn't show up in the Object Browser or quick help, that tells you you need to add a reference to the library containing the object.

References are links to libraries which contain code and/or controls. When you add a reference, everything declared Public in the code will be added to VBA's "Namespace". It will appear in the Object Browser and in the pop-up quick help. The libraries on your References must be present on the computer whenever your database is used, so if you plan to distribute your database you should ensure that either the object libraries are already available on the target computers, or (if the libraries are "redistributables", such as DAO) that you include them in your distribution. Rick Sprague

 

[solo7]

Thanks lads,

Those pesky commas, quotes and stop strike yet again !!

Steady ...