Time

[elvin0809]

good day to all

im trying to explain my problem

i have a table PC_UNIT that having a field of RoomLocation,ComputerName and AVAILABILITY
the availability field having a value of "AVAILABLE" and "NOT AVAILABLE"

then i have a "BORROWING FORM"
i have a field "DateReceive","TimeStarted","ExpectedTimeOfRet urn" "SelectLaboratory" and "ComputerName". the "SelectLaboratory" and "ComputerName" is cascade the value of RoomLocation and ComputerName then the ComputerName will only display the "AVAILABLE" PC in the selected RoomLocation.



My question is it possible that once I select an available PC in "ComputerName" field the Availability of the selected PC will turn to "NOT AVAILABLE" and if the selected computer exceed the time from "TimeStarted" to "ExpectedTimeOfReturn" from the Current time, the AVAILABILITY of the Selected PC will turn to AVAILABLE.

THANKS AND GOD BLESS

[June7]

You either have to manually change the status or use code to modify the status for that record. If the status field is part of the form's RecordSource, could simply:

If Me!statusfield = "AVAILABLE" Then
Me!statusfield = "NOT AVAILABLE"
Else
Me!statusfield = "AVAILABLE"
End If

The trick is figuring out what event to put code in. I think maybe the AfterUpdate event of the ComputerName combobox will serve. Then probably should follow the above code with a requery of the combobox: Me.comboboxname.Requery

[elvin0809]

thanks for the information JUNE7
what if the status field is not on the form? what should i do...
because only available PC are shown in the combobox ComputerName

thanks for helping me

[June7]

If the status field is not included in the form's RecordSource (doesn't have to be bound to a control on form), will have to use an SQL UPDATE statement to save the value to record in appropriate table.

DoCmd.SetWarnings = False
DoCmd.RunSQL "UPDATE tablename SET statusfield = 'NOT AVAILABLE' WHERE uniqueIDfield=" & Me!IDfield
DoCmd.SetWarnings = True
Me.comboboxname.Requery

[elvin0809]

where i gonna type this code? thanks for the information sir

[June7]

In the VBA editor.

One way to initiate procedure:

First, decide what event you want the code in. In your case, probably the AfterUpdate event of combobox. Form in design view, select the combobox, then in the properties dialog on Event tab, in AfterUpdate event select [Event Procedure]. Double click the ellipses (...) to create the procedure and open VBA editor. Cursor will be in the procedure. Start typing code.

[elvin0809]

i have a question again sir what did you mean with the red text in the code?

DoCmd.SetWarnings = False
DoCmd.RunSQL "UPDATE tablename SET statusfield = 'NOT AVAILABLE' WHERE uniqueIDfield=" & Me!IDfield
DoCmd.SetWarnings = True
Me.comboboxname.Requery

i dont know what field is this THANKS SIR

[June7]

That is example of filter criteria. You want to update value of specific record so must provide the SQL with unique criteria that will match the specific record, otherwise ALL records will be modified.

I don't know the names of your fields and form controls, so replace with what your actual names are.

What is your table structure? What is relationship of table form is bound to and the table that has the status field?

[elvin0809]

im having an error on DoCmd.SetWarnings the red text is highlighted in the code and the msg box error says Compile Error: Argument not optional
what should i do?
thanks for the time sir June7

[June7]

Sorry, my error, remove the = signs.

[elvin0809]

parameter msg box appear after i press a commang button

Private Sub Command213_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE PC_UNIT SET AVAILABILITY = 'NOT AVAILABLE' WHERE ComputerName=" & Me!ComputerName
DoCmd.SetWarnings True
Me.ComputerName.Requery
End Sub

1st color = table name
2nd color = field in the table that must change its value to not available
3rd color = ComputerName field name in PC_UNIT
4th color = Me!ComputerName - name of combo box in the borrowing form or LookUp for the ComputerName to see its value
5th color = Name also of the combo box same as 4th color

did I get wrong?
thanks you very much

[June7]

If you are using text instead of numeric value as criteria, need apostrophe delimiters:

WHERE ComputerName='" & Me!ComputerName & "'"

Is ComputerName a unique value field in the table?

Dates use # as delimiter.

[elvin0809]

thank you very much june7 i got it..

1 more question what if the selected PC is has a TimeOfReturn can i change its availability automatically to AVAILABLE where the time is TIME()?

[elvin0809]

thank you june7 i got it

1 more question again... what if i select ComputerName and its value will change to "NOT AVAILABLE" and i have a ReturnTime field... can i change the availability automatically if the ReturnTime=TIME()?

thank you very much

[June7]

You want the status to be changed to "AVAILABLE" because item is returned? How is the time entered?