Form alert option (1 Viewer)

[amd]

Hello, I am creating a membership database (in Access 2016/19), when the persons membership number is selected and the record is shown, if the membership is out of date I would like a pop up to appear on the screen to stop the user going any further until a password is entered to clear the message.

Is this possible to do or just wish full thinking?

I have made the fields change colour (go red) but I have been asked for the pop up to appear.

Any guidance would be really appreciated.

Many thanks and happy holidays.

 

[arnelgp]

you can use a simple InputBox() and Loop if no password or incorrect password has been provided. eg:

Dim strPassword As String

Loop_Here:
strPassword = Inputbox("Please enter the correct password to clear this message.", "Password Required")
If strPassword <> "amd" Then
Msgbox "Invalid password."
Goto Loop_Here
End If
'

 

[oleronesoftwares]

a password

if the password is a constant,@arnelgp has given a good suggestion on how to go about it.

the code can be placed on the after update event of the control.

 

[amd]

Thank you for your replies, but that will only work if you click in the date cell.

I would like a pop up to appear as soon as the record is accessed and the date is overdue.

thanks again

 

[bob fitz]

Thank you for your replies, but that will only work if you click in the date cell.

I would like a pop up to appear as soon as the record is accessed and the date is overdue.

thanks again

Perhaps you should be doing you validation in the forms On Current event then.
EDIT:
Usually validation is done in a controls before update event and/or in the forms before update event.

 

[theDBguy]

Is this possible to do or just wish full thinking?

Hi. Welcome to AWF!

Yes, this is very possible, but I get the impression such a feature could be very annoying to the users. In any case, you should be able to apply the suggestions already given by inserting that code within your code that does the following.

when the persons membership number is selected and the record is shown

Surely, you have some code that does that, correct? Otherwise, how exactly is the membership number selected and the record shown?

 

[amd]

Ok, thanks for your responses.

A friend of mine is opening a new gym and asked me to set up a database for him, all of the data will already be in the database, so validation takes place at entry of data itself.

When the member comes in to the gym, they scan their card or enter their membership number in to the system and their information (limited) will appear including the expiry of the membership.

If the expiry date has arrived, then when the card is scanned, then I want a message to pop up to tell the member to contact staff so that they can pay for further time, but I dont want the message to disappear until a password is entered.

Not sure if that makes sense, but it does to me.

Hope this helps

 

[theDBguy]

If the expiry date has arrived, then when the card is scanned, then I want a message to pop up to tell the member to contact staff so that they can pay for further time, but I dont want the message to disappear until a password is entered.

Not sure if that makes sense, but it does to me.

Ah, I think that's similar to those self-checkout lanes in the supermarket. If something needs a staff's attention, the customer cannot scan another item until a staff resets or attends to the machine first. You just want the client to call a staff for assistance. That makes sense.

 

[oleronesoftwares]

If the expiry date has arrived, then when the card is scanned, then I want a message to pop up to tell the member to contact staff so that they can pay for further time, but I dont want the message to disappear until a password is entered

the suggestions given by members will work, i.e using an input box, go through it, the code will be placed preferably on the after update event of the membership number field.

Placing it on the form on current event might trigger the input box message repeatedly(so I won't advise this), but I await more suggestions from other members.

 

[amd]

Ah, I think that's similar to those self-checkout lanes in the supermarket. If something needs a staff's attention, the customer cannot scan another item until a staff resets or attends to the machine first. You just want the client to call a staff for assistance. That makes sense.

yes that's it, but I am struggling on how to do it

 

[Pat Hartman]

In the AfterUpdate event of whatever control is being used to select the member, Use dLookup() to find the member's expiration date. If it is < today's date, then you can send a notice (email) to the logged in employees that help is needed and then pop up a model form where the password and information to renew can be captured. Since the form is model, the app is essentially locked until someone enters the requested information and it is validated. If they enter an invalid password and hit the enter button, you will display an error message and redisplay the model form. Obviously, you also need to give the member instructions.

You might want to include a wider ranging check so that the member is allowed to log in but gets a notice each time for a week before his membership actually expires.

 

[theDBguy]

but I am struggling on how to do it

But as I said in my earlier post, you would simply include the code provided within your existing code that selects the record and displays it. For example, let's say you have the following pseudo code:

1. Select member
2. Search for member's record
3. Display record
4. Check if expired
5. Turn date display into red color

Then, you simply insert/add the code to request for the password, such as:

1. Select member
2. Search for member's record
3. Display record
4. Check if expired
5. Turn date display into red color
6. Show InputBox() to ask for password
7. Check if password is correct
8. Repeat step 6 if wrong password

Hope that makes sense...

 

[amd]

But as I said in my earlier post, you would simply include the code provided within your existing code that selects the record and displays it. For example, let's say you have the following pseudo code:

1. Select member
2. Search for member's record
3. Display record
4. Check if expired
5. Turn date display into red color

Then, you simply insert/add the code to request for the password, such as:

1. Select member
2. Search for member's record
3. Display record
4. Check if expired
5. Turn date display into red color
6. Show InputBox() to ask for password
7. Check if password is correct
8. Repeat step 6 if wrong password

Hope that makes sense...

Yes it makes sense, but I am struggling on how to do the code for it because what ever I tryI cannot get it to work..

If all coding was as easy as pseudo our life would be easy.....

I will keep trying

thanks

 

[amd]

In the AfterUpdate event of whatever control is being used to select the member, Use dLookup() to find the member's expiration date. If it is < today's date, then you can send a notice (email) to the logged in employees that help is needed and then pop up a model form where the password and information to renew can be captured. Since the form is model, the app is essentially locked until someone enters the requested information and it is validated. If they enter an invalid password and hit the enter button, you will display an error message and redisplay the model form. Obviously, you also need to give the member instructions.

You might want to include a wider ranging check so that the member is allowed to log in but gets a notice each time for a week before his membership actually expires.

thank you for your reply, I have the text boxes turning orange when the date is within 7 days to warn them.

The only thing I am struggling on is when it has expired....

 

[Gasman]

thank you for your reply, I have the text boxes turning orange when the date is within 7 days to warn them.

The only thing I am struggling on is when it has expired....

When the expiry date is less than today ( Date()?)

If it is < today's date

Why not just go on the 7 days to expire and give them a heads up, if it is so difficult, as you already have that sorted?

 

[oleronesoftwares]

Yes it makes sense, but I am struggling on how to do the code for it because what ever I tryI cannot get it to work..

if you share the code you have and the current outcome, someone here might point you in the direction of what the code should be.

 

[theDBguy]

Yes it makes sense, but I am struggling on how to do the code for it because what ever I tryI cannot get it to work..

If all coding was as easy as pseudo our life would be easy.....

I will keep trying

thanks

Hi. If you're still trying to figure out what code to use, @arnelgp already gave it to you in post #2. Have you tried it?

If you're still trying to figure out where to put the code from post #2, where did you place the code you already have to select, display, and change the colors of the member's record?

I gave you pseudo code, because you already have all the code you need. If you gave a try and couldn't get it to work, consider showing us what you tried, so we can tell you why it didn't work.

Without seeing what you got, we can't give you specific suggestions.

 

[Pat Hartman]

The only thing I am struggling on is when it has expired....

You might want to read all the replies again. I told you where/how to do it and arnel posted some code.

 

[oleronesoftwares]

thank you for your reply, I have the text boxes turning orange when the date is within 7 days to warn them.

The only thing I am struggling on is when it has expired....

if you can achieve the 7 days warning you can achieve the expired.

For expired it will be
expirydate<date()

 

[amd]

In the AfterUpdate event of whatever control is being used to select the member, Use dLookup() to find the member's expiration date. If it is < today's date, then you can send a notice (email) to the logged in employees that help is needed and then pop up a model form where the password and information to renew can be captured. Since the form is model, the app is essentially locked until someone enters the requested information and it is validated. If they enter an invalid password and hit the enter button, you will display an error message and redisplay the model form. Obviously, you also need to give the member instructions.

You might want to include a wider ranging check so that the member is allowed to log in but gets a notice each time for a week before his membership actually expires.

Hi, thank you for your persistance with me, I am trying the following code to try and get a form to pop up.

my table is called 'Members', the expiry field is called 'MemberExpiry' and the members ID field is 'MemberID'. The pop up form is called 'red'

the first part looks up the the member info from a search box, my part is in the trial code section and I cannot figure out the dlookup part. I have triedl googling, but still struggling.

Private Sub Combo42_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MemberID] = " & Str(Nz(Me![Combo42], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

''''''''''trial code''''''''''

Dim varX As Variant
varX = DLookup("[MemberExpiry]", "Members" = "[MemberID] = " & Forms![Member]!MemberID)

If varX < Date Then:
DoCmd.OpenForm "red"
End If


'''''''''end of trial code'''''''''''

End Sub