Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 31st, 2005, 03:19 PM
Registered User
 
Join Date: Mar 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select From Where Code not working...help

Ultimately what I'm trying to do is after a user enters a last name and first name on my form, the code below is supposed to search the 2 variables to make sure that name doesn't already exist in the table. I'm trying to select on 2 different variables, and my code is not working.
I think it's my Select From statement that is the problem.
Please advise.

Private Sub FNAME_Exit(Cancel As Integer)
Dim cnCurrent As ADODB.Connection
Dim rsPatient As ADODB.Recordset
Dim strSQL As String

On Error GoTo Error_Handler

Set cnCurrent = CurrentProject.Connection
Set rsPatient = New ADODB.Recordset

rsPatient.Open "SELECT * FROM [Patient tbl] WHERE (lname,fname) = '" & _
    Forms!MainFrm!ptsub!LNAME & "," & Forms!MainFrm!ptsub!FNAME & "'", cnCurrent

Do Until rsPatient.EOF
    MsgBox "This patient already exists. Please check to make sure this is not a duplicate patient."
    rsPatient.MoveNext
Loop

rsPatient.Close
cnCurrent.Close
Set rsPatient = Nothing
Set cnCurrent = Nothing

Exit Sub
Error_Handler:
    MsgBox "An error has occurred."
    Exit Sub



End Sub
 
Old March 31st, 2005, 05:00 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

You say it is not working: what does happen, and what fails to happen?

Try:
Code:
    rsPatient.Open "SELECT * " & _
                   "FROM   [Patient tbl] " & _
                   "WHERE  lname = '" & Forms!MainFrm!ptsub!LNAME & "' AND " & _
                   "       fname = '" & Forms!MainFrm!ptsub!FNAME & "'     ", _
                   cnCurrent
 
Old April 1st, 2005, 03:25 PM
Registered User
 
Join Date: Mar 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That works! Thank you.

Quote:
quote:Originally posted by BrianWren
 You say it is not working: what does happen, and what fails to happen?

Try:
Code:
    rsPatient.Open "SELECT * " & _
                   "FROM   [Patient tbl] " & _
                   "WHERE  lname = '" & Forms!MainFrm!ptsub!LNAME & "' AND " & _
                   "       fname = '" & Forms!MainFrm!ptsub!FNAME & "'     ", _
                   cnCurrent





Similar Threads
Thread Thread Starter Forum Replies Last Post
onChange method of <select> in Html not working pushpa Classic ASP Databases 4 January 23rd, 2007 11:20 AM
Select Expert Not Working bclick Crystal Reports 0 October 3rd, 2006 02:14 PM
Code not working well vinish Beginning PHP 2 November 11th, 2005 08:02 PM
Working with code behind - How To pinkuisadear ASP.NET 1.0 and 1.1 Basics 7 March 11th, 2005 01:13 AM
code not working Toka1 Javascript How-To 12 December 17th, 2003 05:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.