Results 1 to 11 of 11
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INSERT NULL (CLEAR VALUE) (2000 sr 1)

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim cnn As Object
    Dim rst As Object
    Dim strCon As String
    Dim i As Integer

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & gPROVADatabasePath & ";'"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open strCon
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open "CONTROLLO", cnn, 1, 3, 512

    For i = 1 To 6
    If rst.Fields("WBOOK" & Format(i, "00")) = Range("A3") Then
    rst.Fields("WBOOK" & Format(i, "00")) = Null
    rst.Update
    Exit For
    End If
    Next i

    If i = 7 Then
    MsgBox "ERRORE - UTENTO NON LOGGATO", vbExclamation
    End If
    End Sub

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

    Re: INSERT NULL (CLEAR VALUE) (2000 sr 1)

    Tks, Hans...
    But why not is possible to controll this script in StepByStep (with PF8)...

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

    Re: INSERT NULL (CLEAR VALUE) (2000 sr 1)

    The BeforeClose event procedure has an argument (Cancel), therefore it is not a macro. You can set a breakpoint on the first line of the procedure (click in the first line and press F9). When you close the workbook, the code will pause at the breakpoint, and you can then use F8 to single step through the code.

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

    Re: INSERT NULL (CLEAR VALUE) (2000 sr 1)

    Great...
    Dubt why the time to clear the value in the table not is fast?...
    from wath depend the speed of opreation?
    Existis a metod to make this operation with mutch speed?
    peraph because i have a MDB on a server and not in my PC?

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

    Re: INSERT NULL (CLEAR VALUE) (2000 sr 1)

    Clearing the value itself should be more or less instantaneous, but creating the connection to the database will take some time. If several users are trying to update the database at the same time, that will certainly have an impact.

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

    Re: INSERT NULL (CLEAR VALUE) (2000 sr 1)

    OK!...
    Hans, i have tested the script but have a dubt...
    I have tested this script with only a user inserted in the table and the macro not go in:
    If i = 7 Then
    MsgBox "ERRORE - UTENTO NON LOGGATO", vbExclamation
    End If

    but directlly go in end sub...

    peraphs is good this uppdate on script:

    If all value in the fileds Wbook1 to Wbbok6 is cleared (with null) then MsgBox "ERRORE - NESSUN UTENTE LOGGATO", vbExclamation else exit sub....

    In effect find in all Wbookxx and if not is present a value...

    Or not?

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

    Re: INSERT NULL (CLEAR VALUE) (2000 sr 1)

    That's up to you to decide.

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

    Re: INSERT NULL (CLEAR VALUE) (2000 sr 1)

    Hans, trasform my terrible pice of code in a funcional code, i hope you have understand me with this line;-)

    If rst.Fields("WBOOK01") And rst.Fields("WBOOK02") And rst.Fields("WBOOK03") And rst.Fields("WBOOK04") And rst.Fields("WBOOK05") And rst.Fields("WBOOK06") = Null Then
    MsgBox "ERRORE - NESSUN UTENTE LOGGATO", vbExclamation
    End If
    exit sub

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

    Insert Null (Clear Value) (2000 sr 1)

    With the event Private Sub Workbook_Open() in ThisWorkbook i insert with this script a username in A3:
    Private Sub Workbook_Open()
    Dim wshNet
    Sheets("L0785_TOTALE").Select
    Set wshNet = CreateObject("WScript.Network")
    wshGetUserName = wshNet.UserName
    Set wshNet = Nothing
    Range("A3") = ""
    Range("A3") = UCase(wshGetUserName)

    Range("A5") = ""
    Range("D5") = ""

    Range("K1") = ""
    Range("K3") = ""

    Sheets("L0785_TOTALE").Select
    ActiveWindow.SmallScroll ToRight:=-20
    Range("A7").Select
    On Error GoTo FINE
    Kill "C:EPFL0785.EPF"
    FINE:
    Dim cnn As Object
    Dim rst As Object
    Dim strCon As String
    Dim i As Integer

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & gPROVADatabasePath & ";'"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open strCon
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open "CONTROLLO", cnn, 1, 3, 512

    For i = 1 To 6
    If IsNull(rst.Fields("WBOOK" & Format(i, "00"))) Then
    rst.Fields("WBOOK" & Format(i, "00")) = Range("A3")
    rst.Update
    Exit For
    End If
    Next i

    If i = 7 Then
    MsgBox "6 UTENTI LOGGATI", vbExclamation
    End If

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    End Sub

    In Workbook_BeforeClose(Cancel As Boolean) i would want to find in the table the value present in A3 (inserted with the first script) and clear with NULL...

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

    Re: INSERT NULL (CLEAR VALUE) (2000 sr 1)

    You must use the IsNull function to test if a value is null. You can use it like this:

    If IsNull(rst.Fields("WBOOK01")) And IsNull(rst.Fields("WBOOK02")) And rst.Fields("WBOOK03")) And rst.Fields("WBOOK04")) And rst.Fields("WBOOK05")) And rst.Fields("WBOOK06")) Then

    or like this:

    If IsNull(rst.Fields("WBOOK01") & rst.Fields("WBOOK02") & rst.Fields("WBOOK03") & rst.Fields("WBOOK04") & rst.Fields("WBOOK05") & rst.Fields("WBOOK06")) Then

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

    Re: INSERT NULL (CLEAR VALUE) (2000 sr 1)

    I have chosen thsi and work perfect!:

    If IsNull(rst.Fields("WBOOK01") & rst.Fields("WBOOK02") & rst.Fields("WBOOK03") & rst.Fields("WBOOK04") & rst.Fields("WBOOK05") & rst.Fields("WBOOK06")) Then

    You have translate 3 step: my italian, my code, and my idea;-)
    Good... but this is normal for you, you are a Wizard, or not?
    Tks as usual.

Posting Permissions

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