By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,374 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

gettting variable from query

P: n/a
Dee
I have a datasheet subform listing all territories by territory ID#.

One of the fields is "Dealer_Id"

I want to enter a Dealer Id number into the "Dealer_Id" field, then
run an AfterUpdate Event procedure to check if this Dealer is a valid
"assigned dealer" or a potential (not yet assigned a dealership)
dealer.

I already have the query which selects the dealer based on his dealer
id field, using "[Forms]![FmDealer5]![TerritoriesDescription].[Form]![DealerID]"
as criteria. The query works fine and return Dlr_assigned -1 or 0
respectively.

My problem is reading the "Dlr_assigned" field result from the query.

Private Sub DealerID_AfterUpdate()

DoCmd.OpenQuery "QrCheckDlrAssigned"

If Query!QrCheckDlrAssigned!Dlr_assigned = 0 Then (this line is
wrong)
Me.Terassigned = 0
Else: Me.Terassigned = -1
End If

End Sub

I'm obviously getting error messages.

Could someone correct this?
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to open a recordset to read the results of a query. I'm
assuming your query only returns one column and one row. Here are my
changes to your procedure:

Private Sub DealerID_AfterUpdate()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("QrCheckDlrAssigned")
Set rs = qd.OpenRecordset()

If Not rs.EOF Then
Me!Terassigned = rs!Dlr_assigned
Else
' Something happens when query returned Nothing ?
End If

On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing

End Sub

You'll need to put in Error handling.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP/zODIechKqOuFEgEQIn2ACbB6qa5nK4Y83sGFDLZe0cRoFVCmAA oNko
A4/A+ch6PKKGGH6mvOr0mo80
=GwCe
-----END PGP SIGNATURE-----
Dee wrote:
I have a datasheet subform listing all territories by territory ID#.

One of the fields is "Dealer_Id"

I want to enter a Dealer Id number into the "Dealer_Id" field, then
run an AfterUpdate Event procedure to check if this Dealer is a valid
"assigned dealer" or a potential (not yet assigned a dealership)
dealer.

I already have the query which selects the dealer based on his dealer
id field, using "[Forms]![FmDealer5]![TerritoriesDescription].[Form]![DealerID]"
as criteria. The query works fine and return Dlr_assigned -1 or 0
respectively.

My problem is reading the "Dlr_assigned" field result from the query.

Private Sub DealerID_AfterUpdate()

DoCmd.OpenQuery "QrCheckDlrAssigned"

If Query!QrCheckDlrAssigned!Dlr_assigned = 0 Then (this line is
wrong)
Me.Terassigned = 0
Else: Me.Terassigned = -1
End If

End Sub

I'm obviously getting error messages.

Could someone correct this?


Nov 12 '05 #2

P: n/a
So you mean you're just checking for the existence of some value in a
table? Why not use DCount? Then you could do something like:

If DCount(....)>0 Then
'record exists
'Call YesRoutine
Else
'record does not exist
'Call NoRoutine
End If

Otherwise, you have to open a recordset based on the query and then do
a recordcount...
Nov 12 '05 #3

P: n/a
Thanks but it does not work.

I'm getting a copiled error:

"User-defined type not defined"
and "db As DAO.Database" highlighted.

Yes my query only returns one column and one row. Field name of query is
"Dlr assigned" and contains either a -1 or a 0.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This means you do not have the DAO reference checked in your
References. If you are not using an ADP - open a VBA module and, on
the main menu bar, click Tools > References. Scroll down the list of
drivers/programs and select the DAO reference. It may be listed as
Microsoft DAO (version numbers) or as Microsoft Data Access Objects
(version numbers).

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP/7g0oechKqOuFEgEQL24wCgqO3SS3RnSrtDNeg2uMOLA7srJgUA oNDl
zh4+AWdqo4D01zNY2kU+YO9K
=ilWs
-----END PGP SIGNATURE-----
Dee Simon wrote:
Thanks but it does not work.

I'm getting a copiled error:

"User-defined type not defined"
and "db As DAO.Database" highlighted.

Yes my query only returns one column and one row. Field name of query is
"Dlr assigned" and contains either a -1 or a 0.


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.