Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts

    automatic filling of fields

    I have a form with three combos and a subform with the following fields:
    Step, dataprogramada, and datareprogramada datarealizada. The field step has a list box with 15 steps. I'm trying to include a new record 7 of these 15 steps to populate. Can anyone help me?

  2. #2
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Are you wanting to create new record for each step?

    Is the list box set to multi select?
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  3. #3
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HiTechCoach View Post
    Are you wanting to create new record for each step?

    Is the list box set to multi select?
    Thank you for responding. Actually I want to insert a new product in the combo, automatically fill in steps of 7 listings in 7 records. Then a lot of analysis could solve the problem.
    I'm just not getting to the automatic completion dates.
    For example, when entering a date in the date field made ​​the first step, which would automatically fill the field of other scheduled six steps, with 5 days (useful) for each step. Do you have any idea, how can I do?

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I can't follow what you want to do at all.
    Can you carefully describe the table structure you are using, then describe in detail what you want to happen when.
    Regards
    John



  5. #5
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    I agree with John that it would really be helpful to have more details.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  6. #6
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    I will elaborate on.
    I have a form with three synchronized combos, analyst, company and product. A subform with the following fields: scheduled, rescheduled date, date performed, step. The field step has a list box with 15 steps. When I insert a new product in the combo automatically records are filled with 7 of these 15 steps. What I'm trying to do: when a date is entered in the date field made ​​the first step, the scheduled date field automatically fills in the second step a date with one day (useful, not Saturday and Sunday) after the date of the date field held first step. And then the third step pad with 5 days (working days) after the date of the scheduled date field of the second step. so on until the seventh step.
    I put a picture attached, with the dates filled manually, but I'm having automatically.Sem título.png

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    So you need to Update the value in a field for 6 records when a date is entered into the same field for the first record. You need perform an update by writing an SQL statement, then using the CurrentDB.Execute method.
    But to help you with writing the SQL statement we would need to know:
    • The name of the table the records are in
    • The name of the field you want to update
    • What field(s) can be used to determine which records to update. There must a field that indicates which step it is, and some other field that ties all these records together.

    And do you want this to happen if you change an existing date? or only when you enter the date the first time?
    Regards
    John



  8. #8
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Exactly, I want when I type a date in the date field made ​​the first step, the other automatically insert dates in other steps, as explained above. Including this date, after they are updated, you can not edit.
    I am sending attached files. http://dl.dropbox.com/u/53793430/PAS...UTOMATICOS.zip

    Can I send you the file with the bank?relations.pngauxi.png

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is the code I have just used, that seems OK to me.

    Code:
    Private Sub Data_Programada_AfterUpdate()
        Dim sql As String
        Dim dtProgrameDate As Date
        Dim dtNextDate As Date
        Dim intI As Integer
       On Error GoTo Data_Programada_AfterUpdate_Error
    
        If Me.Passo = 1 Then
            If Not IsNull(Me.[Data_Programada]) Then
                dtProgrameDate = Me.[Data_Programada]
                Select Case Weekday(dtProgrameDate, vbSunday)
                Case 6 ' current date is a Friday
                    dtNextDate = dtProgrameDate + 3
                Case 7 ' current date is a Saturday
                    dtNextDate = dtProgrameDate + 2
                Case Else
                    dtNextDate = dtProgrameDate + 1
                End Select
                sql = "Update TblPassosStatus set TblPassosStatus.[dataprogramada] = #" & dtNextDate & "# where (TblPassosStatus.Passo=2) and (TblPassosStatus.CodProduto=" & Me.CodProduto & ")"
               Debug.Print sql
                CurrentDb.Execute sql
                For intI = 3 To 6
                    dtNextDate = dtNextDate + 7
                    sql = "Update TblPassosStatus set TblPassosStatus.[dataprogramada] = #" & dtNextDate & "# where (TblPassosStatus.Passo=" & intI & ") and (TblPassosStatus.CodProduto=" & Me.CodProduto & ")"
                    CurrentDb.Execute sql
                Next intI
                Me.Requery
            End If
        End If
    
       On Error GoTo 0
       Exit Sub
    
    Data_Programada_AfterUpdate_Error:
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Data_Programada_AfterUpdate of VBA Document Form_TblPassosStatus subformulário"
    End Sub
    To stop these dates being changed you could use the OnCurrent Event.
    Code:
    Private Sub Form_Current()
    If Me.Passo = 1 Then
    Me.Data_Programada.Locked = False
    Me.Data_Programada.Enabled = True
    Else
    Me.Data_Programada.Locked = True
    Me.Data_Programada.Enabled = False
    End If
    End Sub
    Regards
    John



  10. #10
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Regards John, I am very grateful for the code you sent me. Just did not work the way I was trying.
    When I type the date scheduled in the first field (DataProgramada) other dates are automatically generated. But the date entered in date field would be held the first step.
    When the user enters the date in the date field held (DataRealizada) the first step prior contact (ContatoPrevio), the field scheduled (scheduled) FASPré the second step, automatically fills in a date with one business day after the date entered in date field performed the first step. (The scheduled date field (DataProgramada) First step is empty). Then the scheduled date field (DataProgramada) third step of the Basic Registration (CadastroBásico), automatically fills in a date with 5 business days after the date of FASPre step. Then the field scheduled date of the fourth step (EnviodeInformações) automatically populates a date with same date as the previous step. Then the field IdentificarInteressados ​​automatically fill in a date with 5 business days after the date of EnvioDeInformações step. After the Schedule insert a date field with automatic 3 working days after the date of the field IdentificarInteressados. Then the field ApresentaçãoComercial, automatically fill in a date with 5 business days after the date of the camp schedule, with one condition: the date to be filled must be on a Wednesday or Thursday. If the fifth day Thursday is perfect. If the fifth business day is Friday, Monday or Tuesday, the date pad with how the first Wednesday. I know that these conditions had not informed in previous messages, it just confusing me with the time I'm trying to implement. I did a test, putting the code you sent me after updating the event in the Date field Held (DataRealizada), but had no effect.
    I'm sending a link to the db, please be kind enough to open the form fFiltros this sequence (Analyst) Carlos (Company) TEST, (Product) Test. Here's an example with the dates, but manually insert.
    Again, thank you.
    http://dl.dropbox.com/u/53793430/Ban...UTOMATICOS.zip
    Last edited by fabiobarreto10; 2012-01-10 at 20:52.

Posting Permissions

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