Please help!!!!! Very Urgent!!!

[Dobbie]

I want to create a validation rule for a field [E_ID] in the form [Check Out] so that when the field is updated, the field should contain only the values of the records which have another field [Status] as "Available" in another query [Equipments Query]?????
I tried the following, but it says there is an error in this:

Private Sub E_ID_AfterUpdate()
If tblEquipments.Status = "Unavailable" Then
MsgBox "The equipment is already rented", vbInformation, "Nexsar Rentals"
End If
End Sub

Any genius with an idea for this???

 

[Simon_MT]

If you use a combibox for E_ID you could include Where tblEquipments.Status = "Unavailable" as thge users would only be able to select E_IDs that are avialable.

Simon

 

[Dobbie]

i am having trouble understanding what you said!!!!!
anyway, E_ID field in check out form is a list-box which displays E_ID of all records in equipment table!!!

 

[Insane_ai]

If you want to prevent the users from selecting a piece of equipment that is already reserved, why not limit the list they use to select it to begin with?
Use a query that requires the Status field is "Available" to populate the list. this avoids error instead of handling it after the fact.

generic example for a form's data source:
SELECT * from tblEquipments WHERE tblEquipments.Status = "Available";

 

[RainLover]

If you use a Combo Box you will get a Message if you input incorrectly.

 

[Dobbie]

thanks!!!! that works but still have the following problem!

consider Equipment 00-053 is unavailable... when i did what you asked the E_ID 00-053 is not in the list. but if i enter it manually using keyboard, it accepts the value.... is there any way that only a value in the list could be selected and only a value in the table can be input via keyboard??

 

[RainLover]

Yes

Go to design view of the form.

Click on the Combo Box and look at the Properties.

You should find an option "Limit to List"

It is in the Data Tab.

 

[smig]

@Dobbie - please read this:
Tips on Posting Questions & Searching for Answers