Linking forms for aleart message(Access 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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?).

Reply | Quote

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

Reply | Quote

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.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!
Computer Specs

Reply | Quote

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.

Reply | Quote

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!
Computer Specs

Reply | Quote

No worries, it’s working a treat now, your a Star!

Cheers

Reply | Quote