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

    WHY THE MACRO FIND ... (2000 sr 1)

    With this macro i find in the table CONTROLLO if is present a value "TAB_OK" in the field TABULATO_OK...
    But the macro find always also if in the filed not is present TAB_OK?
    iN THE DB NOT IS PRESENT NOTHING..............

    <font face="Script MT Bold">Sub TAB_IMPEGNATO()
    Dim PROVADatabase As ADODB.Connection
    Dim RecordSet As ADODB.RecordSet

    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 RecordSet = New ADODB.RecordSet
    RecordSet.Open "CONTROLLO", PROVADatabase, adOpenForwardOnly, adLockPessimistic, adCmdTable
    RecordSet.MoveFirst

    If RecordSet!TABULATO_OK = "TAB_OK" Then
    MsgBox "TABULATO GIA' IMPORTATO, IMPOSSIBILE PROSEGUIRE!"
    RecordSet.Close
    PROVADatabase.Close
    Exit Sub
    End If

    'RecordSet.AddNew
    'RecordSet!TABULATO_OK = "TAB_OK"
    'RecordSet!DATA = "TAB_OK1"
    'RecordSet!USER = Range("A3")
    'RecordSet.Update
    'RecordSet.Close
    'PROVADatabase.Close
    End Sub
    </font face=script>

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    The line

    On Error Resume Next

    suppresses all error messages. The line

    If Err <> 0 Then

    tests whether an error has occurred while opening the database connection. After that, error messages are still turned off. In your situation, the table CONTROLLO is empty, so the statement

    RecordSet.MoveFirst

    causes an error, but because of the "On Error Resume Next" you don't get an error message. You must turn on error handling, and test whether the table contains records. See attachment for code with error handling and testing for an empty table.

    Note: I changed the name of the variable Recordset to rst. It is not a good idea to give a variable the same name as a reserved name.

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    Tks for suggestion and code...
    But where i insert "call my macro" if the condition not existis....
    In effect if the value "TAB_OK" not existis in the table else "call my macro" ecc....

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    Sorry, I don't understand your question.

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    Hans, peraphs you have think when the condition not exist got this blok:
    'RecordSet.AddNew
    'RecordSet!TABULATO_OK = "TAB_OK"
    'RecordSet!DATA = "TAB_OK1"
    'RecordSet!USER = Range("A3")
    'RecordSet.Update
    'RecordSet.Close
    'PROVADatabase.Close

    no i have commented this line...
    If the condition existis ok to exit sub but When the codition not existis call "My macro"...

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    Call your macro instead of the lines you commented out, perhaps?

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    Hans, dubt...
    But how to write always in the first line of mdb before clear the related record, an not add up...

    rst.AddNew
    rst!TABULATO_OK = "TAB_OK"
    rst!DATA = Date
    rst!USER = Sheets("L0785_TOTALE").Range("A3")
    rst.Update
    in effect if i re-start the macro the line is added upp...

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    The line

    rst.AddNew

    creates a new record. If you want to edit the first record instead, remove that line.

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    ok, for this rst.AddNew
    but to clear alla fileds before insert the value?

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    I'm not sure I understand. If you add a new record, you don't have to clear values. If you edit an existing record, the code overwrites existing values. If there are more fields than are set in your code now, set the other fields to Null if necessary.

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    In effect i distibute the wbook to many users.
    And my problem is:
    No permit to import a txt file if other user have just imported...
    Example:
    The user1 import the txt file and after this operation, in the end of macro that import the txt file, write in the access table CONTROLLO variuos value.
    In this mode if other user for example user2 start the macro to import the txt file, in the first line of import file i would want to insert a controll
    If in the field TABULATO_ok is present the value TAB_OK the user2 not can import the txt file...

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    You already have code to read and write the table, so you should be able to do what you want.

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    Hi Hans....
    still on this problem

    With this macro (it start in autopen of wbook) i write 3 value in the refered fields, all is ok!
    The next day when i re open the wbook i would want to clear the old fileds and re write the new value always in the same line of table... and not add up
    If i close and reopen the wbook in various time the record are added up...


    Sub TAB_IMPEGNATO()
    Dim PROVADatabase As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set PROVADatabase = New ADODB.Connection

    PROVADatabase.CursorLocation = adUseClient
    On Error Resume Next
    PROVADatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & _
    gPROVADatabasePath & "';"

    Set rst = New ADODB.Recordset
    rst.Open "CONTROLLO", PROVADatabase, adOpenForwardOnly, _
    adLockPessimistic, adCmdTable

    rst.AddNew
    rst!TABULATO_OK = "TAB_OK"
    rst!DATA = Now
    rst!USER = Range("A3")
    rst.Update

    rst.Close
    Set rst = Nothing
    PROVADatabase.Close
    Set PROVADatabase = Nothing

    End Sub

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    The instruction

    rst.AddNew

    will append a new record at the end. If you remove this line, you will overwrite an existing record. If you don't specify which record, you will always overwrite the first record. Is that what you want?

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

    Re: WHY THE MACRO FIND ... (2000 sr 1)

    Perfect !
    Another modify in autoclose of wbook is possible to clear the value in this table or delete entire line of the table?

Page 1 of 2 12 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
  •