Inserting multiple same records with different date

[shod90]

Dear gents,
Um trying to edit the current form for users which is
Project ID
Work items
Description
Date
Hours

Now if they are having same task to do for 3 days , they will enter the data 3 times exactly , I got an idea to make two field for dates , date from and date to , and what between them will be save automatically with the same data in other fields , can anyone help me to do that task ?


Thanks

[ranman256]

the form will have textboxes for all items including, txtStartDate, and txtEndDate.
click the button to run the query for every date

sub btnInsertDates_click()
dim vDate
dim sSql as string

docmd.setwarnings false
vDate = txtStartDate
while vDate <= txtEndDate
   sSql = "insert into table ([Project ID], [Work items],Description,Date,Hours) values (" & txtID & ",'" & txtWork & "',#"  & vDate & "#," & txtHrs & ")"
   docmd.runSql sSql
   vDate = DateAdd("d",1,vDate)
wend
docmd.setwarnings true
end sub

[JoeM]

My solution is very similar to ranman's. But since I have been working on it for a while, I will post it also.
The key is to create an unbound form, with the following text boxes:
- txtProjectID (I assumed text)
- txtWorkItems (assumed text)
- txtDescription (assumed text)
- txtStartDate (date)
- txtEndDate (date)
- txtHours (assumed number)

Then I added a command button named "cmdAddRecords".

You would also need to replace "TableName" with whatever the name of your table is.
So, here is the code behind the command button that would add all those records.

Private Sub cmdAddRecords_Click()

    Dim mySQL As String
    Dim myDate As Date

'   Check to make sure that end date is not before start date
    If Me.txtEndDate < Me.txtStartDate Then
        MsgBox "Please try again!", vbOKOnly, "Invalid Date Entries!"
    Else
'       Loop through dates
        myDate = Me.txtStartDate
        Do Until myDate > Me.txtEndDate
'           Build insert SQL code
            mySQL = "INSERT INTO TableName ([Project ID], [Work Items], Description, [Date], Hours) "
            mySQL = mySQL & "VALUES('" & Me.txtProjectID & "','" & Me.txtWorkItems & "','" & Me.txtDescription & "',#" & myDate & "#," & Me.txtHours & ")"
'           Run query
            'MsgBox mySQL
            DoCmd.SetWarnings False
            DoCmd.RunSQL mySQL
            DoCmd.SetWarnings True
'           Add one to date
            myDate = myDate + 1
        Loop
        MsgBox "Done!"
    End If
   
End Sub

You can add whatever checks and validations you might need. I just added one, to make sure that txtEndDate is not before txtStartDate.

[shod90]

thanks for the fast responses , But i think it need little change since i have an autonumber ID in my table .
I got that error when i turn setwarnings to true ..


Any idea ?

[JoeM]

If you have an Autonumber field, you will leave it out of the Append Query SQL code (as Access will assign it automatically).