|
 |
access thread: Re: ADO help plz!!
Message #1 by "Bob Bedell" <bdbedell@m...> on Fri, 31 Aug 2001 21:48:13 -0400
|
|
Hi Jon,
Here?s one solution from an ADO driven database search I put together just
to learn the stuff. Use the Find method. Bear in mind my recordset variable
is at module level (mrst.Clients) and that I?m searching for ClientLastName
instead of ClientID (shame on me) but the rest should work, baring
connection problems. Ignore all the gibberish about enabling and disabling
various command buttons. That?s kind?a specific to my UI. But I?m using the
InputBox method to do what you?re trying to do in conjunction with the
Message Box method. But it?s ADO all the way.
Private Sub cmdFind_Click()
Dim strClientLastName As String
strClientLastName = InputBox("Enter Last Name of Client You Want to
Locate")
' Execute Find method to locate record
mrstClients.Find "ClientLastName = '" & strClientLastName & "'",
Start:=1
' Client record not found
If mrstClients.EOF Then
MsgBox "Last Name " & UCase(strClientLastName) & " Not Found!"
mrstClients.MoveFirst
Exit Sub
End If
' Check for first record
mrstClients.MovePrevious
'Debug.Print mrstClients!ClientLastName
If mrstClients.BOF Then
cmdFirst.Enabled = False
cmdPrevious.Enabled = False
cmdNext.Enabled = True
cmdLast.Enabled = True
mrstClients.MoveFirst
DisplayRecords
Exit Sub
Else
mrstClients.MoveNext
mrstClients.MoveNext
End If
'Check for last record
If mrstClients.EOF Then
cmdFirst.Enabled = True
cmdPrevious.Enabled = True
cmdNext.Enabled = False
cmdLast.Enabled = False
mrstClients.MoveLast
Else
mrstClients.MovePrevious
cmdFirst.Enabled = True
cmdPrevious.Enabled = True
cmdNext.Enabled = True
cmdLast.Enabled = True
End If
DisplayRecords
End Sub
>From: "Jon" <jsaam@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] ADO help plz!!
>Date: Thu, 30 Aug 2001 23:06:21
>
>I'm experience a lapse in memory here -
>
>Can someone explain how to use ADO to search a database field for a
>specific value -- and if the value is not found, pop a msgbox up.
>(specifically, I'm have a user enter in their client ID, and then I want
>the CODE to check the database to make sure it's valid--so I'm pulling the
>value I want to search for from the text box on that form)
>
>Thanks in advance!
Message #2 by "Bob Bedell" <bdbedell@m...> on Fri, 31 Aug 2001 22:06:51 -0400
|
|
Hi Jon,
After thought (always seems to be one). I know what you're thinking. It's
the DispalyRecords call, isn't it. And you're thinking "What the (bleep)is
that?" Well it follows, for better or worse. Major drawback of undefined
fields in ADO is the need to explicitly refresh them through endless
function calls. Makes adding aditional fields a major memory headache. Now
where was I? But here is DisplayRecords:
Private Sub DisplayRecords()
txtCaseNumber = mrstClients!CaseNumber
txtUCINumber = mrstClients!UCINumber
txtClientLastName = mrstClients!ClientLastName
txtClientFirstName = mrstClients!ClientFirstName
txtDOB = mrstClients!DOB
txtSSN = mrstClients!SSN
cboCounselor = mrstClients!CounselorID
txtDOA = mrstClients!DOA
cboReferralSource = mrstClients!ReferralSourceID
cboClientType = mrstClients!ClientTypeID
cboProgram = mrstClients!ProgramID
cboLocation = mrstClients!LocationID
txtDOD = mrstClients!DoD
cboDischargeStatus = mrstClients!DischargeStatusID
cboCounty = mrstClients!CountyID
cboDiagnosis = mrstClients!DiagnosisID
cboEducation = mrstClients!EducationID
cboEmployment = mrstClients!EmploymentID
cboEthnicity = mrstClients!EthnicityID
cboMaritalStatus = mrstClients!MaritalStatusID
cboRace = mrstClients!RaceID
cboResidence = mrstClients!ResidenceID
txtPriorTxEpisodes = mrstClients!PriorTxEpisodes
txtChildren = mrstClients!Children
grpGender = mrstClients!GenderID
grpPregnant = mrstClients!PregnantID
grpPsychHx = mrstClients!PriorPsychHxID
End Sub
Just love ADO.
>From: "Jon" <jsaam@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] ADO help plz!!
>Date: Thu, 30 Aug 2001 23:06:21
>
>I'm experience a lapse in memory here -
>
>Can someone explain how to use ADO to search a database field for a
>specific value -- and if the value is not found, pop a msgbox up.
>(specifically, I'm have a user enter in their client ID, and then I want
>the CODE to check the database to make sure it's valid--so I'm pulling the
>value I want to search for from the text box on that form)
>
>Thanks in advance!
Message #3 by jsaam@m... on Tue, 4 Sep 2001 08:21:36 -0800
|
|
lol kewl!
I actually got it working -- unfortunately, I'm using a component that
visual studio has installed, and I can't use the database elsewhere
<doh> --
but atleast it works here and I have an idea on how to do it. I can
forward
you the code if you'd like -- I think it's pretty straight forward.
-----Original Message-----
From: Bob Bedell [mailto:bdbedell@m...]
Sent: Friday, August 31, 2001 5:48 PM
To: Access
Subject: [access] Re: ADO help plz!!
Hi Jon,
Here=92s one solution from an ADO driven database search I put together
just
to learn the stuff. Use the Find method. Bear in mind my recordset
variable
is at module level (mrst.Clients) and that I=92m searching for
ClientLastName
instead of ClientID (shame on me) but the rest should work, baring
connection problems. Ignore all the gibberish about enabling and
disabling
various command buttons. That=92s kind=92a specific to my UI. But I=92m
using the
InputBox method to do what you=92re trying to do in conjunction with
the
Message Box method. But it=92s ADO all the way.
Private Sub cmdFind_Click()
Dim strClientLastName As String
strClientLastName =3D InputBox("Enter Last Name of Client You Want
to
Locate")
' Execute Find method to locate record
mrstClients.Find "ClientLastName =3D '" & strClientLastName & "'",
Start:=3D1
' Client record not found
If mrstClients.EOF Then
MsgBox "Last Name " & UCase(strClientLastName) & " Not Found!"
mrstClients.MoveFirst
Exit Sub
End If
' Check for first record
mrstClients.MovePrevious
'Debug.Print mrstClients!ClientLastName
If mrstClients.BOF Then
cmdFirst.Enabled =3D False
cmdPrevious.Enabled =3D False
cmdNext.Enabled =3D True
cmdLast.Enabled =3D True
mrstClients.MoveFirst
DisplayRecords
Exit Sub
Else
mrstClients.MoveNext
mrstClients.MoveNext
End If
'Check for last record
If mrstClients.EOF Then
cmdFirst.Enabled =3D True
cmdPrevious.Enabled =3D True
cmdNext.Enabled =3D False
cmdLast.Enabled =3D False
mrstClients.MoveLast
Else
mrstClients.MovePrevious
cmdFirst.Enabled =3D True
cmdPrevious.Enabled =3D True
cmdNext.Enabled =3D True
cmdLast.Enabled =3D True
End If
DisplayRecords
End Sub
>From: "Jon" <jsaam@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] ADO help plz!!
>Date: Thu, 30 Aug 2001 23:06:21
>
>I'm experience a lapse in memory here -
>
>Can someone explain how to use ADO to search a database field for a
>specific value -- and if the value is not found, pop a msgbox up.
>(specifically, I'm have a user enter in their client ID, and then I
want
>the CODE to check the database to make sure it's valid--so I'm pulling
the
>value I want to search for from the text box on that form)
>
>Thanks in advance!
|
|
|
View Cart
