Hi...I have a simple loan database, and on the return form most of the fields are bound to the table tblLoanRecord, and the form opens from a list of Items currently out. The return form needs updating with name of person who checked it in, date checked in, days overdue (etc) and these fields exist in tblLoanRecord. I had these fields bound, so that as soon as data entered form it goes straight into table HOWEVER...this can cause problems if person checking it in makes a mistake or gets interrupted, as the data is already changed in the table - and then the item status is changed to 'available' when it should be still 'out'.
So what I decided I should do is obviously keep the bound fields such as 'Item, Loaned To, Date Loaned...etc', but then make the 'CheckedInBy, DateReturned, DaysOverdue, RecordStatus' fields unbound and subject to an INSERT process, creating a SQL_text update of some of the fields in the tblLoanRecord record....thereby inserting these fields into an existing record in the tblLoanRecord table - closing the loan record.
I can't quite figure out how to do it. I thought it would probaby include some sort of WHERE clause - to update tblLoanRecord.LoanRecordID WHERE tblLoanRecordID = Me.txtLoanRecordID (the form being frmLoanRecord_IN). So I have a sql string: SQL_Text = "Insert into tblLoanRecord (ReturnDate, CheckedInBy, OverdueBy, ItemCheckIn, ItemCheckInDetails, RecordStatus)select '" & Me.txtDateReturned & "','" & Me.cboCheckedInBy & "','" & Me.txtDaysOverdue & "','" & Me.cboCheckCmpnt & "','" & Me.txtChkInDetails & "','" & Me.txtRecordStatus & "'"...............but every WHERE clause I've put after this fails. Sorry for the verbosity. Is there an obvious answer to this that is outside my ken?