Tech Assingments

[Flash76]

I am very new to Access and I have not been able to figure this one out. I have to create a daily schedule of techs and who they are assigned to. For instance tech1 on 8-11-10 assigned to TeamLead1. I want to know when assigning techs that once tech1 is assigned on 8-11-10 that tech is no longer an option on 8-11-10 for any other Team Lead. I have right now two tables and I need to know where to go from here I have a Tech table and a Team Lead table. I need to record each tech daily for who they were assigned to and what activity they are working on. Thanks

[maximus]

This is what I have done;

I have a three tables
1) Table tbltech{techid-Primary jkey autonumber, tname, tdetails}=Tech
2) Table tbltechl{tlID-Primary Key autonumber,tlnname,tldetails}=Tech Lead
3) Table tbl_tech_assign_techl {assignment_id - Primary Key Autonumber, tlID, TechId, assign_date}

The main purpose of the third table is to assign Techs to Tech Leaders.

I make a form using the tbl_tech_assign_techl.

And put the Following code in the before update event of the assign_date

Private Sub assign_date_BeforeUpdate(Cancel As Integer)
Dim intCountAssignment As Integer
intCountAssignment = IIf(IsNull(DCount("[assignment_id]", "tbl_tech_assign_techl", "[assign_Date]=#" & Me.assign_date & "# And [techid]=" & Me.techid)), 0, DCount("[assignment_id]", "tbl_tech_assign_techl", "[assign_Date]=#" & Me.assign_date & "# And [techid]=" & Me.techid))

If intCountAssignment > 0 Then
MsgBox "This Tech is already assigned for the entered Date"
Cancel = True
End If
End Sub


This code calculates if there are no of assignments of a Tech in a particular date. If it is null 0 iis returned. If the Value is more than 1 then u get a msg prompt.


I am attaching a Sample please check the form tbl_tech_assing_techl

[Flash76]

Thanks a bunch this will work the only problem I forsee the person who will use this regulary, will have about 150 techs listed he'll only know someone is assigned by either selecting them and getting the error message. Typically in the past using excel we have marked them as "open" for that day if they are un-assigned.

[maximus]

This what I have done I have used a simple unbound form to assign techs to tech leaders. My Tables Remain the same.

I have a unbound form Form1 with:
TextBox2=Type date
Combobox0=Select tech Leader
List4=Display Available list of techs for a date.
List9=Display All Assigned Techs for a given Date.

How to use:

1) Type a Date in the text box.
2) Select A Team Leader
3) Click on generate List
4) Select a tech Name in the list and Click on Assignment button.

What Happens:

1) When u Type a date the code in the afterUpdate event Requeries the List9 Displaying all assignments on that Date.
2) When u click Generate List all Techs that are not assigned on that date their name is displayed.

To Unassign:
1) Select a Assignment in List9. Click Unasigned button. The assignment is calcelled and the tech's name appears in the Available List again.

Code Used:

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim strUnSelectAll As String
Dim strSQLUpdate As String
Dim intRecordCount As Integer
Dim strSQL As String

strUnSelectAll = "Update tbltech Set Assigned=False"
CurrentDb.Execute strUnSelectAll, dbFailOnError

intRecordCount = IIf(IsNull(DCount("[assignment_id]", "tbl_tech_assign_techl", "[assign_date]=#" & Me.Text2 & "#")), 0, DCount("[assignment_id]", "tbl_tech_assign_techl", "[assign_date]=#" & Me.Text2 & "#"))

If intRecordCount > 0 Then
strSQLUpdate = "UPDATE tbltech INNER JOIN tbl_tech_assign_techl ON tbltech.techid = tbl_tech_assign_techl.techid SET tbltech.assigned = True WHERE (((tbl_tech_assign_techl.assign_date)=#" & Me.Text2 & "#));"
CurrentDb.Execute strSQLUpdate, dbFailOnError
End If

strSQL = "Select * From tbltech Where assigned=False"

Me.List4.RowSource = strSQL

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click
Dim strInsertRecord As String
Dim strSQLUpdate As String

If IsNull(Me.Text2) Then
MsgBox "Please Type a Date"
ElseIf IsNull(Me.Combo0) Then
MsgBox "Please Select a Team Leader'"
ElseIf Me.List4 = 0 Then
MsgBox "Please Select a tech'"

Else

strInsertRecord = "Insert Into tbl_tech_assign_techl(tlID,techid,assign_date) Values(" & Me.Combo0 & "," & Me.List4 & ",#" & Me.Text2 & "#);"
CurrentDb.Execute strInsertRecord, dbFailOnError
Me.List9.Requery

strSQLUpdate = "UPDATE tbltech INNER JOIN tbl_tech_assign_techl ON tbltech.techid = tbl_tech_assign_techl.techid SET tbltech.assigned = True WHERE (((tbl_tech_assign_techl.assign_date)=#" & Me.Text2 & "#));"
CurrentDb.Execute strSQLUpdate, dbFailOnError

strSQL = "Select * From tbltech Where assigned=False"

Me.List4.RowSource = strSQL
Me.List4.Requery
Me.List4 = 0

End If


'MsgBox strInsertRecord


Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub

Private Sub Text2_AfterUpdate()
Me.List9.Requery
Me.List4.RowSource = ""
End Sub
Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim strDeleteSQL As String
Dim strSQLUdate As String

strDeleteSQL = "Delete From tbl_tech_assign_techl Where assignment_ID=" & Me.List9
CurrentDb.Execute strDeleteSQL, dbFailOnError
Me.List9.Requery


strUnSelectAll = "Update tbltech Set Assigned=False"
CurrentDb.Execute strUnSelectAll, dbFailOnError



strSQLUpdate = "UPDATE tbltech INNER JOIN tbl_tech_assign_techl ON tbltech.techid = tbl_tech_assign_techl.techid SET tbltech.assigned = True WHERE (((tbl_tech_assign_techl.assign_date)=#" & Me.Text2 & "#));"
CurrentDb.Execute strSQLUpdate, dbFailOnError

strSQL = "Select * From tbltech Where assigned=False"

Me.List4.RowSource = strSQL
Me.List4.Requery



Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

I have made and entry for 06/08/2010 type the date in the text box press enter or tab see the assignment details appear in the Listbox9.

I am attaching a sample. this is a simple sample.

[maximus]

I have attached a sample please check it

[Flash76]

That works perfect thanks

[maximus]

Please mark the thread solved. I am glad the problem is solved