Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How do I Populate fields on a Form from an SQL Query

How do I Populate fields on a Form from an SQL Query

How do I Populate fields on a Form from an SQL Query

I usually use the Access Query's but want to lear how to use SQL in my code.  In the Form Open section, I've retrieved my records via SQL.  (I can do a break and see that the data was retrieved)  How in the world do I get that data to populate the fields I have on my form?  Also, where should I be writing my SQL code?  The stupid instructions show examples of the code but they never seem to tell you how to use or where you should be putting your code!

I must be really stupid or something.....because I don't see anyone asking this anywhere!  What am I missing.

RE: How do I Populate fields on a Form from an SQL Query

Hi Toga,
what you need to use is record sets. This is basically the way you work with query results in code. You can find more details in accesses help (I'd suggest starting with openrecordset). However I'll tell you the basics here so you can get started.
Private Sub Form_Open(Cancel As Integer)
  Dim dbs As Database, rst As Recordset
  Dim strSQL As String

  Set dbs = CurrentDb
  strSQL = "SELECT * FROM Customers WHERE [Surname]= 'Smith'"
  Set rst = dbs.OpenRecordset(strSQL)
  With rst
    Me.FirstName1 = !FirstName2
    Me.SurName1 = !SurName2
    Me.Address1 = !Address2
  End With
  Set dbs = Nothing
End Sub

So in the code above you'd want to change the strSQL to match the SQL you want to use. The variables with 1 in (e.g FirstName1) refer to the controls on your form, these you have to replace with the appropriate names. The variables with 2 in refer to the column names of the table you are running the SQL against, again change these as appropriate.
In this code it is setting the controls equal to the first result returned in the record set. To change which is being used you would need the rst.movenext command to move through the recordset.
I hope this helps you get started, as I say you can learn more from the help, but don't hesitate to ask again if you need anything clarifying,

RE: How do I Populate fields on a Form from an SQL Query

I've been using
Private Sub Form_Open(Cancel As Integer)
  Dim strSQL As String
  strSQL = "SELECT * FROM Customers WHERE [Surname]='Smith'"
  me.recordsource = strsql
end sub
and just name fields to match the fields in the table

Which way is more efficient????

RE: How do I Populate fields on a Form from an SQL Query

3324 good point. Just make sure that the "control source" on the properties page is set to one of the fields the query returns ... else you'll be seeing a "#Error" in the controls.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close