Full Version: Way out of my depth and in need of assistant
UtterAccess Discussion Forums > Microsoft® Access > Access Forms

I'm creating a basic database for some uni work and it was advised to use Access. It's been years since I've used it and have had to keep re-kindling my memory on pretty much everything, including VBA code.

I'm now stuck. When I save a record on my Loan Form, I also need the save button to automatically change the [On_Loan] field of the stock record being loaned to 'True'. This record is stored in the [Stock] table - but I have no idea as to how I would go about doing this.

Any help would be greatly appreciated. Thanks

Edited by: Cramp on Mon Jun 1 9:06:42 EDT 2009.
Welcome to UA!
Is [On_Loan] bound to a control on your form? If so, set the .Visible property to No so users don't see it (or leave visible but .Locked = Yes), and then right before your process which saves the record, set Me.{NameOfYourControlHere} = True.
Hi, thanks for your reply.
n_Loan is not part of this Form, it is part of a separate table called Stock. My 'Available Stock' list box is populated on form load by an instock query that searches for all the Stock in the Stock Table with On_Loan as 'False'. So when a Loan is saved using the Save button, the On_Loan field in the Stock database should be set to 'True', so it will not appear when the Loan form is reloaded - if that makes sense.
Your solution would work, but only if there was a piece of code which would allow me to designate the save to the correct table, for example, [Tables]![Stock]![Stock_ID].[On_Loan] = Me.[On_Loan_Status]... but I realise this code is completely wrong.
What I'm trying to do now is write an SQL query to change the status. I've devised the code;
But when I run this it is coming up with security errors.
Security errors????? What are these errors?????
assume your field is Data Type Yes/No, therefore the part of your query 'TRUE' should be just True. Here is a link to the UPDATE SQL syntax: http://www.w3schools.com/Sql/sql_update.asp
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.