Hi,

I have a table with the following fields:

Week
Year
Artikel ID
some other fields to, but they are not important here...

A Form is made to be able to insert those 3 fields in the table. The combination of all 3 of them is the primary key, so you can only have the artikel once in a given week of that year...

Normally I would have to insert the records 1 by 1. What I would like to know: Is it possible to create a form were I can choose for example to insert artikel number 2 in the year 2007 for the weeks 25, 26, 27, 28, and more if I have to and let acces create the number of record automatically. So If I want it to be created for the weeks 25 till 28 acces would have to create 4 records by itself instead of me creating 4 record one after the other...

Does anyone know if this is possible and if yes how?

Thanks

Yes, this is possible.

I would build a form for doing this chore. Call it frmInput

Create a combo box with the week numbers 1 - 52 as choices, and call it cboStart, then create another combo box with the same information, and call it cboEnd. I am assuming that these are Number data types. If they are not, we will need to modify the code.

Then create a combo box with the year numbers you are going to be working with, like "2005";"2006";"2007"; etc. Call it cboYear. I will assume this is Text data type.

Then create a combo box that you either have entered likely Artikel numbers in, or looks up to the artikel numbers in a table. I am not sure what this number is or where it comes from. You may also just want to create a text box so you can just type in the artikel number. Call it txtArt. I will assume this is a Text data type as well.

Then put a button on the form at the end of the line, without using the wizard. I will give you the code in ADO, since I do mostly Access/SQL, but another poster could give you DAO, if you want to use that.

'----------------------------------
'Button On Click Event

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

'Check for filled combo boxes
'Check Start week
If IsNull(Me.cboStart) Or Me.cboStart = "" Then
   MsgBox "Please enter a start week.", vbCritical
   Exit Sub
Else
   iStart = Me.cboStart
End If

'Check End Week
If IsNull(Me.cboEnd) Or Me.cboEnd = "" Then
   MsgBox "Please enter an end week.", vbCritical
   Exit Sub
Else
   iEnd = Me.cboEnd
End If

'Check to see if the Start week is less than the End week
If iStart > iEnd Then
   MsgBox "Stop messing around and fix the Start and End Weeks!", vbCritical
   Exit Sub
Else
   i = iEnd - iStart 'number of records
End If

'Check Year
If IsNull(Me.cboYear) Or Me.cboYear = "" Then
   MsgBox "Please enter a Year.", vbCritical
   Exit Sub
Else
   sYear = Me.cboYear
End If

'Check Artikel
If IsNull(Me.txtArt) Or Me.txtArt = "" Then
   MsgBox "Please enter an artikel.", vbCritical
   Exit Sub
Else
   sArt = Me.txtArt
End If

'Define recordset to ADD new records with this data.
sSQL = "SELECT * FROM tblYourTableNameWhichYouForgotToPost"

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

'Add First record
   rs.AddNew
   rs("Week") = iStart
   rs("Year") = sYear
   rs("Artikel") = sArt
   rs.Update

'increment iStart
 iStart = iStart + 1

'Add follow up records
Do Until iStart = iEnd

   rs.AddNew
   rs("Week") = iStart
   rs("Year") = sYear
   rs("Artikel") = sArt
   rs.Update
   iStart = iStart + 1

Loop

rs.Close

'------------------------------------------


When you want to add a bunch of records, open the form, select the start week, the end week, the year, and type in an artikel number and press the button. This will add the proper number of records.


Did that work for you?


mmcdonal

Okay, I have to start reviewing my code.

There is no need for the i counter. Remove that, and the Else statement in the area where you check the start and end weeks.

Then do this:


'Define recordset to ADD new records with this data.
sSQL = "SELECT * FROM tblYourTableNameWhichYouForgotToPost"

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

'Add All records here
Do Until iStart = iEnd

   rs.AddNew
   rs("Week") = iStart
   rs("Year") = sYear
   rs("Artikel") = sArt
   rs.Update
   iStart = iStart + 1

Loop

rs.Close

HTH

mmcdonal

Ok, still getting a problem. I'm going to put the code here again to make sure I have inputted the right one...

Private Sub cmdGegevensInvoeren_Click()

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

'check for filled combo boxes
'check start week
If IsNull(Me.WeeknrStart) Or Me.WeeknrStart = "" Then
    MsgBox "Gelieve een weeknummer als start in te geven.", vbCritical
    Exit Sub
End If

'check end week
If IsNull(Me.WeeknrEinde) Or Me.WeeknrEinde = "" Then
    MsgBox "Gelieve een weeknummer als einde in te geven.", vbCritical
    Exit Sub
End If

'check to see if the start week is less than the end week
If iStart > iEnd Then
    MsgBox "Het weeknummer als einde bevindt zich voor het weeknummer als begin. Gelieve dit te wijzigen.", vbCritical
    Exit Sub
End If

'check year

If IsNull(Me.Jaar) Or Me.Jaar = "" Then
    MsgBox "Gelieve een jaar in te vullen", vbCritical
    Exit Sub
Else
    sYear = Me.Jaar
End If

'check artikel

If IsNull(Me.ArtikelNummer) Or Me.ArtikelNummer = "" Then
    MsgBox "Gelieve een artikelnummer in te geven.", vbCritical
    Exit Sub
Else
    sArt = Me.ArtikelNummer
End If

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

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

'Add all records here
Do Until iStart = iEnd

    rs.AddNew
    rs("Weeknummer") = iStart
    rs("Jaar") = sYear
    rs("Artikel_nummer") = sArt
    rs.Update
    iStart = iStart + 1

Loop

rs.Close

End Sub

I think the code is good, because I get a record in the table, the problem I'm still having is that on the form if I select a week number in either one of the combo boxes (doesn't matter if I choose the starting week or the end week), the other combo box automatically takes the same value as the first combo box. So if I select for exemple 6 in the start week combo box, and then I select 10 as the end week, the combo box for start week suddenly shows 10 instead of 6... If I try to move this one to 6 again, then the end week has become 6 instead of 10... this is making me almost crazy...

Could this come from the code, or is this problem coming from somewhere else?

Check the After Update events of the combo boxes to see if they are not sending code to one another.


mmcdonal

I checked, but the after update events of both or empty...

any other idea?

Is there any other code on the form?

mmcdonal

These are all the codes of the form...

Option Compare Database
Option Explicit

Private Sub cmdGegevensInvoeren_Click()

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

'check for filled combo boxes
'check start week
If IsNull(Me.WeeknrStart) Or Me.WeeknrStart = "" Then
    MsgBox "Gelieve een weeknummer als start in te geven.", vbCritical
    Exit Sub
End If

'check end week
If IsNull(Me.WeeknrEinde) Or Me.WeeknrEinde = "" Then
    MsgBox "Gelieve een weeknummer als einde in te geven.", vbCritical
    Exit Sub
End If

'check to see if the start week is less than the end week
If iStart > iEnd Then
    MsgBox "Het weeknummer als einde bevindt zich voor het weeknummer als begin. Gelieve dit te wijzigen.", vbCritical
    Exit Sub
End If

'check year

If IsNull(Me.Jaar) Or Me.Jaar = "" Then
    MsgBox "Gelieve een jaar in te vullen", vbCritical
    Exit Sub
Else
    sYear = Me.Jaar
End If

'check artikel

If IsNull(Me.ArtikelNummer) Or Me.ArtikelNummer = "" Then
    MsgBox "Gelieve een artikelnummer in te geven.", vbCritical
    Exit Sub
Else
    sArt = Me.ArtikelNummer
End If

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

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

'Add all records here
Do Until iStart = iEnd

    rs.AddNew
    rs("Weeknummer") = iStart
    rs("Jaar") = sYear
    rs("Artikel_nummer") = sArt
    rs.Update
    iStart = iStart + 1

Loop

rs.Close

DoCmd.GoToRecord , , acNewRec

End Sub

Private Sub Command13_Click()

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

'check for filled combo boxes
'check start week
If IsNull(Me.WeeknrStart) Or Me.WeeknrStart = "" Then
    MsgBox "Gelieve een weeknummer als start in te geven.", vbCritical
    Exit Sub
End If

'check end week
If IsNull(Me.WeeknrEinde) Or Me.WeeknrEinde = "" Then
    MsgBox "Gelieve een weeknummer als einde in te geven.", vbCritical
    Exit Sub
End If

'check to see if the start week is less than the end week
If iStart > iEnd Then
    MsgBox "Het weeknummer als einde bevindt zich voor het weeknummer als begin. Gelieve dit te wijzigen.", vbCritical
    Exit Sub
End If

'check year

If IsNull(Me.Jaar) Or Me.Jaar = "" Then
    MsgBox "Gelieve een jaar in te vullen", vbCritical
    Exit Sub
Else
    sYear = Me.Jaar
End If

'check artikel

If IsNull(Me.ArtikelNummer) Or Me.ArtikelNummer = "" Then
    MsgBox "Gelieve een artikelnummer in te geven.", vbCritical
    Exit Sub
Else
    sArt = Me.ArtikelNummer
End If

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

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

'Add all records here
Do Until iStart = iEnd

    rs.AddNew
    rs("Weeknummer") = iStart
    rs("Jaar") = sYear
    rs("Artikel_nummer") = sArt
    rs.Update
    iStart = iStart + 1

Loop

rs.Close

DoCmd.Close
DoCmd.OpenForm "frmInlogBAM06", acNormal

End Sub

Private Sub Form_Current()

Dim iweek, iday As Integer

iweek = DatePart("w", Date)
iday = DatePart("d", Date)

If iday = 1 Then
    iweek = iweek - 1
End If

Me.WeeknrStart = iweek

End Sub

Private Sub WeeknrEinde_NotInList(NewData As String, Response As Integer)

    MsgBox "Het ingevulde weeknummer is niet geldig." _
    & vbCrLf & "Gelieve een keuze te maken uit de lijst.", vbExclamation, _
    "Ongeldige invoer"
    Response = acDataErrContinue

End Sub

Private Sub WeeknrStart_NotInList(NewData As String, Response As Integer)

    MsgBox "Het ingevulde weeknummer is niet geldig." _
    & vbCrLf & "Gelieve een keuze te maken uit de lijst.", vbExclamation, _
    "Ongeldige invoer"
    Response = acDataErrContinue

End Sub
Private Sub cmdTerugNaarVorigMenuZonderInvoerGegevens_Click()
On Error GoTo Err_cmdTerugNaarVorigMenuZonderInvoerGegevens_Clic k

    Dim stDocName As String
    Dim stLinkCriteria As String

    DoCmd.Close acForm, "frmTestBuyBA06InvoerBAM"
    stDocName = "frmInlogBAM06"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdTerugNaarVorigMenuZonderInvoerGe:
    Exit Sub

Err_cmdTerugNaarVorigMenuZonderInvoerGegevens_Clic k:
    MsgBox Err.Description
    Resume Exit_cmdTerugNaarVorigMenuZonderInvoerGe

End Sub

I have used the code you gave me twice, because the first one makes it able to input another record again, the second one to input the record and to close and go back to the menu...

First of all, you need to take the Start and End weeks in their variables. Change this:
'check for filled combo boxes
'check start week
If IsNull(Me.WeeknrStart) Or Me.WeeknrStart = "" Then
    MsgBox "Gelieve een weeknummer als start in te geven.", vbCritical
    Exit Sub
End If

'check end week
If IsNull(Me.WeeknrEinde) Or Me.WeeknrEinde = "" Then
    MsgBox "Gelieve een weeknummer als einde in te geven.", vbCritical
    Exit Sub
End If

To this:

'check for filled combo boxes
'check start week
If IsNull(Me.WeeknrStart) Or Me.WeeknrStart = "" Then
    MsgBox "Gelieve een weeknummer als start in te geven.", vbCritical
    Exit Sub
Else
  iStart = Me.WeeknrStart
End If

'check end week
If IsNull(Me.WeeknrEinde) Or Me.WeeknrEinde = "" Then
    MsgBox "Gelieve een weeknummer als einde in te geven.", vbCritical
    Exit Sub
Else
  iEnd = Me.WeeknrEinde
End If


Let me read more.

mmcdonal

Remove this code from the form's On Current event:

Private Sub Form_Current()

Dim iweek, iday As Integer

iweek = DatePart("w", Date)
iday = DatePart("d", Date)

If iday = 1 Then
    iweek = iweek - 1
End If

Me.WeeknrStart = iweek

End Sub


In fact, just remove the entire On Current event.

Let me read more.

mmcdonal