How do I Populate fields on a Form from an SQL Query
How do I Populate fields on a Form from an SQL Query
(OP)
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.
I must be really stupid or something.....because I don't see anyone asking this anywhere! What am I missing.
Talk To Other Members
RE: How do I Populate fields on a Form from an SQL Query
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
RE: How do I Populate fields on a Form from an SQL Query
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