Hello all
I wonder if anyone can help me with this..
I am building a piece of scheduling software for a small company.
I implemented an access database with the following tables:
I have managed to insert into and delete from the database using sql in the following forms: FrmAddStaffMember, FrmEditStaffMember, FrmRemoveStaffMember, FrmMainMenu, FrmStaffAvailabllity.
The form staff availablity consists of a series of 14 check boxes for each day so 98 in all. When the program is running the user selects availble working times for each meber of staff and adds the values in to StaffID and ShiftID
Now I need to generate a list of available employees for each shift and display it in a rota. I am not that sure how to do this, think it might be an algorithym or something and I was wondering if anyone could help?
You have to decide what "available" means, in terms of the data you have in the various tables, then create a WHERE clause in your SELECT statement that selects available employees.
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read. Please Help Us To Save Ana
StaffAvalibility is a table in the database. Inside this table are the fields AvalabilityID, ShiftID and StaffID. This means you know who can work which shifts. What i need is the code so that the vb program will look at the availability table in the databse, decide on who should work when and display it. in the vb program.
StaffAvalibility is a table in the database. Inside this table are the fields AvalabilityID, ShiftID and StaffID. This means you know who can work which shifts. What i need is the code so that the vb program will look at the availability table in the databse, decide on who should work when and display it. in the vb program.
Thats what Al42 is telling you how to do. You need to determine what criteria in the DB is going to mean that an employee is available. Once you know how to tell if the employee is available (I guess I would use a 1 for available and 0 for not) you can use a WHERE clause in your SELECT statement. For example:
Ok i have assigned a field in the database called available (value Yes or NO) and included this in my code. The code now seems to work but i am having problems getting in to print the result to a form in vb. This is my code:
Dim Conn As ADODB.Connection
Dim ADODB1 As ADODB.Recordset
Dim ADODB2 As ADODB.Recordset
Private Sub BtnUpdate_Click()
Set Conn = New ADODB.Connection
Conn.ConnectionString = "DSN=db1"
dim strShifts as string
strShifts = "("
'Now for each checkbox see if it is checked and assign its value to the string strShifts
strShifts = strshifts & ")"
strsql = "Select Staff.Forename,Staff.Surname,JobTitle.Title "
strsql = strsql & "From Staff Inner Join JobTitle.JobID on Staff.JobID "
strsql = strsql & "Where StaffID In (Select StaffAvailability.StaffID From StaffAvailability "
strsql = strsql & "Where StaffAvailability.ShiftID In " & strShifts
Then yu can place the resulting record set into a Felxgrid or a combobox
ok thanks for your help. I am now using the following code:
Code:
Private Sub BtnUpdate_Click()
Set Conn = New ADODB.Connection
Conn.ConnectionString = "DSN=db1"
Conn.Open
strShifts = "("
'Now for each checkbox see if it is checked and assign its value to the string strShifts
strShifts = strShifts & ")"
strsql = "Select Staff.Forename,Staff.Surname,JobTitle.Title "
strsql = strsql & "From Staff Inner Join JobTitle.JobID on Staff.JobID "
strsql = strsql & "Where StaffID In (Select StaffAvailability.StaffID From StaffAvailability "
strsql = strsql & "Where StaffAvailability.ShiftID In " & "strShiftsdim strShifts as string"
strShifts = "("
ViewReportpb.FontName = "arial"
ViewReportpb.FontSize = "10"
Set MyRecset = Conn.Execute(sqlstring)
ViewReportpb.Print "" 'print the results into the picture box
ViewReportpb.Print ""
End Sub
When i run the code i get the error message "comand text was not set for the command object." any ideas?
The Execute method on the Conn object is to perform modification of data only (DB structure also). To get data back you need to open the recordset using the Open method of the Recordset object explicitly.
Dim Conn As ADODB.Connection
Dim ADODB1 As ADODB.Recordset
Dim ADODB2 As ADODB.Recordset
Dim strShifts As String
Private Sub ReturnToMainMenubtn_Click() 'return to main menu button
Menufrm.Visible = True
OverdueReportfrm.Visible = False
End Sub
Private Sub BtnUpdate_Click()
Set Conn = New ADODB.Connection
Conn.ConnectionString = "DSN=db1"
Conn.Open
strShifts = "("
'Now for each checkbox see if it is checked and assign its value to the string strShifts
strShifts = strShifts & ")"
strsql = "Select Staff.Forename,Staff.Surname,JobTitle.Title "
strsql = strsql & "From Staff Inner Join JobTitle.JobID on Staff.JobID "
strsql = strsql & "Where StaffID In (Select StaffAvailability.StaffID From StaffAvailability "
strsql = strsql & "Where StaffAvailability.ShiftID In " & "strShiftsdim strShifts as string"
strShifts = "("
ViewReportpb.FontName = "arial"
ViewReportpb.FontSize = "10"
Set MyRecset = Conn.Execute(sqlstring)
ViewReportpb.Print "" 'print the results into the picture box
ViewReportpb.Print ""
End Sub
Last edited by Hack; Apr 12th, 2007 at 08:34 AM.
Reason: Fixed [code] [/code] tags
Reply With Quote
Originally Posted by ian_bateman3
