lloking up a db field from a form

[Boomshank]

hello,

I am trying to set up a form so that when the back button is clicked, a specidic field in my databse is checked to see if it is true or false, if it is false i want to remove the text from another field.

Any Idea's how i can do this.

My table is called "MovieDatabase"
My Yes/No field is called "OnLoan"
The field that i want to remove the text from is "OnLoanTo"

Thanks in Advance

 

[MStef]

I suggest to do it via UPDATE query.

 

[Galaxiom]

If you are wanting to change the value of a control on the form then use:

If Not Me!forstcontrolname Then
Me!othercontrolname = ""
End If

If you want to check a field from a specific record in a table that is not the current record of the form then use a DLookUp function to retreive the value, test it for T/F and use as above.

DLookUp("T/Ffieldname", "tablename", "conditionfieldname = somevalue")

 

[JANR]

A better choise is to create a transactiontable between the filmTable and lenderTable, as this is a many-to-many situation.

Fields:
TransID - Autonumber
FilmID - Number or text FK to Filmtable
LenderID - Number or text FK to LenderTable
LendDate - Datafield
ReturnDate - Datefield

In this table you Append a record when a film is lended, you append the FilmID, LenderID and Date.

When the film is return you UPDATE this table with the ReturnDate.
Now you have a way to have history data of in/out movement of your films.

JR