Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics 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 February 26th, 2007, 06:45 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default adding multiple records

Hi,

What I am trying to do is to add multiple records at one time.

I have a form with a startdate, an enddate and an artikelnumber. What I would like to have is that when I click on an action button (that has already been made) the records are entered automatically.

The start and enddate will always be a monday. I need the first added record to be the startdate and than every monday that follows (so startdate +7) untill the last record that is added equals the enddate.

The code that is currently linked to the action button is:

Private Sub BijkomendeInvoer_Click()

Dim sArt, sSQL As String
Dim rs As ADODB.Recordset
Dim i, iStart, iEnd As Date

'check for filled combo boxes
'check startdate
If IsNull(Me.Begindatum) Or Me.Begindatum = "" Then
    MsgBox "Gelieve een begindatum in te voeren." & vbCrLf & "Veuillez entrer une date de départ.", vbExclamation
    Exit Sub
Else
    iStart = Me.Begindatum
End If

'check enddate
If IsNull(Me.Einddatum) Or Me.Einddatum = "" Then
    MsgBox "Gelieve een einddatum in te voeren." & vbCrLf & "Veuillez enter une date de fin.", vbExclamation
    Exit Sub
Else
    iEnd = Me.Einddatum
End If

'check to see if start date is less than end date
If iStart > iEnd Then
    MsgBox "De einddatum bevindt zich voor de begindatum." & vbCrLf & "La date de départ se situe après la date de fin.", vbExclamation
    Exit Sub
End If

'check artikel
If IsNull(Me.Artikelnummer) Or Me.Artikelnummer = "" Then
    MsgBox "Gelieve een artikelnummer in te voeren." & vbCrLf & "Veuillez entrer un numéro d'article.", vbExclamation
    Exit Sub
Else
    sArt = Me.Artikelnummer
End If

'define recordset to ADD new records with this data
sSQL = "select * from tblTestBuyBA01"

'open recordset and connection
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject, adOpenDynamic, adLockOptimistic

'add all records here
Do Until iStart = iEnd

    rs.AddNew
    rs("Datum") = iStart
    rs("Artikelnummer") = sArt
    rs.Update
    iStart = iStart + 7

Loop

rs.Close

MsgBox "De Test Buy werd ingevoerd." & vbCrLf & "Le Test Buy a été inséré.", vbInformation

End Sub

Does anyone know what should be changed to this code for it to work?
The fields stardate, enddate and artikelnumber are set up on an unbound form in access 2002. The table where the records needs to be added is tblTestBuyBA01 like you can see in the code. The fields names that are required to fill in are "datum" for date and "artikelnummer" for the artikelnumber...

The line in the code that creates the error is:

rs.Open sSQL, CurrentProject, adOpenDynamic, adLockOptimistic


Reply With Quote
  #2 (permalink)  
Old February 26th, 2007, 08:32 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there.. i don't see anything wrong in your code, what error do you receive????

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
Reply With Quote
  #3 (permalink)  
Old February 27th, 2007, 05:35 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The error I receive is:

Run-time error '3001':

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

When I click on debug the line in error is the one I posted earlier...

any idea what might be the problem?

Reply With Quote
  #4 (permalink)  
Old February 27th, 2007, 08:32 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

mmm... that sql is not wrong.. I don't know why are you receiving your error...

can you change that sql to just the name of the table and try again????

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
Reply With Quote
  #5 (permalink)  
Old February 28th, 2007, 06:22 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for trying to help, but I found another solution... I changed to code to:

Private Sub BijkomendeInvoer_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sArt As String
Dim iStart, iEnd As Date

'check for filled combo boxes
'check startdate
If IsNull(Me.Begindatum) Or Me.Begindatum = "" Then
    MsgBox "Gelieve een begindatum in te voeren." & vbCrLf & "Veuillez entrer une date de départ.", vbExclamation
    Exit Sub
Else
    iStart = Me.Begindatum
End If

'check enddate
If IsNull(Me.Einddatum) Or Me.Einddatum = "" Then
    MsgBox "Gelieve een einddatum in te voeren." & vbCrLf & "Veuillez enter une date de fin.", vbExclamation
    Exit Sub
Else
    iEnd = Me.Einddatum
End If

'check to see if start date is less than end date
If iStart > iEnd Then
    MsgBox "De einddatum bevindt zich voor de begindatum." & vbCrLf & "La date de départ se situe après la date de fin.", vbExclamation
    Exit Sub
End If

'check artikel
If IsNull(Me.Artikelnummer) Or Me.Artikelnummer = "" Then
    MsgBox "Gelieve een artikelnummer in te voeren." & vbCrLf & "Veuillez entrer un numéro d'article.", vbExclamation
    Exit Sub
Else
    sArt = Me.Artikelnummer
End If

'Open the table
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblTestBuyBA02")

'add all records here
Do Until iStart = iEnd + 7

    rst.AddNew
    rst("Datum") = iStart
    rst("Artikelnummer") = sArt
    rst.Update
    iStart = iStart + 7

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

MsgBox "De Test Buy werd ingevoerd." & vbCrLf & "Le Test Buy a été inséré.", vbInformation

End Sub

and this seems to work, so I'm going to leave the code the way it is now...

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 with VBA rohit_ghosh Access VBA 7 June 22nd, 2007 05:05 AM
adding multiple records at the same time Vince_421 Access VBA 14 February 1st, 2007 09:28 AM
Adding records to Tables lgpatterson Access VBA 6 March 20th, 2005 06:23 AM
Ouch: 80004005 (adding multiple records) PeterVR Classic ASP Databases 7 September 14th, 2004 06:36 AM
adding records sinner Classic ASP Databases 5 February 25th, 2004 05:12 PM



All times are GMT -4. The time now is 08:12 PM.


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