Solved

Converting VBA Search Form Query To .NET

Posted on 2009-04-09
6
149 Views
Last Modified: 2013-11-26
Hello,

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 & "'"

        Else

            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_Open_Click:

    Exit Sub

 

Err_Open_Click:

    MsgBox Err.Description

    Resume Exit_Open_Click

Open in new window

0
Comment
Question by:c9k9h
  • 3
  • 2
6 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
Comment Utility
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, ...?
0
 

Author Comment

by:c9k9h
Comment Utility
Okay,

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
        conn.Open()
        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"
        conn.Close()


    End Sub
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
1) ASP.NET or WinForms?

2) 1.1., 2.0, 3.5?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:c9k9h
Comment Utility
VB.NET

I'm trying to use a list view property



0
 

Author Closing Comment

by:c9k9h
Comment Utility
Answered On my own - Thanks!
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Lucky TheLearnedOne!!!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now