DAO-newbie question

  • Thread starter Thread starter Home
  • Start date Start date
H

Home

Hello,

i am using access for a long time now and i run into its limitations so now
i try to use dao. This seems to be a very logical language. One problem, how
can i put the results of my functions in my forms / reports. I assume it's
not something you put in your record source. For example, i have:

Function test() As Variant

Dim dbDatabase As DAO.Database
Dim dtRecord As DAO.Recordset

Set dbDatabase = CurrentDb()
Set dtRecord = dbDatabase.OpenRecordset("tblKlantKort", dbOpenDynaset)

dtRecord.MoveFirst
Do Until dtRecord.EOF
Debug.Print dtRecord![Klant-id]
Debug.Print dtRecord!Naam
Debug.Print dtRecord!Woonplaats
Debug.Print dtRecord!Telefoon
Debug.Print dtRecord!Email
dtRecord.MoveNext
Loop

dtRecord.Close
dbDatabase.Close
End Function

When i execute this, i get to see the records form my vba results window.
How can i manage that when i open a form, these results display immediate?

Thanks!

Rogier
 
If you're accessing your data via DAO, then your form would be unbound (all
controlsources = Unbound) ... then, in a sub you would move through your
recordset and fill your form data:

Dim ctl As Control
Dim fld As DAO.Field
'open your recordset the way you are doing

For each fld in rst.Fields
ctl(fld.Name) = rst.Fields(fld.name).Value
Next

This is aircode, and as such you may need to alter it slightly.

This works IF you name your form's controls to the SAME name as the fields
in your recordset. For example, if you have customer table with fields like
strFName, strLname, then name the control where you want the customer's
First Name to appear strLName ...

Of course, you must now also handle all data manipulation, including moving
through the recordset, adding and deleting records, etc etc BUT this
strategy does give you complete control over what is happening with your
data.

Note: I didn't come up with this strategy, I got it from Allison Balter's
excellent book "Mastering Access 2000 Development", which I highly
recommend. Full of great stuff and well worth the cover price.
 
Back
Top