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

    Best Method (A2k)

    What is the best method to achieve the following.
    I have a datasheet subform, in which criteria of an estimate is entered.
    There are several fields, but the ones in question are as follows:

    [ID],(text)
    [New ],[R-R],[Rep],[Paint],(Numeric - Format 0.00)

    The criteria must be set in [ID] dependant on what is entered in the remaining 4 fields.
    The data which is entered will be hrs.
    Lets take a couple of examples to try and make things a little clear.

    An item can be:

    [New ]1.00, [R-R]1.00, [Rep]0.00, [Paint]1.00 which will make [ID] = "N" ( for new)
    [New ]0.00, [R-R]1.00, [Rep]1.00, [Paint]1.00 which will make [ID] = "R" ( for repair)
    [New ]0.00, [R-R]1.00, [Rep]0.00, [Paint]1.00 which will make [ID] = "P" ( for Paint)

    There can many variations to this, but my question is should I use a Case statement, or An After_Update statement, or an On_Current statement ?

    I have it working at the moment with After_Update of each field using a rather simple method:

    If Me.New >0.01 then
    Me.ID = "N" etc etc

    But was wondering how to handle it a different way using Case:

    Select Case ??[New ],[R-R],[Rep],[Paint] ??
    Case >0,0,0,0
    Me.ID = "N"
    Case 0,>0,>0,>0
    Me.ID = "R"
    Case 0,>0,0,>0
    Me.ID = "P"
    End select


    Any thoughts are appreciated.

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best Method (A2k)

    Dave,

    I don't know enough about the database to know whether I'd suggest that you use a Select Case here, or not...
    But if you do... I'd suggest you make sure you know ALL of the variations... and put a Case Else in there to flag records that don't match any of the prior Case statements...

    You could do it like this:

    Select Case True
    Case [New ]>0 And [R-R]=0 And [Rep]=0 And [Paint]=0
    Me.ID = "N"
    Case [New ]=0 And [R-R]>0 And [Rep]>0 And [Paint]>0
    Me.ID = "R"
    Case [New ]=0 And [R-R]>0 And [Rep]=0 And [Paint]>0
    Me.ID = "P"
    Case Else
    Me.ID = "X" <--- to flag errors... OR you could do something like send a msgbox with the error...
    End select

    HTH

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

    Re: Best Method (A2k)

    Trudi
    Thats perfect.
    I could see this in air code but would have taken me ages to get it right.

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

    Re: Best Method (A2k)

    Thanks for the help on this, but I have to revisit it. The code supplied is fine, albeit I have to revisit the problem.
    A different approach is needed usng a single fields text value "[Id]"

    The four numeric value fields run concurrently, ie:
    [New ], [R-R], [Rep], [Paint].

    The one clause here is that you cannot repair(Rep) an item as well as renew(New) it & Visa Versa, so I have to set the code to check the text value on the after update of each numeric field.
    Here's what I've been playing with.

    Private Sub New_AfterUpdate()

    If Me.id = "R" Then
    MsgBox "You Cannot Replace An Item As Well As Repair It !!," & vbCrLf & "Press Ok To Change To New : " & vbCrLf & "Or Press Cancel To Undo This Selection:", vbOKCancel, "Estimate Error"
    If vbOK Then
    Me.id = "N"
    If vbCancel Then
    Me.id = "R"
    me.Undo
    End If
    End If
    End If



    Private Sub Rep_AfterUpdate()
    If Me.id = "N" Then
    MsgBox "You Cannot Repair An Item As Well As Replace It !!," & vbCrLf & "Press Ok To Change To New : " & vbCrLf & "Or Press Cancel To Undo This Selection:", vbOKCancel, "Estimate Error"
    If vbOK Then
    Me.id = "R"
    If vbCancel Then
    Me.id = "N"
    me.Undo
    End If
    End If
    End If


    I have a load other checking to do also but one step at a time on the after_update of [paint]

    Select Case ID
    Case "N"
    'Do Nothing
    Case "R"
    'Do Nothing
    Case Else
    Me.Id = "P"
    End select


    Where are my first two statements going wrong, they don't change the selection using the vbOk & vbCancel Options.

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

    Re: Best Method (A2k)

    Dave,

    You should call MsgBox as a function instead of as a procedure. The way you do it now, you don't get the return value. Try something like this:

    Private Sub New_AfterUpdate()
    Dim intResult As Integer

    If Me.ID = "R" Then
    intResult = MsgBox("You Cannot Replace An Item As Well As Repair It !!," & vbCrLf & _
    "Press Ok To Change To New : " & vbCrLf & _
    "Or Press Cancel To Undo This Selection:", vbOKCancel, "Estimate Error")

    Select Case intResult
    Case vbOK
    Me.ID = "N"
    Case vbCancel
    Me.id = "R"
    Me.Undo
    End Select
    End If
    End Sub

    Analogously for Rep_AfterUpdate, with the roles of "N" and "R" reversed.

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

    Re: Best Method (A2k)

    Thanks Hans
    This is the core part of the checking procedure which really gives me a good start.
    In the same procedure, if I'm adding code, ie



    Private Sub New_AfterUpdate()
    Dim intResult As Integer

    If IsNull(Me.ID) then
    Me.ID = "N"
    End If

    If Me.ID = "R" Then
    intResult = MsgBox("You Cannot Replace An Item As Well As Repair It !!," & vbCrLf & _
    "Press Ok To Change To New : " & vbCrLf & _
    "Or Press Cancel To Undo This Selection:", vbOKCancel, "Estimate Error")

    Select Case intResult
    Case vbOK
    Me.ID = "N"
    Case vbCancel
    Me.id = "R"
    Me.Undo
    End Select
    End If
    End Sub

    Does my End If go after your version of the code or as I have written above.??
    Also, how do I show the code within my post as yours is (Indented)

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

    Re: Best Method (A2k)

    In this case it isn't really important, but it would be slightly more efficient to write it as

    If IsNull(Me.ID) Then
    ...
    ElseIf Me.ID = "R" Then
    ...
    End If

    Note: you can't include Null as a "case" in a Select Case statement.

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

    Re: Best Method (A2k)

    Thanks again, I will try this next time I post code.



    Far more professional looking.

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Best Method (A2k)

    The Lounge software ignores spaces at the beginning of a line, and also replaces multiple spaces by single spaces. There are two different ways to show indented code:

    1. Use the <!t>[tab]<!/t> tag where you want a "tab". You can insert it from the 1-Click TagPanel, or type it yourself. It will be replaced by 4 spaces.

    2. Put the <!t>[pre]<!/t> tag before, and <!t>[/pre]<!/t> after a piece of code. Again, you can insert these from the 1-Click TagPanel or type them yourself. The text between the tags will be displayed "as is" in a mono-spaced font.

    When I want to post existing code, I copy the code into a Word document, then run a little macro that replaces 2 spaces by <!t>[tab[/b] (I have set my Visual Basic Editor to use 2 spaces as tab):
    <pre>Sub Spaces2Tabs()
    On Error GoTo Err_Sub
    Selection.Find.Execute FindText:=" ", ReplaceWith:="<!t>[tab]<!/t>", Replace:=wdReplaceAll
    Exit Sub
    Err_Sub:
    If Err <> 91 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub</pre>

    Finally, I copy the result and paste it into my post.

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

    Re: Best Method (A2k)

    <P ID="edit" class=small>(Edited by D Willett on 01-Oct-03 15:51. Having a bad day, hadn't set two of the NewYesNo items to true !!)</P>Hans
    The code you supplied works a treat, but with the further checking procedures, I feel I have created too many stumbling blocks for the code to work correctly.

    I'm trying to create a series of events as follows:

    New After Update.
    If New <0.25, change the value to 0(zero) so not to add this value in a summing text later on.
    Set ID to "N" so the user can see it is a new item although no (Labour)value exists.
    Set NewYesNo (Checkbox) to true, run SQL Statement, then set NewYesNo back to false, so further items added to the form are not duplicated.
    Also check the part is not being repaired also.


    The code is now working as attached, I hadn't set a couple of NewYesNo's to true for the SQL to work, but does the code look correct.?
    Attached Files Attached Files

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

    Re: Best Method (A2k)

    I think I've got it.
    Attached is the new code which seems <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> to work.
    Only one error now occurs, when the value from [New ] is "Changed", the code runs the strSQL again and duplicates the part in tblParts. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

    If I can get over this, I will have cracked.
    Attached Files Attached Files

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

    Re: Best Method (A2k)

    I don't understand the purpose of NewYesNo. You set it to Yes immediately before executing the SQL statement, so it will ALWAYS be yes when you execute the SQL, won't it?

    Perhaps you shouldn't insert the records into tblParts in the After Update event of the individual controls, but in the Before Update event of the record. But I'm not sure, since I don't know what you're doing with this code.

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

    Re: Best Method (A2k)

    Hans
    The form is datasheet where a user enters time for each process of repair.
    We can replace items and we can repair items.
    Imagine fitting a new wing, if this is so, you wouldn't repair it also, so these two elements cannot clash, hence the complication of the form.
    On screen it looks simple, but as I've found out, the coding is not. Mainly because I have to look at three elements.

    [ID] The other fields depend on this.
    <0.25 We work to the nearest 1/4 hr. If we replace an item or just tag an item so we know that process is required, we use 0.01. Say fitting that Wing again, we may also replace a moulding as a requirement because they are stick on, but not allocate any time for it because the core time is in the Wing process ie:

    [NFW] [N/S/F Wing] [New ]=1.0, [R-R]=0.5, [Rep]=0.0, [Paint]=3.0
    [NFWM] [N/S/F Wing Mld] [New ]=0.01 0,0,0,
    The iD is set to "N", showing the user it is for renewal although we have set the labour time back to zero.
    In the summing field, the 0.01's are not added up.
    Currently in our Dataflex system, we have adjust the labour to remove all the 0.01's.
    It would be far easier I know to just click a check box, but as you know, my project is much more of a challenge.

    The purpose of the NewYesNo is very simple.

    It is set to True before the SQL fires so that the part is entered into tblParts only once.
    It is then set to false, so when another item is entered as new, the SQL only puts a single part in tblParts.

    strSQL = "INSERT INTO tblParts ( EstimateNo, Supp, Code, Item )SELECT tblEstimateDetails.EstimateNo, tblEstimateDetails.Supp, tblEstimateDetails.Code, tblEstimateDetails.Item FROM tblEstimateDetails WHERE (((tblEstimateDetails.NewYesNo)=True));"

    If I get chance, I will rip the parts out and attach a demo, but for now, Ill just attach a PNG.
    BTW
    I thought my code was working but its not quite right.
    I spent all day on it yesterday and think I have fried my brain.
    I'll look at it again today.
    With the code, the <0.25 & >0.25 part works but is now ignoring the clashing "R" scenarion.
    I Tried to force this with "Goto Skipcode"
    Attached Images Attached Images

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

    Re: Best Method (A2k)

    Attachment:
    Attached Files Attached Files

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

    Re: Best Method (A2k)

    <P ID="edit" class=small>(Edited by HansV on 02-Oct-03 10:37. Oops! This was written while you were posting an attachment.)</P>I'm still <img src=/S/confused.gif border=0 alt=confused width=15 height=20> about this. I am not sure whether you really need to add records to the parts table, perhaps you could use a query instead; but maybe the criteria would be too complicated. How about the following:

    Whenever you need to change something about the parts, first delete ALL records for the relevant EstimateNo and Supp, then append the records anew. You wouldn't need to bother with NewYesNo then, and you don't run the risk of duplicates.

    About the problem of skipping the check for clashing ID's, set a breakpoint in the code and single-step through it to try and find out why.

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
  •