madas:

Hi all, pls supposing i have a table that store some data at mysql side, if for example i have input some info that update the table to about more than 100 row, and i want perform a search of the last row and display the info on datagrid on my form and i want use the Last Row as the condition. How do i go about it. I use this;
[.Open "Select * From accountstate where Account_Balance = (Select LAST(Account_Balance) from accountstate)", Conn, adOpenDynamic, adLockOptimistic] and is giving error messge. But when I search for Maximum Value using this:
[.Open "Select * From accountstate where Account_Balance = (Select MAX(Account_Balance) from accountstate)", Conn, adOpenDynamic, adLockOptimistic] it works perfectly.
NOTE: am interested on the info in the Account Balance column thats why i put the column heading.
pls guys i need you help.

Thanks in advance.

But you said it worked?
LAST is not function in MySQL.
I will look up and find the similar keyword for you.

madas:

Hi all, pls supposing i have a table that store some data at mysql side, if for example i have input some info that update the table to about more than 100 row, and i want perform a search of the last row and display the info on datagrid on my form and i want use the Last Row as the condition. How do i go about it. I use this;
[.Open "Select * From accountstate where Account_Balance =  (Select LAST(Account_Balance) from accountstate)", Conn, adOpenDynamic, adLockOptimistic] and is giving error messge. But when I search for Maximum Value using this:
[.Open "Select * From accountstate where Account_Balance =  (Select MAX(Account_Balance) from accountstate)", Conn, adOpenDynamic, adLockOptimistic] it works perfectly.
NOTE: am interested on the info in the Account Balance column thats why i put the column heading.
pls guys i need you help.

  Thanks in advance.

If I get you right, you are trying to get balances, You don't need to store balances every time, you compute balance when needed.

Lets say your Table is Account_States(TransactionID AutoIncrement, AccountNo bigint, , TType, , Amount Decimal)

If you assume Debits are - and Credits are +ve
The Balance as at when you are posting is:
    select sum(Amount) from Account_States where AccountNo='2099343'
If you have used something AmountDr and AmountCr to mean Debit /Credit respectively
this will work
     select sum(AmountDr) as DebitBalance, sum(AmountCr) as CreditBalance
                       from Account_States Group By AccountNo where AccountNo='2099343'

Like this you only need to store transactions.

Note you can have a table to generate transaction ID separately. The Transaction ID will not need to be AutoIncrement in your accountstatus table, so 1 transaction ID can involve more than 100 records!


If I didn't get you right, please rephrase your question and reply.

Thanks

madas:

Hi all, pls supposing i have a table that store some data at mysql side, if for example i have input some info that update the table to about more than 100 row, and i want perform a search of the last row and display the info on datagrid on my form and i want use the Last Row as the condition. How do i go about it. I use this;
[.Open "Select * From accountstate where Account_Balance =  (Select LAST(Account_Balance) from accountstate)", Conn, adOpenDynamic, adLockOptimistic] and is giving error messge. But when I search for Maximum Value using this:
[.Open "Select * From accountstate where Account_Balance =  (Select MAX(Account_Balance) from accountstate)", Conn, adOpenDynamic, adLockOptimistic] it works perfectly.
NOTE: am interested on the info in the Account Balance column thats why i put the column heading.
pls guys i need you help.

  Thanks in advance.

I went thru ur question again, it looks like you like need info from the last record.
If you have a table like this  TableA(ID Autoincrement, Col1, Col2, )
you can try something like this:
          select * from TableA where ID in (select max(ID) from TableA)


This should give you the last record if ID is auto increment or you have serialised it somehow.

Nairalanda:



I went thru your question again, it looks like you like need info from the last record.
If you have a table like this TableA(ID Autoincrement, Col1, Col2, )
you can try something like this:
select * from TableA where ID in (select max(ID) from TableA)


This should give you the last record if ID is auto increment or you have serialised it somehow.

Hi all thanks for your respond to my question. @Nairalanda, you right i need info from last record of my fouth table (i.e accountstate table). but i dont want use ID as my search condition because i dont have ID on the table. In my table consist of only five columns which are (Balance_Date, Total_Contribution, Total_Loan_Out, Total_Loan_Refund & Account_Balance), that's why I use LAST(Account_Balance) thinking that LAST is a function in Mysql. Because what i am interested is the Last record of (Account_Balance). For more this my code
[Private Sub cmdBalanceSearch_Click()
'On Error Resume Next
Dim rsBSearch As New ADODB.Recordset


With rsBSearch

.Open "Select * From accountstate where Account_Balance = (Select LAST(Account_Balance) from accountstate)", Conn, adOpenDynamic, adLockOptimistic


If rsBSearch.RecordCount >= 0 Then

With dtgAccount

.ClearFields
Set .DataSource = rsBSearch
.Refresh

.Columns(0).Caption = "S/N"
.Columns(0).Width = 900
.Columns(1).Caption = "Balance Date"
.Columns(1).Width = 2000
.Columns(2).Caption = "TotalContribution"
.Columns(2).Width = 2500
.Columns(3).Caption = "TotalLoanOut"
.Columns(3).Width = 2200
.Columns(4).Caption = "TotalLoanRefund"
.Columns(4).Width = 2500
.Columns(5).Caption = "AccountBalance"
.Columns(5).Width = 2400

End With

End If

End With

End Sub
]
The program i am writing is for Cooperative society not for bank, so i need to keep then latest account balance at all time. thats why i want create a search query so that at a click of a button the latest balance (record) will be displayed on the form in Datagrid.

Thanks in advance.

Pls How can i use Crystal report in Vb6.0 pls

madas:


The program i am writing is for Cooperative society not for bank, so i need to keep then latest account balance at all time. thats why i want create a search query so that at a click of a button the latest balance (record) will be displayed on the form in Datagrid.

Thanks in advance.

@MADAS:>

If you must get a record from the last row, it is best to use ID that is auto number. Reason is that you may be picking the wrong item especially when multiple users are accessing the table. If you still don't want to use ID, your table contains a date field. Depending on the structure of your date, you can use it to search, (If your date is stored eg as 2009-08-29:12:34:12, it means you will now depend on 'Microseconds difference' in the date to get the last record -THIS MAY NOT BE VERY CORRECT unless there are not many users/records!)

This code should work  SELECT * FROM TABLE1 as T WHERE T.Balance_Date=(SELECT max(Balance_Date) from Table1)
                                                      #and T.User= #eg if Specific User is targeted!

I feel you think you can't include ID auto-number when your table is already existing. You can use ALTER TABLE command on MySQL interface or on PHP MyAdmin. You can even code in VB by searching if ID column exists 1st then Alter if not.

              ALTER TABLE `TABLE1` ADD `ID` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ;

This Adds Column ID as a Primary Key and Auto Increments.

With this you can be confident in the result of


           select * from Table1 where ID in (select max(ID) from Table1)



Alternatively (get more complicated thou), You can use TRIGGERS to monitor insert into your table and let the trigger do something like in this algorithm
Create a Table Table1Temp
CREATE a Trigger on Insert into Table1
   Delete Content of Table1Temp
   Insert Record Inserted into Table1Temp
End of Trigger
After all your insert> The last Item inserted is what is on the Temp Table
Triggers are like Stored Procedures
Hope this helps.

@BigaFees

This site will give you a nice guide which you can build upon to using Crystal Report

                  http://www.vb6.us/tutorials/using-crystal-reports-vb6-tutorial

However, you can use DataReport that is right on your VB6 installation.

Hope this helps.

I need code for the following problem:
(1) Code that will display printer box (i.e select printer)
(2) Code that will upload data in excel to access using vb6

Thanx

the two are within thread, Plz look for it.

No more questions?

It's a good practice to always have a unique Id column in your tables. Even if you will not use it in retrieving your data. You may also write a code to generate your custom unique Id not necessarily auto-incremented. It is the most reasonable way of accomplishing your aim as MADAS advised. He gave the right answer to your question.
There is not a function like "LAST" in Mysql. Don't imagine it for them. Always refer to documentation when in doubt.

No more questions?

i really don't mean any dis respect guys but i wouldn't believe that vb 6 programmers still exit. i once was a vd 6 programmer myself but have since moved to .net and right now am moving over to java.

Need experienced VB 6 and Vb. net programmers for an interesting project . Will pay for work done. Send CVs to projectvacancies@e-tutornigeria.com.

Well, it depe

shadowmay:

i really don't mean any dis respect guys but i wouldn't believe that vb 6 programmers still exit. i once was a vd 6 programmer myself but have since moved to .net and right now am moving over to java.

It depends on what u want to achieve in life. If u are so much interested in code aspect then you are good enough to continue pursuing languages or business aspect of programming then you are good enough to continue to make your money.

We have many programming languages, even dotnet we use, its version increase in an unprecedented scale; 2001,2002,2003,2005,2008 and even 2010. I think one should be so confused to pursuing these language!

Someone still make serious money with access programming,dos fox pro etc, one many condemn him but what he does works for his clients and they pay him up 200k per installation, he makes his living from it.

I think it is share confusion 4 one to know a tool that will assist him/her achieve an idea faster, one start feeling it is vb6.0.

I use vb.net 2003,2005,2008,2010, I use C#, I use vb6.0 but then why must I be ashamed that am still modifying my vb6.0 business code on my client sites which they still enjoy without complaining?

Think code, think business aspect of your coding solutions, I think that is what we should bear in mind.

It is like a university graduate wasting his time thinking he is better of than the Polytechnic counterpart.

In almost all federal and state universities and Polytechnic you hardly not see them teach GW Basic,qbasic,Fortran,Cobol and other mere forgotten languages.

In case those people need help who will they run to? That is the aim of this thread among other things.

Since you said you mean no insult I shall stop here.

Program on how to make marque UP,Down,Left Right

Add a timer & label control on your form
Set the interval to say 60
Timer.interval=60
set label name to LblMarque


Private Sub Timer1_Timer()
Call MoveLeft(LblMarque, Me) ' to scroll a label control with its content named LblMarque to left

'Call MoveRight(LblMarque, Me) ' to scroll a label control with its content named LblMarque to Right
'Call MoveUp(LblMarque, Me) ' to scroll a label control with its content named LblMarque to Up
'Call MoveDown(LblMarque, Me) ' to scroll a label control with its content named LblMarque to Down

End Sub

' Past below on your form

Public Sub MoveLeft(Obj As Control, frm As Form)
Obj.Move Obj.Left + 30
If Obj.Left > frm.Width Then
Obj.Left = -1 * (frm.ScaleWidth)
End If
End Sub

Public Sub MoveRight(Obj As Control, frm As Form)
Obj.Move Obj.Left - 30
If (Obj.Left + Obj.Width) < 0 Then
Obj.Left = frm.Width
End If
End Sub

Public Sub MoveUp(Obj As Control, frm As Form)
Obj.Move Obj.Left, Obj.Top + 30
If Obj.Top > frm.Height Then
Obj.Top = -1 * (frm.ScaleHeight)

End If
End Sub

Public Sub MoveDown(Obj As Control, frm As Form)
Obj.Move Obj.Left, Obj.Top - 30

If (Obj.Top + Obj.Height) < 0 Then
Obj.Top = frm.Height
End If
End Sub


Hope it helps someone

Below is code on how to set a active printer before printing.

Public Function Set_Printer(PrinterName As String)
Dim Prt As Printer
For Each Prt In Printers
If Prt.deviceName = PrinterName Then
Set Printer = Prt
End If
Next
End Function

Below is code to get all the printers in your system and add them in either combox or list box

Public Sub Get_Printer_List(cbo As Control)
On Error GoTo errh
Dim I As Integer
If TypeOf cbo Is ComboBox Then
Dim cto As ComboBox
Set cto = cbo
cto.Clear
Else
Dim lst As ListBox
Set lst = cbo
lst.Clear
End If



For I = 0 To Printers.Count - 1
If TypeOf cbo Is ComboBox Then
cto.AddItem Printers(I).deviceName
Else
lst.AddItem Printers(I).deviceName
End If

Next I

Exit Sub

errh:
MsgBox "Check you printer", vbInformation, "Error"
End Sub

The code below paints form

Public Sub PaintForm3D(frm As Form)
' This Sub draws lines around the Form to make it 3d

' white, upper - horizontal
frm.Line (0, 0)-(frm.ScaleWidth, 0), &HFFFFFF, BF
' white, left - vertical
frm.Line (0, 0)-(0, frm.ScaleHeight), &HFFFFFF, BF
' darkgrey, right - vertical
frm.Line (frm.ScaleWidth - 15, 0)-(frm.ScaleWidth - 15, _
frm.Height), &H808080, BF
' darkgrey, lower - horizontal
frm.Line (0, frm.ScaleHeight - 15)-(frm.ScaleWidth, _
frm.ScaleHeight - 15), &H808080, BF

End Sub

create folder that will not display error if the path is not existing or in use

'to module

Public Declare Function CreateDirectory Lib "kernel32" Alias "CreateDirectoryA" (ByVal lpPathName As String, lpSecurityAttributes As SECURITY_ATTRIBUTES) As Long
Public Type SECURITY_ATTRIBUTES
nLength As Long
lpSecurityDescriptor As Long
bInheritHandle As Long
End Type

' to form

Public Function CreateFolder(FolderPath As String) As Boolean

Dim Cool As SECURITY_ATTRIBUTES
Cool.bInheritHandle = 395226
Cool.lpSecurityDescriptor = 0
Cool.nLength = 1024

CreateFolder = CreateDirectory(FolderPath, Cool)

End Function

LuckyCo are you still there? Need to create some Vbasic 6 solutions. Will like to have some help. Should be mutually rewarding. Best wishes. I can be reached at komotundeyoung@yahoo.com

You can contact me on 08036025235, incase you have something you want me to do 4 you. Thanks.

Thanks. Will contact you this morning. Im impressed by your prompt and ever willing response. God bless you.

Good,thanks

Well if you really need anything in vb you are lucky that am on this forum, you can check out this blog
www.visualbasicguru..com, or you can also call me on 07031043884.

I also have programs for sale so if you are interested you give me a buzz
God bless you.

No more questions?

Thanks LuckyCO. Im putting my request together in form of meaningful specs. Will get back to you , once through
kyoung

Hello beknown,and other nairalander, I got the download you gave to me and this is how it went.
I first installed mssql2000 on my system then I went through the control panel as u have said in the download then clicking on the system DSN tab, I found out that sql server is showing on the page,

then I clicked on configure,


Then next

Then next


Then next



Then finish



Let me first appreciate u for the information, but I still have some problems. I have not successfully establish a connection and all my code have not been working. Pls if you can just try to write a sample programme that will contain just username and password using mssql and probably mysql, I will so much appreciate it. And pls I will always understand without pictures so that u can upload enough tips without making a large file. Just write them out in steps. I will always appreciate any recommendations too. Thanks in advance.

tolubonnke:

Hello beknown,and other nairalander, I got the download you gave to me and this is how it went.
I first installed mssql2000 on my system then I went through the control panel as u have said in the download then clicking on the system DSN tab, I found out that sql server is showing on the page,

then I clicked on configure,


Then next

Then next


Then next



Then finish



Let me first appreciate u for the information, but I still have some problems. I have not successfully establish a connection and all my code have not been working. Pls if you can just try to write a sample programme that will contain just username and password using mssql and probably mysql, I will so much appreciate it. And pls I will always understand without pictures so that u can upload enough tips without making a large file. Just write them out in steps. I will always appreciate any recommendations too. Thanks in advance.

Do you want to make connection to SQLSever 2000?, if so you dont need to use DSN.
You can use connection string to do that.