Help Please

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

---

Quote:

---

Originally Posted by joelw

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.

We know what you are trying to do, we just dont know your database well enough - which is why I asked the questions in my previous post.

i understand sorry so yes if you can pull up access you should be able open up this code and see what i got.

No we cant - we can only see the code, but we need to understand the database structure, and we can only do that if you explain it.

can i show it to you in screen shot?

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).

the tables are to big the exeed php 2.0 what should i do?

The important thing is to answer the questions...

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

---

dont forget to look at the screen shot too.

The screenshot is a floorplan?

Which tables are in your DB?
Which fields do the tables have?
How are the tables related?

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

Why not send the database structure as text?

Like:

Code:

---

tbl_Worker
    Id
    Name
    Function

tbl_WorkPlace
    Id
    Location
   
tbl_Assignment
    Id
    Worker_Id
    WorkPlace_Id
    Time_Start
    Time_Stop

---

(from my example earlier)

Good plan. :thumb:

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?