Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey Loungers.

    Looking for a bit opf help again so hopefully some of you nice folk can steer me right. I have a database just now that is used by 6 or 7 diffrent teams , some have separate teabl for specific team work. I want to link an alert message on one of the forms so that when someone in the Data processing team is recording post coming in when they enter the DTRN(just a 9 digit identifier) a message box will appear telling them that the case is being worked on by another team and what officer to alert. This is probably very simple to do but I'm still pretty new to Access. I take it this would be in the on update for the text box on the Data processing form? What would the code be for this too?

    Thanks for any help in advance.

    Cheers

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Gary,

    I'm not sure of the exact scenario you describe. Simple answer, an after update would open a query to find the team responsible for the item. If a team is found, the message box can be opened (or a control on screen can display the name of the person/team - less annoying for the data entry person). Is this enough help?

    More complicated answer - are you trying to prevent the data processing team from working on a record at the same time as another team? If so....

    Firstly, you need to be able to identify that the record is being edited. A simple locking mechanism would write the name/ID of the editor, plus the date and time, into the record that is being edited. Clicking on Save or Cancel then deletes the locking data. One of the first actions under the Edit button would be to check the locking fields, and only allow editing if the field is empty.

    An Admin screen to unlock these records is often also necessary, to cope with power failures or Ctl-Alt-Del leaving the record in a locked state.

    Now, the system knows if the record is in use. As you say, an After Update event could check the record and bring up the name of the person who locked it.

    Hope this is of use.

    Regards,

    Jules

  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
    Quote Originally Posted by Gary Hamilton View Post
    Hey Loungers.

    Looking for a bit opf help again so hopefully some of you nice folk can steer me right. I have a database just now that is used by 6 or 7 diffrent teams , some have separate teabl for specific team work. I want to link an alert message on one of the forms so that when someone in the Data processing team is recording post coming in when they enter the DTRN(just a 9 digit identifier) a message box will appear telling them that the case is being worked on by another team and what officer to alert. This is probably very simple to do but I'm still pretty new to Access. I take it this would be in the on update for the text box on the Data processing form? What would the code be for this too?

    Thanks for any help in advance.

    Cheers
    From your description, I'm thinking this is not a record locking issue (which is only applicable when someone has started editing a record). I believe you want (need) to set a flag in the record that basically tells you which team is working on the case (since I'm guessing this may take some time, perhaps days?).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, thanks for the replies and sorry for how long it's taken to get back but I managed to destroy my cable modem.

    Yeah it's not a record lock I'm looking for just something to flag up to the team that logs post in that someone on one of the other teams has an open case with that DTRN, so pretty much when post comes in and someone enters the DTRN I'm looking for a message box to appear and tell them something along the lines of

    "Case being dealt with by [Officers Name] on [TeamName]"

    These will be on 2 different tables, so what I want is after the post in team enters the DTRN on the Case Tracking form( linked to the StatsCaseTrackingTbl) andthey TAb or click on the next part of the form it'll search for a Matching DTRN on the AFStargetsTbl and if a matching one is there then it will show the message box.

    I hope this makes some kind of sense to someone.

    Cheers

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Gary,

    Maybe you want something like this:
    Code:
    Sub DispAlert()
    
      Dim vCity As Variant
      Dim vAssetClass As Variant
      Dim zCondition  As String
      Dim zAcctNo     As String
      
      zAcctNo = "45789"    '*** Retrieve this from your form field DTRN ***
      zCondition = "[AccountNo] = " & zAcctNo
      
      vCity = DLookup("[City]", "MasterAccts", zCondition)
      vAssetClass = DLookup("[AssetClass]", "MasterAccts", zCondition)
      
      MsgBox "The Account No: " & zAcctNo & " is located in " & vCity & _
              " and is of Asset Class: " & vAssetClass, vbOKOnly
    End Sub
    Of course you'd subistitute your table & variable names as appropriate and probablly get the zAcctNo from your form field.
    You would also insert this code in an an AfterUpdate event for the DTRN field.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You, Sir, are a Gentleman and Scholar! Thank you so much, that's just what I needed. And thanks to everyone for the input on this and my other threads, can't believe how helpful everyone is on the forums here.

    Cheers

  7. #7
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One thing, when there is no matching record in the other table I still get the message box with "This case is being delt with by" and no name as there is not always going to be a matching record, is there a way around this appearing? I'm just messing around with it just now and figuring out how I want it to appear but what I've got is

    Private Sub DTRN_AfterUpdate()

    Dim vDealing As Variant
    Dim zCondition As String
    Dim zDTRN As String

    zDTRN = "DTRN"
    zCondition = "AFSDTRN = " & zDTRN

    vDealing = DLookup("[Dealing With]", "AFSteamtargets", zCondition)

    MsgBox "The Case " & zREF & " is being dealt with by " & vDealing, vbOKOnly


    End Sub

    I'll add to this with more entries in the message box but this was just for testing

    Cheers

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    This should do the trick.
    Code:
    Private Sub DTRN_AfterUpdate()
    
    Dim vDealing As Variant
    Dim zCondition As String
    Dim zDTRN As String
    
    zDTRN = "DTRN"
    zCondition = "AFSDTRN = " & zDTRN
    
    vDealing = DLookup("[Dealing With]", "AFSteamtargets", zCondition)
    
    if vDealing !="" Then
      MsgBox "The Case " & zREF & " is being dealt with by " & vDealing, vbOKOnly
    End if
    
    
    End Sub
    Sorry for taking so long to respond I've been on the road all day in the RV...boy retirement is tough!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well it's alright for some, eh?

    I think I'm doing something wrong, I keep getting a compile error unless I remove the ! from

    if vDealing !="" Then

    but then the message fails to appear even when there is a matching record though having some success when I tried

    If Not IsNull (vDealing) Then

    though some records are still not showing when they should but this could just be an error in some of my test data and I think I'll just have an early day and sort it out tomorrow. Pub time!

    Thanks again for the help and the patience with my non existant VBA skillz! Hope the RV'ings going well.

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Gary,

    Sorry for the mixup. Normally != {not equal} works in programming languages. However, not in VBA you can use <> in it's place. I tested this and it works fine.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No worries, it's working a treat now, your a Star!

    Cheers

Posting Permissions

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