Gasman
Enthusiastic Amateur
- Local time
- Today, 14:26
- Joined
- Sep 21, 2011
- Messages
- 14,053
No Close, but no cigar.
Look at the syntax for dlookup properly.
Look at the syntax for dlookup properly.
thanks, but still none the wiser, I am a beginner and just about the smash computer up. Any chance of a simple example with explanation?No Close, but no cigar.
Look at the syntax for dlookup properly.
varX = DLookup("MemberExpiry", "Members", "MemberID = " & Me.MemberID)
or, if MemberID is a string:
varX = DLookup("MemberExpiry", "Members", "MemberID = '" & Me.MemberID "'")
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.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.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
Awesome, thank you , that did it. It still removed the brackets after date though.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
Try setting the Control Box property of the form to No.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 youTry setting the Control Box property of the form to No.
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
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 youthe 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