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

    if else in a button... (2000 sr 1)

    ... in the macro INSERISCI i ahve make 2 condition but the second not work "Type not correspondent"

    code:

    Sub INSERISCI()
    Sheets("MOD_307").Select
    If Range("E22").Value <> 0 Then
    MsgBox "INSERIMENTO NON POSSIBILE QUADRATURA ERRATA! CONTROLLARE."
    Exit Sub
    Else
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''
    'error here
    If (Range("C8") Or Range("C11") Or Range("C14") Or Range("I8") Or Range("I11") Or Range("I14")) = "ATTENZIONE CDI/CE NON TROVATO!!!" Then
    MsgBox "INSERIMENTO NON POSSIBILE ATTENZIONE! CDI/CE NON TROVATO!!! CONTROLLARE."
    Exit Sub
    Else
    Call CDI
    End If
    End If
    End Sub

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

    Re: if else in a button... (2000 sr 1)

    You have to write out the condition for each cell:
    <code>
    If Range("C8") = "ATTENZIONE CDI/CE NON TROVATO!!!" Or _
    Range("C11") = "ATTENZIONE CDI/CE NON TROVATO!!!" Or _
    Range("C14") = "ATTENZIONE CDI/CE NON TROVATO!!!" Or _
    Range("I8") = "ATTENZIONE CDI/CE NON TROVATO!!!" Or _
    Range("I11") = "ATTENZIONE CDI/CE NON TROVATO!!!" Or _
    Range("I14") = "ATTENZIONE CDI/CE NON TROVATO!!!" Then
    </code>
    Note that the extra set of parentheses ( ) has been removed.

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

    Re: if else in a button... (2000 sr 1)

    Good!

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

    Re: if else in a button... (2000 sr 1)

    My Great....
    You have already a wbook... If you see, with your help;-), i have make a new project to export value from excel to MDB.
    But in this case i have a problem.
    If you see, for example the table CDI are present many line with a value geted from excel.
    I dont understand, for example, when start the macro CDI, why in to table CDI are present in the middle of table 2 line filled with a "zero" ????
    I would want to append only the valid value...

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

    Re: if else in a button... (2000 sr 1)

    You have two superfluous lines rs.AddNew:

    ...
    'TERZA RIGA DARE
    rs.AddNew
    If Range("C12").Value = "127" Then
    ...

    and

    ...
    'PRIMA RIGA AVERE
    rs.AddNew
    If Range("I6").Value = "127" Then
    ...

    These lines add a new record but don't write any data to it. Remove both rs.Addnew lines.

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

    Re: if else in a button... (2000 sr 1)

    No words!:-)

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

    Re: if else in a button... (2000 sr 1)

    OK... Hans see that macro, it make a new table into mdb.
    is possible to arrange this in this mode:
    Copy the table CDI in this mode: CDI_126_815 if in to a cell of the column C (range d6,d9,d12) is present
    815.
    In effect maintain the table CDI from the next event. If into same range of column is present a new value: for exaple 816, recopy the table CDI in this mode: CDI_126_816
    If in the range mentioned already existis a value 815 or 816 skip this opearation
    This operation before to copy the related value from sheet...

    Example: the condition If Range("C6").Value = "126" is true Then create a new table CDI_126_815

    Const strDatabase = "GCD01F4500DATIPUBBLICABOUASSCDI.MDB"
    Sub CreateTable()
    Dim adoxCatalog As ADOX.Catalog
    Dim adoxTable As ADOX.Table
    Dim adoCN As ADODB.Connection

    'Establish connection to the database
    Set adoCN = New Connection
    With adoCN
    .Provider = "Microsoft.Jet.OLEDB.4.0" '4.0 for Access 2000, 3.51 for Access 97
    .ConnectionString = strDatabase
    .Open
    End With


    'Now associate our connection to a Catalog object
    Set adoxCatalog = New ADOX.Catalog
    Set adoxCatalog.ActiveConnection = adoCN

    'Create the table definition
    Set adoxTable = New ADOX.Table
    With adoxTable
    .Name = "NUOVA_TABELLA"
    .Columns.Append "CAMPO01", adInteger ' NUMERICO, INTERO LUNGO
    .Columns.Append "CAMPO02", adVarWChar, 100 ' TESTO LUNG 100
    .Columns.Append "CAMPO03", adCurrency ' VALUTA
    .Keys.Append "PrimaryKeyItemID", adKeyPrimary, "CAMPO01" ' DEFINISCE CHIAVE PER IL CAMPO ItemID
    End With

    'Append the table to the database
    adoxCatalog.Tables.Append adoxTable
    'Clean up and close
    End Sub

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

    Re: if else in a button... (2000 sr 1)

    It is very inefficient to create a new table for every value. Instead, you should store all records in the CDI table. In fact, you should reduce the number of fields in CDI, there is no need to have separate sets of fields for "C" and for "D". The value C or D and the value in the Part_1 field enable you to distinguish between the records.

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

    Re: if else in a button... (2000 sr 1)

    To be clear... This is a Debit (D) Credit maintain list of Account.
    I distribute to many user the same sheet.
    All users can insert variuos value in the yellow cells(the first cell is Principal Account, the second is the Sub/ Principal Account, the Thirt is the Sub/Sub Principal Account, in the clear blue cell is the ammount)
    If you see the sheet DB is the complete list of all Account number.

    I explain my dubt:
    Put in to table CDI all refered 126, in to table 127 all refered 127, ecc...
    My problem: if i insert all value in the table CDI for all category of 126 how can to exctract from table for example 126/815 Debit and Credit and put the value into sheet attached?

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

    Re: if else in a button... (2000 sr 1)

    You can use a query in the database to select all records for a specific account.

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

    Re: if else in a button... (2000 sr 1)

    Good, Hans....
    I have used your suggestion to put all data refered 126 (naturally D and C distingwish the value field), good idea.
    I attache the new project (attention i have changed the table CDI in 126)
    The suggestion to use the query is a good idea, but not for me.....(i dont know minimal the use of query). If you make for me an exaple...
    For the inport from Access into Excel i have many experince (tks youyr help) in this acse not are problem;-)

    My idea to extract:
    Insert in the sheet BALANCING 3 list box and when i select one account extract the data and put in this sheet, do you like my idea?
    Example:
    First list box: call the Principal account
    Second list box: call the Sub Principal account
    Tirdh list box: call the Sub/Sub Principal account
    Prerss ok, "At Voila" le jeux son...

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

    Re: if else in a button... (2000 sr 1)

    Tks Hans for query but form me i have many difficukt to use this suggestion... Many tks always.
    I found another way for this project...
    in this mode i not disturb you, for simple method for you, but for me is impossible...
    Tks for all...

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

    Re: if else in a button... (2000 sr 1)

    perhaps for me this way is more simpler:

    In the sheet BALANCING force a entry value:
    in G1 Master Account (PART_1 example 126)
    in H1 Sub Master Account (PART_2 example 815)
    in I1 Sub/sub Master Account (PART_3 example 0)

    And use this (naturalli this is an old macro to refer an old project tath you know well) i want only an help to set the condition of exctraction param before to init the import (for me the condition is located with the comment line), this mode i a good idea?:

    Long code moved to attachment by HansV

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

    Re: if else in a button... (2000 sr 1)

    I have attached the databse with a simple query. Open it in design view to see how it was made, and open it in datasheet view to see what it does. Enter 126 or 127 when you are asked to enter Part_1.

    It would be possible to use combo boxes in the Balancing sheet, but it is not trivial to make them work together.

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

    Re: if else in a button... (2000 sr 1)

    Do you want to move data from the spreadsheet to the database or from the database to the spreadsheet?

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