Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  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 mdb (2000 sr 1)

    In the wbook is present a macro "Sub AUT_SI_TASSI()".
    My 2 problem:
    1)
    I copy the value from the line 54 and put it in to mdb ESTERO table AUT_SI_TASSI, all ok!
    i would want to insert this condition in to macro:
    if into cell T55 is present a value recopy with the same parameter of line 54, else in to cell 55 not is present no value, run only the value in to cell 54

    2)
    insert into refered fileds of cells W54 and Y54 the value in the same format of the cells.
    Example:
    filed TASSO_BASE 2,000
    field TASSO_CLIENTE 2,000

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

    Re: if else in a mdb (2000 sr 1)

    There is a password on the VBA code in the workbook...

  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 mdb (2000 sr 1)

    Great Hans, first, good morning... and sorry for psw
    21374

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

    Re: if else in a mdb (2000 sr 1)

    I cannot find a macro named AUT_SI_TASSI in the workbook.

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

    Re: if else in a mdb (2000 sr 1)

    Oh my heat...
    Insert in your standard module.
    I am elementar I have insert * 100 but for me not is professional;-)

    Sub AUT_SI_TASSI()
    Sheets("LAVORAZ").Select
    'Microsoft ActiveX Data Objects 2.x library

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:EPFESTERO.MDB;" & _
    "User Id=admin;" & _
    "Password="

    Set rs = New ADODB.Recordset
    rs.Open "select * from AUT_SI_TASSI", cn, adOpenStatic, adLockOptimistic

    rs.AddNew

    rs.Fields("SETTORE").Value = Range("B54").Value
    rs.Fields("COPE").Value = Range("C54").Value
    rs.Fields("DATA_COMP").Value = Range("D54").Value
    rs.Fields("IMP_RIC").Value = Range("E54").Value
    rs.Fields("DATA_RIC").Value = Range("F54").Value
    rs.Fields("DATA_GEST").Value = Range("G54").Value
    If Range("H54").Value = "0" Then
    rs.Fields("DATA_OSU").Value = Null
    Else
    rs.Fields("DATA_OSU").Value = Range("H54").Value
    End If
    rs.Fields("DATA_ESE").Value = Range("I54").Value
    rs.Fields("UT_COMP").Value = Range("J54").Value
    rs.Fields("UT_RIC").Value = Range("K54").Value
    rs.Fields("UT_GES").Value = Range("L54").Value
    If Range("M54").Value = "0" Then
    rs.Fields("UT_ORG").Value = Null
    Else
    rs.Fields("UT_ORG").Value = Range("M54").Value
    End If
    rs.Fields("MERCATO").Value = UCase(Range("N54").Value)
    rs.Fields("INDEX").Value = Range("AL54").Value & ".XLS"
    rs.Fields("SPORT").Value = Range("P54").Value
    rs.Fields("C/C").Value = Range("Q54").Value

    rs.Fields("TASSO_BASE").Value = (Range("W54").Value) * 100
    rs.Fields("TASSO_CLIENTE").Value = (Range("Y54").Value) * 100

    rs.Fields("NOMINATIVO").Value = Range("R54").Value
    rs.Fields("COD_SPORT").Value = Range("T54").Value

    rs.Update
    rs.Close
    cn.Close
    End Sub

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

    Re: if else in a mdb (2000 sr 1)

    About problem 2):

    You should not multiply W54 and Y54 by 100. Instead, set the number of decimal places to 3 for the TASSO_BASE and TASSO_CLIENTE fields in the table in the database. That is the format used in the spreadsheet.

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

    Re: if else in a mdb (2000 sr 1)

    Can you try to explain your first question again? I don't understand it, I'm afraid.

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

    Re: if else in a mdb (2000 sr 1)

    Hummmmm....
    If in to cell T55 is present a value make the same about line 54 (the line 54 and related cells is always filled of many value) but the line 55 and related cells not always....

    In effect to the default the macro insert into mdb the value into mdb refred line 54.

    My idea....
    Recopy the macro Sub AUT_SI_TASSI() with another name in the same module for example Sub AUT_SI_TASSI_55() and cahange all refered link for cells in to line 55.
    Good, start the first macro and end this if into cell T55 not is present nothing.
    But if into cell T55 is present a value start the macro Sub AUT_SI_TASSI_55()...
    Naturally put this new value into same table.
    Hope i am clear, boh?!

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

    Re: if else in a mdb (2000 sr 1)

    If I understand you correctly, you want the following:

    - Always copy row 54 into the table in the database.
    - If T55 is not blank, also copy row 55 into the table.

    Is that correct?

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

    Re: if else in a mdb (2000 sr 1)

    Good morning Hans, sorry for delay but yesterday they have been engaged with a important breefing in my farm. Sorry.
    YES, perfect you have understand me, as usual. When the lesson of english for me;-)
    Tks.

    this is the conditiont hat I would want

    - Always copy row 54 into the table in the database.
    - If T55 is not blank, also copy row 55 into the table.

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

    Re: if else in a mdb (2000 sr 1)

    I have attached a text file with a modified version of the AUT_SI_TASSI procedure. I have split off the part that copies a row to the database into a separate procedure CopyRow. It's called like this in AUT_SI_TASSI:

    ' Copy row 54
    CopyRow rs, 54
    ' Test if T55 is not blank
    If Range("T55") <> "" Then
    ' If so, copy row 55
    CopyRow rs, 55
    End If

  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 mdb (2000 sr 1)

    GREAT!
    But.... difficult to set the feileds for percentage.
    Please attach my mdb with new setting fileds.
    Tks.

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

    Re: if else in a mdb (2000 sr 1)

    It's quite simple:
    - Open the table in design view.
    - Click in the TASSO_BASE field.
    - Set Decimal places to 3 (see screenshot)
    - Repeat for TASSO_CLIENTE.

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

    Re: if else in a mdb (2000 sr 1)

    hummmm.
    Maked your suggestion.
    But into mdb the fileds is 0,000% not 2,000% after i have run your macro, BOH?!

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

    Re: if else in a mdb (2000 sr 1)

    Sorry, I should have seen that. You must also change the Field Size (Dimensione campo) from Long Integer (Intero lungo) to Single or Double (don't know the Italian - ?? and doppio?)

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
  •