[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Sql select results to text boxes access 2010

i want to run a select query which will return 5 columns and 20 rows.

I have a form that has a 5x20 grid of text boxes, and i want to paste the results from the select query into these text boxes.

How can i do this?
0
CaptainGiblets
Asked:
CaptainGiblets
  • 5
  • 4
1 Solution
 
mbizupCommented:
How about setting up a form in continuous forms view with 5 textboxes arranged touching each other horizontally, and space minimized between rows of the detail section.

Then set the control sources of the textboxes to the fields in your query. If your query has 20 rows this should give you the appearance you want.
0
 
CaptainGibletsAuthor Commented:
I have another 2 sets of 20 boxes that will be filled out, and then a submit button to be pressed to enter that data into the form. However they cant be linked controls as many checks need to be performed on the entered data before they are ammended to the the database.
0
 
Rey Obrero (Capricorn1)Commented:
* Not really sure why you want to do this, you can use a bound form and life will be easy.

anyway, if you want to use an unbound form

(btw, you can not copy the result of the query and paste it to an open unbound form)

open select query as recordset

set rs=currentdb.openrecordset("yourselectqueryname")

me.text1=rs.Fields(0)  ' or  me.text1=rs!NameOfField1
me.text2=rs.Fields(1)   ' or  me.text1=rs!NameOfField2


etc...
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
CaptainGibletsAuthor Commented:
its not a view from sql the actual query i run is

"select Auto, [Date Recorded], Name, [Trading As Name], [Date Sent], Terms, MD, [MD2], [Administrator ] from V_Sentto where v_sentto.[Administratrator] = '" & Me.cboadmin & "'"
0
 
Rey Obrero (Capricorn1)Commented:
you can do this, then
dim ssql as string, rs as dao.recordset

ssql="select Auto, [Date Recorded], Name, [Trading As Name], [Date Sent], Terms, MD, [MD2], [Administrator ] from V_Sentto where v_sentto.[Administratrator] = '" & Me.cboadmin & "'"


set rs=currentdb.openrecordset(ssql)

if  rs.eof then exit sub

me.text1=rs.Fields(0)  ' or  me.text1=rs!NameOfField1
me.text2=rs.Fields(1)   ' or  me.text1=rs!NameOfField2
0
 
CaptainGibletsAuthor Commented:
Set rs = CurrentDb.OpenRecordset(ssql)
 
returns error object variable or With block variable not set... Is this  because it is an ADP database?
0
 
Rey Obrero (Capricorn1)Commented:
.. omg..
Is this  because it is an ADP database? possibly
use ADODB recordset not DAO.
0
 
CaptainGibletsAuthor Commented:
i then get the same error with this line haha

Set rs = CurrentDb.OpenRecordset(ssql)
0
 
Rey Obrero (Capricorn1)Commented:
using adodb
this is a sample code to use

dim ssql
ssql="select Auto, [Date Recorded], Name, [Trading As Name], [Date Sent], Terms, MD, [MD2], [Administrator ] from V_Sentto where v_sentto.[Administratrator] = '" & Me.cboadmin & "'"

dim rs as new ADODB.Recordset
rs.open ssql, currentproject.connection, adOpenKeySet, adLockOptimistic
0
 
Rey Obrero (Capricorn1)Commented:
see this link for more info

http://support.microsoft.com/kb/195082
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now