code error 09 in vb 6 accounting system

[Doogle]

The method I'd use would be to store the Cost Price in Dollars in the Table, together with the %age Net Profit I want to make on each sale. At the start of the day, input the exchange rate and then calculate today's Selling Price

Selling Price [Euro] = Cost Price[Dollars] * (1 + (%age Profit / 100) * Todays Exchange Rate.

Thus if I purchased a Pen for 1 USD and wanted to make 120% Net Profit and the Euro Exchange rate was 1$ = 0.75 Euro

Selling Price = 1 * (1 + 1.2) * 0.75 Euro = 1.65 Euro

If the exchange rate changes to 1$ = 0.72 Euro the selling price would be 1.58 Euro etc.

If you're just holding the Selling Price (in Euro) in the Table, then you'll have to express the current exchange rate as a %age of the original rate (i.e the rate you used to calculate the selling price in the first place)

Thus if you're selling at 1.58 Euro, based on an exchange rate of 1$ = 0.72 Euro and the exchange rate changes to 1$ = 0.75 Euro (i.e. % difference of (0.75 - 0.72) / 0.72 = 4.166% ) you now sell at 1.58 + (1.58 * 4.166/100) = 1.65 Euro. Thus you need to store the Selling Price and the exchange rate it's based on in the Table.

ie.
Percentage Change = (Yesterday's exchange rate - Today's exchange rate) * 100 / Yesterday's exchange rate
Today's Selling Price= Yesterday's Selling Price * (1 + (Percentage Change / 100))

EDIT: It would be a 'strange' Retail Store where the Selling Prices for products changed on a daily basis - your Customers would never know how much a Pen was going to cost them. I'd be surprised if I bought a Pen from you yesterday at 1.58 Euro, today it cost me 1.65 Euro and perhaps the next day only 1.50 Euro. I expect fluctuations but not on a dialy basis. It would be a nightmare for the sales staff to go round every day changing the price tags for everything !

[The Welshman]

The traditional way of accounting across multiple currency environments is to store ALL your values in a home currency (presumably Euros in this case).

So when you are entering stock, you should put in how many dollars you paid and what the exchange rate was at that time, then store the result in Euros.

This way you can easily calculate actual Profit/Loss reports by comparing your purchase Costs in Euros with the Sale costs in Euros.
If you store them in different currencys, and you do not store the exchange rate at the time of transaction then producing the same report on different days with different current exchange rates will give you different answers.

[nawar]

hello Mr.Doogle ... it's a great idea to make a new table / column that saves the prices .
you do always surprise me with awesome ideas ... but you know now that i can't do that without ur help ..
can you please guide me to do this in code or to make this possible inside the program ?

thank you

[nawar]

THE Welshman thank you for the replay , but i will go with Mr.Doogle idea it's better and completing the program, also it's more close to the market and to the users of this programs.

[nawar]

Mr. Doogle ... those are the errors i get for the code u made me to edit all the prices.. for some reason i get this error

[Doogle]

In the SELECT statement you have to substitute your table's Name for 'Table' and the Column name that holds the Price for 'Price'

[nawar]

In the SELECT statement you have to substitute your table's Name for 'Table' and the Column name that holds the Price for 'Price'

I did changed the price with the qmimi_sh.... The qmimi is the culomn that holds the price in the db

[Doogle]

Look at your code again, in Post #45, unless my eyes deceive me there's a line there:

strSQL = "SELECT Price FROM Table"

Hint, it's immediately before the one in yellow.

[nawar]

Look at your code again, in Post #45, unless my eyes deceive me there's a line there:

strSQL = "SELECT Price FROM Table"

Hint, it's immediately before the one in yellow.

Yes sir i got it
Anyway i want the idea u told me about. , like making a new table with changable prices its breliant one

[nawar]

Still getting the same error...

this is the things i have changed as you told me

[Doogle]

Take a deep breath, have a cup of coffee and re-read the advice you've been given.

It was never suggested you change the SQL to "tblArtikujt", it was never suggested you use

rs.Open db,adOpenStatic, adLockOptimistic

[nawar]

i am sorry sir but i can't fix it it's harder than i thought ..
can you please show me how ?

[Doogle]

How hard can it be to change

strSQL = "SELECT Price FROM Table"

to

strSQL = SELECT qmimi_shitj FROM tblArtikujt"

????

[nawar]

still getting the same error

[nawar]

here is the error :

[Spoo]

How hard can it be to change

strSQL = "SELECT Price FROM Table"

to

strSQL = SELECT qmimi_shitj FROM tblArtikujt"

????

Doogle

Perhaps you meant this instead

strSQL = SELECT qmimi_shitjes FROM tblArtikujt"

Spoo

[nawar]

still the same ... Error .

[Spoo]

Nawar

Did you also change these ...

Do Until rs.EOF
    rs![qmimi_shitjes] = rs![qmimi_shitjes] + (curDelta * rs![qmimi_shitjes] / 100)
    rs.Update
    rs.MoveNext
Loop

EDIT

BTW, did you ever define db?

Set rs = New ADODB.Recordset
strSQL = "SELECT qmimi_shitjes FROM tbdArtikujt"
rs.Open db, adOpenStatic, adLockOptimistic

EDIT 2

I'm a DAO kinda guy (as opposed to ADO), so my knowledge
of the syntax is minimal. Nonetheless, I found this in MSDN

Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim varDate As Variant

' Open connection.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

' Open employee table.
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable

Perhaps you need to modify your code to follow the above
example.

Spoo

[nawar]

Nawar

Did you also change these ...

Do Until rs.EOF
    rs![qmimi_shitjes] = rs![qmimi_shitjes] + (curDelta * rs![qmimi_shitjes] / 100)
    rs.Update
    rs.MoveNext
Loop

EDIT

BTW, did you ever define db?

Set rs = New ADODB.Recordset
strSQL = "SELECT qmimi_shitjes FROM tbdArtikujt"
rs.Open db, adOpenStatic, adLockOptimistic

EDIT 2

I'm a DAO kinda guy (as opposed to ADO), so my knowledge
of the syntax is minimal. Nonetheless, I found this in MSDN



Perhaps you need to modify your code to follow the above
example.

Spoo

Yes man i already changed these qmimi and also tried ti define the db it seens like there is something wrong in accessing the db i also tried to add the pass link and the db location but still the same error!!!

[Doogle]

strSQL = "SELECT qmimi_shitjes FROM tbdArtikujt"
rs.Open strSQL, db, adOpenStatic, adLockOptimistic

[nawar]

Still the same Error:...

strSQL = "SELECT qmimi_shitjes FROM tbdArtikujt"
rs.Open strSQL, db, adOpenStatic, adLockOptimistic

and the qmimi_shitj also ...
but nothing works

[Doogle]

Still the same Error:...

So, let's get this clear, you've changed the code so it now reads:

Set RS = New ADODB.RecordSet
strSQL = "SELECT qmimi_shitjes FROM tbdArtikujt"
RS.Open strSQL, db, adOpenStatic, adLockOptimistic

and you're still getting "the same Error ", i.e. error '3001', on the 'RS.Open' statement ?

[nawar]

yes sir i still getting an error .. but it's a different message and different number as in the picture below

[nawar]

and also i have already tried qmimi_shitj not shitjes as u wrote !

[Doogle]

The error message indicates that the connection to the Database is not active.Go back to Post #34 and read this bit again:

where: 'Table' is the name of the table containing the Price, 'Price' is the column name representing the Price and 'db' is an open connection to the DataBase. Assumes that 'Price' is a Currency Type in the Table.

So far we've managed to get the Table and the Column name correct all we need now is the Database Connection. I used 'db' as the Database connection, yours is 'strConek'. What do you think you should change ?

[nawar]

i have changed the db , but still getting error ...

[Doogle]

Reread Posts #34 and #65

[nawar]

Reread Posts #34 and #65

i am sorry but i still getting errors ... what ever i do ... i can show you million way i have already tried but with no success... if u can make it easier for me i will appreciate that...

thank you

[nawar]

couldn't fix it ... nothing works

[The Welshman]

Doogle's code in #25 shows opening a recordset that works.
Try using that as a basis for what you are doing here.

[nawar]

Doogle .. i end up with this code .. can you please help me a little ..

  
Private Sub cmdChangePrice_Click()
Call dbconek
With ar
Dim curDelta As Currency
Dim rs As ADODB.Recordset
If txtDeltaPrice.Text <> "" Then
    If IsNumeric(txtDeltaPrice.Text) Then
        curDelta = CCur(txtDeltaPrice.Text)
        Set rs = New ADODB.Recordset
        criteria = "Select *From tblArtikujt Where qmimi_shitjes='" & frmArtLista.txtDeltaPrice & "'"
        rs.Open criteria, strConek, adOpenStatic, adLockOptimistic
        If Not (rs.BOF And rs.EOF) Then
            Do Until rs.EOF
                rs![qmimi_shitjes] = rs![qmimi_shitjes] + (curDelta * rs![qmimi_shitjes] / 100)
                rs.Update
                rs.MoveNext
            Loop
        End If
    Else
        MsgBox "Please enter a numeric percentage value to apply to all the Prices"
    End If
Else
    MsgBox "Change Percentage cannot be blank"
End If
End Sub

[nawar]

Doogle's code in #25 shows opening a recordset that works.
Try using that as a basis for what you are doing here.

welshman .. dude i am trying to do what he says but i can't figure out .. it's very hard to do it my self , at the same time i keep trying to learn something and to figure out what is this and what is that for ...
anyway i think i am getting closer )
i just need a little push in here

thanks for the replay

[The Welshman]

You dont need the 'with ar', its a shortcut when you're using properties of ar repeatedly.
Also you might need to declare criteria.

Other than that it looks like it should do what you want.

[nawar]

i did like this one ...

as this code to delete a db

Call dbconek
With ar
criteria = "Select *From tblartikujt Where barkodi ='" & txtbar & "'"
.Open criteria, strConek, adOpenStatic, adLockOptimistic
!barkodi = txtbarkodi
!pershkrimi = txtpershkrimi
!furnizuesi = lblsupp
!kategoria = lblcat
!qmimi = txtblerja
!sasia = txtsasia
!tvsh = txtvat
!qmimi_shitjes = txtshitja
.Delete
MsgBox "The article was deleted successfully.", vbInformation, "Fshirja!"
.Close
End With
lv.Refresh
End If
End If
lv.ListItems.Remove (lv.SelectedItem.Index)
End Sub

[nawar]

man please

[Doogle]

You still don't seem to have got the hang of telling us what the problem actually is and where it's happening.

What is the code in Post#74 meant to do, what is the error message and on what line is it happening?

[nawar]

this is the code i end up with to change the prices but it's not working

Private Sub cmdChangePrice_Click()
Call dbconek
With ar
Dim curDelta As Currency
Dim rs As ADODB.Recordset
If txtDeltaPrice.Text <> "" Then
    If IsNumeric(txtDeltaPrice.Text) Then
        curDelta = CCur(txtDeltaPrice.Text)
        Set rs = New ADODB.Recordset
        criteria = "Select *From tblArtikujt Where qmimi_shitjes='" & frmArtLista.txtDeltaPrice & "'"
        rs.Open criteria, strConek, adOpenStatic, adLockOptimistic
        If Not (rs.BOF And rs.EOF) Then
            Do Until rs.EOF
                rs![qmimi_shitjes] = rs![qmimi_shitjes] + (curDelta * rs![qmimi_shitjes] / 100)
                rs.Update
                rs.MoveNext
            Loop
        End If
    Else
        MsgBox "Please enter a numeric percentage value to apply to all the Prices"
    End If
Else
    MsgBox "Change Percentage cannot be blank"
End If
End Sub

how i end up with this code ...
i was looking on this one and did almost the same as it is mix with your code

Call dbconek
With ar
criteria = "Select *From tblartikujt Where barkodi ='" & txtbar & "'"
.Open criteria, strConek, adOpenStatic, adLockOptimistic
!barkodi = txtbarkodi
!pershkrimi = txtpershkrimi
!furnizuesi = lblsupp
!kategoria = lblcat
!qmimi = txtblerja
!sasia = txtsasia
!tvsh = txtvat
!qmimi_shitjes = txtshitja
.Delete
MsgBox "The article was deleted successfully.", vbInformation, "Fshirja!"
.Close
End With
lv.Refresh
End If
End If
lv.ListItems.Remove (lv.SelectedItem.Index)
End Sub

[nawar]

this is your code but can't make it working ..!

Private Sub cmdChangePrice_Click()
Dim strConek As String
Dim curDelta As Currency
Dim rs As ADODB.Recordset
If txtDeltaPrice.Text <> "" Then
    If IsNumeric(txtDeltaPrice.Text) Then
        curDelta = CCur(txtDeltaPrice.Text)
        Set RS = New ADODB.RecordSet
strConek = "SELECT qmimi_shitjes FROM tbdArtikujt"
RS.Open strConek, dbconek, adOpenStatic, adLockOptimistic
        If Not (rs.BOF And rs.EOF) Then
            Do Until rs.EOF
                rs![qmimi_shitjes] = rs![qmimi_shitjes] + (curDelta * rs![qmimi_shitjes] / 100)
                rs.Update
                rs.MoveNext
            Loop
        End If
    Else
        MsgBox "Please enter a numeric percentage value to apply to all the Prices"
    End If
Else
    MsgBox "Change Percentage cannot be blank"
End If
End Sub

[nawar]

errors are still the same as usual.

[Doogle]

Please post the code for subroutine dbconek