Results 1 to 8 of 8
  1. #1
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107

    navigating records on form

    I have a data entry form that I use to enter inventory tickets. The recordsource for this form is an SQL table that is in ticket number order. This table contains about 7000 records. When an inventory ticket is entered, the record for that ticket is marked counted by setting a field called “status” to “C”. I want to be able to navigate my ticket table using this form in three ways:


    1. When the form is first opened, I want to go to the first uncounted ticket.
    2. As tickets are entered, I want to skip over already counted tickets when scrolling to next record.
    3. I want the user to be able to pull up any record (counted or not) by entering the desired ticket number in the ticket number field.
    What is the best method to handle this? I don’t think I can filter because I need access to ALL records. I believe the docmd.findrecord method might be best, but the form opens a little slow and I am also having trouble determining which event I need to use to put my logic in for finding a specific ticket number.

    Please advise.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Dealing with SQL will definitely be slow. Depending on the number of records, you may want to think about first creating a temp table of uncounted records. On form open, use the filter and keep it going while you scroll thru the records. If a ticket is entered and doesn't exist on the filtered set of records, remove the filter, show the record, then replace the filter afterwards.

  3. #3
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    A temp table is not an option because we have multiple users entering tickets at the same time.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Do the other then.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMHO, to do exactly as you say in one form loaded with one recordset may not be possible. Consider the following:
    1 - as long as the data source isn't a table, you should be able to base the form on a query. However, #3 requires that you load all records, but apply a filter where the Status is Null. Whatever sort order you apply in the query would dictate what comes "first" as long as it works satisfactorily.
    2 - since the filter shows only where Status is not C (it is Null) you will get no C status records anyway.
    3 - this is where the problem comes in. If you base the apply/remove filter decision based on a form textbox containing the ticket number and that textbox is bound, then the problem becomes one of an alternating filter (on/off) between Null and values that may be there, as you navigate through records. If you're only going to use the textbox as a search tool and it won't be bound, then the AfterUpdate event of the textbox will need to modify the filter so as to restrict the form's records to be those that contain that value. As you navigate off that record, the original filter would need to be reinstated and applied (Current event).

    An alternative is to divide into to processes. One where you open the form to satisfy the first two goals, the other to go to a specific record. That could be done via a 'process selection' type of form, or a button on the open form that will close it and reopen it to suit.
    Last edited by Micron; 01-17-2018 at 10:27 AM. Reason: spelin
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried to set the sort order of the form's recordsource to have it sorted ascending on the Status field and descending on the Ticket Number field (in this order)? Now what would be left to implement number 3 is to replace the default navigation buttons at the bottom of the form with your own custom ones and add code to get the current record's bookmark, requery the form (this would push any newly "c"ounted records to the buttom), find the current record then go next or previous depending to which button was pushed.

    Cheers,
    Vlad

  7. #7
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Thank you all. Y'all have convinced me filtering is the way to go and y'all have me pointed in the right direction. I will implement your suggestions and let you know the results.

  8. #8
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    It was quite simple after you all's suggestions got me to think about it correctly. I put a filter on the status field in the form properties, but I did not apply it on load in the properties. I prompt the user for the ticket number they want to begin with when opening the form. If they choose a ticket number that has already been entered, I leave the filter off. If they choose one that has not been entered, I turn it on. The user at any point can click on the ticket number field to choose another beginning ticket number. I then go through the same steps.

    Thanks again for all of your valuable suggestions.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 09-12-2019, 07:40 PM
  2. Replies: 1
    Last Post: 07-06-2017, 05:55 AM
  3. Replies: 1
    Last Post: 01-05-2017, 11:28 PM
  4. Navigating Records on unbound form with VBA
    By redbull in forum Access
    Replies: 21
    Last Post: 07-03-2013, 10:26 AM
  5. No navigating through records??
    By cvansickle in forum Forms
    Replies: 2
    Last Post: 10-27-2010, 04:05 PM

Posting Permissions

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