Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check if on list (office xp)

    Hello,
    I have a Data base for a Club which works pretty well and i use the same format each year, but i need to create some type of control to look up a query with regards to the previous year. I have the query that shows who won Novice classes last year as they are not allowed to enter them this year. On my form i have a text box for class Number which when the number is put also populates the class name i need some sort of control that when the class number is input it will check the query to see if a particular person won that class last year . Hope this is clear Thanks for any help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if on list (office xp)

    Hi Les,

    Long time no see! Could you provide some details about which tables and which fields are involved?

  3. #3
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if on list (office xp)

    Hi Hans,
    Yes long time Its that time of year again Horse Show time Using the same Data Base you Helped me with Last year.
    The rules of our shows State that if a person has won a novice class the previous year they are not allowed to enter that Class this year.
    I have a Query from last Year Titled Winners of Novice classes which i have saved as a Table for this year.
    On my form i have a text box for class Number which auto populates the Class Name Text Box and enters information on to a table Called Entries, from which i create all my queries etc.. I have Created a Macro on the form on the Class Number Text Box which opens the Winners of Novice Classes Table for me to check if
    The person i am putting in has won a novice class or not, THe macro Is
    1 Open Table Datasheet Read only 2 Msg Box do you want to save entry 3 Close on the close side i need an option to save or not save the entry
    At present if I only have the option Yes /No /Prompt if the person is on that list i need to put them in a different class, so i need a yes /No button
    If I press Yes it will save and exit table If No it wont save and exit table back to form to enable me to select a different class.
    Hope this makes sense
    Hope all is well with you i am still in Longdendale we are getting a few snow flurries tonite

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if on list (office xp)

    Les,

    I would put VBA code in the Before Update event of the Class Number text box. Let's say that the Novice class has number 37.

    Private Sub Class_No_BeforeUpdate(Cancel As Integer)
    ' See if user entered Novice class
    If Me.Class_No = 37 Then
    ' See if member has won Novice classes last year
    If Not IsNull(DLookup("Member", "Winners of Novice Classes", "Member = " & Chr(34) & Me.Member & Chr(34))) Then
    ' Inform user
    MsgBox "This member cannot enter Novice Class this year!", vbInformation
    ' And cancel the update
    Cancel = True
    End If
    End If
    End Sub

    You will have to substitute the correct names.

    I'm fine; we're going to have some sleet/snow/hail the coming days, but otherwise, it's a pretty mild winter.

  5. #5
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if on list (office xp)

    Hans,
    Me Again
    Script works ok, but there are 7 novice classes not just one, tried to alter your script but not getting anywhere,
    Help

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if on list (office xp)

    Hi Les, alter it like this:

    Private Sub Class_No_BeforeUpdate(Cancel As Integer)
    ' See if user entered Novice class
    Select Case Me.Class_No
    Case 37, 40, 42, 43, 56, 78, 92
    ' See if member has won Novice classes last year
    If Not IsNull(DLookup("Member", "Winners of Novice Classes", "Member = " & Chr(34) & Me.Member & Chr(34))) Then
    ' Inform user
    MsgBox "This member cannot enter a Novice Class this year!", vbInformation
    ' And cancel the update
    Cancel = True
    End If
    End Select
    End Sub

    Of course, you must substitute the actual numbers of the seven novice classes in the line Case 37, 40, 42, 43, 56, 78, 92.

  7. #7
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if on list (office xp)

    Thanx Hans,
    Now works, i had If ME Class_no = 1,2,3,4,
    One last thing and i will leave you in peace, is it possible to to query Member and Horse or Pony Name in this Script as a a combinationas for if a member obtains a new horse they can then enter a novice class as a new combination i have a table showing Showing all the combinations of Member and Horse or pony, I have tried see under
    Private Sub Class_No_BeforeUpdate(Cancel As Integer)
    ' See if user entered Novice class
    Select Case Me.Class_No
    Case 2, 16, 20, 21, 40
    ' See if member and horse or pony has won Novice classes last year
    If Not IsNull(DLookup("Member","Horse or Pony Name", "Winners of Novice Classes", "Member and Horse or Pony Name = " & Chr(34) & Me.Member and Horse or Pony Name & Chr(34))) Then
    ' Inform user
    MsgBox "This Member cannot enter This Novice Class this year!", vbInformation
    ' And cancel the update
    Cancel = True
    End If
    End Select
    End Sub
    Keeps Coming up with error
    Thanx

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if on list (office xp)

    If you have field names with spaces, you must enclose them in square brackets. But is "Member and Horse or Pony Name" the name of a control on your form? If so, try this:

    If Not IsNull(DLookup("[Member and Horse or Pony Name]", "[Winners of Novice Classes]", "[Member and Horse or Pony Name] = " & Chr(34) & Me.[Member and Horse or Pony Name] & Chr(34))) Then

    If "Member" and "Member and Horse or Pony Name" are separate controls on the form, you must probably use something like this:

    If Not IsNull(DLookup("[Member and Horse or Pony Name]", "[Winners of Novice Classes]", "[Member and Horse or Pony Name] = " & Chr(34) & Me.[Member] & " " & Me.[Horse or Pony Name] & Chr(34))) Then

  9. #9
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if on list (office xp)

    Its Me Again Hans,
    Thanks for script but cant get it to work, the controls on the form. Members name linked to Members Name Table, Horse or Pony name linked to horse or pony name table, Members name is not shown in Horse or pony Table only membership Number Animals are linked to Member by a combination ID
    the relevant controls on form are Name and Members No combo Box, Class No text box and Horse or Pony combo box,
    Tried changing Members and Horse or Pony Name in script to Horse and Pony Name Only also [Members],[Horse or Pony Name] etc cant get it to work.
    Any Further Help sorry

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if on list (office xp)

    This is too hard to follow without seeing the database. Could you post a stripped down version?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it. (Of course, this only helps if you're using Access 2000 or later.)
    <LI>Attach the zip file to a reply.[/list]Thanks.

  11. #11
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if on list (office xp)

    Hans,
    On Form Class Number when code put in only code that will run is the first one you sent i need to check against Member and Horse and Pony
    Zip attached
    Attached Files Attached Files

  12. #12
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if on list (office xp)

    Sorry Hans,
    Missed a Form off you need to operate
    new zip attached
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if on list (office xp)

    My best bet would be this:

    Private Sub Class_No_BeforeUpdate(Cancel As Integer)
    ' See if user entered Novice class
    Select Case Me.Class_No
    Case 2, 16, 20, 21, 40
    ' See if member has won Novice classes last year
    If DCount("*", "Winners of Novice Classes", "Member = " & Chr(34) & Me.Member & Chr(34) & _
    " And [Horse or Pony Name] = " & Chr(34) & Me.[Horse or Pony Name] & Chr(34)) > 0 Then
    ' Inform user
    MsgBox "This member/pony or horse combination cannot enter a Novice Class this year!", vbInformation
    ' And cancel the update
    Cancel = True
    Me.Class_No.Undo
    End If
    End Select
    End Sub

    but since there are no data to test it on in your database, I can't be sure.

  14. #14
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if on list (office xp)

    Hans,
    Thanks for code, but could not get it to work maybe i am doing something wrong.
    THe Data in the table Winners of Novice classes is what i am trying to validate so that if a Member and horse or Pony name appears anywhere in that table as a combination they will not be allowed to enter the same Novice Class this year, however if the Member enters with a new Horse or pony they will not be on the table as a combination and therfore they can enter the class

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if on list (office xp)

    In the database as posted by you, the combinations have all been cleared, so after choosing a member, there are no horses/ponies to choose from. So I can't test anything.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •