UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Recordset To Retreive Value, Any Version    
 
   
2ME
post Oct 16 2019, 07:37 AM
Post#1



Posts: 683
Joined: 13-May 10
From: Egypt


I have this code to retrieve value

CODE
Private Sub Plantype_AfterUpdate()
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("ssql")

ssql = " SELECT tbl_sampleplan.sample " & _
" FROM tbl_Plan INNER JOIN tbl_sampleplan ON tbl_Plan.planID = tbl_sampleplan.PlanId " & _
" WHERE (((tbl_sampleplan.num1)<= [forms]![form1].quantity) AND ((tbl_sampleplan.num2)>= [forms]![form1].quantity) AND ((tbl_Plan.planID)= [forms]![form1].Plantype)); "
rs.MoveFirst

Me.sample = rs.Fields("sample")

rs.Close
Set rs = Nothing
End Sub

but i get an error because of Dim db As Database

--------------------

M.E.M
Go to the top of the page
 
arnelgp
post Oct 16 2019, 07:43 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


use

Dim db As DAO.Database

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
2ME
post Oct 16 2019, 07:48 AM
Post#3



Posts: 683
Joined: 13-May 10
From: Egypt


I ended up with the following code

CODE
Private Sub SamplePlan_AfterUpdate()

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Dim ssql As String
Set rs = db.OpenRecordset("ssql")
ssql = " SELECT tbl_sampleplan.sample " & _
" FROM tbl_Plan INNER JOIN tbl_sampleplan ON tbl_Plan.planID = tbl_sampleplan.PlanId " & _
" WHERE (((tbl_sampleplan.num1)<= [forms]![add quality Report].quantity) AND ((tbl_sampleplan.num2)>= [forms]![add quality Report].quantity) AND ((tbl_Plan.planID)= [forms]![add quality Report].sampleplan)); "
Debug.Print ssql
rs.MoveFirst
Me.sample = rs.Fields("sample")
rs.close
Set rs = Nothing
End Sub


and I get run time error 3078 can't find the input table or query ssql

--------------------

M.E.M
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 07:52 AM
Post#4


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


You try to open the recordset BEFORE you define the SQL for that recordset. That ain't gonna work.

Change the sequence.
This post has been edited by GroverParkGeorge: Oct 16 2019, 07:52 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Oct 16 2019, 07:58 AM
Post#5



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


CODE
Private Sub SamplePlan_AfterUpdate()

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim ssql As String

Set db = CurrentDb
ssql = " SELECT tbl_sampleplan.sample " & _
" FROM tbl_Plan INNER JOIN tbl_sampleplan ON tbl_Plan.planID = tbl_sampleplan.PlanId " & _
" WHERE (((tbl_sampleplan.num1)<= [forms]![add quality Report].quantity) AND ((tbl_sampleplan.num2)>= [forms]![add quality Report].quantity) AND ((tbl_Plan.planID)= [forms]![add quality Report].sampleplan)); "
Debug.Print ssql
Set rs = db.OpenRecordset(ssql)
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Me.sample = rs.Fields("sample")
End If
rs.close
Set rs = Nothing
Set db = Nothing
End Sub

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
2ME
post Oct 16 2019, 07:59 AM
Post#6



Posts: 683
Joined: 13-May 10
From: Egypt


I tried

CODE
Private Sub SamplePlan_AfterUpdate()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Dim ssql As String

ssql = " SELECT tbl_sampleplan.sample " & _
" FROM tbl_Plan INNER JOIN tbl_sampleplan ON tbl_Plan.planID = tbl_sampleplan.PlanId " & _
" WHERE (((tbl_sampleplan.num1)<= [forms]![add quality Report].quantity) AND ((tbl_sampleplan.num2)>= [forms]![add quality Report].quantity) AND ((tbl_Plan.planID)= [forms]![add quality Report].sampleplan)); "

Set rs = db.OpenRecordset("ssql")
rs.MoveFirst
Me.sample = rs.Fields("sample")
rs.close
Set rs = Nothing
End Sub

but it doesn't seem to work and I get the same error

--------------------

M.E.M
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 08:05 AM
Post#7


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


We're getting closer. However, I overlooked that you are using a forms reference in the SQL string. You can't do it that way in VBA. It needs to be concatenated into the string along these lines. You'll need to tweak this a bit to make sure I got all the quotes and ampersands in the right spots.

" WHERE (((tbl_sampleplan.num1)<= " & [forms]![add quality Report].quantity & ") AND ((tbl_sampleplan.num2)>= " & [forms]![add quality Report].quantity & ") AND ((tbl_Plan.planID)= " & [forms]![add quality Report].sampleplan & ")); "

Also, if any of those controls are strings, not numbers, you'll need to add the appropriate string delimiters as well. I'm looking at "Sampleplan" for example.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
2ME
post Oct 16 2019, 08:06 AM
Post#8



Posts: 683
Joined: 13-May 10
From: Egypt


arnelgp Thanks for your help
I used your code but I get run time error 3061 two few parameter Expected 2
because of this line set rs = db.OpenRecordset(ssql)

--------------------

M.E.M
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 08:07 AM
Post#9


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


See my response above

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
2ME
post Oct 16 2019, 08:14 AM
Post#10



Posts: 683
Joined: 13-May 10
From: Egypt


GroverParkGeorge you are great
thank you very much
I got it and it works

--------------------

M.E.M
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 08:21 AM
Post#11


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


Arnel and I are happy to help.

Continued success with your project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Oct 16 2019, 04:03 PM
Post#12


UtterAccess VIP
Posts: 11,663
Joined: 6-December 03
From: Telegraph Hill


CODE
' ...
Set rs = db.OpenRecordset("ssql")
' ...


You are passing a literal string, and not the variable. Try:
CODE
' ...
Set rs = db.OpenRecordset(ssql)
' ...



hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
2ME
post Oct 17 2019, 08:26 AM
Post#13



Posts: 683
Joined: 13-May 10
From: Egypt


cheekybuddha thank you very much for your help

--------------------

M.E.M
Go to the top of the page
 
cheekybuddha
post Oct 17 2019, 08:52 AM
Post#14


UtterAccess VIP
Posts: 11,663
Joined: 6-December 03
From: Telegraph Hill


yw.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th November 2019 - 12:18 AM