Check-in/Check-out problem and afterupdate problem

[AccessInitiate]

I've searched thoroughly for terms similar to this topic, but I didn't find what I was looking for. I posted in someone else's thread that was relevant, but since her problem has been solved, I was skeptical on whether anyone would go in there to read my post.

I'm having trouble with my database in terms of checking in tools and checking out the tools. I sort of have the check-out working, where current date is inputted into the "date checked out" field. I was able to get that to update into a table, where the records are stored.

The main problem is checking in the tools. I tried using the same logic of taking the current date and putting it into the "date checked in" field, but it would just create a new record in the table and not update the one that it's supposed to; the original record that was used for the check out.

~How do I get the check-in form to update for the same exact record that was used for the check out?

-----

There's also a problem with the check out afterupdate function. I have a combo box in a form that should update the subform so that all the records for the selected criteria is displayed. That doesn't work. The code I used was:

Private Sub cboName_AfterUpdate()

Dim strSQL As String
Dim LSQL As String
Dim LSearchString As String

LSearchString = cboName

'Filter results based on search string
LSQL = "select * from CheckOut"
LSQL = LSQL & " where Name LIKE '" & LSearchString & "'"

Form_sfrmCheckOut.RecordSource = LSQL

End Sub

~How can I fix this code so that the combo box will update the subform and display all the records for any name criteria that is selected?


Sorry for the long post. Please, any assistance would be greatly appreciated.

Thank you!
Jeremy

 

[Sum Guy]

Your first problem:

The way I would normally approach this problem is:
When you check out a tool a new record whould be created.
When you check the tool back in, you should be EDITING or updating the existing record.
Check your form and subform(s) and make sure you don't have the Data Entry set to Yes when you're updating.

Again my approach would be to have two different forms:
Form 1 would have the Data Entry property set to yes. This form would be used only for check outs.
Form 2 would open as a result of a search, it would allow you to edit the existing record and "check in" the tool. Data Entry would be set to NO.

I don't know how familiar you are with Access. Setting a form's properties to DataEntry -> Yes means you have a new blank record everytime you open the form.

Hope this helped.

 

[HiTechCoach]

I've searched thoroughly for terms similar to this topic, but I didn't find what I was looking for. I posted in someone else's thread that was relevant, but since her problem has been solved, I was skeptical on whether anyone would go in there to read my post.

I'm having trouble with my database in terms of checking in tools and checking out the tools. I sort of have the check-out working, where current date is inputted into the "date checked out" field. I was able to get that to update into a table, where the records are stored.

The main problem is checking in the tools. I tried using the same logic of taking the current date and putting it into the "date checked in" field, but it would just create a new record in the table and not update the one that it's supposed to; the original record that was used for the check out.

~How do I get the check-in form to update for the same exact record that was used for the check out?

-----

There's also a problem with the check out afterupdate function. I have a combo box in a form that should update the subform so that all the records for the selected criteria is displayed. That doesn't work. The code I used was:

Private Sub cboName_AfterUpdate()

Dim strSQL As String
Dim LSQL As String
Dim LSearchString As String

LSearchString = cboName

'Filter results based on search string
LSQL = "select * from CheckOut"
LSQL = LSQL & " where Name LIKE '" & LSearchString & "'"

Form_sfrmCheckOut.RecordSource = LSQL

End Sub

~How can I fix this code so that the combo box will update the subform and display all the records for any name criteria that is selected?


Sorry for the long post. Please, any assistance would be greatly appreciated.

Thank you!
Jeremy

Jeremy,

I have found that you should NEVER EDIT an existing record in a table that has record that are considered transactions. You add new records. This is based on sounds accounting principles and also allow an audit trail. In your case, you do have transaction records.

The way I do this in my apps is with a single transaction table. I apply all the "best practices" of accounting and inventory control.

The basics are:

To check out, create a record and set the transaction type to "checkout" and the quantity is a positive amount. Also including the any additional fields like date/time, etc.

To check in, create a record and set the transaction type to "check in" and the quantity is a negative amount. Also including the any additional fields like date/time, etc.


In inventory warehousing terms:

I also look at is as a transfer between inventories. This means that you would use two trannsactio0nt o check in /out. One to remove from the "stock" of one warehouse and another to add "stock" to the other warehouse.

To get the quantity on hand, you simple sum the quantity field.

I look at equipment/tools check out to a person as just another type of warehouse. Then I can apply all the "best practices" of inventory control.