Adding Records with VBA

rohit_ghosh · June 22nd, 2007, 03:24 AM

Hi,

Can someone lemme know how to add records in a table named contacts with VBA.

The fields are name, date of birth, date

The data added will be the same everyday that is

Name = John.K

Date of birth = 26-07-1977

Task = Create Reports

Date = todays date(the user shouldn't have to manually input this it should get automatically populated.

I should also be able to give the user an option to assign the name, Date of birth and Task for a range of dates...i.e., 25-06-2007 to 29-06-2007. The range of date option will populate multiple record sets and place the dates in the Date field. In this case for 5 days without manually updating the dates. Help will highly appreciated.

robzyc · June 22nd, 2007, 03:45 AM

Hello again rohit_ghosh,

Try:

Code:

Sub AddRec()
On Error GoTo AddRec_Err

Dim name As String
Dim dob As String
Dim task As String

name = "John.K" 'Amend this if required to pull the date from elsewhere.
dob = Format$(CDate("26/07/1977"), "yyyymmdd") 'Google "Universal Date Format (UDF)" - All dates should be stored in UDF and parsed.
task = "Create Reports"

'Create a connection to the current database.
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

'Create the SQL INSERT statement.
Dim sql As String
sql = "INSERT INTO [TableName]" & vbCrLf & _
    "([Name], [Date of Birth], [Date], [Task])" & vbCrLf & _
    "VALUES" & vbCrLf & _
    "('" & name & "', '" & dob & "', '" & Format$(Date, "yyyymmdd") & "', '" & task & "')"

'Execute the SQL INSERT
cnn.Execute sql

AddRec_Exit:
    Exit Sub

AddRec_Err:
    MsgBox "Error! " & Err.Number & " -> " & Err.Description
End Sub

That should do the trick!
Of, you never mentioned the Field name for 'Task', so as you can see, I have just called it 'Task'.

Kind Regards,
Rob

rohit_ghosh · June 22nd, 2007, 04:02 AM

Hi Rob,

Where should I add the table name tblTasks and the Database name Volume.mdb here.

Also how should I provide an option to create multiple recordsets based on Date ranges.
For example the user should be able to update the information for all the days of the next week till Friday.
This will create 5 recordsets

robzyc · June 22nd, 2007, 04:10 AM

rohit_ghosh,

Replace the 'TableName' in the SQL = ".... Statement with 'tblTasks'.

Is the Volume.mdb file seperate to the database being executed? Is it a standalone front-end?
If this is the case, then you really need to get yourself a book on Programming ADO as a reference,
there is far to much to cover in posts on a forum.

Also, be sure of your terminology, "updating" does not (or should not) return Recordsets.
Recordsets are for viewing/reading data, its good practise not to do both, its expensive on the system.
Updating data should be done by executing UPDATE/INSERT commands (as shown above).

Regards,
Rob

rohit_ghosh · June 22nd, 2007, 04:17 AM

The Volume.mdb is the present database I am working on. I have figured out how to enter the table name.

My question now is how to enter multiple recordsets at a time for several days with ADO. Like for Mon to friday next week.

Many thanks for your help again....

robzyc · June 22nd, 2007, 04:29 AM

Ah I get what you mean, you want to create a record for Mon-Fri for (following your example) John.K...

I have made some modifications (highlighted) to the original code to find the next monday, then add records for Mon-Fri..

Code:

Sub AddRec()
On Error GoTo AddRec_Err

Dim name As String
Dim dob As String
Dim task As String

name = "John.K" 'Amend this if required to pull the date from elsewhere.
dob = Format$(CDate("26/07/1977"), "yyyymmdd") 'Google "Universal Date Format (UDF)" - All dates should be stored in UDF and parsed.
task = "Create Reports"

'Find the Next Monday
Dim nextMon As Date
nextMon = Date
Do Until Weekday(nextMon) = VbDayOfWeek.vbMonday
    nextMon = DateAdd("d", 1, nextMon)
Loop

'Create a connection to the current database.
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

'Set the Temp Date - Start on next monday, for five days.
Dim tempDate As Date
tempDate = nextMon

Dim dayNum As Integer
For dayNum = 1 To 5
    'Create the SQL INSERT statement.
    Dim sql As String
    sql = "INSERT INTO [TableName]" & vbCrLf & _
        "([Name], [Date of Birth], [Date], [Task])" & vbCrLf & _
        "VALUES" & vbCrLf & _
        "('" & name & "', '" & dob & "', '" & Format$(tempDate, "yyyymmdd") & "', '" & task & "')"

    'Execute the SQL INSERT
    cnn.Execute sql

    'Add a day to the tempDate
    tempDate = DateAdd("d", 1, tempDate)
Next

AddRec_Exit:
    Exit Sub

AddRec_Err:
    MsgBox "Error! " & Err.Number & " -> " & Err.Description
End Sub

rohit_ghosh · June 22nd, 2007, 04:54 AM

Excellent stuff Rob...you are my access Guru!!!

I know I am asking too much but i am learning a lot from you....

Could you please lemme know a way to create record sets for several users. Based on the above code.

Also is it possible to restrict the users to use their own code through input box or login IDs on access.

robzyc · June 22nd, 2007, 05:05 AM

rohit_ghosh,

No problem, knowledge is power and all that!
Changing the line "name = "John.K"" to say whatever name you wish will cause the name in the record to change.

From what I can gather, you are now getting into mixing different data in one table, which is a bit
of a sin in terms of databases.

What you should really have is two tables, one for the "Users", another for "Tasks".
You would then link these tables.

For example, if "John.K" accidently entered the wrong Date of Birth, and created a couple of weeks of tasks,
then to correct the date of birth would result in 11 changes (10 for each record for each week, one for the
change to the code/parent table).

Once this is done, you can then prompt the user for login information, which would query the users table
to see if its the correct password etc. And log them in you could then store the UserName in a global variable.
This variable will then be used to insert the username into the queries. And so on...

Read up on linking tables in Access and how Relational databases work, have a play, and give us a shout
if you get stuck.

Regards,
Rob