Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2011
    Posts
    36

    Unanswered: Consecutive Dates

    I have a text box with a date picker that once updated with a date will update 6 other text boxes with consecutive dates, whats the best way to do this?

    Thanks

  2. #2
    Join Date
    Sep 2011
    Posts
    36
    Thinking about it im confused about how to do this entire form, Basically what I have got is a row of 7 boxes each that represents a consecutive day of the week in date format. Each of these 7 rows has a cloumn of 4 boxes that represent positions that need filling on a daily basis at work, so will have a name entered into it. The date wil be the primary key in the database along with the 4 names in each position but the form will make 7 records at once on saving. Is this possible? Once form saving 7 records?

    Thanks

  3. #3
    Join Date
    Sep 2011
    Posts
    36
    Could I use 7 different SQL insert statments one for each day of the week (seperate record)

    Something Like this


    strSQL = "Insert Into HaemDailyRota1(Date1, TP1, TP2, TP3, TP4)" & _
    "Values("MonDate", "FM1", "FM2", "FM3", "FM4")

    strSQL = "Insert Into HaemDailyRota1(Date1, TP1, TP2, TP3, TP4)" & _
    "Values("TueDate", "FT1", "FT2", "FT3", "FT4")

    strSQL = "Insert Into HaemDailyRota1(Date1, TP1, TP2, TP3, TP4)" & _
    "Values("WedDate", "FW1", "FW2", "FW3", "FW4")

    strSQL = "Insert Into HaemDailyRota1(Date1, TP1, TP2, TP3, TP4)" & _
    "Values("ThuDate", "FTH1", "FTH2", "FTH3", "FTH4")

    strSQL = "Insert Into HaemDailyRota1(Date1, TP1, TP2, TP3, TP4)" & _
    "Values("FriDate", "FF1", "FF2", "FF3", "FF4")

    strSQL = "Insert Into HaemDailyRota1(Date1, TP1, TP2, TP3, TP4)" & _
    "Values("SatDate", "FSA1", "FSA2", "FSA3", "FSA4")

    strSQL = "Insert Into HaemDailyRota1(Date1, TP1, TP2, TP3, TP4)" & _
    "Values("SunDate", "FSU1", "FSU2", "FSU3", "FSU4")

    Debug.Print strSQL
    cnn.Execute strSQL
    Response = acDataErrAdded

    My primary key is a date how would I Identify this?
    Last edited by srichar3; 09-22-11 at 18:36.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could do it like that, but with the instruction cnn.Execute strSQL after each instruction changing the value of strSQL, which should be (pay attention to the spaces and the quotes):
    Code:
    strSQL = "Insert Into HaemDailyRota1 (Date1, TP1, TP2, TP3, TP4) " & _
             "Values ('MonDate', 'FM1', 'FM2', 'FM3', 'FM4')"
    And so on...

    If you want a more compact code, you could use:
    Code:
    Const c_SQL As String = "Insert Into HaemDailyRota1 (@D, TP1, TP2, TP3, TP4) Values ('MonDate' '@T1', '@T2', '@T3', '@T4')"
    Dim i As Integer
    
    For i = 1 To 7
        CurrentDb.Execute = Replace(Replace(c_SQL, "@T", Choose(i, "FM", "FT", "FW", "FTH", "FF", "FSA", "FSU")), "@D", Me.Date1.Value)
    Next i
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    36
    Hi Thanks for that, im going to have to satrt paying you at this rate do I need any special syntax for the date fields on either the source or destination? Date1 and Mondate for example in the first SQL statement.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Do not worry, I'll send you the bill!

    The same rule applies everywhere in Access: If the column (field) of the table is defined as of Date/Time type, the only valid format is mm/dd/yyyy (month/day/year), while it can be displayed differently, according to the settings in the Control Panel of Windows.

    The only confusing feature is that Access can perform some implicit conversions that are sometimes erroneous (is 4/5/2011 the fourth of May or the fifth of April?). You can force the conversion to the correct type and value by using: "#" & Format(MyDate, "mm/dd/yyyy") & "#" or, in some cases, by performing an explicit casting using CDate().
    Have a nice day!

  7. #7
    Join Date
    Sep 2011
    Posts
    36
    Tried several different formats on your first example and just keep getting data type missmatch, im obviously not getting the syntax right.

    When trying your compact example I get argument not optional on the "CurrentDb.Execute = " Bit

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the definiton for Date1, TP1, TP2, TP3 and TP4 into the table?
    Have a nice day!

  9. #9
    Join Date
    Sep 2011
    Posts
    36
    Date1 is defined as a date and the others are strings, is this what you mean?

    Sorry I'm a complete novise at this.

    For some reason when it errors on this form it doesn't go into Debug mode so I cannot see which parameter it doesn't like.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try compiling the code
    there may be a problem elsewhere which Access is whinging about
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2011
    Posts
    36
    So where would I need to define the correct date format for my table field Date1? do I need to to this before the SQL statment or on the SQL statement line?

  12. #12
    Join Date
    Sep 2011
    Posts
    36
    Heres my code for a single SQL any suggestion appreciated

    Private Sub Command299_Click()
    Dim cnn As New ADODB.Connection
    Dim Date1 As Date
    Dim strSQL As String

    Monday = "#" & Format(Me.Monday.Value, "mm/dd/yyyy") & "#"

    Dim bytUpdate As Byte


    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection

    bytUpdate = MsgBox("Do you want to add " & _
    Monday.Value & " to the list?", _
    vbYesNo, "Non-list item!")
    If bytUpdate = vbYes Then


    strSQL = "Insert Into HaemDailyRota1 (Date1, Early, Late, LateLate, Nights) " & _
    "Values ('Monday', 'FM1', 'FM2', 'FM3', 'FM4')"

    Debug.Print strSQL
    cnn.Execute strSQL
    Response = acDataErrAdded

    ElseIf bytUpdate = vbNo Then

    Response = acDataErrContinue

    Me!Monday.Undo

    End If

    Exit Sub

    ErrHandler:

    MsgBox Err.Number & ": " & Err.Description, _
    vbOKOnly, "Error"

    End Sub

    Thanks

  13. #13
    Join Date
    Sep 2011
    Posts
    36
    Just done a little test with the following statement

    strSQL = "INSERT INTO HaemDailyRota1([Date1], Early, Late, LateLate, Nights)" & _
    "VALUES ('20/07/2011', 'FM1', 'FM2', 'FM3', 'FM4')"

    I typed the date in, instead of directing the date from my form, and the problem is its just using the values I have typed in, its not directed to the fields from my form. Its just writing FM1, FM2 etc into my table, Howdo I point it at my form?

    Thanks

  14. #14
    Join Date
    Sep 2011
    Posts
    36
    Going to answer my own question here, finnally got it working with this code,

    strSQL = "INSERT INTO HaemDailyRota1(Date1, Early, Late, LateLate, Nights)" & _
    "Values ('" & Monday & "','" & FM1 & "','" & FM2 & "','" & FM3 & "','" & FM4 & "');"

    So back to my original question how can I update 6 consecutive date boxes by just changing one?

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •