Add records between dates 1

[BrianLe]

In AC97, what do I need to specify the date to get the records into the tblMWHLongRangeOrig for the block of dates between tbxStartDate and tbxEndDate, provided there is not already a record for the date? On frmMWHLongRangeOrig I have tbxStartDate, tbxEndDate, tbxMWHUnit1, tbxMWHUnit2, and tbxUnit3. I have following code.

Private Sub cmdAddRecord_Click()

On Error GoTo Err_cmdAddRecord_Click
 'DoCmd.SetWarnings False

 
   Dim tbx1 As TextBox, tbx2 As TextBox, tbx3 As TextBox, tbx4 As TextBox, tbx5 As TextBox, tbx6 As TextBox, SQL As String, DQ As String, itm As Variant
   
   
   Set tbx1 = Me!tbxStartDate
   Set tbx2 = Me!tbxEndDate
   Set tbx3 = Me!tbxMWHUnit1
   Set tbx4 = Me!tbxMWHUnit2
   Set tbx5 = Me!tbxMWHUnit3
   
   DQ = """"
  
      
      
 SQL = "INSERT INTO tblMWHLongRangeOrig (MWHDate,MWHUnit1ProjOrig,MWHUnit2ProjOrig,MWHUnit3ProjOrig) " & _
                 "VALUES(#" & tbx1.Value & "#," _
                            & DQ & tbx3.Value & DQ & "," _
                            & DQ & tbx4.Value & DQ & "," _
                            & DQ & tbx5.Value & DQ & ");"

          
         Debug.Print SQL
         DoCmd.RunSQL SQL
       
   
   Set tbx1 = Nothing
   Set tbx2 = Nothing
   Set tbx3 = Nothing
   Set tbx4 = Nothing
   Set tbx5 = Nothing
   
Exit_cmdAddRecord_Click:
    Exit Sub

Err_cmdAddRecord_Click:
   MsgBox Err.Description
    Resume Exit_cmdAddRecord_Click
    


End Sub

[code/]

I think I need some type of "For Each" statement.

Thanks,

Brian



 I want to add MWHDate, MWHUnit1Orig, ... to the table  that I want add records to.

 

[dhookom]

If I understand correctly, try:

Dim datThis as Date
For datThis =  Me!tbxStartDate to Me!txtEndDate

 SQL = "INSERT INTO tblMWHLongRangeOrig (MWHDate,MWHUnit1ProjOrig,MWHUnit2ProjOrig,MWHUnit3ProjOrig) " & _
                 "VALUES(#" & datThis & "#," _
                            & DQ & tbx3.Value & DQ & "," _
                            & DQ & tbx4.Value & DQ & "," _
                            & DQ & tbx5.Value & DQ & ");"
Next

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BrianLe]

Works great! Had to set the "Indexed" field property for MWHDate in tblMWHLongRangeOrig to "Yes(No Duplicates)" so wouldn't add records with duplicate dates. Also set Cmd.SetWarnings False to stop getting all the warnings.

Thanks, Brian