Results 1 to 9 of 9

Thread: Find first

  1. #1
    Lounger
    Join Date
    Apr 2010
    Location
    Sofia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you help me build a code with FindFirst event? In my tabular form i have 2 fields AralCode and Code.I want to enter OnClick event in the field AralCode to find the same number in the field Code, if any.For example, if the number in the AralCode is 7777
    to find the number 7777 in the field Code.Can i do that?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Are you using DAO or ADO to work with your recordset? FindFirst is a method rather than event, and works with DAO recordsets, and for large recordsets can be pretty slow, even if the field in question is indexed. As to your specific question, are your two fields in the same table, or are they in different tables. If they are in different tables, the most common approach is to use a query with a join between the two tables based on the AralCode and Code fields. That makes it automatic. If they are in the same table, but the values are in different tables, then you may have to use an unbound control and do the population of the control for Code using VBA. Hope this helps - if you have further questions, please reply to this thread and we will try to assist you.
    Wendell

  3. #3
    Lounger
    Join Date
    Apr 2010
    Location
    Sofia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much for your reply.I am using DAO.The values are in one table. My table is called products.In the table products i have the following rows : " productid","productname","code" and "AralCode". For example if the AralCode is 7777 i want by clicking on it to find the number 7777 in the row called "code".If there is not such a number,to get the message " no code available".
    I will be much obliged if you help me

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by pelin View Post
    Thank you very much for your reply.I am using DAO.The values are in one table. My table is called products.In the table products i have the following rows : " productid","productname","code" and "AralCode". For example if the AralCode is 7777 i want by clicking on it to find the number 7777 in the row called "code".If there is not such a number,to get the message " no code available".
    I will be much obliged if you help me
    This is pretty much "air" code, as I didn't really test or debug it, but should give you the general idea:
    Code:
    Public Function FindMatchingRecord(StringToFind As String)
    
    Dim myDB As DAO.Database
    Dim myRec As DAO.Recordset
    
    Set myDB = CurrentDb()
    Set myRec = myDB.OpenRecordset("YourTable")
    myRec.FindFirst (StringToFind)
    If myRec.NoMatch Then
        MsgBox "No code available!", vbOKOnly, "No Match"
    Else
        myForm!CodeControl = StringToFind
    End If
    myRec.Close
    Set myRec = Nothing
    myDB.Close
    Set myDB = Nothing
    
    End Function
    You will have to substitute your table names and control names in the code above. I should also note that this sort of design for a table is a bit unusual. The only time we see this sort of thing is where you are doing a self-join with a table, and there is actually a pretty easy thing you can do in a query to acomplish this without writing any VBA.
    Wendell

  5. #5
    Lounger
    Join Date
    Apr 2010
    Location
    Sofia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your reply.I could not use your suggestion because i am sure i did not apply it correctly. Therefore i made an abridged table and form and sent it with an attachment.When i try to click for example on the AralCode 15462 i get the message " operation is not supported for this type of object". Would you help?

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The attachment does not seem to be present. Can you have another go at attaching it.
    Regards
    John



  7. #7
    Lounger
    Join Date
    Apr 2010
    Location
    Sofia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you. Sorry for the mistake.I am sending the attachement again
    Attached Files Attached Files

  8. #8
    Lounger
    Join Date
    Apr 2010
    Location
    Sofia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    The attachment does not seem to be present. Can you have another go at attaching it.
    Thank you. Did i send the attachement correctly ?

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Attachment was fine.

    But, not really sure what the purpose of this is for.
    Seems an odd application.

    Firstly what should actually happen when you find a code?
    Does it do anything with it or not?

    Do you want to move to the record or just leave the entry in the field?

    Also why use the on click event?
    This is triggered as soon as you click in the control
    You ought to use DoubleClick or AfterUpdate Events

    Anyway, whatever the case, the code you had as your function ought to read
    at the start.

    [code]
    Dim myDB As DAO.Database
    Dim myRec As DAO.Recordset
    'Need this line to build the correct find string syntax where code is a Number
    Dim strFind As String

    Set myDB = CurrentDb()
    Set myRec = myDB.OpenRecordset("products")
    'This creates the find string
    strFind = "
    Code:
    =" & StringToFind
    myRec.FindFirst (strFind)
    'The rest of the code is as it was....
    'As to what to do if the code is found

    The Line

    myForm!CodeControl = StringToFind

    as Wendell says is AIR Code and performs no function

    You could leave this blank and NOTHING happens
    Otherwise you could tell it to do something, but I have no idea what that would be.

    IF this is all to be handled within the Form anyway, and it is based upon the Products table
    You could handle all of it within the Forms Event, and just set MyRec = Me.RecordsetClone

    However, if it is being handled to an external Function, you could pass it the Recordset and the Code.
    Andrew

Posting Permissions

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