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

    Tracking Dates (A2000 SR1)

    I have a table "tblTrackingDates"

    In my Database, I need to record several important Dates and Times.
    When an estimate is created
    When An Invoice is created
    When a Vehicle is Collected etc etc etc.

    I have many forms to which I want to export from them to tblTrackingDates , key dates.
    For example:

    frmDetails has a field ([EstimateCreationDate]), when the form closes, I want this date along with (EstimateNo]) and ([Supp]) to be recorded in tblTrackingDates.

    And so the same for various different forms.

    What would be the best way to acheive this via the on close event of a form.
    Thanks in haste
    Dave

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

    Re: Tracking Dates (A2000 SR1)

    I'm thinking possibly an SQL statement

    Would this acheive what I'm trying to do.

    strSQL="SELECT [tblDetails].[EstimateNo], [tblDetails].[Supp], [tblDetails].[EstCreationDate] INTO tblTrackingDates
    FROM tblDetails
    WHERE ((([tblDetails].[EstimateNo])=[forms]![frmDetails]![EstimateNo]) And (([tblDetails].[Supp])=[forms]![frmDetails]![supp]));"

    Dave

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Tracking Dates (A2000 SR1)

    I'd create a sub that created and executed an Append query to your tblTrackingDates. You might call the sub from the AfterInsert event on any form, passing to it certain specific information, like what type of Record are you tracking, the date, etc.
    Something like: AddToTrackingDates "Invoice", [InvoiceNo],[InvoiceDate]
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Tracking Dates (A2000 SR1)

    I'm getting a little lost here.
    Here's what I have so far, an Append query:

    INSERT INTO tblTrackingDates ( EstimateNo, Supp, EstimateCreationDate )
    SELECT [tblDetails].[EstimateNo], [tblDetails].[Supp], [tblDetails].[DateofReferral]
    FROM tblDetails
    WHERE ((([tblDetails].[EstimateNo])=[forms]![frmDetails]![estimateno]) And (([tblDetails].[Supp])=[forms]![frmDetails]![supp]));

    If I were to put this on a button how would I execute it

    Dim strSQL as String
    Dim LinkCriteria as String

    strSQL = "INSERT INTO tblTrackingDates ( EstimateNo, Supp, EstimateCreationDate )
    SELECT [tblDetails].[EstimateNo], [tblDetails].[Supp], [tblDetails].[DateofReferral]
    FROM tblDetails
    WHERE ((([tblDetails].[EstimateNo])=[forms]![frmDetails]![estimateno]) And (([tblDetails].[Supp])=[forms]![frmDetails]![supp]));"

    I'm lost here. I also need the [DateOfReferral] to be enterred only once, will the append do this.

    Thanks
    Dave

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

    Re: Tracking Dates (A2000 SR1)

    I'm getting closer.
    I now have this which does work, as you see I have supressed the Append warnings.
    I just need help a little further.
    I want to modify the code so that it checks "tblTrackingDates" to see if the record already exists.
    With the Append Query, won't the record be continously updated, I would rather the first created record only.

    Regards
    Dave


    DoCmd.SetWarnings False

    DoCmd.RunSQL ("INSERT INTO tblTrackingDates ( 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

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

    Re: Tracking Dates (A2000 SR1)

    I've sorted it.

    Private Sub Command341_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFilter As String
    Dim strSQL As String
    Dim strValue As String
    Set db = CurrentDb
    strFilter = "tblTrackingDates.EstimateNo =" & Forms!frmDetails!EstimateNo & " and tblTrackingDates.Supp = " & Forms!frmDetails.Supp
    strSQL = "Select * from tblTrackingDates where " & strFilter
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    If rst.RecordCount = 0 Then
    DoCmd.RunSQL ("INSERT INTO tblTrackingDates ( EstimateNo, Supp, EstimateCreationDate )SELECT [tblDetails].[EstimateNo], [tblDetails].[Supp], [tblDetails].[DateofReferral]FROM tblDetails WHERE ((([tblDetails].[EstimateNo])=[forms]![frmDetails]![estimateno]) And (([tblDetails].[Supp])=[forms]![frmDetails]![supp]))")
    Else
    'Nothing
    End If

    End Sub

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Tracking Dates (A2000 SR1)

    I was under the impression that this tracking table was intended to track all sorts of dates, from all sorts of other tables. Therefore, it would be nice if you had some sort of generic routine for adding info to it, rather than having a hardcoded version for each of these other tables. Based on that, I suggested you create a public function that would append a record, based on parameters passed to it. Something like this:<pre>Public Sub AddToTrackingTable (ID as variant, Description as Variant, TrackingDate as Date)
    Dim strSQL as string
    strSQL = "INSERT INTO tblTrackingDates ( EstimateNo, Supp, EstimateCreationDate ) " _
    & " VALUES (" & ID & "," & Description & "," & TrackingDate & ")"
    currentdb.execute strsql
    end sub</pre>

    Now, this assumes EstimateNo, Supp, and EstimateCreateDate are actually fields in tblTrackingDates, which I think is not likely if this is to be used by other tables as well. Be that as it may, you call the sub like this:
    AddToTrackingDate me!estimateNO, me!Supp, Me!DateOfReferral


    You
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Tracking Dates (A2000 SR1)

    Mark thanks for the info.
    I've been thinking deeply about this a nd can now foresee problems arising.
    If I am using one table for the tracking dates, when in a multi-user environment, I may have record locking problems.
    So I think I'm better creating a separate form to DLookup the dates I need. I already have the date fields in each table which are automatically created on adding records.
    The table defaults are =Date()

    I need the DLookup now, to pull this info back.

    =DLookUp("DateOfReferral","tblDetails")

    I need to modify the DLookup to include: Where the EstimateNo and Supp are The same as frmDetails.

    =DLookUp("DateOfReferral","tblDetails")Where EstimateNo and Supp = forms!frmDetails!EstimateNo and forms!frmDetails!supp

    What would the syntax be for that.

    Thanks
    Dave

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

    Re: Tracking Dates (A2000 SR1)

    =DLookUp("DateOfReferral","tblDetails","EstimateNo = " & forms!frmDetails!EstimateNo & " and Supp = " & forms!frmDetails!supp)
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Tracking Dates (A2000 SR1)

    The WHERE portion of DLookup is exactly like an SQL WHERE clause except it doesn't include the word "where". So it would look like this:
    =DLookUp("DateOfReferral","tblDetails"), "EstimateNo = forms!frmDetails!EstimateNo and Supp = forms!frmDetails!supp")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Tracking Dates (A2000 SR1)

    Thanks all, the Dlookup has solved my problem.

    Just one thought, I created an unbound form with numerous texts and DLookup's in them to search for information from different tables.
    When loading, the data momentarily lapses, it takes a split second to show the information.
    Is this charactalistic for DLookups and will their be a problem when the tables are populated with thousands of records.
    For now, the DLookup's will serve the purpose, but if anyone knows of a quicker method, I would be very interested.

    Thanks all again
    Regards
    Dave

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Tracking Dates (A2000 SR1)

    Dlookups are slow, and they seem to be the last think Access does when it displays a record on a form. If you were using a bound form, you could put the Dlookup in the query. There is a slight loss of speed going to a new record, but then the screen is populated quickly, without that pause at the Dlookup field.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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