|
 |
access thread: Need Piece of Code Badly
Message #1 by "ivan matema" <ivanmatema@m...> on Wed, 30 Oct 2002 13:43:46
|
|
i want to compare 2 textbox entries with 2 values in 2 different fields of
a recordset. A sort of authenticating process, like logging into a system.
Ive only done this in VB and its not quite working for me in access.
HELLLLLPPPPPPPPPPPPPPP!!!!!!!!!!!!!!!!ok, so maybe thats a bit 2
much....Help!!!!Soonest
Message #2 by "Carnley, Dave" <dcarnley@a...> on Wed, 30 Oct 2002 09:04:42 -0600
|
|
if me.txt1 = rs!field1 and me.txt2 = rs!field2 then
...
how's that?
-----Original Message-----
From: ivan matema [mailto:ivanmatema@m...]
Sent: Wednesday, October 30, 2002 7:44 AM
To: Access
Subject: [access] Need Piece of Code Badly
i want to compare 2 textbox entries with 2 values in 2 different fields of
a recordset. A sort of authenticating process, like logging into a system.
Ive only done this in VB and its not quite working for me in access.
HELLLLLPPPPPPPPPPPPPPP!!!!!!!!!!!!!!!!ok, so maybe thats a bit 2
much....Help!!!!Soonest
Message #3 by "Bob Bedell" <bobbedell15@m...> on Wed, 30 Oct 2002 15:26:31 +0000
|
|
Hi Ivan,
I have a form that uses as multiple-parameter search as a gateway to a
client record data entry database. The "authentication", (i.e. search
for clients that already exist in the database) involves four
parameters and searches for an exact match on all four parameters.
(I'm still working on the near match functionality). The code uses
ADO Parameter objects to build the record set.
First, create a saved query to recieve the parameters from the text
boxes on your form. My query is named "qryClientSearch". The criteria
parameters are:
[Forms]![frmSearch]![txtLastName]
[Forms]![frmSearch]![txtFirstName]
[Forms]![frmSearch]![txtSSN]
[Forms]![frmSearch]![txtDoB]
I fill the 4 texboxes on frmSearch with values and click cmdSearch. The
following code executes:
'~~~~~~~~~~Start~~~~~~~~~~
Public Sub SearchParameters()
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim cat As ADOX.Catalog
Dim prc As ADOX.Procedure
Dim ctl As Control
Set cat = New ADOX.Catalog
' Retrieve the catalog info for the query
cat.ActiveConnection = CurrentProject.Connection
Set prc = cat.Procedures("qryClientSearch")
' Get a command object from the Procedure
Set cmd = prc.Command
' Loop through the parameters
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm
' And populate the recordset
Set rst = cmd.Execute
Do Until rst.EOF
'Debug.Print rst.Fields(0).Value
rst.MoveNext
Loop
Forms!frmSearch!lstExactMatch.RowSource = "qryClientSearch"
If Forms!frmSearch!lstExactMatch.ListCount = 0 Then
Forms!frmSearch!lstExactMatch.Visible = False
Forms!frmSearch!lblNoMatches.Visible = True
For Each ctl In Forms!frmSearch.Controls
If ctl.ControlType = acTextBox Then
ctl = Null
ctl.Enabled = False
ctl.BackColor = -2147483633
End If
Next
End If
rst.Close
Set rst = Nothing
Set prc = Nothing
Set cmd = Nothing
Set cat = Nothing
End Sub
The executed "qryClientSearch" is assigned as the rowsorce of a listbox
on frmSearch. Any matches that are found display in the list box.
Double-clicking an entry in listbox open a form displaying the clients
complete record.
Best,
Bob
>From: "ivan matema" <ivanmatema@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Need Piece of Code Badly
>Date: Wed, 30 Oct 2002 13:43:46
>
>i want to compare 2 textbox entries with 2 values in 2 different fields of
>a recordset. A sort of authenticating process, like logging into a system.
>
>Ive only done this in VB and its not quite working for me in access.
>HELLLLLPPPPPPPPPPPPPPP!!!!!!!!!!!!!!!!ok, so maybe thats a bit 2
>much....Help!!!!Soonest
_________________________________________________________________
Get faster connections -- switch to MSN Internet Access!
http://resourcecenter.msn.com/access/plans/default.asp
Message #4 by "Bob Bedell" <bobbedell15@m...> on Wed, 30 Oct 2002 15:34:41 +0000
|
|
Hi Ivan,
The code behind cmdSearch calls the SearchParameters procedure which is
in its own standard module.
This code is a handy way for me to display mutilpe records if multiple
matches occur (e.g., clients with more than one client ID number).
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Need Piece of Code Badly
>Date: Wed, 30 Oct 2002 15:26:31 +0000
>
>Hi Ivan,
>
>I have a form that uses as multiple-parameter search as a gateway to a
>client record data entry database. The "authentication", (i.e. search
>for clients that already exist in the database) involves four
>parameters and searches for an exact match on all four parameters.
>(I'm still working on the near match functionality). The code uses
>ADO Parameter objects to build the record set.
>
>First, create a saved query to recieve the parameters from the text
>boxes on your form. My query is named "qryClientSearch". The criteria
>parameters are:
>
>[Forms]![frmSearch]![txtLastName]
>[Forms]![frmSearch]![txtFirstName]
>[Forms]![frmSearch]![txtSSN]
>[Forms]![frmSearch]![txtDoB]
>
>I fill the 4 texboxes on frmSearch with values and click cmdSearch. The
>following code executes:
>
>'~~~~~~~~~~Start~~~~~~~~~~
>
>Public Sub SearchParameters()
>
> Dim cmd As ADODB.Command
> Dim rst As ADODB.Recordset
> Dim prm As ADODB.Parameter
> Dim cat As ADOX.Catalog
> Dim prc As ADOX.Procedure
> Dim ctl As Control
>
> Set cat = New ADOX.Catalog
>
> ' Retrieve the catalog info for the query
> cat.ActiveConnection = CurrentProject.Connection
> Set prc = cat.Procedures("qryClientSearch")
>
> ' Get a command object from the Procedure
> Set cmd = prc.Command
>
> ' Loop through the parameters
> For Each prm In cmd.Parameters
> prm.Value = Eval(prm.Name)
> Next prm
>
> ' And populate the recordset
> Set rst = cmd.Execute
> Do Until rst.EOF
> 'Debug.Print rst.Fields(0).Value
> rst.MoveNext
> Loop
>
> Forms!frmSearch!lstExactMatch.RowSource = "qryClientSearch"
>
> If Forms!frmSearch!lstExactMatch.ListCount = 0 Then
> Forms!frmSearch!lstExactMatch.Visible = False
> Forms!frmSearch!lblNoMatches.Visible = True
>
> For Each ctl In Forms!frmSearch.Controls
> If ctl.ControlType = acTextBox Then
> ctl = Null
> ctl.Enabled = False
> ctl.BackColor = -2147483633
> End If
> Next
>
> End If
>
> rst.Close
> Set rst = Nothing
> Set prc = Nothing
> Set cmd = Nothing
> Set cat = Nothing
>End Sub
>
>The executed "qryClientSearch" is assigned as the rowsorce of a listbox
>on frmSearch. Any matches that are found display in the list box.
>Double-clicking an entry in listbox open a form displaying the clients
>complete record.
>
>Best,
>
>Bob
>
>>From: "ivan matema" <ivanmatema@m...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Need Piece of Code Badly
>>Date: Wed, 30 Oct 2002 13:43:46
>>
>>i want to compare 2 textbox entries with 2 values in 2 different fields of
>>a recordset. A sort of authenticating process, like logging into a system.
>>
>>Ive only done this in VB and its not quite working for me in access.
>>HELLLLLPPPPPPPPPPPPPPP!!!!!!!!!!!!!!!!ok, so maybe thats a bit 2
>>much....Help!!!!Soonest
>
>
>_________________________________________________________________
>Get faster connections -- switch to MSN Internet Access!
>http://resourcecenter.msn.com/access/plans/default.asp
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
Get faster connections -- switch to MSN Internet Access!
http://resourcecenter.msn.com/access/plans/default.asp
|
|
|
View Cart
