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

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

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?

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

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