May I Pick Your Brains?

[JediYodaNT]

So, I have a new project that I'm putting together for my team, and I'm sure it's something simple, but I'm at a loss for how to go about this. Any help you guys can provide would be greatly appreciated.

I have two lists of accoutn prefixes, phase 1 and phase 2. All accounts with a prefix listed in the Phase 1 table cannot be touched after Date X, and after Date Y we cannot touch any accounts with a prefix listed on Phase 1 or Phase 2.

What I'm trying to create is a simple form with a text box for an associate to enter in the prefix, click a "Check Prefix" button and have a window pop up and say either "You may make changes to this account." or "This account may not be altered." Then, when they click the "ok" button, the form would reset.

Would a tool of this nature be easy to create?

If someone could steer me in the right direction I would really appreciate it.

 

[MarkK]

At first glance this doesn't seem difficult, but I'm sort of curious about your work-flow design. Like why would someone enter a prefix first? I would expect the user to navigate to or find an account in the system because they are required to perform some task in respect to that data. It would only be upon finding the record in question that it could be determined whether that account or record is subject to editting restrictions.
I would endeavour then to calculate the phase or editablility status of the current record automatically and not require the user any extra steps to open a distinct form to enter a prefix and hit a key to perform logic and lock or unlock the record.
Check out the form's Current event, where you can run code when a particular record is loaded. Leverage the prefix available in the current record and/or any dates it may contain and display the status immediately. Consider this code ...

Private Sub Form_Current()
[COLOR="Green"]  'check that the record exists[/COLOR]
  If Not Me.NewRecord Then
[COLOR="Green"]    'if it does, determine whether edits are allowed and set the form's property[/COLOR]
    Me.AllowEdits = GetAllowEditsForAccount(Me.AccountID, Me.PhasePrefix, Me.AccountDate)
  Else
[COLOR="Green"]    'and for new records always allows edits[/COLOR]
    Me.AllowEdits = True
  End If
[COLOR="Green"]  'show and hide visual cues indicating edit status for this record[/COLOR]
  Me.EditsAllowedImage.Visible = Me.AllowEdits
  Me.EditsNotAllowedImage.Visible = Not Me.AllowEdits
End Sub

Private Function GetAllowEditsForAccount(AccountID as long, Phase as string, Date1 as date) As Boolean
[COLOR="Green"]  'perform logic here to determine if edits are allowed for the given account[/COLOR]
[COLOR="Green"]  'this routine might be in a different module so other DB objects can easily determine editability for this object type, 
  'thus we pass in all pararms it might need
[/COLOR]  GetAllowEditsForAccount = <logic engine result>
End Function

Does this make sense? Let me know,

 

[JediYodaNT]

Well, to explaing the flow issue. We have an older system that we are moving accounts off of and onto a new system. On "Date X" all accounts with the given prefix from the "Phase 1" list will have been moved to the new system (which my team does not have access to), and then on "Date Y" all accounts with the given prefix from the "Phase 2" list will have been moved as well. If one of my assocaites makes an adjustment to an account after the move date, there are some serious ramifications.

My goal with this tool is to simply help my team to quickly check the account prefix for a document they have received for processing, and if it has been moved, let them know not to touch it. Sadly, we do not have the budget, or the permissions needed o adjust our current systems to engage a lock of sorts as the accounts are moved, so this is intended to help increase efficiency, and reduce oversights on my team.

Lagbolt, thank you for your suggestion. Sadly, I am really new to Access and VBA, so I am trying to pick apart the code you suggested and see how I would need to rework it in my current project.

This is what I have so far, and if I'm going about this the wrong way, please feel free to let me know. I'm trying to keep this is simple as possible for both myself, from the creation side, and for my associates, from the use side.

Phase1: Table

Phase2: Table

PrefixCheck: Form

On the form, I have a Text box called "Prefix" with an "OnUpdate" event that changes the case to upper case. (All prefixes in Phase1 and Phase2 are uppercase only).

I also have a command button for the associate to click on. My hope was that I could create a simple macro that would compare the current date with the start date of Phase 2. If the current date is before the start date, only check the Phase 1 table to see if PrefixCheck.Prefix is listed, but if the current date is onor after the Phase 2 start, check both tables.

After the comparison is run, a simple dialog box would pop up to say somethingto the effect of "change" or "don't change".
​

I hope all that made some sense. I know it's always abstract when you're not living in the context of the team and the normal workflow.

 

[MarkK]

Consider the possibility of using a union query which returns records from BOTH phase tables so you don't have to do two checks. Consider SQL like ...

SELECT PhasePrefix, #1/1/2010# As LockDate
FROM tPhase1
UNION 
SELECT PhasePrefix, #6/1/2010# As LockDate
FROM tPhase2;

Note that we create a field called 'LockDate' as a 'hard-coded' date into the SQL. Now we essentially have one table where each prefix is in the same record with the date it expires. Assume we save this as a query called 'qAllPhase.'
Now, to determine if a record exists with a specific PhasePrefix and a LockDate prior to 'SomeDate'--which I think is your requirement--you can do ...

DCount("*", "qAllPhase", "PhasePrefix = '" & PhasePrefix & "' AND LockDate < #" & SomeDate & "#")

If this count is non-zero, then the specified PhasePrefix is locked for edits on SomeDate.
If the count is zero, then the prefix either hasn't been locked as of SomeDate or doesn't exist in either phase and isn't locked.
Lemme know if this needs more clarification, or if I've missed a feature of your requirement,