if else in a mdb (2000 sr 1)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

I cannot find a macro named AUT_SI_TASSI in the workbook.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?!

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?)

Reply | Quote

“GREAT 2 , THE RETURN…”
with the last your suggestion all work perfect!
Tks for patience.
Sal
Note: dont worry for Italian knoledgment yau are always clear…;-)

Reply | Quote