data access page search code

  • Thread starter Thread starter KarenG0927
  • Start date Start date
K

KarenG0927

I'm about to go crazy! I'm trying to make a search page for employees without
MS Access. I know VBA well enough, but I can't get the onclick coding to
filter the query. I'm going to put the coding I had on the original form I
converted to DAP. Here's the basics:

Table:

tblMeter_Orders (fields I have on the DAP that need to be searched)
1. Meter_ID (Prim Key, AutoNum (Not searched,needed to edit records)
2. Cty_ID - tblCounty has Cty_ID (Prim Key,AutoNum); Cty_Name (Text,No
Dups)
3. Dev_ID - dbo_tblDevelopment has DevelopmentID (Prim Key,Auto Num);
Development (Text, Dups Allowed)
4. Address (Text, Dups Allowed)
6. Request_Number (Text, Dups Allowed)

Search Set Up:

optSearch
1. County
2. Development
3. Address
4. Request Number

optFilterType *used for optSearch 2-4:
1. Is Like
2. Is Exactly

cboCriteria *used for optSearch 1-2:
RecordSource is tblCounty (optSearch 1) or dbo_tblDevelopment (optSearch 2)

txtCriteria *used for optSearch 3-4

cmdSearch

cmdReset

Code On Original Form:

Private Sub cmdReset_Click()

Me.Filter = Null
Me.FilterOn = False
Me.optSearch.Value = 1
Me.cboCriteria.Visible = True
Me.cboCriteria.Enabled = True
Me.cboCriteria.TabStop = True
Me.txtCriteria.Visible = False
Me.txtCriteria.Enabled = False
Me.txtCriteria.TabStop = False
Me.optFilterType.Value = 2
Me.optFilterType.Enabled = False
Me.cboCriteria = ""
Me.cboCriteria.RowSource = "SELECT tblCounty.Cty_ID, tblCounty.Cty_Name FROM
tblCounty ORDER BY tblCounty.Cty_Name;"

End Sub

Private Sub cmdSearch_Click()

Dim RetVal As String 'Criteria to search for
Dim SerVal As String 'Value to search against
Dim strWhere As String 'The filter to use
Dim strFilterTypeStart As String 'How criteria is filtered using like or
equals
Dim strFilterTypeEnd As String 'How criteria is filtered, the end part
Dim strPrompt As String 'MsgBox prompt to use
Dim strCheckCtl As String 'Control to check for a value before filter is
applied

Select Case Me.optSearch.Value 'Set values for MsgBox and Filter

Case 1:
strCheckCtl = "Me.cboCriteria"
strPrompt = "Please select a county."
SerVal = "Cty_ID"
RetVal = Me.cboCriteria

Case 2:
strCheckCtl = "Me.cboCriteria"
strPrompt = "Please select a development."
SerVal = "DevelopmentID"
RetVal = Me.cboCriteria

Case 3:
strCheckCtl = "Me.txtCriteria"
strPrompt = "Please enter address."
SerVal = "Meter_Address"
RetVal = Me.txtCriteria

Case 4:
strCheckCtl = "Me.txtCriteria"
strPrompt = "Please enter request number."
SerVal = "Request_Number"
RetVal = Me.txtCriteria

End Select

If Me.optSearch.Value <> 1 Then 'Set type of filter (like or equals)
If Me.optFilterType.Value = 1 Then
strFilterTypeStart = " Like '*"
strFilterTypeEnd = "*'"
Else
strFilterTypeStart = " = '"
strFilterTypeEnd = "'"
End If
Else
strFilterTypeStart = " = '"
strFilterTypeEnd = "'"
End If

If IsNull(strCheckCtl) Or Len(strCheckCtl) < 1 Then 'Check for a
search criteria
MsgBox strPrompt, vbOKOnly + vbInformation, "Selection Needed"
'Cancel filter
Exit Sub
Else
strWhere = SerVal & strFilterTypeStart & RetVal &
strFilterTypeEnd 'Make filter
End If

Me.Filter = strWhere
Me.FilterOn = True

End Sub

Private Sub optSearch_AfterUpdate()

Dim strSQL As String

Select Case optSearch.Value

Case 1:
Me.cboCriteria.Visible = True
Me.cboCriteria.Enabled = True
Me.cboCriteria.TabStop = True
Me.txtCriteria.Visible = False
Me.txtCriteria.Enabled = False
Me.txtCriteria.TabStop = False
Me.optFilterType.Value = 2
Me.optFilterType.Enabled = False
Me.cboCriteria = ""
strSQL = "SELECT tblCounty.Cty_ID, tblCounty.Cty_Name FROM
tblCounty ORDER BY tblCounty.Cty_Name;"

Case 2:
Me.cboCriteria.Visible = True
Me.cboCriteria.Enabled = True
Me.cboCriteria.TabStop = True
Me.txtCriteria.Visible = False
Me.txtCriteria.Enabled = False
Me.txtCriteria.TabStop = False
Me.optFilterType.Enabled = True
Me.cboCriteria = ""
strSQL = "SELECT dbo_tblDevelopment.DevelopmentID,
dbo_tblDevelopment.Development FROM dbo_tblDevelopment ORDER BY
dbo_tblDevelopment.Development;"

Case 3:
Me.cboCriteria.Enabled = False
Me.cboCriteria.Visible = False
Me.cboCriteria.TabStop = False
Me.txtCriteria.Enabled = True
Me.txtCriteria.Visible = True
Me.txtCriteria.TabStop = True
Me.optFilterType.Enabled = True
Me.txtCriteria = ""

Case 4:
Me.cboCriteria.Enabled = False
Me.cboCriteria.Visible = False
Me.cboCriteria.TabStop = False
Me.txtCriteria.Enabled = True
Me.txtCriteria.Visible = True
Me.txtCriteria.TabStop = True
Me.optFilterType.Enabled = True
Me.txtCriteria = ""

End Select

Me.cboCriteria.RowSource = strSQL

End Sub


I hope I didn't give to much or too little...lol
So, here's the catches:
-The users can't be search restricted to the data on the page itself
(there's over 10,000 records)
-The results need to be listed in a continious form style, not one at a
time. Too many records can be under a street, development, etc.
-The records need editing abilities
-Development must have the LIKE search ability. I am incorporating the
database our company uses, and the table doesn't not have 'no duplicates' set
up for the text field


Thanks in advance, I REALLY REALLY REALLY appreciate the help!!!!
 
My suggestion: stop bothering yourself with DAP and switch to something else
like ASP.NET.

DAP is a dead technology by now because of the great difficulty of coding
with it and unless you are a wizard at the DOM (Document Object Model) of IE
and Javascript; you cannot do anything advanced with DAP (and even then, you
will be hit in the back by the bugs of DAP more often than a politician).

Believe my personal experience, DAP is a dead end and unless you want to do
only some simple things, you're losing your time with this.
 
Back
Top