Form alert option

[Minty]

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]

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]

In fact it looks like you got your code from the first link, the ms site, but did not pay attention to the example?

 

[arnelgp]

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():

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]

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():

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]

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():

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.

 

[arnelgp]

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]

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

 

[theDBguy]

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.

 

[amd]

Try setting the Control Box property of the form to No.

thank you

 

[Isaac]

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

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]

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

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