by Syed Zubair Ali (7 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 5.0
Difficulty: Unknown Difficulty
Originally Published: Mon 25th May 2009
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
A good database record finder. Search each record letter by letter and show result in msflexgrid, great code who are searching such
VBcoders
'Take one text box, one msflexgrid,one combo box, 4 lables and 3 command 'buttons
'command captions :(1) Find By (2) Refresh (3) Cancel
'take a data control and connect to biblio database and recordset Publishers
'msflexgrid datasource propoerty connect to data1
'and paste this code
'-------------------
Private Sub Combo1_Click()
Label1.Caption = "Search By " & Combo1.Text & " :"
Text1.Enabled = True
Text1.SetFocus
End Sub
Private Sub Combo1_DragDrop(Source As Control, X As Single, Y As Single)
Text1.Enabled = True
End Sub
Private Sub Command1_Click()
Combo1.Enabled = True
Combo1.SetFocus
Command1.Enabled = False
SendKeys "{HOME}+{END}"
End Sub
Private Sub Command2_Click()
On Error GoTo errh
Data1.RecordSource = "Select * from Publishers"
Data1.Refresh
MSFlexGrid1.Refresh
Text1.Text = ""
Text1.Enabled = False
Combo1.Enabled = False
Command1.Enabled = True
Combo1.Text = "Name"
MSFlexGrid1.LeftCol = 0
MSFlexGrid1.TopRow = MSFlexGrid1.Row
For I = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.ColWidth(I) = 3000
MSFlexGrid1.ColAlignment(I) = flexAlignCenterCenter
Next I
Exit Sub
errh:
MsgBox "Your EOF or BOF is out of order." & vbCrLf & "Please click on Refresh Button.", vbInformation, "Error Msg"
End Sub
Private Sub Command3_Click()
If MsgBox("Do You Want To Goto @ Desktop?", vbQuestion + vbYesNo, "Exit Confirmation") = vbYes Then
Unload Me
Exit Sub
End If
End Sub
Private Sub Data1_Reposition()
Data1.Caption = "Searching Criteria like " & "[" & Text1.Text & "]" & " Total Number of Matches Found : " & Data1.Recordset.AbsolutePosition + 1 & " of " & Data1.Recordset.RecordCount
End Sub
Private Sub Form_Activate()
For I = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.ColWidth(I) = 3000
MSFlexGrid1.ColAlignment(I) = flexAlignCenterCenter
Next I
MSFlexGrid1.RowHeight(0) = MSFlexGrid1.RowHeight(1) * 2.5
End Sub
Private Sub Form_Load()
Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2
With Combo1
.AddItem "Name"
.AddItem "ID No"
.AddItem "Company Name"
.AddItem "Zip Code"
.AddItem "Address"
.Text = "Name"
End With
Label1.Caption = "Search By " & Combo1.Text & " :"
Label2.Caption = "Only Text is Allowed."
End Sub
Private Sub MSFlexGrid1_RowColChange()
crow$ = "Row " & MSFlexGrid1.Row
ccol$ = "Col " & MSFlexGrid1.Col
Label3.Caption = crow$ & " && " & ccol$
End Sub
Private Sub MSFlexGrid1_SelChange()
numrows = Abs(MSFlexGrid1.SelectionMode = flexSelectionByRow) + 1
numcols = Abs(MSFlexGrid1.SelectionMode = flexSelectionByColumn) + 1
If numcols + numrows = 0 Then
Label4.Caption = "[No Selecetion}"
Else
Label4.Caption = numrows & " x " & numcols
End If
End Sub
Private Sub Text1_Change()
Dim strSQL As String, sqlquery As String
Select Case Combo1.Text
Case Is = "Name"
MSFlexGrid1.LeftCol = 1
sqlquery = "Select * from Publishers"
strSQL = strSQL & " where Name like '*" & Trim(Text1.Text) & "*'"
If Data1.Recordset.EOF Then
MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
SendKeys "{Home}+{End}"
On Error Resume Next
If Text1.Text = "" Then Data1.RecordSource = "Select * from Publishers"
Else
Data1.RecordSource = "Select * from Publishers where Name like '*" & Trim(Text1.Text) & "*'"
Data1.Recordset.MoveNext
Data1.Refresh
End If
Case Is = "ID No"
MSFlexGrid1.LeftCol = 0
strSQL = "Select * from Publishers"
strSQL = strSQL & " where PubID '*" & Trim(Text1.Text) & "*'"
If Data1.Recordset.EOF Then
MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
SendKeys "{Home}+{End}"
On Error Resume Next
If Text1.Text = "" Then Data1.RecordSource = "Select * from Publishers"
Else
Data1.RecordSource = "Select * from Publishers where PubID like '*" & Trim(Text1.Text) & "*'"
Data1.Recordset.MoveNext
Data1.Refresh
End If
Case Is = "Company Name"
MSFlexGrid1.LeftCol = 2
sqlquery = "Select * from Publishers"
strSQL = strSQL & " where [Company Name] like '*" & Trim(Text1.Text) & "*'"
If Data1.Recordset.EOF Then
MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
SendKeys "{Home}+{End}"
On Error Resume Next
If Text1.Text = "" Then Data1.RecordSource = "Select * from Publishers"
Else
Data1.RecordSource = "Select * from Publishers where [Company Name] like '*" & Trim(Text1.Text) & "*'"
Data1.Recordset.MoveNext
End If
Case Is = "Zip Code"
MSFlexGrid1.LeftCol = 6
sqlquery = "Select * from Publishers"
strSQL = strSQL & " where Zip like '*" & Trim(Text1.Text) & "*'"
If Data1.Recordset.EOF Then
MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
SendKeys "{Home}+{End}"
On Error Resume Next
If Text1.Text = "" Then Data1.RecordSource = "Select * from Publishers"
Else
Data1.RecordSource = "Select * from Publishers where Zip like '*" & Trim(Text1.Text) & "*'"
Data1.Recordset.MoveNext
End If
Case Is = "Address"
On Error Resume Next
MSFlexGrid1.LeftCol = 3
sqlquery = "Select * from Publishers"
strSQL = strSQL & " where Address like '*" & Trim(Text1.Text) & "*'"
If Data1.Recordset.EOF Then
MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
SendKeys "{Home}+{End}"
On Error Resume Next
If Text1.Text = "" Then Data1.RecordSource = "Select * from Publishers"
Else
Data1.RecordSource = "Select * from Publishers where Address like & "
Data1.Recordset.MoveNext
End If
End Select
Data1.Refresh
MSFlexGrid1.Refresh
Label2.Visible = True
End Sub
Private Sub Text1_KeyPress(KeyAscii As Integer)
Select Case Combo1.Text
Case Is = "Name", "Company Name"
Label2.Caption = "Only Text is Allowed."
Select Case KeyAscii
Case vbKeyDelete
Case vbKeyReturn
Case vbKeyBack
Case vbKeyHome
Case vbKeyEnd
Case vbKeyTab
Case vbKeySpace
Case 65 To 90
Case 97 To 122
Case Else
Beep
KeyAscii = 0
End Select
End Select
Select Case Combo1.Text
Case Is = "ID No", "Zip Code"
Label2.Caption = "Only Number(s) Allowed."
Select Case KeyAscii
Case vbKeyDelete
Case vbKeyReturn
Case vbKeyBack
Case vbKeyHome
Case vbKeyEnd
Case vbKeyTab
Case vbKeySpace
Case 48 To 57
Case Else
Beep
KeyAscii = 0
End Select
End Select
End Sub
No comments have been posted about A good database record finder. Search each record letter by letter and show result in msflexgrid, g. Why not be the first to post a comment about A good database record finder. Search each record letter by letter and show result in msflexgrid, g.
