|
 |
access thread: Select Value from another form using DAO
Message #1 by Christopher Mohr <CMohr@b...> on Thu, 24 Jan 2002 08:14:13 -0800
|
|
Here is my code
Private Sub Form_Open(Cancel As Integer)
Dim db As Database
Dim rsCompany As Recordset
Dim intNumber As Integer
Set db = CurrentDb()
Set rsCompany = db.OpenRecordset _
("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
" WHERE CompanyID = Forms!Company!CompanyName", _
" ORDER BY ContactName ASC", _
dbOpenDynaset)
End Sub
Christopher Mohr
Sales/Business Development
Bay Advanced Technologies, LLC
Fabricated Materials Division
(xxx) xxx-xxxx
(xxx) xxx-xxxx Fax
cmohr@b...
Message #2 by "Ian Ashton" <ian@c...> on Fri, 25 Jan 2002 07:28:23 -0000
|
|
Christopher,
1) You need to take the "Forms!Company!CompanyName" outside the quotes:
2) There should not be a comma after the WHERE Clause
3) Either the Field Names or the "*" in the first line are redundant - use
one or the other.)
4) As a suggestion only: Specify "DAO" as the type of Database and
Recordset. This will ease the transfer to other versions of access which
may, by default, have a reference to ADO and none to DAO unless you add it
in.
Suggested Code Snippet:
Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rsCompany As DAO.Recordset
Dim intNumber As Integer
Set rsCompany = db.OpenRecordset _
("SELECT CompanyID, ContactID, ContactName FROM Contacts" & _
" WHERE CompanyID = " & _
Chr$(34) & Forms!Company!CompanyName & Chr$(34) _
" ORDER BY ContactName ASC", _
dbOpenDynaset)
End sub
The Chr$(34) is to put in a double quote. If you use a single quote
Character -'-, the code would fail if the Company name already had a single
quote as part of it. e.g. "O'Malley & Co"
I hope that this helps,
Ian Ashton
----------------------------------------------------------------------------
-----
Your Code:
Private Sub Form_Open(Cancel As Integer)
Dim db As Database
Dim rsCompany As Recordset
Dim intNumber As Integer
Set db = CurrentDb()
Set rsCompany = db.OpenRecordset _
("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
" WHERE CompanyID = Forms!Company!CompanyName", _
" ORDER BY ContactName ASC", _
dbOpenDynaset)
End Sub
-----Original Message-----
From: Christopher Mohr [mailto:CMohr@b...]
Sent: Thursday, January 24, 2002 4:14 PM
To: Access
Subject: [access] Select Value from another form using DAO
Here is my code
Private Sub Form_Open(Cancel As Integer)
Dim db As Database
Dim rsCompany As Recordset
Dim intNumber As Integer
Set db = CurrentDb()
Set rsCompany = db.OpenRecordset _
("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
" WHERE CompanyID = Forms!Company!CompanyName", _
" ORDER BY ContactName ASC", _
dbOpenDynaset)
End Sub
Christopher Mohr
Sales/Business Development
Bay Advanced Technologies, LLC
Fabricated Materials Division
(xxx) xxx-xxxx
(xxx) xxx-xxxx Fax
cmohr@b...
Message #3 by "Christopher Mohr" <cmohr@b...> on Fri, 25 Jan 2002 22:22:47
|
|
Ian,
> Christopher,
>
> 1) You need to take the "Forms!Company!CompanyName" outside the quotes:
> 2) There should not be a comma after the WHERE Clause
> 3) Either the Field Names or the "*" in the first line are redundant -
use
> one or the other.)
> 4) As a suggestion only: Specify "DAO" as the type of Database and
> Recordset. This will ease the transfer to other versions of access which
> may, by default, have a reference to ADO and none to DAO unless you add
it
> in.
>
> Suggested Code Snippet:
>
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As DAO.Database
> Dim rsCompany As DAO.Recordset
> Dim intNumber As Integer
>
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName FROM Contacts" & _
> " WHERE CompanyID = " & _
> Chr$(34) & Forms!Company!CompanyName & Chr$(34) _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End sub
>
> The Chr$(34) is to put in a double quote. If you use a single quote
> Character -'-, the code would fail if the Company name already had a
single
> quote as part of it. e.g. "O'Malley & Co"
>
>
>
> I hope that this helps,
>
> Ian Ashton
>
> -------------------------------------------------------------------------
---
> -----
> Your Code:
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As Database
> Dim rsCompany As Recordset
> Dim intNumber As Integer
>
>
> Set db = CurrentDb()
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
> " WHERE CompanyID = Forms!Company!CompanyName", _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End Sub
>
> -----Original Message-----
> From: Christopher Mohr [mailto:CMohr@b...]
> Sent: Thursday, January 24, 2002 4:14 PM
> To: Access
> Subject: [access] Select Value from another form using DAO
>
>
> Here is my code
>
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As Database
> Dim rsCompany As Recordset
> Dim intNumber As Integer
>
>
> Set db = CurrentDb()
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
> " WHERE CompanyID = Forms!Company!CompanyName", _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End Sub
>
> Christopher Mohr
> Sales/Business Development
> Bay Advanced Technologies, LLC
> Fabricated Materials Division
> (xxx) xxx-xxxx
> (xxx) xxx-xxxx Fax
> cmohr@b...
>
>
>
Message #4 by "Christopher Mohr" <cmohr@b...> on Fri, 25 Jan 2002 22:25:17
|
|
Ian,
That didn't work, I ever copy and pasted your code. I come up with a
syntax error message
Chris
> Christopher,
>
> 1) You need to take the "Forms!Company!CompanyName" outside the quotes:
> 2) There should not be a comma after the WHERE Clause
> 3) Either the Field Names or the "*" in the first line are redundant -
use
> one or the other.)
> 4) As a suggestion only: Specify "DAO" as the type of Database and
> Recordset. This will ease the transfer to other versions of access which
> may, by default, have a reference to ADO and none to DAO unless you add
it
> in.
>
> Suggested Code Snippet:
>
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As DAO.Database
> Dim rsCompany As DAO.Recordset
> Dim intNumber As Integer
>
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName FROM Contacts" & _
> " WHERE CompanyID = " & _
> Chr$(34) & Forms!Company!CompanyName & Chr$(34) _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End sub
>
> The Chr$(34) is to put in a double quote. If you use a single quote
> Character -'-, the code would fail if the Company name already had a
single
> quote as part of it. e.g. "O'Malley & Co"
>
>
>
> I hope that this helps,
>
> Ian Ashton
>
> -------------------------------------------------------------------------
---
> -----
> Your Code:
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As Database
> Dim rsCompany As Recordset
> Dim intNumber As Integer
>
>
> Set db = CurrentDb()
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
> " WHERE CompanyID = Forms!Company!CompanyName", _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End Sub
>
> -----Original Message-----
> From: Christopher Mohr [mailto:CMohr@b...]
> Sent: Thursday, January 24, 2002 4:14 PM
> To: Access
> Subject: [access] Select Value from another form using DAO
>
>
> Here is my code
>
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As Database
> Dim rsCompany As Recordset
> Dim intNumber As Integer
>
>
> Set db = CurrentDb()
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
> " WHERE CompanyID = Forms!Company!CompanyName", _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End Sub
>
> Christopher Mohr
> Sales/Business Development
> Bay Advanced Technologies, LLC
> Fabricated Materials Division
> (xxx) xxx-xxxx
> (xxx) xxx-xxxx Fax
> cmohr@b...
>
>
>
Message #5 by "John Ruff" <papparuff@c...> on Fri, 25 Jan 2002 15:08:47 -0800
|
|
Chris,
Do this
Dim db As DAO.Database
Dim rsCompany As DAO.Recordset
Dim intNumber As Integer
Dim strSQL as string
strSQL="("SELECT CompanyID, ContactID, ContactName " & _
"FROM Contacts " & _
"WHERE CompanyID = Forms!Company!CompanyName " & _
"ORDER BY ContactName"
Set rsCompany = db.OpenRecordset(strSQL, dbOpenDynaset)
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: Christopher Mohr [mailto:cmohr@b...]
Sent: Friday, January 25, 2002 10:25 PM
To: Access
Subject: [access] RE: Select Value from another form using DAO
Ian,
That didn't work, I ever copy and pasted your code. I come up with
a
syntax error message
Chris
> Christopher,
>
> 1) You need to take the "Forms!Company!CompanyName" outside the
> quotes:
> 2) There should not be a comma after the WHERE Clause
> 3) Either the Field Names or the "*" in the first line are redundant -
use
> one or the other.)
> 4) As a suggestion only: Specify "DAO" as the type of Database and
> Recordset. This will ease the transfer to other versions of access
> which may, by default, have a reference to ADO and none to DAO unless
> you add
it
> in.
>
> Suggested Code Snippet:
>
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As DAO.Database
> Dim rsCompany As DAO.Recordset
> Dim intNumber As Integer
>
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName FROM Contacts" & _
> " WHERE CompanyID = " & _
> Chr$(34) & Forms!Company!CompanyName & Chr$(34) _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End sub
>
> The Chr$(34) is to put in a double quote. If you use a single quote
> Character -'-, the code would fail if the Company name already had a
single
> quote as part of it. e.g. "O'Malley & Co"
>
>
>
> I hope that this helps,
>
> Ian Ashton
>
> ----------------------------------------------------------------------
> ---
---
> -----
> Your Code:
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As Database
> Dim rsCompany As Recordset
> Dim intNumber As Integer
>
>
> Set db = CurrentDb()
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
> " WHERE CompanyID = Forms!Company!CompanyName", _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End Sub
>
> -----Original Message-----
> From: Christopher Mohr [mailto:CMohr@b...]
> Sent: Thursday, January 24, 2002 4:14 PM
> To: Access
> Subject: [access] Select Value from another form using DAO
>
>
> Here is my code
>
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As Database
> Dim rsCompany As Recordset
> Dim intNumber As Integer
>
>
> Set db = CurrentDb()
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
> " WHERE CompanyID = Forms!Company!CompanyName", _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End Sub
>
> Christopher Mohr
> Sales/Business Development
> Bay Advanced Technologies, LLC
> Fabricated Materials Division
> (xxx) xxx-xxxx
> (xxx) xxx-xxxx Fax
> cmohr@b...
>
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to
> unsubscribe send a blank email to $subst('Email.Unsub').
>
Message #6 by "John Ruff" <papparuff@c...> on Fri, 25 Jan 2002 15:11:12 -0800
|
|
Oops, There is one too many " and a ( that is not suppose to be in the
strSQL.,
Do this
Dim db As DAO.Database
Dim rsCompany As DAO.Recordset
Dim intNumber As Integer
Dim strSQL as string
' This is the Oops
strSQL="("SELECT CompanyID, ContactID, ContactName " & _
"FROM Contacts " & _
"WHERE CompanyID = Forms!Company!CompanyName " & _
"ORDER BY ContactName"
' Should be
strSQL="SELECT CompanyID, ContactID, ContactName " & _
"FROM Contacts " & _
"WHERE CompanyID = Forms!Company!CompanyName " & _
"ORDER BY ContactName"
Set rsCompany = db.OpenRecordset(strSQL, dbOpenDynaset)
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: Christopher Mohr [mailto:cmohr@b...]
Sent: Friday, January 25, 2002 10:25 PM
To: Access
Subject: [access] RE: Select Value from another form using DAO
Ian,
That didn't work, I ever copy and pasted your code. I come up with
a
syntax error message
Chris
> Christopher,
>
> 1) You need to take the "Forms!Company!CompanyName" outside the
> quotes:
> 2) There should not be a comma after the WHERE Clause
> 3) Either the Field Names or the "*" in the first line are redundant -
use
> one or the other.)
> 4) As a suggestion only: Specify "DAO" as the type of Database and
> Recordset. This will ease the transfer to other versions of access
> which may, by default, have a reference to ADO and none to DAO unless
> you add
it
> in.
>
> Suggested Code Snippet:
>
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As DAO.Database
> Dim rsCompany As DAO.Recordset
> Dim intNumber As Integer
>
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName FROM Contacts" & _
> " WHERE CompanyID = " & _
> Chr$(34) & Forms!Company!CompanyName & Chr$(34) _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End sub
>
> The Chr$(34) is to put in a double quote. If you use a single quote
> Character -'-, the code would fail if the Company name already had a
single
> quote as part of it. e.g. "O'Malley & Co"
>
>
>
> I hope that this helps,
>
> Ian Ashton
>
> ----------------------------------------------------------------------
> ---
---
> -----
> Your Code:
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As Database
> Dim rsCompany As Recordset
> Dim intNumber As Integer
>
>
> Set db = CurrentDb()
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
> " WHERE CompanyID = Forms!Company!CompanyName", _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End Sub
>
> -----Original Message-----
> From: Christopher Mohr [mailto:CMohr@b...]
> Sent: Thursday, January 24, 2002 4:14 PM
> To: Access
> Subject: [access] Select Value from another form using DAO
>
>
> Here is my code
>
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As Database
> Dim rsCompany As Recordset
> Dim intNumber As Integer
>
>
> Set db = CurrentDb()
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
> " WHERE CompanyID = Forms!Company!CompanyName", _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End Sub
>
> Christopher Mohr
> Sales/Business Development
> Bay Advanced Technologies, LLC
> Fabricated Materials Division
> (xxx) xxx-xxxx
> (xxx) xxx-xxxx Fax
> cmohr@b...
>
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to
> unsubscribe send a blank email to $subst('Email.Unsub').
>
Message #7 by "Ian Ashton" <ian@c...> on Sat, 26 Jan 2002 11:58:59 -0000
|
|
OOPS!
I Left out an ampersand after the second chr$(34)
the code should be:
Set rsCompany = db.OpenRecordset _
("SELECT CompanyID, ContactID, ContactName FROM Contacts" & _
" WHERE CompanyID = " & _
Chr$(34) & Forms!Company!CompanyName & Chr$(34) & _
" ORDER BY ContactName ASC", _
dbOpenDynaset)
-----Original Message-----
From: Christopher Mohr [mailto:cmohr@b...]
Sent: Friday, January 25, 2002 10:25 PM
To: Access
Subject: [access] RE: Select Value from another form using DAO
Ian,
That didn't work, I ever copy and pasted your code. I come up with a
syntax error message
Chris
> Christopher,
>
> 1) You need to take the "Forms!Company!CompanyName" outside the quotes:
> 2) There should not be a comma after the WHERE Clause
> 3) Either the Field Names or the "*" in the first line are redundant -
use
> one or the other.)
> 4) As a suggestion only: Specify "DAO" as the type of Database and
> Recordset. This will ease the transfer to other versions of access which
> may, by default, have a reference to ADO and none to DAO unless you add
it
> in.
>
> Suggested Code Snippet:
>
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As DAO.Database
> Dim rsCompany As DAO.Recordset
> Dim intNumber As Integer
>
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName FROM Contacts" & _
> " WHERE CompanyID = " & _
> Chr$(34) & Forms!Company!CompanyName & Chr$(34) _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End sub
>
> The Chr$(34) is to put in a double quote. If you use a single quote
> Character -'-, the code would fail if the Company name already had a
single
> quote as part of it. e.g. "O'Malley & Co"
>
>
>
> I hope that this helps,
>
> Ian Ashton
>
> -------------------------------------------------------------------------
---
> -----
> Your Code:
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As Database
> Dim rsCompany As Recordset
> Dim intNumber As Integer
>
>
> Set db = CurrentDb()
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
> " WHERE CompanyID = Forms!Company!CompanyName", _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End Sub
>
> -----Original Message-----
> From: Christopher Mohr [mailto:CMohr@b...]
> Sent: Thursday, January 24, 2002 4:14 PM
> To: Access
> Subject: [access] Select Value from another form using DAO
>
>
> Here is my code
>
> Private Sub Form_Open(Cancel As Integer)
>
> Dim db As Database
> Dim rsCompany As Recordset
> Dim intNumber As Integer
>
>
> Set db = CurrentDb()
> Set rsCompany = db.OpenRecordset _
> ("SELECT CompanyID, ContactID, ContactName, * FROM Contacts" & _
> " WHERE CompanyID = Forms!Company!CompanyName", _
> " ORDER BY ContactName ASC", _
> dbOpenDynaset)
>
> End Sub
>
> Christopher Mohr
> Sales/Business Development
> Bay Advanced Technologies, LLC
> Fabricated Materials Division
> (xxx) xxx-xxxx
> (xxx) xxx-xxxx Fax
> cmohr@b...
>
>
>
|
|
|
View Cart
