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

[Toga]

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.

 

[huzza]

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
    .MoveFirst
    Me.FirstName1 = !FirstName2
    Me.SurName1 = !SurName2
    Me.Address1 = !Address2
    .Close
  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,
Richard

 

[Guest_imported]

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????

 

[rafe]

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.