Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RunSQL withcurrent Date() (A2000 SR1)

    Instead of a field value [EstimateCreationDate] inserting into tblDates, Can the current date be inserted into tblDates instead.
    How would the SQL statement need to be changed to acheive this.
    I'm trying to populate a table "tblDates" after certain triggers, ie each time the same report is printed, a field value is changed, a combo value changes etc.
    In some of my tables this is quite easy because they have default =Date() fields in them.
    Of course a record is only created once, so keeps the original date.

    Certain actions are required to be recorded by date.

    Dim db As DAO.Database
    Dim strSQL As String
    Set db = CurrentDb
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("INSERT INTO tblDates ( EstimateNo, Supp, EstimateCreationDate )SELECT [tblDetails].[EstimateNo], [tblDetails].[Supp], [tblDetails].[DateofReferral]FROM tblDetails WHERE ((([tblDetails].[EstimateNo])=[forms]![frmDetails]![estimateno]) And (([tblDetails].[Supp])=[forms]![frmDetails]![supp]))")
    DoCmd.SetWarnings True


    Regards
    Dave

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    You can use the Date() function for this:

    DoCmd.RunSQL "INSERT INTO tblDates ( EstimateNo, Supp, EstimateCreationDate ) SELECT [tblDetails].[EstimateNo], [tblDetails].[Supp], Date() FROM tblDetails WHERE ((([tblDetails].[EstimateNo])=[forms]![frmDetails]![estimateno]) And (([tblDetails].[Supp])=[forms]![frmDetails]![supp]))"

    (there is no need to enclose the SQL statement in parentheses)

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: RunSQL withcurrent Date() (A2000 SR1)

    I don't like DoCmd.RunSQL, but however you run it, chane your sSQL string to this:

    "INSERT INTO tblDates ( EstimateNo, Supp, EstimateCreationDate )SELECT [tblDetails].[EstimateNo], [tblDetails].[Supp], <font color=blue>#" & Date() & "#</font color=blue> FROM tblDetails WHERE ((([tblDetails].[EstimateNo])=[forms]![frmDetails]![estimateno]) "And (([tblDetails].[Supp])=[forms]![frmDetails]![supp]))"
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    Thanks Hans & Charlotte, this solved my prob.

    Charlotte, Can you tell me why you have reservations with the DoCmd.RunSQL and which way would you recommend.

    I have another SQL which would quite handy to know.

    I use an internal messaging system, (A bit like an internal E.Mail system.)
    When a job is complete, a user has to manually write a completion slip, if he forgets to write one, reception pull a strip off him, Its funny to watch sometimes, but not for him. Anyway.

    He has a tickbox, Ideally if he ticks this box, it would be great if this triggered an SQL, populating another table with pre-determinned text. ie.

    On click
    Insert into tblWIPREMINDER,WhoFor,From,Now(),Message.

    WhoFor = "KW"
    From ="DW"
    Now() = CurrentDate&Time
    Message = "This vehicle is complete"

    Is this possible and how.

    Once again, thanks to both for the help.

    Dave

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    Charlotte
    I'd also be interested in your thoughts on the DoCmd.RunSQL item. I use this quite a lot in my db.
    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: RunSQL withcurrent Date() (A2000 SR1)

    There isn't anything inherently wrong with it. I just prefer to execute the SQL using a method that can provide feedback like records affected, which can't be done with DoCmd.RunSQL.
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    What method is that Charlotte?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: RunSQL withcurrent Date() (A2000 SR1)

    I usually create a querydef object and use its Execute method. The querydef also has a RecordsAffected property that will tell you how many records were appended, updated, deleted, etc.
    Charlotte

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    Thank you, that may certainly come in handy.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    I've modified the SQL to include the field [Status] & Forms!frmDetails!cmbStatus so I can pull info later with a DLookup.
    It appends one row as it should but puts the Date() in "tblDetails.[Status].
    I guess whats happening is the [AuthorisationDate] & [Status] fields are getting swapped around.

    What have I done wrong ???
    Thanks
    Dave


    DoCmd.RunSQL "INSERT INTO tblDates ( EstimateNo, Supp, AuthorisationDate, status ) " & " SELECT [tblDetails].[EstimateNo], " & " [tblDetails].[Supp], " & "[tblDetails].[status], " & " date() " & " FROM tblDetails " & " WHERE (((([tblDetails].[EstimateNo])=[forms]![frmDetails]![estimateno]) And " & " ([tblDetails].[Supp])=[forms]![frmDetails]![supp] and " & "([tblDetails].[STATUS])=[forms]![frmDetails]![cmbstatus]))"

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    If you read your own code carefully, you'll see that first, you enumerate the fields to be inserted in this order:

    ( EstimateNo, Supp, AuthorisationDate, status )

    and then in the SELECT part, you switch the date and status:

    SELECT [tblDetails].[EstimateNo], " & " [tblDetails].[Supp], " & "[tblDetails].[status], " & " date() "

    So you must change the order in one of these places.

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    Hans
    Thanks I sorted it by swapping the Insert section.
    Now that it works, I find it not very efficient.
    The first time it runs, it pastes 0 records, then when run again it Pastes 1
    I've left the Setwarnings remmed out so I can see this happening.
    Can you see anything in my code which is preventing the SQL functioning correctly the first time.

    Dave


    Private Sub Command0_Click()
    Dim db As DAO.Database
    Dim strSQL As String
    Set db = CurrentDb
    Forms!frmDetails!cmbStatus = "A"
    'DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblDates ( EstimateNo, Supp, Status, AuthorisationDate ) " & " SELECT [tblDetails].[EstimateNo], " & " [tblDetails].[Supp], " & "[tblDetails].[status], " & " date() " & " FROM tblDetails " & " WHERE (((([tblDetails].[EstimateNo])=[forms]![frmDetails]![estimateno]) And " & " ([tblDetails].[Supp])=[forms]![frmDetails]![supp] and " & "([tblDetails].[STATUS])=[forms]![frmDetails]![cmbstatus]))"
    'DoCmd.SetWarnings True
    Forms!frmDetails!DummyEst.SetFocus
    DoCmd.Close acForm, Me.name
    End Sub

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    Hi Dave,

    This is just a guess. You might try inserting a statement <font face="Georgia">RunCommand acCmdSaveRecord</font face=georgia> before the DoCmd.RunSQL to make sure that all values have been updated. Otherwise, the SQL might not pick up the modified value of cmbStatus.

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    Thanks for the help, Hans.
    After manipulating the code I finally came up with this, it works every time.
    Dave


    Private Sub Command0_Click()
    Forms!frmDetails!cmbStatus = "A"
    Dim db As DAO.Database
    Set db = CurrentDb
    RunCommand acCmdSaveRecord
    'DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblDates ( EstimateNo, Supp, Status, AuthorisationDate ) " & " SELECT [tblDetails].[EstimateNo], " & " [tblDetails].[Supp], " & "[tblDetails].[status], " & " date() " & " FROM tblDetails " & " WHERE (((([tblDetails].[EstimateNo])=[forms]![frmDetails]![estimateno]) And " & " ([tblDetails].[Supp])=[forms]![frmDetails]![supp] and " & "([tblDetails].[STATUS])=[forms]![frmDetails]![cmbstatus]))"
    'DoCmd.SetWarnings True
    Forms!frmDetails!DummyEst.SetFocus
    DoCmd.Close acForm, Me.name
    End Sub

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RunSQL withcurrent Date() (A2000 SR1)

    Hello Dave,

    Two final remarks:

    (1) In the code you posted for Command0_OnClick, the variable db is never used. So the lines

    Dim db As DAO.Database
    Set db = CurrentDb

    are superfluous and can be omitted, unless you posted only part of the code and the variable db is actually used.

    (2) The SQL string can be simplified to

    "INSERT INTO tblDates (EstimateNo, Supp, Status, AuthorisationDate) SELECT EstimateNo, Supp, Status, Date() FROM tblDetails WHERE EstimateNo = Forms!frmDetails!EstimateNo And Supp = Forms!frmDetails!Supp And Status = Forms!frmDetails!cmbStatus"

    Of course, if you don't like such a long line, you can break it:

    "INSERT INTO tblDates (EstimateNo, Supp, Status, AuthorisationDate) " & _
    "SELECT EstimateNo, Supp, Status, Date() FROM tblDetails " & _
    "WHERE EstimateNo = Forms!frmDetails!EstimateNo And Supp = Forms!frmDetails!Supp And Status = Forms!frmDetails!cmbStatus"

Page 1 of 2 12 LastLast

Posting Permissions

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