?
Solved

need experts help

Posted on 2003-03-02
29
Medium Priority
?
227 Views
Last Modified: 2010-04-07
hi
hope can help me with this
have table
acc no,accname,credit
 1        A     100
 2        B     200

this my code and work good
For i = 1 To MSFlexGrid1.Rows - 1
 rec.Seek Array(MSFlexGrid1.TextMatrix(i, 1)), adSeekFirstEQ
       If Not rec.EOF Then
    bal = rec!credit
    rec!credit = bal + MSFlexGrid1.TextMatrix(i, 2)
    rec.Update
   Else
    sql = "insert into xx(ACCno,ACCNAME,credit)values(" & MSFlexGrid1.TextMatrix(i, 1) & ",'" & MSFlexGrid1.TextMatrix(i, 3) & "'," & MSFlexGrid1.TextMatrix(i, 2) & ")"
   db.Execute sql
End If

problme:
i want make this for each month and of course it will not
be good idea to make 12 tables for 12 months

i want make same code but if month changed execute same code but with new month

will be very greatfull with any help;wait expert s help
thanks
0
Comment
Question by:egkids
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 10
  • 3
29 Comments
 

Author Comment

by:egkids
ID: 8053380
hope my question is uploaded!!
0
 
LVL 8

Expert Comment

by:bukko
ID: 8053607

What about adding a Month collumn, rather than 12 tables?
You can make the month part of the primary key if required, so keeping it unique.

bukko
0
 

Author Comment

by:egkids
ID: 8053694
i can't change accno i want make new fields maybe name month how add month only in col?

month  accno  accname  credit
1      101     a        100

2      101     a         70



as u can see  from my code in first post
check if A exist then edit credit if not exist then add new

but want separate months
hope u understand what i want to do

thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 8

Expert Comment

by:bukko
ID: 8053742

That would still work.
Check if a record exists for Account A and Month 1, add if not.
This will work in the same way as your 12 tables idea but it will be more relational and will give you less problems in the future.
If you list each function you want to perform, I'll show you how.

bukko
0
 
LVL 8

Expert Comment

by:bukko
ID: 8053747

Also, you don't need to change the account number to change the primary key.
The primary key can be a composite of the account number and the month number.

bukko
0
 

Author Comment

by:egkids
ID: 8053951
hmmm sound good" will check for key and month"
ok how can add month only in the table
and how check with both?

please tell me more if u can add sample code will be great

thanks
0
 

Expert Comment

by:applayer
ID: 8054411
Hello,

I agree with bukko in that adding a month or a date column is definitely the way to go.  Here is a suggestion for your primary key though...You can create a column for your primary key which is just a number that auto-increments and use that as your primary key.

I know that you probably won't ever want to display that in your grid...but here's a trick you can use:  Keep a column in your grid and fill it with the auto-incremented number which you use for your primary key...just mind the spacing of your columns and you can insure that the column with your primary key is never actually visible on the form!

Another possible suggestion is that you can loop through the table containing your data, and based on the values of your data you can configure and fill the values which get displayed on your data grid.  I'm listing below a huge chunk of code from one of my programs...it's messy, and not applicable directly to your situation...but it will show you how to loop through a table, and based on the values you find in the table configure and display information in your data grid.

In my code below, the data found in "gridClientData.Col = 5" is the unique ID for that record, and it comes from the database based on a column which is set to auto-increment.  I just set up my data grid so that the column widths ended up such that .col 5 never displays on the form.  

Anyway, that's a lot of info, but I hope some of it might help you out.  If you go the route of the unique identifier using an auto increment column...you only have to check if the person has clicked on a row where the column matches your ID...not the month and ID.

One last suggestion is that you can use a variable in your forms to store your unique ID, and then you go check that variable against the database, and the grid when somebody clicks on the grid to see if you are in the right place.

here's some code from one of my apps:

Private Sub FillDataGrid()
  Dim adoConn As Connection
  Dim adoRS As Recordset
  Dim strConn As String
  Set adoConn = New Connection
  Set adoRS = New Recordset
  strConn = "DSN=CSI;"
  adoConn.Open (strConn)
  adoRS.Open "select * from SurveyAnswers", adoConn, adOpenKeyset, adLockReadOnly

  Dim x
  Dim strSplit() As String
  Dim strTemp As String
  If adoRS.EOF = True Then
    gridClientData.Rows = 1
    Exit Sub
  End If

  adoRS.MoveLast
  adoRS.MoveFirst

  While Not adoRS.EOF
    If adoRS("Archive") = "No" Then
    intOkayToSort = 1
    gridClientData.Rows = x + 2
    gridClientData.Row = x + 1
    gridClientData.RowHeight(x + 1) = 275
    gridClientData.BackColorSel = RGB(15, 51, 83)
    'gridClientData.BackColorSel = RGB(230, 130, 34)
    '-----------------------------------------------
    gridClientData.Col = 1
    If Len(adoRS("ClientID")) > 0 Then
      gridClientData.Text = adoRS("ClientID")
    Else
      gridClientData.Text = " "
    End If
    '-----------------------------------------------
    gridClientData.Col = 2
    gridClientData.Text = adoRS("ClientFirstName") & " " & adoRS("ClientMiddleName") & " " & adoRS("ClientLastName")
    '-----------------------------------------------
    gridClientData.Col = 3
    gridClientData.Text = adoRS("CounselorFirstName") & " " & adoRS("CounselorLastName")
    '-----------------------------------------------
    gridClientData.Col = 4
    If Len(adoRS("DateTimeStart")) > 0 Then
      strTemp = adoRS("DateTimeStart")
      strSplit = Split((strTemp), " ")
      If Len(strSplit(0)) > 0 Then
        gridClientData.Text = Trim(Format(strSplit(0), "mm/dd/yyyy"))
      Else
        gridClientData.Text = " "
      End If
    End If
    '-----------------------------------------------
    gridClientData.Col = 5
    If Len(adoRS("CaseID")) > 0 Then
      gridClientData.Text = adoRS("CaseID")
    Else
      gridClientData.Text = " "
    End If
    '-----------------------------------------------
    x = x + 1
    End If
    adoRS.MoveNext
  Wend
  adoRS.Close
  adoConn.Close

End Sub
0
 

Author Comment

by:egkids
ID: 8054557
hi
i add coulmn in table name month
my table now
month - accno - accname - credit
 
problme i want to seek for both accno and month if found both (true and true=true) then do my code

but i can't make key for month because there are many acc under same month i have key for accno only
can seek without key? only index with month field??
i don't know i feel  lost my way again

text4 is name of month, but not working

rec.Seek Array(MSFlexGrid1.TextMatrix(i, 1), "& text4 &"), adSeekFirstEQ

0
 

Author Comment

by:egkids
ID: 8054649
help me to correct this
rec.Seek Array(MSFlexGrid1.TextMatrix(i, 1), (Text4.Text)), adSeekFirstEQ

0
 

Author Comment

by:egkids
ID: 8054689
hellooo experts group!!
0
 

Expert Comment

by:applayer
ID: 8055196
hello egkids...

"help me to correct this
rec.Seek Array(MSFlexGrid1.TextMatrix(i, 1), (Text4.Text)), adSeekFirstEQ"

I would drop this approach if I were you...

if i was you then i would have a table with the following columns...id would be a column set to auto-increment that is sure to be unique.

id - month - day - year - time - ampm - accno - accname - credit

1. the columns i would add are id, day, year, time, and "ampm", where you code whether it is am or pm.

2. set your column id to be of type integer, or bigint, and set it to auto-increment.

3. forget (seriously!) the code you are currently working with that loops through the number of rows in your grid control

4. loop through the table in your database and fill the data grid with the information you need.






0
 

Expert Comment

by:applayer
ID: 8055221
hello egkids...

"help me to correct this
rec.Seek Array(MSFlexGrid1.TextMatrix(i, 1), (Text4.Text)), adSeekFirstEQ"

I would drop this approach if I were you...

if i was you then i would have a table with the following columns...id would be a column set to auto-increment that is sure to be unique.

id - month - day - year - time - ampm - accno - accname - credit

1. the columns i would add are id, day, year, time, and "ampm", where you code whether it is am or pm.

2. set your column id to be of type integer, or bigint, and set it to auto-increment.

3. forget (seriously!) the code you are currently working with that loops through the number of rows in your grid control

4. loop through the table in your database and fill the data grid with the information you need.






0
 
LVL 8

Accepted Solution

by:
bukko earned 400 total points
ID: 8056404

If your primary key was a composite of account number and month, then it would be unique.

For example:

Acc   Month  
A     1      (A*1)
A     2      (A*2)
B     1      (B*1)
B     2      (B*2)

However, you can also do as applayer suggests (twice) and use an entirely separate primary key.
Either was would work.
the only problem with using Seek is that you can only search one column at a time.
Better to use Find, e.g.:

rec.Find "acno='" & acc_no & "' and month=" & month

Regards

bukko
0
 

Author Comment

by:egkids
ID: 8058249
hi all
applayer  i think u don't know what is the problme here
maybe i wasn't clear enough!!
no problme to fill flexgrid with data  i take that from another table and then user choose what enry he/she want to post to ledger and my code make some modification to this journal entry in grid that why make loop through the grid and post it to ledger
and can make trial balance but the problme i want make trial balance for each month only that mean i should sepreate ledger for each month but i won't do that i want make another table as i said in first post
month accno accname credit debit
1      101    a      100    150
2      101    a      50      50

i think i will try  bukko  suggestion
but does find working fast as seek
i use access xp can make composite with it

thanks for all suggestions :))

0
 

Author Comment

by:egkids
ID: 8074912
hi all
thanks bukko for help
i add new col in table
Acc   Month  
A     1      (A*1)
A     2      (A*2)
B     1      (B*1)
B     2      (B*2)
and it is working good

u r great bukko :)))

may i ask something then will close this question

about vb data report " i don't use data environment"

will ask the user in my form to use date between ... and ....
just want to know how this two date can send from texbox to appear in header of my report in lable
can send sql to data sourcer of my report? how?

pleaase if u know any resourcer about data report tell me
thanks so much; u all great help

:))
0
 
LVL 8

Expert Comment

by:bukko
ID: 8075580

What are you using to produce the report?

bukko
0
 

Author Comment

by:egkids
ID: 8075585
bukko you are the best :))
0
 

Author Comment

by:egkids
ID: 8075606
use vb 6 data report
lol we add comment at same time
0
 

Author Comment

by:egkids
ID: 8075622
very interested to talk with u if u like here it is my mail
sourcer28@hotmail.com
:))
0
 
LVL 8

Expert Comment

by:bukko
ID: 8075671

Have you tried something like:

DataReport1.Show
DataReport1.Sections(0).lblDateFrom.Caption = "01/01/01"

I'm not familiar with the data report, but looking on the web it seems as if you can modify the fields in each section manually.

Post back if this is no good.

bukko
0
 

Author Comment

by:egkids
ID: 8075700
user put in textbox date (between ... and ...)
want this date show in report
i make my report like this
With DataReport1
          Set .DataSource = Nothing
              .DataMember = ""
          Set .DataSource = rec.DataSource
              With .Sections("section1").Controls
                 For i = 1 To .Count
                   If TypeOf .Item(i) Is RptTextBox Then
                             .Item(i).DataMember = ""
                     .Item(i).DataField = rec.Fields.Item(i - 1).Name
                     End If
                  Next i
               End With
             .show
            End With
0
 
LVL 8

Expert Comment

by:bukko
ID: 8075779

I assume from your code that .Sections("section1") is the report body.
Does that mean that .Sections("section0") is the header?

If so, try this:

Dim strFromDate As String
Dim strToDate As String

strFromDate = "2003/01/01" ' Get this from user input
strToDate = "2003/02/01"   ' ...and this
With DataReport1
         Set .DataSource = Nothing
             .DataMember = ""
         Set .DataSource = rec.DataSource
             With .Sections("section0").Controls
                ' You may need to modify these index values
                ' to be those in your header.
                ' Also, I'm guessing that the correct property
                ' to set is "text" - it might be "value", etc.
                .Item(0).Text = strFromDate
                .Item(1).Text = strToDate
             End With
             With .Sections("section1").Controls
                For i = 1 To .Count
                  If TypeOf .Item(i) Is RptTextBox Then
                            .Item(i).DataMember = ""
                    .Item(i).DataField = rec.Fields.Item(i - 1).Name
                    End If
                 Next i
              End With
            .show
           End With

Regards

bukko
0
 

Author Comment

by:egkids
ID: 8138991
hi bukko
i add new question hope u can help me :))
i have table

KEY  VAL
5     100
2     20
2     35
5     70

duplicate value for each key
how can make aggregation for each key
i mean i want seek for each key >1 and take sum(val)and put in another table like this

key  val
5    170
2    55
thanks for help

actully it problme with tree view children
want take total val for some nodes :))
0
 
LVL 8

Expert Comment

by:bukko
ID: 8139611

The following SQL will return the values the way you want:
(You didn't say the table name, so I'll assume my_table)

SELECT key,SUM(value) FROM my_table ORDER BY key GROUP BY key

To insert these into another table:
(e.g. new_table)
INSERT INTO new_table SELECT key,SUM(value) FROM my_table ORDER BY key GROUP BY key

Let me know how you get on.

Regards

bukko
0
 

Author Comment

by:egkids
ID: 8144235
hello bukko the best programmer on the web:)))
thanks so much for help

about the problme i make what i want but u know have feeling can do better and maybe working not perfect in future :((

let me tell u what is going on here
the problme i have trial table have all accounts and want make trial with total i mean customers accounts want it show under customers with total of them not individulas for each one and the same for payable and inventory
so i was try take nodes children accounts under the parent

my first table test it trial with detials
ACCNAME, total credit,total debit,parent of this node

i made another table test2 where i put the aggregation of node
db.Execute "insert into test2 SELECT test.parent, Sum(test.totatD) AS totatD,Sum(test.totalC) AS totalC,tree.ACCNAME FROM test INNER JOIN tree ON test.parent = tree.key WHERE (((test.parent)<'4_')) GROUP BY test.parent, tree.ACCNAME"

and then i take the rest of accounts i didn't make aggregation to them like capital

db.Execute "INSERT INTO test2 ( ACCNAME, totatD, totalC, parent ) SELECT test.ACCNAME, test.totatD, test.totalC, test.parent FROM test WHERE (((test.parent)>'4_'  And (test.parent)<>'6_'))"

sound silly i bet u have alot of things better than read what am i doing
any way i'm very greatfull to you
:))
egkids




0
 
LVL 8

Expert Comment

by:bukko
ID: 8144437

That looks good to me.
I'm unsure whether you are recreating your totals each time or whether you intend to update after each change.
If you update after each change, you might want to consider using a transaction. That way if you get an error, you can roll back the whole transaction and your totals will stay in sync with the individual records.

Regards

bukko
0
 

Author Comment

by:egkids
ID: 8144926
hi bukko
about this part of sql
test.parent = tree.key WHERE (((test.parent)<'4_')) GROUP BY test.parent
is there any way to start Descending not Ascending
because i want start with biger number of parent to make aggregation  starting with last child ;
it start with next number after 4 and it is 2-bankeg
A-bank
    2-bankeg
       3-bankeg111(last child)


want start with 3 put in 2 then put 2 in A

LOL i bet u want shoot me now

:))))
0
 

Author Comment

by:egkids
ID: 8145570
hi'
mostly i use insert into sql
have 2 question
if 2 user add new recoreds at same time can make problme
because i didn't use open table or adoptimistic?

2-about the case u told me
INSERT INTO new_table SELECT key,SUM(value) FROM my_table ORDER BY key GROUP BY key
can use sync with it?

thanks
0
 
LVL 8

Expert Comment

by:bukko
ID: 8147417

To sort descending...
ORDER BY column DESC
You can also mix ascending and descending with different columns, e.g.:
ORDER BY column1 DESC, column2, column3 DESC, etc...

The reason I asked which database you are using is because most of your problems can be fixed more easily if you are using SQL Server. This is especially the case with multiple users.

bukko




0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question