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
> Retrieve Sample Quantity In A Form, Access 2003    
 
   
2ME
post Oct 15 2019, 03:12 PM
Post#1



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


Hi,

I have a two tables the first table for plan type (PlanID, Plantype) and related to table tbl_sampleplan (Planid, num1, num2, sample)

in a form I have a field named quantity, and field plan type I need a VBA function that when I select the plan type and the quantity falls in the range between num1 and num2 to retrieve the sample

thanks in advance



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

M.E.M
Go to the top of the page
 
arnelgp
post Oct 15 2019, 03:23 PM
Post#2



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


you need a query:

"select tbl_sampleplan.planid, num1, num2, sample from tbl_sampleplan " & _
"inner join [plan type] on tbl_sampleplan.planid = [plan type].planid " & _
"where [plan type].plantype = '" & [txtPlanType] & "' And " & _
[txtNum] & " between tbl_sampleplan.num1 and tbl_sampleplan.num2;"
This post has been edited by arnelgp: Oct 15 2019, 03:24 PM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
2ME
post Oct 15 2019, 03:44 PM
Post#3



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


there is a form in the attached file

I need when I select the Plan type and the quantity to retrieve the sample size


Attached File(s)
Attached File  New_Microsoft_Access_Database.zip ( 49.02K )Number of downloads: 6
 

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

M.E.M
Go to the top of the page
 
MadPiet
post Oct 15 2019, 04:11 PM
Post#4



Posts: 3,361
Joined: 27-February 09



Why not just use a regular query and point the parameters/filters at the open form? Way easier. The basic syntax is

=Forms![FormName]![ControlName]
Go to the top of the page
 
2ME
post Oct 15 2019, 05:07 PM
Post#5



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


I used the following code, and i SQL retreived the correct value

Private Sub Plantype_AfterUpdate()
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)<= me.quantity) AND ((tbl_sampleplan.num2)>= me.quantity) AND ((tbl_Plan.planID)= me.Plantype)); "
Me.sample = ssql
Debug.Print ssql
End Sub

I need to assign the value to the quantity field
This post has been edited by 2ME: Oct 15 2019, 05:10 PM

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

M.E.M
Go to the top of the page
 
MadPiet
post Oct 15 2019, 05:27 PM
Post#6



Posts: 3,361
Joined: 27-February 09



You have to open a recordset based on that, and then assign the value from the recordset to the control on your form.

dim rs as dao.recordset
set rs = currentdb.OpenRecordset(strsql)

rs.MoveFirst

me.MyControl = rs.Fields("TheFieldNameContainingTheValue")

rs.Close
set rs=nothing
Go to the top of the page
 
2ME
post Oct 15 2019, 05:32 PM
Post#7



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


could you please restructure my code as i don't know to use recordset

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

M.E.M
Go to the top of the page
 
2ME
post Oct 15 2019, 05:40 PM
Post#8



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


i ended up with this 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

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

M.E.M
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2019 - 03:20 AM