Looping through Recordset

  • Thread starter Thread starter Denis
  • Start date Start date
D

Denis

First, here are some components of my database:

tblPersonnel contains many fields including- PersID (PK)
tblPersQuals contains many fields including - fldQual,
PersID (FK)
tblQualifications is a 'Master' list of Qualifications (No
duplicates)

frmQualificationSelect (source tblPersQuals) - is
specific to a single person; contains a listbox (lstQuals)
based on tblQualifications; and contains a subform
sfrmPersQuals, which is based on tblPersQuals.

By double-clicking values found in lstQuals, my goal was
to populate these values in the fldQual field found in
sfrmPersQuals. I have achieved this through an Update
Query.

My problem, however, is that I am able to populate
sfrmPersQuals with the same qualification more than once
(i.e. 2 or more instances of "Access" , "PG" etc).

My uneducated guess is that I need some type of code to
loop through the current recordset found in sfrmPersQuals
to see if the qualification already exists and have a
MsgBox pop up with something like "This qualification
already exists". The problem is that I don't know how to
loop through the recordset.

Any help would be greatly appreciated.
Thank you
Denis
 
Simplest way to avoid the problem might be to set a unique index on the
combination of fldQual + PersID in tblPersQuals. Any attempt to add the
qualification for the same person a 2nd time will then fail.

1. Open tblPersQuals in design view.

2. Open the Indexes dialog (View menu).

3. Enter a name for the index. You can use any valid name, but a meaningful
one might be:
fldQualPersID

4. In the 2nd column of the dialog, beside this name, choose fldQual.

5. In the lower pane of the dialog, set the Unique property to Yes.

6. On the next row in the dialog, leave the Index Name blank, and choose the
field PersID. This means the one index (you entered only one name) consists
of 2 fields (2 rows), and since you set its Unique property to Yes, Access
will not permit the combination to be duplicated.

7. Save the change to the table. Close. Test.
 
Hi Allen,
Honestly, I have never used the Indexes Dialog box (didn't
really know what it was for). Your instructions were very
explicit and things are working just fine now.
Thank you again,
Denis
 
Back
Top