ok currently at work im working on a job that has vb script inside of access. My problem is what im trying to do is set it up so i can actually put a time into it and it for to search avail cubes to seat someone as apose to showing all of the cubes. does that make sense? if anyone could help this is what i have so far! thanks in advance.
Code:
Private Sub cmd_RunSTF_Click()
Dim i As Integer
Dim x As Integer
lblTitle.Caption = "Cubes With No Agent Assigned from """ & txt_fsSegStart.Value & """ To """ & txt_fsSegEnd.Value & """"
ResetCube
List880.RowSource = "SELECT tbl_Temp_Assigned_Agent.[Cube ID], tbl_Temp_Assigned_Agent.[Shift Start], tbl_Temp_Assigned_Agent.[Shift End] FROM tbl_Temp_Assigned_Agent WHERE (((tbl_Temp_Assigned_Agent.[Shift Start])<" & txt_fsSegStart & ") AND ((tbl_Temp_Assigned_Agent.[Shift End])>" & txt_fsSegEnd & "));"
For i = 0 To Form.List880.ListCount - 1
For x = 0 To Form.Controls.Count - 1
ObjectName = Form(x).Name
If ObjectName = "lbl_" & List880.ItemData(i) Then
Form(x).BackColor = 8215296
Form(x).ForeColor = 16777215
End If
Next
Next
txt_fsSegStart.Value = ""
txt_fsSegEnd.Value = ""
End Sub
ok currently at work im working on a job that has vb script inside of access. My problem is what im trying to do is set it up so i can actually put a time into it and it for to search avail cubes to seat someone as apose to showing all of the cubes. does that make sense?
Ah, no.
What are you looking for? Do you need to write a query? Are you trying to setup a database? I'm not sure what you are asking for here.
no the data base is written the whole program looks like this.
Code:
Private Sub ColorCube(BGColor)
'***********************************************
'** Loops throw all of the objects on the form
'** and changes the color of those that match
'** the selected criteria.
'***********************************************
Dim ControlName As String
Dim i As Integer
For i = 0 To Form.Controls.Count - 1
ObjectName = Form(i).Name
If ObjectName = "lbl_" & Sel_Agent.Value Then
Form(i).BackColor = BGColor
Form(i).ForeColor = 16777215
End If
Next
End Sub
Private Sub ResetCube()
'***********************************************
'** Loops throw all of the objects on the form
'** and resets the color of the object back to
'** the defalt color.
'***********************************************
Dim i As Integer
For i = 0 To Form.Controls.Count - 1
ObjectName = Form(i).Name
If Left(ObjectName, 4) = "lbl_" Then
Form(i).BackColor = 16777215
Form(i).ForeColor = 0
End If
Next
End Sub
Private Sub cmd_RunSTF_Click()
Dim i As Integer
Dim x As Integer
lblTitle.Caption = "Cubes With No Agent Assigned from """ & txt_fsSegStart.Value & """ To """ & txt_fsSegEnd.Value & """"
ResetCube
For i = 0 To Form.List880.ListCount - 1
If chk_fsSunday.Value = 1 Then
ElseIf chk_fsMonday.Value = 1 Then
End If
For x = 0 To Form.Controls.Count - 1
ObjectName = Form(x).Name
If ObjectName = "lbl_" & List880.ItemData(i) Then
Form(x).BackColor = 8215296
Form(x).ForeColor = 16777215
End If
Next
Next
txt_fsSegStart.Value = ""
txt_fsSegEnd.Value = ""
End Sub
This does not
Private Sub cmd_RunSup_Click()
Dim i As Integer
Dim x As Integer
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
Set DB = OpenDatabase("C:\Documents and Settings\ktsandberg\Desktop\PMS Live Linked.mdb")
lblTitle.Caption = "Reps Assigned To Supervisor """ & txt_fsSup2.Value & """"
a = Split(cmb_fsSup.Value, " ")
SQL = "Select tbl_Temp_Assigned_Agent.[Cube ID] from tbl_Temp_Assigned_Agent Where tbl_Temp_Assigned_Agent.[Assig Sup] = '" & a(0) & "'"
Set RS = DB.OpenRecordset(SQL)
cmb_fsSup.Value = ""
ResetCube
For i = 0 To RS.RecordCount - 1
For x = 0 To Form.Controls.Count - 1
ObjectName = Form(x).Name
If ObjectName = "lbl_" & RS(i) Then
Form(x).BackColor = 8215296
Form(x).ForeColor = 16777215
End If
Next
Next
RS.Close
DB.Close
End Sub
Private Sub cmd_RunLead_Click()
Dim i As Integer
Dim x As Integer
lblTitle.Caption = "Reps Assigned To Lead """ & txt_fsLead2.Value & """"
cmb_fsLead.Value = ""
ResetCube
For i = 0 To Form.List840.ListCount - 1
For x = 0 To Form.Controls.Count - 1
ObjectName = Form(x).Name
If ObjectName = "lbl_" & List840.ItemData(i) Then
Form(x).BackColor = 8215296
Form(x).ForeColor = 16777215
End If
Next
Next
End Sub
the first section of code was the part of the code that dosnt run.
when you enter the code into acess your able to put a time into access but i want it to span over 24 hours blocking out cubicles that are already being used so when it searches for say 7:30 it will search all cubilce and display only the desks have no one in it.
What do you have so far?
How have you setup your database?
the simplest would be to have a table with workers, a table with workplaces and a table with assignments.
The assignment table would hold a worker_id, a workplace_id and begin and endtimes.
You can then query the database for all workplaces that have no assignment in the timeperiod you're interested in.
It is always a good idea to give details of why it doesn't run.. does it give you an error? (if so, what was it?) does it return the wrong results? or something else entirely?
One obvious issue is that you have not delimited your date values appropriately, they need to be surrounded by the # character, eg:
VB Code:
List880.RowSource = "SELECT [Cube ID], [Shift Start], [Shift End] FROM tbl_Temp_Assigned_Agent WHERE ([Shift Start]<#" & txt_fsSegStart & "#) AND ([Shift End]>#" & txt_fsSegEnd & "#);"
(note that I have removed tbl_Temp_Assigned_Agent. from all the field names because there is only one table - so you dont need to specify it).
no error message when you click run it pulls up but it dosnt really search for anything i need it to search for desks that are availibale at the certain time frame that im looking for.
Ok, just to be sure... are the [Shift Start] and [Shift End] fields set up with a data type of Date? What are the kind of values in them?
As jeroen79 implied, it would also be useful for us to know the structure of the database.. does this table contain all of the info we need? Is it just in these 3 fields?
Am I right in thinking that the values are the ones that are available, or are they the ones that are being used?
this is the whole data base structure from start to finish and those are the ones being used so basically what im trying to do is make it so that when i click run it will search the data base for any avail seats that are not being used.
Sure.. just click on "Post Reply" below, then click on the "Manage Attachments" button to upload the file.
That still wont be enough tho, you need to answer the questions in Post #8 (as the not all the answers can be shown in a screen-shot, and despite our best efforts none of us are mind readers).
If you open the table in Design view, is the data type of the [Shift Start] and [Shift End] fields set to Date?
(a few example values would be useful too)
Does the table tbl_Temp_Assigned_Agent contain a list of available cubes, or ones that are used? (if "used", do you have a table which contains a list of the valid cubes?)
ok first short time stamp is what i believe it is set to.
and second there is a table with a list of all cubes and tbl_Temp_Assigned_Agent in generated from a few other tables as well. tbl_Temp_Assigned_Agent does not use cube ID as a uniqe Identifier data type string. tbl_Temp_Assigned_Agent is a list of people who sit at a cube (cube ID) and the start time and end time for thier shifts as separate items as data type: Short Time
Good stuff, I know what is going on now - my original assumptions were wrong! You basically want to find values that aren't in the table.
Am I right in thinking that your original query (or my replacement in post #6, which is how it should be) returned all of the records where cubes are used between the specified times (and no other records)?
If so we can use that to build what we need.. something like this should do the job:
VB Code:
Dim strSQL as String
strSQL = "SELECT [Cube ID] " _
& "FROM [U]CubeList[/U] " _
& "WHERE [Cube ID] NOT IN ( " _
& " SELECT [Cube ID] " _
& " FROM tbl_Temp_Assigned_Agent " _
& " WHERE ([Shift Start]<#" & txt_fsSegStart & "#) AND ([Shift End]>#" & txt_fsSegEnd & "#) " _
& ")"
List880.RowSource = strSQL
(replace CubeList with the name of your table that lists all the valid Cubes).
Note that if your original query didn't return what I said, we will need need to alter the WHERE clause in it so that it does (and then copy the WHERE clause to the relevant part of the code here).
ok this is what my vb code looks like now. I also included a picture of what the cubes table looks like now what its doing is not only is it pulling cubes with nobody in them its pulling cubes that have two people at them. so here is what i got for vb sript.
Code:
Private Sub cmd_RunSTF_Click()
Dim strSQL As String
strSQL = "SELECT [Cube ID] " _
& "FROM tbl_Temp_Assigned_Agent " _
& "WHERE [Cube ID] NOT IN ( " _
& " SELECT [Cube ID] " _
& " FROM tbl_Temp_Assigned_Agent " _
& " WHERE ([Shift Start]<#" & txt_fsSegStart & "#) AND ([Shift End]>#" & txt_fsSegEnd & "#) " _
& ")"
List880.RowSource = strSQL
For i = 0 To Form.List880.ListCount - 1
For x = 0 To Form.Controls.Count - 1
ObjectName = Form.Controls(x).Name
If ObjectName = "lbl_" & List880.ItemData(i) Then
Form(x).BackColor = 8215296
Form(x).ForeColor = 16777215
End If
Next
Next
txt_fsSegStart.Value = ""
txt_fsSegEnd.Value = ""
End Sub
well i tried to send the table structure but the screen shots were to big and the last bunch of code is now searching the only thing it is doing now is pulling up empy cubes and all cubes with 2 people in them. also i said earlier there is a table with a list of all cubes and tbl_Temp_Assigned_Agent in generated from a few other tables as well. tbl_Temp_Assigned_Agent does not use cube ID as a uniqe Identifier data type string. tbl_Temp_Assigned_Agent is a list of people who sit at a cube (cube ID) and the start time and end time for thier shifts as separate items as data type: Short Time.
Hope that helps
CubeList should not have been replaced by tbl_Temp_Assigned_Agent, instead it should be the table which lists all of the CubeID's.
It does not matter that CubeID isnt unique in tbl_Temp_Assigned_Agent, as the sub-query (the part based on your original query) simply returns a list of all "used" ones - but it sounds like your original query does not return all of the ones that are used (and nothing else), is this correct?
Reply With Quote
Originally Posted by joelw
