"jonefer" wrote:

> I have a prototype which I created in MS Access
> It is a fairly simple model, and I would like to take advantage of
> VB.NET,ADO.NET and ASP.NET to construct the same model.
>
> The search page has only 4 field parameters and I use 2 Tabs, the first, to
> enter the parameters and the 2nd to show a listbox of narrowed results, which
> the user can select from to get the final details of a member:
>
> Name_ID, SSN, DOB, SEX
>
> I have a generic query called qMemberSelect
> Which is fashioned by the combination of fields the user selects
>
> After this is constructed, a 2nd query filters the list even further for the
> Listbox
>
> I guess my main concern is, should I replace the qMemberSelect query with a
> DataReader? Or a DataSet?
>
> Below is a brief snipit of how I setup the qMemberSelect to work.
>
>
> Private Sub cmdFindMember_Click()
> Dim strQry As String
> Dim qdf As DAO.QueryDef
> Dim db As DAO.Database
> Dim sSearchChoice As String
> Dim sCriteriaDesc As String
> Dim lResultCount As Integer
>
> 'Logging Variables
> Dim NUID As String
> Dim dtNow As Date
> Dim sAction As String
> Dim sOutcome As String
> Dim TimeEnd As Date
> Dim sScreen As String
> Dim sSearchingFor As String
>
>
> NUID = GetUserNUID
> dtNow = Now
> sScreen = "Search Screen"
>
>
> strQry = ""
> strQry = "SELECT * FROM MEMBERSHIP "
>
>
>
> If IsNull(Me.txtNAME_ID) And IsNull(Me.txt_SSN) And IsNull(Me.txt_DOB) And
> IsNull(Me.txt_Sex) Then
> MsgBox "Please enter data in at least one field.", vbInformation, "No
> data to search"
> Me.txtNAME_ID.SetFocus
> Exit Sub
> End If
>
>
> 'Name/ID only
> If Not IsNull(Me.txtNAME_ID) And IsNull(Me.txt_SSN) And IsNull(Me.txt_DOB)
> And IsNull(Me.txt_Sex) Then
> If IsNumeric(Me.txtNAME_ID) Then
> strQry = strQry & "WHERE [MRN] = " & Me.txtNAME_ID & ";"
>
> sCriteriaDesc = "MRN: '" & Me.txtNAME_ID & "'"
> Else
> strQry = strQry & "WHERE [MemName] like '" & Me.txtNAME_ID & "*';"
>
> End If
> End If
>
> 'Name/ID and SSN
> If Not IsNull(Me.txtNAME_ID) And Not IsNull(Me.txt_SSN) And
> IsNull(Me.txt_DOB) And IsNull(Me.txt_Sex) Then
> If IsNumeric(Me.txtNAME_ID) Then
> strQry = strQry & "WHERE [MRN] = " & Me.txtNAME_ID & " AND [SSN]
> like '" & Me.txt_SSN & "*';"
>
> sCriteriaDesc = "MRN: '" & Me.txtNAME_ID & "'"
> Else
> strQry = strQry & "WHERE [MemName] like '" & Me.txtNAME_ID & "*' AND
> [SSN] like '" & Me.txt_SSN & "*';"
>
> sCriteriaDesc = "Member Full Name beginning with: '" & Me.txtNAME_ID
> & "'"
>
> End If
>
> ‘.... and so on until
>
> Set db = CurrentDb
> Set qdf = db.QueryDefs("qMembershipSelect")
> qdf.SQL = strQry
>
> db.QueryDefs.Refresh
>
> '--------====================
> The 2nd query for the listbox results is as follows:
> SELECT DISTINCT MRN,
> MemNAME AS Member,
> DOB,
> SEX,
> SSN,
> GROUP,
> SGR, [FROM-DT], [THRU-DT]
> FROM qMembershipSelect AS Y
> GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
> HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
> qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
> From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
> ORDER BY MemNAME, [FROM-DT] DESC;
>
>
>