Form alert option (1 Viewer)

Minty

AWF VIP
Local time
Today, 14:26
Joined
Jul 26, 2013
Messages
10,355
This is as clear an explanation as I have ever found http://access.mvps.org/access/general/gen0018.htm

Make sure you understand the different data types. It's where 99% of newcomers get it wrong.
If a number is stored in a text field you have to treat it as text.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:26
Joined
Sep 21, 2011
Messages
14,053
That, while not being a wise choice might be an option. :)
You need patience to program, and especially when you start and are more prone to errors.

The links I gave you gives plenty of examples.

Basically you generally need 3 parameters for the function
1. What to return
2. Table of query to search
3. Optional, but the criteria for the lookup.

Strangely enough you appear to have got the criteria correct? Most people fail on that parameter.
We are now on a second page, so I cannot leave this and copy your code, so look closely at the syntax and my list of parameters above.

Once you get this correct, the syntax is the same for the other domain functions DSum,DMax etc.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:26
Joined
Sep 21, 2011
Messages
14,053
In fact it looks like you got your code from the first link, the ms site, but did not pay attention to the example? :(
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:26
Joined
Feb 19, 2002
Messages
42,983
syntax:
Code:
varX = DLookup("MemberExpiry", "Members", "MemberID = " & Me.MemberID)

or, if MemberID is a string:

varX = DLookup("MemberExpiry", "Members", "MemberID = '" & Me.MemberID "'")
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:26
Joined
May 7, 2009
Messages
19,175
what is the RecordSource of your Form?
is it Members table also?
if the answer is yes, then you do not need to use DLookup():
Code:
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''''''''''

If Nz(![MemberExpiry], 1) < Date() Then
    DoCmd.OpenForm "red"
End If


'''''''''end of trial code'''''''''''
End If
End Sub
 

amd

New member
Local time
Today, 14:26
Joined
Dec 14, 2021
Messages
17
what is the RecordSource of your Form?
is it Members table also?
if the answer is yes, then you do not need to use DLookup():
Code:
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''''''''''

If Nz(![MemberExpiry], 1) < Date() Then
    DoCmd.OpenForm "red"
End If


'''''''''end of trial code'''''''''''
End If
End Sub
Thank you arnelgp for this, I will give it a try shortly and get let you know how I got on. Yes I only have one members table which is the source for the form.
 

amd

New member
Local time
Today, 14:26
Joined
Dec 14, 2021
Messages
17
what is the RecordSource of your Form?
is it Members table also?
if the answer is yes, then you do not need to use DLookup():
Code:
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''''''''''

If Nz(![MemberExpiry], 1) < Date() Then
    DoCmd.OpenForm "red"
End If


'''''''''end of trial code'''''''''''
End If
End Sub
Hi, I have tried the code and just get an error (see attched image), my field is called MembershipExpiry and is formated as a date. When I type the Date() in, it automatically removes the brackets.

I also had to remove the last End if due to another error.

I have added my table in design view if that helps, there are a few more fields, but they are not relevant to this.

Many thanks for your help.
 

Attachments

  • error1.PNG
    error1.PNG
    12 KB · Views: 75
  • info.PNG
    info.PNG
    10.5 KB · Views: 79

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:26
Joined
May 7, 2009
Messages
19,175
can you post the RecordSource of your Form.
Obviously MembershipExpiry field is not on your Recordsource.

//Edit:

add rs! to your code:

If Nz(rs![MembershipExpiry], 1) < Date() Then
DoCmd.OpenForm "red"
End If
 

amd

New member
Local time
Today, 14:26
Joined
Dec 14, 2021
Messages
17
can you post the RecordSource of your Form.
Obviously MembershipExpiry field is not on your Recordsource.

//Edit:

add rs! to your code:

If Nz(rs![MembershipExpiry], 1) < Date() Then
DoCmd.OpenForm "red"
End If
Awesome, thank you , that did it. It still removed the brackets after date though.


Another question if possible for anyone, is there anyway to remove the minimise, restore and close buttons in the top right corner of forms to stop them being closed down without clicking a button on the form to authorise it being closed?

Thank you again
 

Attachments

  • bar.PNG
    bar.PNG
    14.9 KB · Views: 87

theDBguy

I’m here to help
Staff member
Local time
Today, 07:26
Joined
Oct 29, 2018
Messages
21,358
Another question if possible for anyone, is there anyway to remove the minimise, restore and close buttons in the top right corner of forms to stop them being closed down without clicking a button on the form to authorise it being closed?
Try setting the Control Box property of the form to No.
 

Isaac

Lifelong Learner
Local time
Today, 07:26
Joined
Mar 14, 2017
Messages
8,738
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

the really really simple solution (and possibly would be acceptable to you based on what I am sensing the sophistication level desired is or is not), is simply an InputBox ...

This may give you some ideas if you play with this sub running it..

Code:
Sub Test()
Dim strPassword As String, strInput As String
strPassword = "ThePassword"

Do Until strInput = strPassword
    strInput = InputBox("Here is a message.  Now enter the password to get rid of this thing", " ")
Loop

End Sub
 

amd

New member
Local time
Today, 14:26
Joined
Dec 14, 2021
Messages
17
the really really simple solution (and possibly would be acceptable to you based on what I am sensing the sophistication level desired is or is not), is simply an InputBox ...

This may give you some ideas if you play with this sub running it..

Code:
Sub Test()
Dim strPassword As String, strInput As String
strPassword = "ThePassword"

Do Until strInput = strPassword
    strInput = InputBox("Here is a message.  Now enter the password to get rid of this thing", " ")
Loop

End Sub
thank you
 
Last edited:

Users who are viewing this thread

Top Bottom