Category Archives: VB6

Update null value in Microsoft Access Database


The regular SQL is null update query  may fail while updating Access database. Access DB has some different is null checking system. Put the Is Null statement at the end of the where section may fix the problem.

Here is the query

UPDATE Sales SET Sales.Taxable = 0
 WHERE Sales.Taxable Is Null

Saving values to registry in VB6


Visual Basic simplyfies the way you can store and retrieve values. Usually you can place value in configuration files with easy code. If you want to go more advanced , can keep them at Windows Registry, were the OS keep track of its ihabitants.

Visual Basic 6 allows you store and retrieve values by using SaveSetting and GetSettings methods

Call SaveSetting("MyApp", "Software", "Key", "12312AZ90")
Print GetSetting("MyApp", "Software", "Key")

YouTube Video

Data entry in MSHFlGrid control vb6


MSHFlex Grid[Visual Basic 6.0] control is a goog tool for showing data in a tabular format as in Excel.2018-08-31_221326

Data entry in  Grid also possible with a Text Box which require handling Keyup,KeyPress Events of the TextBox control.

Place a GridControl and a TextBox to your project and add the following code the Textbox Events.

Public Sub MCellEnter()
With MSHFlexGrid_POrder
txt_DEntry.Text = .TextMatrix(.row, .col)
txt_DEntry.Move (.CellLeft + .Left), (.CellTop + .Top), _
.CellWidth, .CellHeight
txt_DEntry.Visible = True
DoEvents
txt_DEntry.SetFocus
End With
End Sub

The above code place the text box on the active cell by adjusting hight, width and top of Text Box

Private Sub txt_DEntry_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
With MSHFlexGrid_POrder
.TextMatrix(.row, .col) = txt_DEntry
If .col < .Cols – 1 Then
.col = .col + 1
MCellEnter
Else
.col = 0
If (.row + 1) < .Rows Then
.row = .row + 1
End If
MCellEnter
End If

End With
End If

End Sub

The above code handle the enter keybehaviour and following keep track of Arrow key movements.

Private Sub txt_DEntry_KeyDown(KeyCode As Integer, Shift As Integer)
With MSHFlexGrid_POrder
.Refresh
.TextMatrix(.row, .col) = txt_DEntry

Select Case KeyCode
Case KeyCodeConstants.vbKeyDown
If .row + 1 < .Rows Then
.row = .row + 1
MCellEnter
End If
Case KeyCodeConstants.vbKeyUp
If (.row – 1) > 0 Then
.row = .row – 1
MCellEnter
End If
Case KeyCodeConstants.vbKeyRight
If (.col + 1 < .Cols) Then
.col = .col + 1
MCellEnter
End If
Case KeyCodeConstants.vbKeyLeft
If (.col – 1) >= 0 Then
.col = .col – 1
MCellEnter
End If
End Select
End With

End Sub

That’s all you need to know

Exporting DB tables to Excel in VB6


It is something like complicated when you want to export reports from reporting objects like List view in VB6. In fact Excel object has provided a way to directly export contents of a table into Excel format in a quick manner using DAO or ADO connection.

To illustrate this we need,

  1. A DAO, ADO database connection
  2. Excel Objects
  3. Loop to print column heading

Connection

You may be familiar with the database connections in VB6, the following is an example of ADO data connection.

Dim adodb_db As New ADODB.Connection
Dim adodb_rs As New ADODB.Recordset

adodb_db.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\emp.mdb;Persist Security Info=False”
adodb_db.Open
adodb_rs.Open “select * from Accounts “, adodb_db, adOpenKeyset

The excel objects

The excel objects are required to create a new Excel file in order to export the rows of a table.

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

The columns

The CopyFromRecordset of Excel object didn’t fetch columns, so we do it manually as follows.

If adodb_rs.RecordCount > 0 Then
adodb_rs.MoveFirst
adodb_rs.MoveNext
adodb_rs.MoveLast
adodb_rs.MoveFirst

‘Start a new workbook in Excel
Set oExcel = CreateObject(“Excel.Application”)
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

For c = 0 To adodb_rs.Fields.count – 1
oBook.Worksheets(“Sheet1”).Cells(1, c + 1) = adodb_rs.Fields(c).Name
Next c

End if

Copying the rows to Excel

The Excel Worksheet object’s CopyFromRecordset help you to copy records to excel rows, all you need to specify the cell range.

oBook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset adodb_rs

The complete code

Here is the complete code

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Dim adodb_db As New ADODB.Connection
Dim adodb_rs As New ADODB.Recordset

adodb_db.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\SherPharma\SIGMA1819.mdb;Persist Security Info=False”
adodb_db.Open
adodb_rs.Open “select * from Account_Transactions “, adodb_db, adOpenKeyset

‘Set rs = MedicalShop.OpenRecordset(“select entryno,itemname,total from Sales_Particulars “)
If adodb_rs.RecordCount > 0 Then
adodb_rs.MoveFirst
adodb_rs.MoveNext
adodb_rs.MoveLast
adodb_rs.MoveFirst

‘Start a new workbook in Excel
Set oExcel = CreateObject(“Excel.Application”)
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

For c = 0 To adodb_rs.Fields.count – 1
oBook.Worksheets(“Sheet1”).Cells(1, c + 1) = adodb_rs.Fields(c).Name
Next c
oBook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset adodb_rs

oExcel.Visible = vbTrue

End If

 

Add auto field with alter statement in Access/Vb6


Create auto filed ,which automatically generate value, programmatically in Vb6 is easy with two line of code.

What we will do when you need create auto value programmatically or with query.

Here what you need to do

  1. Create a number/text field.
  2. Alter the filed as auto .

  alter table YourTable column [auto] counter(1,1)

You can use the statement with Execute method of ADO connection in VB6  as follows

 adodbconnection.Execute “alter table YourTable  alter column [auto] counter(1,1)”

VB6 : Adding a Grid control to Data Report


A summary in sales/purchase invoice a necessary for clarifying levied tax details or something like that. In VB6 Report allows you use limited controls only (Label, Textbox, Image control etc)

A grid view of summary as follows may add some beauty to your report, isn’t it?

salesprintgrid

As you know, you can’t  add a grid control to your Visual Basic 6.0 Data Report. So we need to alternate method. I suggest adding a screen picture of the grid instead of the using control, this can be quiet easy. Continue reading

Copying rows from one Access database to Another using Query


In access database, we can copy rows from one table to another using query. How can we transfer rows from one database to another?

Using the in  keyword in Insert statement we can specify an external database path as follows in a access query

insert into Account_Transactions(Date ,Name ,Type ) in ‘D:\Sherpharma\T.mdb’ select  Account_Transactions.Date,Account_Transactions.Name,Account_Transactions.Type from  Account_Transactions

Here we copy the rows from current database to the T.mdb files. We can also use this query in programming as well.

 

How to add font selector property to User-control in VB6


Adding a property to a user control in VB6 requires 4 subroutines to be filled, the Let/Set, Get and property Bags respectively.

Let and get have the variant as return type as an argument, which means any value can be accessed through the property.

Public Property Get Interval() As Variant

Interval = Timer1.Interval

End Property

Public Property Let Interval(ByVal vNewValue As Variant)

Timer1.Interval = vNewValue

PropertyChanged “Interval”

End Property

Suppose you want to change how do you get the Font Dialog within the Property Window. This can be possible with use of stdfont  in place of Variant as follows

Public Property Set Font(ByVal Nfont As StdFont)

Set lbl_Time.Font = Nfont

Refresh

PropertyChanged “Font”

End Property

Public Property Get Font() As StdFont

Set Font = UserControl.lbl_Time.Font

End Property

The complete VB6 Stopwatch Project can be accessible in my GitHub repository

VB6: List all printers using code in Windows


Listing all printers with name, Port in VB6 is simple as listing default printer port using Kernel32 library . Today we going show you how to do this.

As usual we start with function declaration, then define a new function which add all information into a list box. Here we goes

Kernel32 GetProfileString method

Declare Function GetProfileString Lib “kernel32.dll” Alias “GetProfileStringA” (ByVal lpAppName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long) As Long

Add printer information into a list box

Public Sub GetPrinterList(lstPrinter As ListBox)
Dim PrintData As Printer
Dim defprinterpos%
For Each PrintData In Printers
‘ Add printer name and port to list
lstPrinter.AddItem PrintData.DeviceName & ” at: ” & PrintData.Port
‘ Check for default printer
If PrintData.DeviceName = Printer.DeviceName Then defprinterpos = lstPrinter.NewIndex
Next
lstPrinter.ListIndex = defprinterpos%
End Sub

This sub procedure will take list box as argument, and add printer to this list box.

Call the sub

The final line of code just invoke the procedure and see what we have. 
Happy coding

VB6: List default printer using code in Windows


kernel32 hold lots of interesting function that you may not utilized yet. With the Profile String  functionality programmers can list default printer name, with serial port, Driver etc. Is that interesting. 


Just create a bunch of function and call it.

Declare the function

Declare Function GetProfileString Lib “kernel32.dll” Alias “GetProfileStringA” (ByVal lpAppName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long) As Long

Define the GetDefaultPrinter function

Public Function GetDefaultPrinter() As Printer
    Dim strBuffer As String * 254
    Dim iRetValue As Long
    Dim strDefaultPrinterInfo As String
    Dim tblDefaultPrinterInfo() As String
    Dim objPrinter As Printer
    ‘ Retreive current default printer information
    iRetValue = GetProfileString(“windows”, “device”, “,,,”, strBuffer, 254)
    strDefaultPrinterInfo = Left(strBuffer, InStr(strBuffer, Chr(0)) – 1)
    tblDefaultPrinterInfo = Split(strDefaultPrinterInfo, “,”)
    For Each objPrinter In Printers
        If objPrinter.DeviceName = tblDefaultPrinterInfo(0) Then
            ‘ Default printer found !
            Exit For
        End If
    Next
    ‘ If not found, return nothing
    If objPrinter.DeviceName tblDefaultPrinterInfo(0) Then
        Set objPrinter = Nothing
    End If
    Set GetDefaultPrinter = objPrinter
End Function

Use the function to list default  

Private Sub Command8_Click()
Dim objPrinter As Printer
Set objPrinter = GetDefaultPrinter()
Text2.Text = “Default printer is: ” + objPrinter.DeviceName & _
Chr(13) & ” Driver name is: ” + objPrinter.DriverName _
& Chr(13) & ” Port is: ” + objPrinter.Port
Set objPrinter = Nothing
End Sub