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

    Update Table (A2k)

    I'm revamping my Estimating form and Parts form.
    I use an SQL to update tblParts with data from tblEstimateDetails:

    Private Sub New_AfterUpdate()
    If Me.New = 0.01 Then
    Me.NewYesNo = True
    Me.New = 0
    End If
    If Me.New > 0.01 Then
    Me.NewYesNo = True
    End If
    DoCmd.RunCommand acCmdSaveRecord
    Dim strSQL As String
    strSQL = "INSERT INTO tblParts ( EstimateNo, Supp, Code, Item )SELECT tblEstimateDetails.EstimateNo, tblEstimateDetails.Supp, tblEstimateDetails.Code, tblEstimateDetails.Item FROM tblEstimateDetails WHERE (((tblEstimateDetails.NewYesNo)=True));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings True
    DoCmd.RunCommand acCmdSaveRecord
    Forms!frmEstimateDetails!sbfPartsDetails.Requery
    Forms!frmEstimateDetails!sbfOtherDetails.Requery
    End Sub


    This works fine, but need the code to only update the parts table with data that is not already in there.
    I'll explain.
    If I enter FB (Front Bumper) and run the code, the table holds FB (Front Bumper)
    If I add a part OFW (O/S/F Wing) after the code has updated and requeried, the table holds:

    FB (Front Bumper)
    FB (Front Bumper)
    OFW (O/S/F Wing)

    Two Front Bumpers!!

    I may have covered something similar to this in the forum before.

    I think the code needs to check the recordset to see if any values are already in the table for this partiucular [EstimateNo] & [Supp] and only allow new data which is not duplicated.

    A little help along the way would be helpful.

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

    Re: Update Table (A2k)

    Here's what I've come up with so far.
    To check if the part code exists After Update of the cboCode combo:
    The idea is to prevent the code from duplication, before it happens.
    This code also produces the same error on the Before Update

    Private Sub cboCode_AfterUpdate()
    Dim PartCheck As Integer
    Dim strWhere As String
    strWhere = "EstimateNo = Forms!frmEstimateDetails!EstimateNo And Supp = Forms!frmEstimateDetails!Supp"
    PartCheck = DCount("*", "tblParts", "Code=" & Me!cboCode & " And " & strWhere)
    If PartCheck > 0 Then
    MsgBox "Part Exists"
    End If


    I get error 2001 you cancelled the prev operation.

    This will only leave the problem of preventing inserting data already stored.

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

    Re: Update Table (A2k)

    You never set NewYesNo to False, so the records with NewYesNo accumulate. Perhaps it'll help if you set it to False after DoCmd.RunSQL strSQL. I'm not sure, since I don't understand the role of New and NewYesNo.

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

    Re: Update Table (A2k)

    You show a message box but that's it, it has no further consequence. Perhaps this code should be in cboCode_BeforeUpdate, and if PartCheck > 0 you should set Cancel = True, to prevent updating of cboCode.

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

    Re: Update Table (A2k)

    Hans
    I wrote a very long explanation on the fundamentals behind the New & NewYesNo, it was over 500 words and got dumped somewhere.
    To keep it brief:
    In the [New.] column, either a bulk time ie, 8hrs can be enterred if the part accumilates time, or, 0.01hrs for an item associated to the main item but, not occuring any time.
    The 0.01 is basically a tag to let the database know it's a new item, but not total up at the end.

    Private Sub New_AfterUpdate()
    If Me.New = 0.01 Then
    Me.NewYesNo = True
    Me.New = 0
    End If
    If Me.New > 0.01 Then
    Me.NewYesNo = True
    End If
    DoCmd.RunCommand acCmdSaveRecord
    Forms!frmEstimateDetails!sbfPartsDetails.Requery
    Forms!frmEstimateDetails!sbfOtherDetails.Requery
    End Sub


    The NewYesNo never needs to be set to false, because it is false by default.

    The other question, the codes not finished and will only contain some setfocus events etc.

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

    Re: Update Table (A2k)

    Dave,
    <hr> I wrote a very long explanation on the fundamentals behind the New & NewYesNo, it was over 500 words and got dumped somewhere.<hr>
    That happens to me sometimes too <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16>. I copy the text I've typed in a reply to the clipboard from time to time (if I remember <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, or compose a reply in Word and then paste it into the reply box when it's finished.

    NewYesNo may be false by default, but once it has been set to True in your code, and used to append new records to tblParts, you don't reset it to False, so next time the records will be appended again. That's why I suggested to set NewYesNo to False immediately after executing the append query.

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

    Re: Update Table (A2k)

    Hans
    Yes, the penny has dropped.

    I'll try it and see what happens.

    Have a good day.

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

    Re: Update Table (A2k)

    Although some of the above is fixed,I still can't get the before update event to work, and whats more to the problem, how will this effect speed when the table has many entries ?

    I've attached the error and below is the codes for before and after events of the combo:
    I'm just wondering if some other event is clashing with the code !


    Option Compare Database
    Option Explicit
    Private Sub cboCode_AfterUpdate()
    'Dim PartCheck As Integer
    'Dim strWhere As String
    'strWhere = "EstimateNo = Forms!frmEstimateDetails!EstimateNo And Supp = Forms!frmEstimateDetails!Supp"
    'PartCheck = DCount("*", "tblParts", "Code=" & Me!cboCode & " And " & strWhere)
    'If PartCheck > 0 Then
    'MsgBox "Part Exists"
    'End If

    '=======================
    If cboCode = "UN" Then
    txtItem.Locked = False
    txtItem = Null
    Else
    txtItem.Locked = True
    txtItem = cboCode.Column(1)
    Skip:

    ' Me.Requery
    ' DoCmd.GoToRecord , , acNewRec
    End If
    Exit Sub
    End Sub

    Private Sub cboCode_BeforeUpdate(Cancel As Integer)
    Dim PartCheck As Integer
    Dim strWhere As String
    strWhere = "EstimateNo = Forms!frmEstimateDetails!EstimateNo And Supp = Forms!frmEstimateDetails!Supp"
    PartCheck = DCount("*", "tblParts", "Code=" & Me!cboCode & " And " & strWhere)
    If PartCheck > 0 Then
    MsgBox "Part Exists"
    Cancel = True

    End If
    End Sub

    Private Sub cboCode_NotInList(NewData As String, response As Integer)
    MsgBox "Invalid Item !!" & " " & "You Must Use An Item From The List Or Use The Code UN", vbOKOnly, "!!"
    response = acDataErrContinue
    End Sub
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If cboCode = "" And Not IsNull(txtItem) Then
    MsgBox "You Cannot Create A Description Without A Code", vbOKOnly, "!!"
    cboCode.SetFocus
    Cancel = True
    End If
    If cboCode = "UN" And IsNull(txtItem) Then
    MsgBox "You MUST Enter An Item.", vbCritical + vbOKOnly, "!!"

    txtItem.SetFocus
    Cancel = True
    End If
    End Sub
    Private Sub Form_Current()
    If cboCode = "UN" Then
    txtItem.Locked = False
    Else
    txtItem.Locked = True
    End If
    End Sub
    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
    Case vbKeyF3
    DoCmd.GoToRecord , , acNewRec
    Case vbKeyF5
    DoCmd.RunCommand acCmdSaveRecord
    Me.Requery
    DoCmd.GoToRecord , , acNewRec

    Case vbKeyF10
    DoCmd.RunCommand acCmdSaveRecord
    Me.Requery
    DoCmd.Close acForm, "frmEstimateDetails", acSaveNo
    End Select
    End Sub
    Private Sub New_AfterUpdate()
    If Me.New = 0.01 Then
    Me.NewYesNo = True
    Me.New = 0
    Me.id = "N"
    End If
    If Me.New > 0.01 Then
    Me.NewYesNo = True
    End If
    DoCmd.RunCommand acCmdSaveRecord
    Dim strSQL As String
    strSQL = "INSERT INTO tblParts ( EstimateNo, Supp, Code, Item )SELECT tblEstimateDetails.EstimateNo, tblEstimateDetails.Supp, tblEstimateDetails.Code, tblEstimateDetails.Item FROM tblEstimateDetails WHERE (((tblEstimateDetails.NewYesNo)=True));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings True
    Me.NewYesNo = False
    Me.id = "N"
    DoCmd.RunCommand acCmdSaveRecord
    Forms!frmeSTIMATEDetails!sbfPartsDetails.Requery
    Forms!frmeSTIMATEDetails!sbfOtherDetails.Requery
    End Sub
    Attached Files Attached Files

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

    Re: Update Table (A2k)

    Dave,

    The error occurs in the line

    PartCheck = DCount("*", "tblParts", "Code=" & Me!cboCode & " And " & strWhere)

    The screen shot shows a string as code in the combo box. So you probably need to surround Me!cboCode with quotes:

    PartCheck = DCount("*", "tblParts", "Code=" & Chr(34) & Me!cboCode & Chr(34) & " And " & strWhere)

    As you probably know, Chr(34) is the code for the double quote ".

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

    Re: Update Table (A2k)

    Hans
    I was close wasn't I ?

    We have now solved the duplication problem but one thing I forgot to mention <img src=/S/blush.gif border=0 alt=blush width=15 height=15> , The code "UN" is the only code we need to allow to duplicate.

    Sorry for messing you about, can we do this ?

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

    Re: Update Table (A2k)

    Dave, you can exit cboCode_BeforeUpdate if the code is "UN":

    Private Sub cboCode_BeforeUpdate(Cancel As Integer)
    Dim PartCheck As Integer
    Dim strWhere As String
    ' No need to check for duplicates if code is "UN"
    If Me.cboCode = "UN" Then Exit Sub
    ' Check for duplicates
    strWhere = ...
    etc.

    (Get out if the code is "UN" - sounds like Dubya.)

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

    Re: Update Table (A2k)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <big> I should have seen that </big> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Can we have a smiley that shoots us in the foot, or kicks our shins ??

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

    Re: Update Table (A2k)

    We have <!t>[stupidme]<!/t>: <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

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

    Re: Update Table (A2k)

    Thanks, Noted !!
    <img src=/S/bash.gif border=0 alt=bash width=35 height=39> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

Posting Permissions

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