• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

Converting VBA Search Form Query To .NET


Any help on this would be hugely appreciated. I'm trying to convert an Old Access Database over to VB.NET and it's been a while since using VB.NET The backend is a .MDB (for now).

I have the following code I wish to convert.. All the form items are the same - names etc... I do have an option group on both forms which I would like to still pull the case from - I'm pretty sure this is possible in .NET?

The trickier part as I'm aware of will be when the user clicks the item that populates in the listbox, how to load the form as unlike access, the textboxes do not have control sources.

Thanks so much!!!
***Code to Populate List Box On Search Form***
Dim dB As DAO.Database
Dim rS As DAO.Recordset
Dim sSQL As String
Set dB = CurrentDb
sSQL = ""
sSQL = sSQL & "SELECT [PendingClaimNum], Last, First, DatePending, Custodian, PendingID     "
sSQL = sSQL & "FROM [Pending] "
Select Case Me.fraCriteriaType.Value
    Case 1
        sSQL = sSQL & "WHERE [PendingClaimNum]  = '" & Me.txtClmNo & "'"
    Case 2
        If IsNull(Me.txtFirstName) Then
            sSQL = sSQL & "WHERE Last LIKE '" & Me.txtLastName & "'"
            sSQL = sSQL & "WHERE Last LIKE'" & Me.txtLastName & "' AND First LIKE '" & Me.txtFirstName & "'"
        End If
    Case 3
        sSQL = sSQL & "WHERE DatePending = '" & Me.txtDate & "'"
    Case 4
        sSQL = sSQL & "WHERE Custodian = '" & Me.cboSpecialist.Value & "' " & "And IsNull(DateClosed) " & " And PendingStatus = 'Pending' "
    'Case 5
        'sSQL = sSQL & "WHERE ProvName LIKE '" & Me.txtProvider & "'"
    'Case 6
        'sSQL = sSQL & "WHERE ProvNum LIKE '" & Me.providernum & "'"
End Select
sSQL = sSQL & " ORDER BY [DatePending] ASC"
Me.lstResults.RowSource = sSQL
If Me.lstResults.ListCount = 0 Then
    MsgBox "Your search did not find a match"
end if
end sub
***Code To Open Result***
Dim stDocName As String
Dim stLinkCriteria As String
On Error GoTo Err_Open_Click
stDocName = "Pending"
stLinkCriteria = "[PendingID]=" & Me.lstResults.Column(5)
DoCmd.OpenForm stDocName, , , stLinkCriteria
    Exit Sub
    MsgBox Err.Description
    Resume Exit_Open_Click

Open in new window

  • 3
  • 2
1 Solution
Bob LearnedCommented:
I would love to provide help, but I don't really see a specific question.  Are you looking for a way to use ADO.NET, create form logic, form design, ...?
c9k9hAuthor Commented:

Specifically I'm trying to populate a ListView with multiple columns and then have the end user be able to select a record the query pulled and have it open record in form view.... I've made a bit of progress but I'm having problems with the multiple columns in the listview - datagrid works but i can't see how the user can select a record and open it from this view.

Here is what i have for code:

 Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click

        Dim OptControlArray() As Control = {OptName, OptClaimNo, OptDate, OptCustodian}
        Dim sConnectionString, sSQL As String

        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\diw07\My Documents\Daily Backups\phone memo backend.mdb"
        sSQL = "SELECT PendingID, PendingClaimNum, Last, First, DatePending, Custodian, [Pending].[PendingClaimNum] & ' ' & [Pending].[Last] & ' ' & [Pending].[First] & ' ' & [Pending].[DatePending] & ' ' & [Pending].[Custodian] As Row FROM Pending WHERE Last Like '" & txtLastName.Text & "'"
        'sSQL = "SELECT PendingClaimNum, Last, First, DatePending, Custodian FROM Pending WHERE Last Like '" & txtLastName.Text & "'"

        Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
        Dim cmd As New System.Data.OleDb.OleDbCommand(sSQL, conn)
        Dim dr As System.Data.OleDb.OleDbDataReader
        Dim dat As OleDbDataAdapter = New OleDbDataAdapter(sSQL, conn)
        Dim dt As New DataSet()
        dat.Fill(dt, "Pending")
        lstResults.DataSource = dt.Tables("Pending").DefaultView
        lstResults.DisplayMember = "Row"
        lstResults.ValueMember = "PendingID"

    End Sub
Bob LearnedCommented:
1) ASP.NET or WinForms?

2) 1.1., 2.0, 3.5?
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

c9k9hAuthor Commented:

I'm trying to use a list view property

c9k9hAuthor Commented:
Answered On my own - Thanks!
Lucky TheLearnedOne!!!
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now