Hi,

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.

On_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;

UPDATE STOCK_TBL SET ON_LOAN = 'TRUE'
WHERE STOCK_ID=FORMS!LOAN_TBL!STOCK_ID;

But when I run this it is coming up with security errors.

Security errors????? What are these errors?????

I 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