Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 22nd, 2007, 03:24 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 33
Thanks: 0
Thanked 0 Times in 0 Posts
Default Adding Records with VBA

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.

Reply With Quote
  #2 (permalink)  
Old June 22nd, 2007, 03:45 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Reply With Quote
  #3 (permalink)  
Old June 22nd, 2007, 04:02 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 33
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #4 (permalink)  
Old June 22nd, 2007, 04:10 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Reply With Quote
  #5 (permalink)  
Old June 22nd, 2007, 04:17 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 33
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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....


Reply With Quote
  #6 (permalink)  
Old June 22nd, 2007, 04:29 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #7 (permalink)  
Old June 22nd, 2007, 04:54 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 33
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #8 (permalink)  
Old June 22nd, 2007, 05:05 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding records to db djobes31770 VB Databases Basics 3 November 27th, 2007 08:27 AM
adding multiple records Vince_421 VB Databases Basics 4 February 28th, 2007 06:22 AM
Adding records to Tables lgpatterson Access VBA 6 March 20th, 2005 06:23 AM
Adding records using form and subform marcin2k Access VBA 3 March 7th, 2005 03:37 PM
adding records sinner Classic ASP Databases 5 February 25th, 2004 05:12 PM



All times are GMT -4. The time now is 09:23 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.