Page 1 of 4 123 ... LastLast
Results 1 to 15 of 56
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    for the wizard Hans... Insert record in the userfo (2000 sr 1)

    I Hans, with this procedure i have historized the record i access (3.400.450 cut and paste code 1 month of hard work)...
    <font face="Script MT Bold">Option Explicit
    Global Const gPROVADatabasePath = "E:MACROL0785-AUTCOGLIANDROPROVA.MDB"
    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" ( _
    ByVal lpBuffer As String, _
    nSize As Long) As Long
    Public Sub UpdatePROVA( _
    ByVal SERVIZIO As String, _
    ByVal NewNOTE_BOUValue As String, _
    ByVal NewSPESEValue As String, _
    ByVal NewDATA_ATTValue As String, _
    ByVal NewCODValue As String, _
    ByVal NewNOTA_LIBValue As String)

    Dim PROVADatabase As ADODB.Connection
    Dim ProvaRecordSet As ADODB.Recordset
    Dim ProvaStoriaRecordSet As ADODB.Recordset

    If SERVIZIO = "" Then
    MsgBox "ID SERVIZIO MANCANTE."
    Exit Sub
    End If

    Set PROVADatabase = New ADODB.Connection

    PROVADatabase.CursorLocation = adUseClient
    On Error Resume Next
    PROVADatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & gPROVADatabasePath & "'; User Id=admin; Password=;"
    If Err <> 0 Then
    MsgBox "NON TROVATO PROVA.MDB. PERCORSO ERRATO!"
    Exit Sub
    End If

    Set ProvaRecordSet = New ADODB.Recordset

    Dim strTable As String
    ' Take the name of the sheet after "L0785_"
    strTable = Mid(ActiveSheet.Name, 7)
    If strTable = "TOTALE" Or strTable = "CDI_50" Then
    ProvaRecordSet.Open strTable, PROVADatabase, adOpenForwardOnly, adLockPessimistic, adCmdTable
    End If

    Set ProvaStoriaRecordSet = New ADODB.Recordset
    ProvaStoriaRecordSet.Open "TOTALE_STORIA", PROVADatabase, adOpenForwardOnly, adLockPessimistic, adCmdTable

    ProvaRecordSet.MoveFirst

    ProvaRecordSet.Find "SERVIZIO = " & SERVIZIO
    If Not ProvaRecordSet.EOF Then
    If Not ProvaRecordSet!NOTE_BOU = NewNOTE_BOUValue _
    Or Not ProvaRecordSet!SPESE = NewSPESEValue _
    Or Not ProvaRecordSet!DATA_ATT = NewDATA_ATTValue _
    Or Not ProvaRecordSet!COD = NewCODValue _
    Or Not ProvaRecordSet!NOTA_LIB = NewNOTA_LIBValue _
    Then
    ProvaStoriaRecordSet.AddNew
    ProvaStoriaRecordSet!DateTimeStamp = Now
    ProvaStoriaRecordSet!userid = GetWindowsUserName
    ProvaStoriaRecordSet!SERVIZIO = SERVIZIO
    ProvaStoriaRecordSet!BEFORE_NOTE_BOU = ProvaRecordSet!NOTE_BOU
    ProvaStoriaRecordSet!BEFORE_SPESE = ProvaRecordSet!SPESE
    ProvaStoriaRecordSet!BEFORE_DATA_ATT = ProvaRecordSet!DATA_ATT
    ProvaStoriaRecordSet!BEFORE_COD = ProvaRecordSet!COD
    ProvaStoriaRecordSet!BEFORE_NOTA_LIB = ProvaRecordSet!NOTA_LIB
    ProvaStoriaRecordSet!AFTER_NOTE_BOU = NewNOTE_BOUValue
    ProvaStoriaRecordSet!AFTER_SPESE = NewSPESEValue
    ProvaStoriaRecordSet!AFTER_DATA_ATT = NewDATA_ATTValue
    ProvaStoriaRecordSet!AFTER_COD = NewCODValue
    ProvaStoriaRecordSet!AFTER_NOTA_LIB = NewNOTA_LIBValue
    ProvaStoriaRecordSet.Update
    End If
    ProvaRecordSet!NOTE_BOU = NewNOTE_BOUValue
    ProvaRecordSet!SPESE = NewSPESEValue
    ProvaRecordSet!DATA_ATT = NewDATA_ATTValue
    ProvaRecordSet!COD = NewCODValue
    ProvaRecordSet!NOTA_LIB = NewNOTA_LIBValue
    ProvaRecordSet.Update
    Else
    MsgBox "INDICE SERVIZIO NON TROVATO!"
    End If

    ProvaRecordSet.Close

    End Sub
    Private Function GetWindowsUserName() As String
    Dim Buffer As String
    Dim Length As Integer
    Buffer = Space$(20)
    Length = GetUserName(Buffer, Len(Buffer))
    GetWindowsUserName = Trim(Buffer)
    GetWindowsUserName = Left(GetWindowsUserName, Len(GetWindowsUserName) - 1)
    GetWindowsUserName = UCase(GetWindowsUserName)
    End Function</font face=script>

    My actually prob....

    During the scroll and with the ID SERVIZIO read in the table TOTALE_STORIA and if in this table is present the record show all line refred of this record in the userform...
    In effect intercept from SERVIZIO during the scroll, and if the record intercepted is present in the TOTALE_STORIA make appear a button in a side of userform and if i clikck onthis show the related line from table in to a user form...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    This is far too complicated. The overhead of continually reading the database while scrolling will make it very slow.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    [img]/forums/images/smilies/sad.gif[/img]
    ok... for slow serach
    But you have other way to show the line of the table ...?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    I don't really understand what you want.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    ok example...
    This procedure insert a new line in the table TOTALE_STORIA for every changement of record and add a new line if the value are re-changed (history of variuos changement of record).
    In effect with a button on the original userform INDIRIZZARIO (remember?) if i click on this show the lines refred from the record if this have more of one changement...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    But how do you want to display these records in the userform? If you read data from the history table into the userform, it will probably be written to the spreadsheet. You don't want that, do you?

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    HUm... Accccccc for my English.

    Example:
    I scroll, with the db L0785_TOTALE, during this scroll the actuall ID SERVIZIO is present in the table TOTALE_STORIA you make to appear in the userform a button (in thsi case the id in the sheet and in the table is present). If i click on the button make to appear a new user form with the line present in the table TOTALE_STORIA, in this case the user see wath is the history or chronology of variuos changement for the record actually finded...
    In effect a lookup from the scroll of userfrom and table...

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    No, i dont want to write in the sheet, show only the line from the table in a new userform...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    And this is what the result looks like on my PC.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    Create a command button on your form named cmdStoria, with caption STORIA. The code for this button is in the attached zip file. You will have to add error handling yourself, the way it is done in OpenPROVADatabase.

    The button opens a userform named Storia. This form is also in the zip file. You can import the form into your spreadsheet.

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    NO... WORS
    YES...1.000.000, TKS
    Naturally when i have assigned this post only for you, I did not mistake myself...
    ONLY YOU!

    A little modify:
    1) Is possible to colorize the first line (with the name of fileds) in red and bold
    2) autofit the record in the userform

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    Both modifications are difficult - they would involve Windows API calls, I fear, and I can't help you with that.

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    No problem! As usual tks you have solved the first big prob is the same.....

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    I have insert in event change thi contion but then butto show always... (naturally if nothing is present in this txtbox nothing is changaed)

    Peraphs i must insert this controll i the scroll change event?

    Private Sub TextBox35_Change()
    If Indirizzario.TextBox35 = "" Then
    Indirizzario.CMDSTORIA.Enabled = False
    Else
    Indirizzario.CMDSTORIA.Enabled = True
    End If
    End Sub

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: for the wizard Hans... Insert record in the userfo (2000 sr 1)

    You should also put this code in the ScrollBar1_Change procedure, below the part where the text boxes are filled.

Page 1 of 4 123 ... LastLast

Posting Permissions

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