Searching other entities via a form!!!

[DONJAMES]

I have four entities: LOAN, PATIENT, EQUIPMENT and REPAIR.
The entity EQUIPMENT has a status of Available, in Repair and on Loan. If on the piece of equipment is on loan in LOANS, it cannot appear in repair and the opposite for repairs - the repaired piece of equipment can't be in LOANS at the same time. When the piece of equipment is returned, the status must be set back to "A" (for available) in the equipment entity.

In the form to enter a new for the LOAN entity, when SAVE is clicked (or something else!!), i want to change the status in equipment to "L" but how??

Basically, how can I change the something in a table using the event procedures.

Help

 

[RickSpr]

The LOAN table obviously has to have a foreign key to the EQUIPMENT table (the REPAIR table will, too). I'll assume this foreign key consists of a column called EquipmentID, which is the primary key in the EQUIPMENT table. This column should be bound to a control on the LOAN entry form. Let's suppose this control is a text box named txtEquipID

In the SAVE button's Click event (you could also use the Form_AfterUpdate event), you want to update the EQUIPMENT table's Status column to 'L'. To do this, you need to execute a SQL statement that looks something like this:
UPDATE EQUIPMENT SET Status = 'L'
WHERE EquipmentID =
Forms![<name of loan form>]![txtEquipID]
You can just put this SQL statement in a constant in a module. To execute it within the event procedure, just use:
DoCmd.RunSQL <name of constant> Rick Sprague

 

[DONJAMES]

Thanks for your assistance but there are a couple of things I want to check...

You're right about how the tables and entities are set up, what I'm not sure about is the coding!

You wrote:
UPDATE EQUIPMENT SET Status = 'L'
WHERE EquipmentID =
Forms![<name of loan form>]![txtEquipID]
1) How do I assign a constant to that!
2) 'WHERE EquipmentID=' does it = Forms!? Should it be on the same line?


The second query I wanted to pose is that I need to write a piece of code which checks first if Status = R then it cannot loan and if Status = L it cannot send for Repair. The codes for each check will be on separate forms, but how do I check what the status is?

Any ideas would be nice.

Thanks again

 

[RickSpr]

Yes, that should all be on the same line, or you can break it up onto multiple lines as shown in this example:

    Const UpdStatus = "UPDATE EQUIPMENT " _
          & "SET Status = 'L' " _
          & "WHERE EquipmentID = " _
          & &quot;Forms![<form name>]![txtEquipID]&quot;

&quot;Forms![<form name>]![txtEquipID]&quot; is an expression that looks in the application's Forms collection for a form with the given name, and then looks in that form's Controls collection for a control named txtEquipID. The value of that control is what is being compared with EquipmentID. I mean for you to replace <form name> with the actual name of your LOAN form.

You can easily check the value of the Status column within code using the DLookup() function. DLookup retrieves a value from a specified column in a specified table. You just need to give it a key for the row you want. Check the help docs for more information. Rick Sprague