Re: data access page search code
- From: "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
- Date: Sat, 15 Nov 2008 14:07:27 -0500
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.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"KarenG0927" <KarenG0927@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:16B45E25-EEA1-4200-AEE8-45E0072102C0@xxxxxxxxxxxxxxxx
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!!!!
.
- References:
- data access page search code
- From: KarenG0927
- data access page search code
- Prev by Date: Creating a Lookup Table Using Do...Until Loop
- Next by Date: Re: Access 2007 and Visual Studio 2008
- Previous by thread: data access page search code
- Next by thread: Check to see if a file is open in word
- Index(es):
Relevant Pages
|
