Page 1 of 3 123 LastLast
Results 1 to 15 of 34

Code to find record that matches a text in a control box

  1. #1
    Marcia's Avatar
    Marcia is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    37

    Code to find record that matches a text in a control box

    I have a form where a string, say '2006-1000' is entered in a control box "PreviousNo" (this is the current but unfinished record). On exiting the "PreviousNo" box the cursor must go to "TaxDecNo" control, find the record that matches the string entered in the "PreviousNo" then open that record. On the matching record is a yes/no box. After selecting yes, the cursor then goes back to the "NameOfOwner" control box of the current record for the user to continue filling up the form. I hope I am making sense. my knowledge is limited to GoToControl so please help provide the code for the ff events:
    1. On exit from "PreviousNo", go to "TaxDecNo" control


    2. Find the value matching the "PreviousNo" entered in the unfinished record
    3. Open the matching record
    4. After yes is selected by the user in the matching record, go back to the "NameOfOwner" control of the current and unfinished record
    Thank you.

  2. #2
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,040
    Below code assumes your form is bound. I had to guess at some control names.
    Code:
    Private Sub PreviousNo_AfterUpdate()
        Dim rstClone As DAO.Recordset
        Dim strSearch As String
        strSearch = Trim(Me.PreviousNo)
        If strSearch <> "" Then
            Set rstClone = Me.RecordSetClone
            rstClone.FindFirst "[PreviousNo] = '" & strSearch & "'"
            If rstClone.NoMatch Then
                MsgBox "not found."
            Else
                Me.Bookmark = rstClone.Bookmark
                TaxDecNo.setfocus
           End If
       End If
       On Error Resume Next
       rstClone.Close
       Set rstClone = Nothing
    End Sub
    
    Private sub chkYesControl_AfterUPdate()
        if chkYesControl = true then    
            me.NameOfOwner.setfocus
        end if
    End Sub
    Last edited by davegri; 09-01-2019 at 10:43 AM. Reason: quotes clarif

  3. #3
    Marcia's Avatar
    Marcia is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    37
    Thank you Davegri. There is a problem here. The code should search the value of the "PreviousNo" in the records of the "TaxDecNo" field.
    Last edited by Marcia; 09-01-2019 at 04:28 PM. Reason: Change attachment

  4. #4
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,040
    OK, search that field...

    Code:
    Private Sub PreviousNo_AfterUpdate()
        Dim rstClone As DAO.Recordset
        Dim strSearch As String
        strSearch = Trim(Me.PreviousNo)
        If strSearch <> "" Then
            Set rstClone = Me.RecordSetClone
            rstClone.FindFirst "[TaxDecNo] = '" & strSearch & "'"
            If rstClone.NoMatch Then
                MsgBox "not found."
            Else
                Me.Bookmark = rstClone.Bookmark
            End If
       End If
       On Error Resume Next
       rstClone.Close
       Set rstClone = Nothing
    End Sub
    Private sub chkYesControl_AfterUPdate()
        if chkYesControl = true then    
            me.NameOfOwner.setfocus
        end if
    End Sub

  5. #5
    Marcia's Avatar
    Marcia is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    37
    Great! One more issue that cropped up. I failed to mention that there is a sub form so when "Yes" is clicked, the cursor jumped to the sub form where there is a "NameOfOwner" field. It should go back to the "NameOfOwner" field of the current/uncompleted form. I'm sorry for giving piecemeal information. Thank you.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,275
    davegri seems to be off line, so maybe I can pitch in. If this code is on the subform and you want focus to shift to a control on the main form, then

    Code:
    Private sub chkYesControl_AfterUPdate()     
    if chkYesControl = true then    
       Forms!NameOfYourMainForm.NameOfOwner.setfocus
    end if
    
    End Sub
    Sorry if I'm out to lunch!
    Can't say I'm a fan of having controls in a form/sub form relationship where any controls have the same name.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  7. #7
    Marcia's Avatar
    Marcia is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    37
    Thank you Mike. I copied the edited code and it still came out with the same problem. I am attaching the snapshots of the result. I didn't know that a main form has a name, I thought the default name is "MainForm" so I copied that into the code replacing "NameOfYourForm". After selecting "Yes", the next name in the record opens (TaxDec3.png), not the current form I am working on (TaxDec1.png) When I deleted the Sub Private ChkYescontrol code, it still skips to the subform. TaxDec1.zipTaxDec2.zipTaxDec3.zip

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,275
    You'll need to add the images to your post rather than upload them as zipped files. Downloading every pic, unzipping it, opening it not only takes too long (my Windows photo viewer is painfully slow) I end up with files I don't need. Much easier to see them as images in your post.

    Form and Forms are among the many reserved words that you should not use, so no, there is no such thing as a default name "form" for a form and I suspect it's a word you would not be able to use if you tried. Your last post doesn't indicate that you substituted your form name, but rather you used the word "form". The form name is evident in the navigation pane where all your db objects such as queries/tables/reports are listed.

    My answer was based on the understanding that the code I copied was on your subform. If it isn't, then it's probably of no help.

  9. #9
    Marcia's Avatar
    Marcia is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    37
    Point taken on the zipped images. I changed the form names into MainForm and MainTable subform but the problem still persists. Does the tabbed form have any bearing on the code? I'm so useless in trying to find the broken link, meantime after selecting yes, I click the "last record button" to return to the current form.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,275
    @davegri, I can butt out (I don't want to hijack your assistance). I'd say it's time to post a zipped copy of the db in question as there are too many questions for me. I can't tell if the first form is a search form (why go to another form to finish a record if it's a bound form instead?) or if there's a form on a navigation form, or a form on a subform or what. I've got a lot of outside work to do so I don't have a lot of time to guess through it.

    @Marcia, so why not replace the zipped image files with the actual picture files (inserted into your post commentary)? Consider posting a zipped db copy as noted above, unless davegri is ok with things as they are going.

  11. #11
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,040
    I have pretty much the same questions as Micron. Too many unknowns at this point. A zipped copy of the DB would be best.

  12. #12
    Marcia's Avatar
    Marcia is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    37
    Micron and Davegri, you are such helpful souls. Attached is the sample file. I removed the tabbed forms because the formula in Sex and Address would not work until I removed the tabs. I am still in trial and error stage so if what I made should be shot to Mars I won't be offended because you will suggest a better structure.Real propety Tax Declarations.zip

  13. #13
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,040
    I didn't see the reason for the subform. Both the main form and subform were based on the same table. The way to show that configuration is with a split form. The advantage of the split form is that if you click on a row in the datasheet, the main form instantly shows that record.
    There were no primary keys defined. I added autonumber primary keys, added a foreign key to MainTable for the owner and established referential integrity.
    Real propety Tax Declarations davegri-v01.zip

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,287
    Marcia,

    Can you step back an give us a 4 or 5 line overview of what the business is in plain English?
    You're asking about details of values on forms, but that represents How you have set up your database in Access.
    What do PreviousNo and TaxDecNo represent? How do they relate?

    MainTable is not very informative. NameofOwner would seem to only allow a single owner --could you have multiple owners?
    PreviousNo and PreviousOwner allow for only 1 value each. If you need to follow the ownership history of a property/item, your structure precludes this.
    Every table in a relational database should have a primary key.

    I admit to not knowing your business context, but I feel you have several entities within your MainTable structure.

    Good luck with your project.

  15. #15
    Marcia's Avatar
    Marcia is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    37
    HTML Code:
    Can you step back an give us a 4 or 5 line  overview of what the business is in plain English?
    I am helping my assessor friend who is 3 years shy from retirement. He wants to leave a legacy in the form of a computerized system of recording and managing a real property assessment database. The assessor's office keeps more than 200,000 tax declarations of real estate properties issued by the local government, they do not issue titles. It issued an original tax dec to Mary numbered 2006-22753 recorded under the TaxDecNo field. The remarks is "New". This was revised by tax dec number 2006-16546. Revision is made when there are changes in ownership, assessed values, market values, area or boundaries. When the user adds the Tax dec record 2006-16546 (TaxDecNo) in the form, 2006-22753 is entered in the PreviousNo field. After entering the Previous Number, the user must find the 2006-22753 record to mark the "Revised" field to "Yes". The default of "Revised" field is "No" until the tax dec is revised.
    When all the tax decs have been entered in the database, the ff reports should be generated:
    1. A list of all un-revised properties owned by Mary or any owner for that matter
    2. An index of the history of Tax Dec 2006-22753. The report should show that it was revised by 2006-16546. Tens of thousands of tax decs issued in the 1950s had undergone at least 7 revisions.
    3. Other general purpose reports
    The office keeps a file of the tax decs in the year they were issued, there is no separate file for each owner.
    Early on I haven't appreciated the significance of a primary key. I will look into Davegri's post.
    I hope this clarifies the project we are working on. Thank you.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-16-2018, 09:15 PM
  2. Replies: 10
    Last Post: 04-04-2018, 10:36 AM
  3. Replies: 11
    Last Post: 02-21-2016, 02:34 PM
  4. Replies: 4
    Last Post: 08-01-2014, 09:20 AM
  5. Help: Comparison query to find non-matches
    By 14erclimber in forum Queries
    Replies: 6
    Last Post: 06-09-2010, 09:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums