Persistence across sessions

[RonL]

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

[ssanfu]

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

[June7]

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.

[RonL]

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

[June7]

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'?

[RonL]

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

[June7]

The listbox (or combobox) would have Jobs table as a ControlSource or did you mean RowSource?

[RonL]

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

[June7]

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.

[alcapps]

to answer one of your questions you can read this..
http://office.microsoft.com/en-us/ac...010238988.aspx

hope that helps..

[RonL]

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

[June7]

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!