network point of sale system problem (important)

dear all i have a problem with my system with the following
i make point of sale system to big store using vb6 and access 2000 the store have four (4) computers 3 clients and one server i install the system in all the pc's and the 1 server and 3 clients the system is very good using but there is some problems i need you to help me
1- there is some orders not saved (maybe) duplicated in the order table so i remove the primary keys from all the tables but there is some items not saved i dont know why
for example i cash 1 and cash 2 save in the same time 2 orders one of the orders not saved all the item some items loose from the order but the price is right (the total of the order is right)
no problem in the prices in the form but in the data base some items not exits  please i need your help

2-  i use the exe server for all the client as shourt cut so they really use the exe file from the system .

3- is the big data let the network slow?

4- what can i do to store all the data to the master table and detail table
i.m ( i have two table one master and one details the master no any problem but the details not all the item save on it )  
5 - i use ado programming

the network is work group i dont use ip address for this system

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

whether u used rs.Update method or using "update <tablename> set <colname> = <value> where <your condition> method in ur application.Or u may use rs.NewRecord also. It seems to be some times have problems while adding records in access database in networked mannerr. U better suggested to use Querries to manipulate all ur transactions instead of using rs.NewRecord or rs.Update methods.

Try this or post ur code so that i can suggest u appropriately.

With regards
N.Pradap Kumar
abdulhameedsAuthor Commented:
on save click

    tname = "S_Sales"
    fname = "ORDER_NO"
    Text1.Text = F_MAX(tname, fname)
'get tham order number    

 VSTRR = "sales"
'save order master (tra)
db.Execute "insert into tra (ordno,ordtype,ordcurr,ordDate,cashno,empname)  " _
& " values (" & Val(Text1.Text) & ",'" & VSTRR & "'," & Val(Text25.Text) & ",'" & Format(MaskEdBox1, "dd/mm/yyyy") & "','" & vCashNo & "','" & vEmpName & "')"

 Dim I As Double
 Dim VOrderDis As String

    VOrderDis = "sales voucher"    
    For I = 0 To List1(0).ListCount - 1

'' all the items from the lists (no,class,type,qty , price,tax,total)
    db.Execute "insert into traDet  " _
    & " values(" & Text1.Text & "," & List1(0).List(I) & "," & Val(List1(7).List(I)) & "," & List1    (2).List(I) & "," & List1(3).List(I) & "," & List1(4).List(I) & "," & List1(9).List(I) & ") "

Dim VSTRR As String

VSTRR = "sales"

'for the stores item
& " VALUES(" & 0 & "," & Val(Text1.Text) & "," & List1(0).List(I) & "," & 0 & "," & List1(2).List(I) & ",'" & VSTRR & "')  "

 Next I

this is what i do in all the clients (cash 1 , cash 2 , cash 3 )

in the tradet not all the data saved but the tra  table every thinig is ok
just i loose some items from the tradet

i mean some times when i view the order details from the database its reduced why ? i dont know where is the error just i need you to know that i remove all the primary key from tra and tradet tables

Exit Sub

Where are you getting the value of  text1.text for ordno ?
Are you retrieving it from the database or is each PC generating the ordno ?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

abdulhameedsAuthor Commented:
from the server i said before that  each pc have the short cut of the exeuction file from the server so the order number come from the server database

Public Function F_MAX(ByVal T_name As String, ByVal F_name As String) As Double
    Dim rs_max As Recordset
    Dim sql As String

    Set rs_max = New Recordset
    sql = "select max(" & F_name & ") as s  from " & T_name
    rs_max.Open sql, db, adOpenKeyset, adLockOptimistic
    If Not (IsNull(rs_max!S)) Then
        F_MAX = rs_max!S + 1
        'F_MAX = 2804
        F_MAX = 1
    End If

End Function

becouse it must give us the serial number from the server
becouse they cant give the same order number  from 2 pc's
i mean 1-2-3-4-5-6-7-8-9-10........
the order number 1 not redudunat for tax issue
abdulhameedsAuthor Commented:
function  FillExits(eno as string ) as string
Dim RS As Recordset
Dim SQL As String
Set RS = New Recordset
SQL = "select * from empWorks where empno='" & Combo1.Text & "' and edate=# " & Format(Date, "mm/dd/yyyy") & " #"
RS.Open SQL, db, adOpenKeyset, adLockOptimistic
If RS.RecordCount > 0 Then
Fillexits=  rs!status
End If
end function

'in form
if  FillExits="IN" then
'do your command
elseif FillExits="out" then
'do your command
end if

abdulhameedsAuthor Commented:
sorry this is not for this iam trying to delete last comment
can i know why don't u install ur application in each client and share the access database by changing the connection string in each client m/c?
so, ur network traffic will be minimized.

after you did the above, What my suggestion is change the column type of tra(master table) table's ordno to Auto Number. So, that orderno will be generated automatically.
follow the steps to make perfect execution of master - detail tables.

1. Insert a record into master table.
2. Read the max value of the ordno so that u can get the latest ordno
(if ur system is multi client m/c then get the ordno using where condition on the following other fields
"SELECT ordno FROM tra WHERE ordtype = '" & VSTRR & "',ordcurr = " & _
" & Val(Text25.Text) & ",ordDate = '" & Format(MaskEdBox1, "dd/mm/yyyy") & "',cashno= '" & vCashNo & "',empname = '" & vEmpName & "'")

3. use the aquired order no to update transaction table.

so, ur database integrity will be maintained efficiently and ur network will be freed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
<<   Set rs_max = New Recordset
    sql = "select max(" & F_name & ") as s  from " & T_name
    rs_max.Open sql, db, adOpenKeyset, adLockOptimistic
    If Not (IsNull(rs_max!S)) Then
        F_MAX = rs_max!S + 1
        'F_MAX = 2804
        F_MAX = 1
    End If

So when are you updating F_MAX, if you are not updating it immediately all of the pc's can grab the same order number. Then you are going to lose data.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.