Find first active record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2002

Table tMstrAgeGrp
Fields AgeGrpId - AutoNumber
FundingID - Long Integer
Active - Yes/No

Form with RecordSource of tEnrolments and form includes a control for
FundingID

In the AfterUpdate of a combo-box I wish to find (from tMstrAgeGrp) AgeGrpID
for the first Active record (by AgeGrpID) with matching FundingID.

Then, if an AgeGrpID was returned do the same for the next matching record.

tMstrAgeGrp should contain only 0, 1 or 2 Active records for a FundingID.

I hope that I have explained well enough.
 
Hi Graham,

The SQL string:

"SELECT AgeGrpId FROM tMstrAgeGrp WHERE Active = True AND FundingID = " &
Me.FundingId

will retrieve a recordset of all qualifying AgeGrpId. (I assume the value of
FundingId against which you wish to match is available as Me.FundingId.) How
you deploy this SQL is up to you.

You can then move up and down the recodset as you wish - the actual methods
depend upon whether you have an ADO or a DAO recordset.

Rod
 
Thanks Rod

I can see the logic in that but, unfortunately, I am not familiar with the
concepts of ADO or DAO.

Access/VBA is not my first language and I tend to look at problem resolution
in the light of what I am used to.

Looks like I have some researching on how to use the resultant recordset.
 
Hi Graham,

Do so. From the VBA window open Help and search on DAO. Specifically read up
on OpenRecordset and the Move methods.

Your resultant code will look something like (this is not complete!)

Dim dbs as DAO.Database
Dim rst as DAO.Recordset
dim strSQL as String
strSQL = "............."
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.BOF AND rst.EOF then
'nothing found'
Else
'use the move methods to point at different rows Note refer to fields as
rst!fieldname
End If

I'm not sure whether dbOpenSnapshot is appropriate for you. Read up and
decide for yourself.

Regards,

Rod
 
Back
Top