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

    Change Code & SQL (A2000 SR1)

    I need to change my Insert into code slightly to produce a different result in a table.
    The following code works ok but produces a new record from the SQL .
    Refer to figure 1 in the attachment.You will see duplicate records.
    This causes a problem elsewhere in the database with unbound DlookUp fields.
    Figure 2 would be much better where all the dates are added to an existing record.
    So basically the code should look in the table for the record that matches frmDetails!EstimateNo & Supp, add it if it exists and append the date to the table. When its run again, the record already exists and just appends.


    Private Sub Command0_Click()
    Forms!FrmDetails!Status = "A"
    RunCommand acCmdSaveRecord
    Forms!FrmDetails.Refresh
    DoCmd.SetWarnings False
    DoCmd.RunSQL "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!Status"
    DoCmd.SetWarnings True
    Forms!FrmDetails!DummyEst.SetFocus
    DoCmd.Close acForm, Me.Name
    End Sub
    Attached Files Attached Files

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

    Re: Change Code & SQL (A2000 SR1)

    Here's what I have so far:

    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 = "tblDates.EstimateNo = " & Forms!FrmDetails!EstimateNo & " And tblDates.Supp = " & Forms!FrmDetails!Supp
    strSQL = "Insert Into tblDates Where " & strFilter
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    If rst.RecordCount = 0 Then ??????????????



    Regards
    Dave

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

    Re: Change Code & SQL (A2000 SR1)

    Dave,

    You wrote "add it if it exists". Don't you mean "add it if it doesn't exist yet" ?

    This is just off the top of my head, I didn't test it:

    ...
    Dim strWhere As String
    Dim strSQL As String
    strWhere = "EstimateNo = Forms!frmDetails!EstimateNo And Supp = Forms!frmDetails!Supp And Status = Forms!frmDetails!Status"
    If IsNull(DLookup("EstimateNo", "tblDates", strWhere) Then
    strSQL = "INSERT INTO tblDates (EstimateNo, Supp, Status, AuthorisationDate) SELECT EstimateNo, Supp, Status, Date() FROM tblDetails WHERE " & strWhere
    Else
    strSQL = "UPDATE tblDates SET AuthorisationDate = Date() WHERE " & strWhere
    End If
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    ...

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

    Re: Change Code & SQL (A2000 SR1)

    I'm getting
    Expected list or separator on this line Hans

    If IsNull(DLookup("EstimateNo", "tblDates", strwhere)Then


    Regards
    Dave

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

    Re: Change Code & SQL (A2000 SR1)

    Dave,

    As I mentioned, it was air code. I forgot a closing bracket; it should be

    If IsNull(DLookup("EstimateNo", "tblDates", strwhere)) Then

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

    Re: Change Code & SQL (A2000 SR1)

    Hans
    seeing as it is "Air" code, it works.
    I tried brackets in all kinds of places except for the obvious !!.
    Thanks
    Ever greatful
    dave

Posting Permissions

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