Results 1 to 12 of 12
  1. #1
    RonL is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Location
    NJ
    Posts
    114

    Persistence across sessions

    Another beginners question if I may. Let's say I have a database with several jobs in it. The main form shows the data for a particular job whose name is given in a list box (which is bound to a "lookup table" containing job names with each jobname's primary key represented by a foreign key in the main table, because the record source for the main form is a query limited by the foreign key of that job.). The user will be working with this set of data for some time. What's the proper way to compel the job name in the list box to persist across sessions? Store it on closing the database in a 1-record table? If so, how do I load that combo before the form's recordsource needs to fire.



    thanks, Ron
    Last edited by RonL; 01-29-2013 at 07:00 AM. Reason: Clarity

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    As a start, see this page at Allen Browne's site
    Return to the same record next time form is opened - http://allenbrowne.com/ser-18.html

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Not only would you have to store the job ID but also the user ID then need to know which user is using the db and load their job when the form opens. Changing the RowSource of the combobox is not relevant because it is a bound combobox. Controls used to enter search criteria should not be bound to field, otherwise you would change data in a record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    RonL is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Location
    NJ
    Posts
    114
    Thanks June & Steve. That link will help a lot - it confirms my notion of a "memory table." June, only one or two users, no login or the like. The concern was to return to the same job - ie. a subset of the entire db - hence the thought that I needed to ascertain the job key so that it could be placed in the SQL where clause for the forms recordsource before the latter actually fires. Realize now, I probably don't need to "set the listbox first (which would contain the job key), then have the forms recordsource use it to fire," since I all unique id's are already in the current record and can be saved from there upon unload. Sorta like updating a one record join table just before closing. (Will still need that "job listbox," so the user can change the job at will, but I can refresh that from the "sys" table as well.) Gotta think when I have time, but thank you for putting me on track! (I edited first post for clarity.) -Ron

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    You said the job listbox 'is bound to a "lookup table" containing job names'. Is this control actually 'bound' - has a ControlSource? Or are you referencing the RowSource when you say 'bound'?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    RonL is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Location
    NJ
    Posts
    114
    Quote Originally Posted by June7 View Post
    You said the job listbox 'is bound to a "lookup table" containing job names'. Is this control actually 'bound' - has a ControlSource? Or are you referencing the RowSource when you say 'bound'?
    I'm sorry June, you're right of course, I meant that the listbox (or should it be combo?) would have the Jobs table as a ROWSOURCE (Corrected in Edit). Btw by "job" I mean "project" - ie. an independent entity as opposed to, say, a descriptor for an individual. (For the most part, there will be only one "job" active at a time.)

    I haven't actually coded anything yet, or even created all the tables. Just planning things out in my head. The idea is for the listbox to provide the filter for the recordsource (query) of the main form. I know that's basic functionality, but I'm still not clear exactly how it will work on form load. If a form's recordsource depends on an unbound control, how does one ensure that the unbound control is populated before the recordsource fires? Hopefully the AllenBrowne technique will clarify.

    I know methods are better worked out if one actually tries to do them first :-) Will be back with questions if needed.

    Sorry for the confusion. Many thanks, Ron
    Last edited by RonL; 01-29-2013 at 05:49 PM. Reason: Vital Correction

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The listbox (or combobox) would have Jobs table as a ControlSource or did you mean RowSource?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    RonL is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Location
    NJ
    Posts
    114
    Quote Originally Posted by June7 View Post
    The listbox (or combobox) would have Jobs table as a ControlSource or did you mean RowSource?
    I keep making the same mistake! RowSource, and I corrected the post above. So sorry to waste your time correcting me.

    In case it won't necessarily clarify as I get into coding, let me paste the generic question from above: If a form's recordsource depends on an unbound control (in the same form) - ie. a particular record in a ROWSource lookup table - how does one ensure that, on form load, the unbound control is populated before the form's recordsource fires?

    -Ron

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Not sure about the order of events. I think the Open event would work. There are many ways to filter the form with code.

    1. Did you say you are using a parameterized query as the form's RecordSource? Query the table for the 'current' job and set value in the combobox then requery the form.

    2. From another form (possibly a MainMenu) open the form filtered to the 'current' job using the WHERE CONDITION argument of DoCmd.OpenForm. Unbound combobox for filtering then becomes irrelevant because only the one record is in the form's recordset.

    3. Query table for the 'current' job and set the Filter and FilterOn properties of the form. Unbound combobox code could reset the Filter property for selected job if you want to allow users to switch to another job on this form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    to answer one of your questions you can read this..
    http://office.microsoft.com/en-us/ac...010238988.aspx

    hope that helps..

  11. #11
    RonL is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Location
    NJ
    Posts
    114
    Thanks to you both. As to June's question about parameterized query, if you mean one where the system actually solicits input from the user, no, not that. As mentioned, I'm just thinking ahead at this point. I started out thinking in terms of only a single "job." Then I figured it's a simple extension to store multiple jobs in the same table, hence the notion of a related table containing the job names, and an unbound lookup control with which the user could switch jobs mid-session. So I was just going to define the recordsource of the form as a simple Select etc. 'where JobID= expression in the listbox" followed by a form requery - ie. the classic way you switch the recordset of a subform (although I don't see need for a subform in this context, since I'm planning a display format where the unbound listbox would go in the header). But then I realized that when the user leaves off a session, he'll want the next one to start up in the same job, indeed the same record, where he left off. Hence this thread. I think my first try will be June's suggestion #1, not via a parameterized query, but simply by looking up the "bookmark" data per the AllenBrowne approach mentioned above. The thing is, knowing the sequence of events (thanks akapps) doesn't necessarily tell me how to first populate the listbox so that the form's recordset will populate properly from its recordsource definition. Yes, it may require manipulating the recordsource in code. Or maybe MainForm/SubForm is really necessary to handle this properly (?). One thing I know for sure, the user wants to add records in a spread-sheet simulated environment, so I gotta have a datasheet-like environment, probably a continuous form.

    It certainly is important to understand the firing sequence of events when a form loads. But from my (very limited) experience, it can nevertheless be tedious to work out where the necessary pieces of code should go. Will be back, as needed, after I get into it.

    Thanks again, Ron

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Parameterized doesn't necessarily mean input from user. It just means the query has filter criteria. If dynamic, there will be user input which can be by popup prompt or reference to controls on form. Good luck and happy programming!
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-22-2011, 02:24 PM
  2. Replies: 5
    Last Post: 08-30-2011, 09:17 AM
  3. Replies: 1
    Last Post: 06-16-2010, 09:25 AM

Tags for this Thread

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