Solved

Converting VBA Search Form Query To .NET

Posted on 2009-04-09
6
154 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 24114412
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
ID: 24115592
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
ID: 24116441
1) ASP.NET or WinForms?

2) 1.1., 2.0, 3.5?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:c9k9h
ID: 24116494
VB.NET

I'm trying to use a list view property



0
 

Author Closing Comment

by:c9k9h
ID: 31568497
Answered On my own - Thanks!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24118116
Lucky TheLearnedOne!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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 …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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