c9k9h
asked on
Converting VBA Search Form Query To .NET
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!!!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1) ASP.NET or WinForms?
2) 1.1., 2.0, 3.5?
2) 1.1., 2.0, 3.5?
ASKER
VB.NET
I'm trying to use a list view property
I'm trying to use a list view property
ASKER
Answered On my own - Thanks!
Lucky TheLearnedOne!!!
ASKER
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.OL
sSQL = "SELECT PendingID, PendingClaimNum, Last, First, DatePending, Custodian, [Pending].[PendingClaimNum
'sSQL = "SELECT PendingClaimNum, Last, First, DatePending, Custodian FROM Pending WHERE Last Like '" & txtLastName.Text & "'"
Dim conn As New System.Data.OleDb.OleDbCon
Dim cmd As New System.Data.OleDb.OleDbCom
Dim dr As System.Data.OleDb.OleDbDat
conn.Open()
Dim dat As OleDbDataAdapter = New OleDbDataAdapter(sSQL, conn)
Dim dt As New DataSet()
dat.Fill(dt, "Pending")
lstResults.DataSource = dt.Tables("Pending").Defau
lstResults.DisplayMember = "Row"
lstResults.ValueMember = "PendingID"
conn.Close()
End Sub