Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    16

    Unanswered: Query and multiple item form manipulation

    Hi... i´m having some problems solving my new work. I'm building a database to record hollidays from a group of people.
    I've a table (Ferias) to store the information, its fields are (numberID,Data_ini,Data_fim,N_dias). numberID identifies the person, Data_ini receives the first day of hollidays and Data_fim receives the last one, N_dias stores the number of days between them.
    I´ve made a query called Consulta_ferias which has the following fields: numberID, rank, name, N_dias(this number is the sum of all records whith the same numberID).
    Then i've built a form based on the query, called Consulta_ferias.In addition i inserted 42 text boxes that are used to represent the days of the month. This form has the multiple item view as the predefined view.
    I´ve written some code that paints that boxes acording to the data inserted in the table Ferias. It's working ok but all the records showed in the subform appeared all the same way. Here's the code:

    Option Compare Database

    Private Sub Pintar(NIM As String)
    On Error GoTo Err_erro
    Dim i, e, valor_dia, mesCab, anoCab, num_record, dia_ini, dia_fim As Integer
    Dim dia As Variant
    dia = Array(Texto1, Texto2, Texto3, Texto4, Texto5, Texto6, Texto7, Texto8, Texto9, Texto10 _
    , Texto11, Texto12, Texto13, Texto14, Texto15, Texto16, Texto17, Texto18, Texto19, Texto20, Texto21 _
    , Texto22, Texto23, Texto24, Texto25, Texto26, Texto27, Texto28, Texto29, Texto30, Texto31, Texto32 _
    , Texto33, Texto34, Texto35, Texto36, Texto37, Texto38, Texto39, Texto40, Texto41, Texto42)
    Dim dbs As Dao.Database, rst As Dao.Recordset
    Dim Data_fim, data_Cab, Data_ini As Date
    Dim strSQL, nimFer As String

    data_Cab = Forms!Calendario.Form!txtCalendarHeading.Value
    mesCab = Month(data_Cab)
    anoCab = Year(data_Cab)
    nimFer = NIM

    strSQL = "SELECT * FROM Ferias WHERE NIM=" & "'" & nimFer & "'"

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveLast
    rst.MoveFirst
    num_record = rst.RecordCount
    MsgBox num_record

    'LIMPAR TODOS OS DIAS DO CALENDÁRIO
    For i = 0 To 41
    dia(i).BackColor = RGB(255, 255, 255)
    Next i
    '***********************************

    For e = 1 To num_record
    Data_ini = rst.Fields(2).Value
    dia_ini = Day(Data_ini)
    Data_fim = rst.Fields(3).Value
    dia_fim = Day(Data_fim)

    For i = 0 To 41
    If Year(Data_ini) = anoCab And Month(Data_ini) = mesCab Then
    valor_dia = dia(i).Value
    If valor_dia >= dia_ini And valor_dia <> "" And valor_dia <= dia_fim Then
    dia(i).BackColor = RGB(0, 255, 0)
    Else
    'dia(i).BackColor = RGB(255, 255, 255)
    End If
    Else
    'dia(i).BackColor = RGB(255, 255, 255)
    End If
    Next i
    rst.MoveNext
    Next e

    rst.Close
    dbs.Close

    Exit_erro:
    Exit Sub

    Err_erro:
    Resume Exit_erro
    End Sub

    Private Sub Gantt_chart()
    Dim dbs As Dao.Database, rst As Dao.Recordset
    Dim numRecord, i As Integer
    Dim NIM As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Consulta_Ferias")
    rst.MoveLast
    rst.MoveFirst
    numRecord = rst.RecordCount

    For i = 1 To numRecord
    NIM = rst.Fields(0).Value
    MsgBox NIM
    Pintar (NIM)
    rst.MoveNext
    Next i

    rst.Close
    dbs.Close

    End Sub

    Private Sub Texto1_Click()
    Gantt_chart

    End Sub

    '*************************

    Private Sub Pintar(NIM As String) - paints the text boxes in a record;
    Private Sub Gantt_chart() - paints all text boxes in all records of the subform;
    Private Sub Texto1_Click() - used just for tests.When i click text1 it executes Sub Gantt_chart.

    The problem is that all records are painted in the same way.It shouldn't be like that because diferente people has diferent hollidays.

    Could you please help me?
    Thank you.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Since there is only one set of controls for a subform's detail section, when you "paint" one yellow, you are "painting" the control (say NumberID). Since there is only one NumberID control, they all go yellow. That's normal. I know of no way to change that behaviour.

    What you'd have to do is place, say, 10 different NumberID controls on a normal form (not a subform) and then populate all the fields with code. You'd have to code record navigation as well.

    If this database has only one table, then why not do this in something like Excel?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jun 2008
    Posts
    16
    Thank you for your answer. I'll try to take other way. Someone know how can i represent the information (begin date and end date) in a Gantt Chart type graph?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'm sure the mods will say "new question, new thread."
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •