0

Im building a key inventory mgmt system. I've created a query that show's me keys currently not in use by identifying which keys have been returned, aren't lost or have never been rented. I copied this query into the look up field for key_id in my keyActivity table (used to record key sign outs). The issue is that the query does not update to provide available keys until the table keyActivity is closed and opened again

Example: I open keyActivity, indicate that key_id = 5 is lost. When I go to a new record and select the key to sign out, key_id = 5 is presented as being available. It is not until I close the table, open it again, that key = 5 is removed from the list.

Here you can see key 5 is indicated as lost in id 5 but in id 7 when selecting a key, 5 is available when it shouldn't be. enter image description here

Is there anyway to fix this or set this up to work as intended. I plan on using forms to present all the information. Is there a form solution perhaps?

Community
  • 1
  • 1
Batman
  • 5,563
  • 18
  • 79
  • 155
  • This is caused by [record locking](http://en.wikipedia.org/wiki/Record_locking). I haven't used Access in years, but it used to lock several records at once or an entire table. Try changing the [default locking strategy](http://office.microsoft.com/en-gb/access-help/set-options-for-a-shared-access-database-mdb-HP005188297.aspx) to 'edited record'. The option is available in tool\options\advanced. – Steve Jan 13 '13 at 08:10
  • I changed it to 'edited record' but it still shows up in the dropdown. – Batman Jan 13 '13 at 08:24
  • I assume if I use a form I can put in a code that will refresh the form (and table within it) on record updates. From there, perhaps the lookup query will update and show the more recent keys available? Something like me.refresh or me.requery? Do you think that might solve the problem? – Batman Jan 13 '13 at 08:39
  • @Batman can you clarify what `keyActivity` is? Is that your this main form name? You need to trigger to update each time you choose a `key` from these display items. To say a **[`requery`](http://office.microsoft.com/en-us/access-help/refresh-or-requery-data-HA010256400.aspx#BM1)** – bonCodigo Jan 13 '13 at 09:37
  • @bonCodigo I don't have anything in forms yet. keyActivity is the main table were registration occurs. I guess when I do create the forms and place some code to update, this won't be an issue? – Batman Jan 13 '13 at 09:49
  • @Batman it depends again for the reasons I have given. For concurrency updates and locking [Jet database engine offers control of your record locking when accessing it programmatically (as opposed to through the Access UI)](http://stackoverflow.com/questions/694921/ms-access-mdb-concurrency).. Once you have created your form and tried out please let us know if you any issues. – bonCodigo Jan 13 '13 at 13:08

1 Answers1

2

The suggestion you would be better off with a Form to change table data. It can be easily requery-ed to update the table according to the changes you make and to display the udpated data accordingly. Please also read on the given references for further info.

In terms of data updating and locks in a multi user environment this article could be helpful.

"Access is NOT a database server. It's a desktop database. It has been pushed to the limit to support mutli-user environments, but only in the sense that you can share the "back end" database across a network." ... ...

"Even the record locking is performed by the Front End. All of the front end database applications share the "lock file" (a file with the same name as the database file, but with the extension LDB); but that file is simply a mechanism that the front ends use to determine which front end can make changes to the database." ....

Here is a difference between requery and refresh:

Me.Requery forces the entire recordset (underlying data) for the form to reload. This means ALL of the records in your current form will reload. Your current position will be lost, so if you're sitting on record 10 of 100, you'll find yourself back on the first record. Me.Requery is essentially the same as closing and reopening the form. Any new records added by other concurrent users will be available. Likewise any records that have been deleted will disappear. Requery essentially "re-runs the query" that pulled the data into the form in the first place. You can also use requery to update the data in a list box or combo box.

Me.Refresh saves the current record that you're working on. It will also retrieve any changes (but not additions or deletions) to any records shown in the current form. Any calculations on the form (unbound fields) are recalculated. Refresh does NOT reload the recordset. You do not lose your position in the form (you stay on the current record). Any new records added by other users will not be shown.

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91