Creating a check list when adding a new record

[Moss100]

Hello,

When I add a new applicant to my table I would also like to add a number of records to a checklist table.

My tables are:

tbl_Applicant with unique identifier of AppId
tbl_ChecklistApp with fields as follows:

AppIdLink - Unique foreign ID link
AppItem - This contains the Check item I want to add - such as "Passport seen"....etc..
AppCheck - This will allow the user to later fill in Yes, No, N/a etc...

So each time I add a new record to tbl_Applicant, I want to add records into the tbl_ChecklistApp, ensuring that the AppID is also added.

I have achieved this by using a simple insert statement for each AppItem I want to add.

I would prefer however to keep all the checklist items in a separete table (tbl_Checklist_Item) for ease of maintenance and so I would like to build code to do the following.

When I add a new Applicant:

To loop through the tbl_Checklist_Item and add a new record into tbl_ChecklistApp for each item, ensuring that the current AppID is added to each inserted record.

This way I am able to have a check list for each new applicant I add & also have a way to easily maintain the contents of the checklist.

Many thanks for your help - Mark

 

[MajP]

So if you had a default table filled with items to populate:
tbl_CheckListItems

ItemID	ItemName
1	Passport Seen
2	Item 2
3	Item 3

.....
then you could do an insert by passing in the new user id

Public Sub InsertNewChecks(AppID As Long)
  Dim strSql As String
  strSql = "INSERT INTO tbl_ChecklistApp ( AppIDLink, AppItem ) "
  strSql = strSql & "SELECT tbl_Applicant.AppID, tbl_ChecklistItems.ItemName FROM tbl_Applicant, tbl_ChecklistItems "
  strSql = strSql & "Where AppID = " & AppID
  CurrentDb.Execute strSql
End Sub

Make sure in your tbl_ChecklistApp that you index the combination of the two fields
AppIdLink - Unique foreign ID link
AppItem - This contains the Check item I want to add - such as "Passport seen"....etc
so that you cannot create duplicates.

In this example I passed in the checkslist name to the AppItem field, but I would probably pass in the Check ItemID instead. This is a reference back to the check list items instead of the actual value. So I would store 1 instead of "Passport Seen".

 

[Moss100]

Thank you very much - I take your point abount using an ID rather than a value.

Just one last thing if you will.

If I want to add a field into the tbl_CheckListItems to control whether items are included (say a Yes/No field called "Enabled") how would the above code be modified?

Many thanks for you speedy help

 

[MajP]

add that to your where statement.
strSql = strSql & "Where tbl_ChecklistItems.Include = True and AppID = " & AppID