Appending? Creating? Help!

[deejayquai]

Hi

I'm still trying to solve my problem. I have two tables- students and
activity assessments, they're linked one-to-many. From a
lookup/combo/list [however?] I'd like to be able create a series of new
activity assessment records each with the activity name field populated
per student or group of students.

i.e. Bob has no activity assessments but on a form 10 activities (golf,
football etc)are highlighted, click a command button and viola! Bob now
has 10 unique assessment records each with the activity names taken
from the lookup. OR Bob and the rest of his group members are all
automatically given 10 activity assessment records each with the same
activity names - on the click of a button!

Basically- I'm trying to automate the record creation/activity name
data entry procedure rather than having to do it 10 times per student.

Help- thanks

David

PS I'm a novice.
PPS I'll think of a prize for the first correct answer...

[David Mann]

One way of doing this...

Create a table listing the activities.

Use an Append Query to add records to your table of activity assessments.
The name field is set to whatever the name is.
The Activity field is taken from the table of activities, but only if the
relevant box is ticked.

I assume you will add in the actual assessments manually later...

David

<de********@gma il.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

Hi

I'm still trying to solve my problem. I have two tables- students and
activity assessments, they're linked one-to-many. From a
lookup/combo/list [however?] I'd like to be able create a series of new
activity assessment records each with the activity name field populated
per student or group of students.

i.e. Bob has no activity assessments but on a form 10 activities (golf,
football etc)are highlighted, click a command button and viola! Bob now
has 10 unique assessment records each with the activity names taken
from the lookup. OR Bob and the rest of his group members are all
automatically given 10 activity assessment records each with the same
activity names - on the click of a button!

Basically- I'm trying to automate the record creation/activity name
data entry procedure rather than having to do it 10 times per student.

Help- thanks

David

PS I'm a novice.
PPS I'll think of a prize for the first correct answer...

[Bob Quintal]

de********@gmai l.com wrote in
news:11******** **************@ g47g2000cwa.goo glegroups.com:

Hi

I'm still trying to solve my problem. I have two tables-
students and activity assessments, they're linked one-to-many.
From a lookup/combo/list [however?] I'd like to be able
create a series of new activity assessment records each with
the activity name field populated per student or group of
students.

i.e. Bob has no activity assessments but on a form 10
activities (golf, football etc)are highlighted, click a
command button and viola! Bob now has 10 unique assessment
records each with the activity names taken from the lookup.
OR Bob and the rest of his group members are all automatically
given 10 activity assessment records each with the same
activity names - on the click of a button!

Basically- I'm trying to automate the record creation/activity
name data entry procedure rather than having to do it 10 times
per student.

Help- thanks

David

PS I'm a novice.
PPS I'll think of a prize for the first correct answer...

I've done something very similar, using two multiselect
listboxes.

Listbox1 contains the student.IDs and student.Names.
Listbox2 contains the activity.IDs and activity.names

the DO_It button runs a little bit of Visual Basic code,
the core part of which is nested for each loops.

'Declarations required, not shown

For each lb1Id in Listbox1.ItemsS elected
For each lb2Id in Listbox2.ItemsS elected
docmd.runsql "INSERT into StudentActiviti es " _
& " (Student.ID, ActivityID) " _
& " VALUES ( " & lb1ID & "," & lb2ID & ")"
next
next

just use the vb help on listboxes to flesh things out.

--
Bob Quintal

PA is y I've altered my email address.

[deejayquai]

thanks Bob

This is where I've got to with it now:

The code I've got is as follows:
--------------------------------------------------------

Private Sub Command5_Click( )
For Each lb0ID In List0.ItemsSele cted
For Each lb1ID In List2.ItemsSele cted
DoCmd.RunSQL "INSERT into tblAssessmentAc tivityProfile ( [Group
Member ID], [Activity] ) " _
& " VALUES ( " & lb0ID & "," & lb1ID & ")"
Next
Next
End Sub
--------------------------------
It does append a new record into the tblAssessmentAc tivityProfile but
not for the Group Member I select in List0 and rather than append the
activity name from List2 it appends a number. I'm not sure where I'm
going wrong, for a start I don't understand what lb0ID and lb1ID are
and I've checked the help and can't find anything there to enlighten me
on how to proceed.

thanks

David

[Bob Quintal]

de********@gmai l.com wrote in
news:11******** **************@ z14g2000cwz.goo glegroups.com:

thanks Bob

This is where I've got to with it now:

The code I've got is as follows:
--------------------------------------------------------

Private Sub Command5_Click( )
For Each lb0ID In List0.ItemsSele cted
For Each lb1ID In List2.ItemsSele cted
DoCmd.RunSQL "INSERT into tblAssessmentAc tivityProfile
( [Group
Member ID], [Activity] ) " _
& " VALUES ( " & lb0ID & "," & lb1ID & ")"
Next
Next
End Sub
--------------------------------
It does append a new record into the
tblAssessmentAc tivityProfile but not for the Group Member I
select in List0 and rather than append the activity name from
List2 it appends a number. I'm not sure where I'm going
wrong, for a start I don't understand what lb0ID and lb1ID are
and I've checked the help and can't find anything there to
enlighten me on how to proceed.

thanks

David

lboID and Lb1Id are just variable names for counters.(I use the
abbreviation for List Box, the listbox # and the suffix ID)
You should dim them as variants.

the Values line should be fleshed out, the numbers you see are
just the row numbers from the listboxes. In a good table design
you would show the student name, but store the student ID and
show the activity name but store the activity ID.

to actually store the names, modify the VALUES section of the
code to something like this:

VALUES ( """ & list0.column(1, lb0ID) & """,""" & list1.column
(1),lb1ID & """)

note the use of triple quotes, because you are now dealing with
text strings,
not that in the column property column(columnnu mber , rownumber)
you should change the 1 to which column is used to hold the name
you want
--
Bob Quintal

PA is y I've altered my email address.

[deejayquai]

Bob

Here's what I've got now-

------------------
Private Sub Command5_Click( )

Dim lb0ID As Variant
Dim lb2ID As Variant

For Each lb0ID In List0.ItemsSele cted
For Each lb2ID In List2.ItemsSele cted
DoCmd.RunSQL "INSERT into tblAssessmentAc tivityProfile ( [Group
Member ID], [Activity] ) " _
& "VALUES ( """ & List0.Column(1, lb0ID) & """,""" &
List2.Column(2, lb2ID) & """) "

Next
Next
End Sub
-------------------------

It's telling me when I try to append that there is a "type conversion
failure" is this to do with me trying to put a string into the [Group
Member ID] field which is a number type field indexed to the primary
key in the tblGroupMembers ? In terms of the columns- List0 shows the
surnames but it's the ID that's hidden that I want so I'm presuming
that's column 1 and it's the activity name in List2 that I'm after
which I'm assuming is column 2.

thanks again

David

[deejayquai]

Bob (et al)

I've cracked it thanks! I just had to change the List0 column to 0 and
List2 column to 1 and it now seems to work fine both for one group
member and one activity and for many members and many activities.

Many thanks for your help- now onto the next problem...!

Regards

David