Need Help with Check-In-Out Form

[s9y]

Hi Everyone! I'm a beginner access user and i'm working on this project where users can check-out and check-in scan guns through a form.

I'm using below code in Equipment_out in Before Update event to check for duplicate check-outs.

Dim Answer As Variant
Answer = DLookup("[Scan_Gun_Out]", "Query_Out", "[Scan_Gun_Out] = '" & Me.Scan_Gun_Out & "'")
If Not IsNull(Answer) Then
MsgBox "This Scan Gun has already been Checked-Out !!!" & vbCrLf & "Please notify FGI coordinator", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
Cancel = True
Else:
End If

Where i'm having problem is to check back in a scan gun and make it available to check-out again. I'm sure i need a new code and thats where i need help.

Thanks again for helping me in this and i really appreciate your time and help.

 

[boblarson]

A couple of things.

1. It would appear that you need your formula on the before update of the FORM and NOT the control.

2. Just for your coding knowledge Else does not need : after it.

If Something Then
   ' do whatever here
Else
   ' do alternative here
End If

3. Explain your process of how your users are supposed to be doing things, if this was working correctly.

 

[CJ_London]

I presume you are looking for some code to which will book the gun back in again?

If so, if you can post your table details (table name, field name and field type) for the tables used and any relationships we'll see what we can do.

 

[s9y]

CJ

My table name is Table1 and here are the field names and data type

Employee_ID Text
Scan_Gun_Out Text
Printer_Out Text
Scan_Gun_In Text
Printer_In Text
Date&Time Date/Time for time stamp purpose

Thats excatly i want to that once the scan has been checked in by an employee with time stamp then it should be ready to check-out by another employee.

Thanks !!!

 

[CJ_London]

Oooh - not quite what I expected!

What is entered into the gun in/out and printer in/out fields - is it a date or gun/printer serial number/description?

Also, can you supply some example data 5 or 6 rows - I need to understand how the table works a bit better

For example is this effectively a record of what equipment each person holds or held or a record of what equipment has been checked in/out and to who

 

[s9y]

Sorry . . .
So for Employee_ID, i have a different table which is linked to a combo box in my form (populates a list of employees)

For Gun and Printer in-out, employees will scan their serial numbers

Date is now shown on the form but in Table1 it reports it as a time stamp to track when a particular gun or scanner was checked-in or out.

Thanks
s9y

 

[CJ_London]

Sorry a few more questions:

1. Is EmployeeID in the other table a number or text?
2. Is Timestamp in Table1 set to a default of now()?
3. If an employee checks out a gun on day 1, a printer on day 2 and returns the gun on day 3, which day is the timestamp?

If you could do what I ask and post some details of what you have in table 1

e.g.

EmployeeID    Gunout    gunin    printerout    printerin    timestamp
fred              g1          g2        p1              p2            1/1/12
george          g2                     p5                              2/1/12
harry            g4                     p2                              2/1/12
 
or is it more like
fred                       g2                                             25/12/11
fred                                                      p2              25/12/11 
fred                                       p1                             30/12/11   
fred             g1                                                       1/1/12
 
or something else?

 

[s9y]

Hi CJ,

Employee_ID is Text
Time_Stamp is set to Now()

It will be 3 entries for 3 equipment check-in and outs.
Also the check-in and check-outs will not input on the same row of the table.
Different entries to keeping things separate.

I hope this helps you helping me.

Thanks
s9y

 

[CJ_London]

OK so more like my second suggestion?

EmployeeID Gunout gunin printerout printerin timestamp
fred g2 25/12/11
fred p2 25/12/11
fred p1 30/12/11
fred g1 1/1/12

 

[s9y]

Yes, exactly the same.

The code i have is checking for duplicate entries but how to check-in again thats the problem.

 

[s9y]

Hi CJ,

I have made some modifications to my existing table. please see attached and let me know if you can help.

Thanks
s9y

 

[CJ_London]

Hi s9y

I've had a look and need to ask some more questions:

1. Can an employee have more than one gun issued to them at the same time?
2. The same question for printers
3. In Table1 - what does gun status and printer status mean?

Should then be able to come up with a solution

Thanks

CJ

 

[s9y]

Hi CJ,

Appreciate all your time helping me with this.

Answers to your questions:

1. I want to limit only 1 employee can check-out more than 1 gun to himself.
2. No to printers. One employee 1 printer.
3. The Status field i thought should be helpful in programming. Whenever a employee will scan-in a gun or a printer, the checkbox get checked. I thought while using a look-up command maybe this will be useful.
If you can make a code without using the status field, that'll work for me too.

Thanks
s9y

 

[CJ_London]

Hi,

I had to change thing round a bit but this now works. Once a gun or printer has been selected it is automatically updated plus a few other bits and pieces

Hope it does what you want

 

[s9y]

Thank you very much CJ. I'm gonna try to input all the data and test it a few times with different scenarios. It's working beautifully rite now . . . . thanks again !!!

 

[CJ_London]

no problem

 

[s9y]

Still need few refinements.

1. How can i add Employee name in Table2 and ItemsCheckedout, need it for reporting purposes.

2. i want the Form to refresh automatically after an employee checks-out or in any equipment.

3. Do want to make another query for equipment checked-in to see the consistency.


As i said earlier that i'm a beginner user and the code you used was like from different world to me lol but really an awesome job. Thank you much.

 

[CJ_London]

1. How can i add Employee name in Table2 and ItemsCheckedout, need it for reporting purposes. - create a query, linking to the employee table on employee_id - it is not good practice to use tables directly, you shoul always do it through a form or report

2. i want the Form to refresh automatically after an employee checks-out or in any equipment. That is supposed to happen - note how the checkin/out fields enable/disable according to the current position and contents of the checkout/in controls update immediately

3. Do want to make another query for equipment checked-in to see the consistency. not sure what you mean. If you want to see what equipment you have available to check out see the queries behind the two checkout combo's

 

[s9y]

So i was trying to add a third table (Employee Info) to itemscheckedout query so i can have Employee names there but that didn't worked. it looked simple but ahhh didnt worked.
What would you suggest in this case?

 

[CJ_London]

this adds he employee name into itemschecked out - note that the employee_id column needs to remain because it is used elsewhere

SELECT Items.ID, Items.ItemCode, Items.ItemType, Table2.Employee_ID, [Employee Info].Employees
FROM (Table2 INNER JOIN Items ON Table2.ItemID = Items.ID) INNER JOIN [Employee Info] ON Table2.Employee_ID = [Employee Info].ID
WHERE (((Table2.CheckOut)=True) AND ((Table2.Date_Time)=(Select max(Date_Time) From Table2 as tmp where Itemid=table2.Itemid)))