Record sets reading from tables for functions.

  • Thread starter Thread starter David Mc
  • Start date Start date
D

David Mc

Currently i can read data from databases using recordsets
in vba. The problem i have got is that i want ot be able
to write a custom function that will aloow me to read data
from a table. How could i use record sets to do this.

Like
read customer data (firstname, surname and age) and return
in a function depending on the value entered into the
function parametres, like 1 for firstname, 2 for surname
and 3 for age.

I know how to create functions, its just reading data from
recordsets that i have a problem with.

David


Data used to write to a table is below

Sub AddNewResitLetterDetails(Student_ID, Course_Title,
Element_Description, Date_Taken)

Dim dbsITExaminations As Database
Dim rstResit As Recordset
Dim strStudentID As String
Dim strCourse As String
Dim strElement As String
Dim strExamDate As String


Set dbsITExaminations = CurrentDb
Set rstResit = dbsITExaminations.OpenRecordset
("tblResits", dbOpenDynaset)

strStudentID = Student_ID
strCourse = Course_Title
strElement = Element_Description
strExamDate = Date_Taken

' Call the function that adds the record.
AddName rstResit, strStudentID, strCourse, strElement,
strExamDate

rstResit.Close

End Sub

Function AddName(rstResitTemp As Recordset, strStudentID
As String, strCourse As String, strElement As String,
strExamDate As String)

' Adds a new record to a Recordset using the data
passed
' by the calling procedure. The new record is then made
' the current record.
With rstResitTemp
.AddNew
!StudentID = strStudentID
!Course = strCourse
!Element = strElement
!ExamDate = strExamDate
.Update
End With

End Function
 
Your second line of code in the AddNewResitLetterDetails
subroutine should read:
Dim rstResit As DAO.Recordset

Make sure the AddName function accepts a DAO.Recordset
argument.

Regards,
Jen
 
The following is the basic jist of how you would do it in DAO.

Set dwsAW = CreateWorkspace(<NameOfWorkspace>, <UserName>, <Password>,
<DBEngineType>)
Set ddbMD = dwsAW.OpenDatabase(<FullDBFileNameIncludingPath>,
<TrueForExclusiveModeOrFalseForSharableMode>, <TrueOrFalseForReadOnly>)
Set drsMCH = .ddbMD.OpenRecordset(<TableName>, dbOpenTable,
<dbOptions>,<LockEdits> )
 
It simple to use DLookUp rather than Recordset. Something like:

(assuming StudentID is a TEXT Field)

****Air-code only****
Function fnGetItem(ByVal strStudentID As String, _
ByVal bytFieldToGet As Byte) As Variant

Select Case bytFieldToGet
Case 1
fnGetItem = DLookUp("[FirstName]","[tblStudent]", _
"[StudentID] = " & Chr$(34) & strStudentID & Chr$(34))

Case 2
fnGetItem = DLookUp("[SurName]","[tblStudent]", _
"[StudentID] = " & Chr$(34) & strStudentID & Chr$(34))

Case 3
fnGetItem = DLookUp("[Age]","[tblStudent]", _
"[StudentID] = " & Chr$(34) & strStudentID & Chr$(34))

Case Else
fnGetItem = Null

End Select
End Function
****

BTW, we don't generally store Age in a Table since if you do, every Record
will have the wrong Age a year from now.
 
Back
Top