Setting up a subform

I have set subforms up for records but, I don't know how I could do this one
without using a union Query which I have successfully accomplished but I know
its not the way it should be done.

I have a database I am doing for bible study.

I have a table, SubjectId (autonumber Primary Key), Subject (title of the
study set to txt field). Then, I have bible verse (memo), Remarks (memo)
Since they can be over 255 characters. Then I continue on bible verse1,
remarks1, Etc. clear up to 40.

Now, If I set up another table and have subjectid, (number) I linked the 2
tables in a query. Now I know I can add A BibleVerseID (autonumber) bible
verse(memo), Remarks (memo). Here is my question?

When Im in the form based on the query that links the 2 forms, How would I
go to the next record in the subform For the bible Verse and remarks, without
specifying again that it is tied to the Subject. I was told I could have
unlimited bible verses and remarks and wouldn't be wasting space. Then once
that soulution is figured out, then if I go to a new subject it wouldnt put
the ID number in for the old subject. I know there has to be a code on after
update on the record it would automatically would put the last ID stored in
for tieing the bible Verse and remarks with the correct subject. Thanks, I
know others had to go through the same problem that I had also.

On Jul 12, 3:15*pm, BrianPaul
wrote:
I have set subforms up for records but, I don't know how I could do this one
without using a union Query which I have successfully accomplished but I know
its not the way it should be done.

I have a database I am doing for bible study.

I have a table, SubjectId (autonumber Primary Key), Subject (title of the
study set to txt field). *Then, I have bible verse (memo), Remarks (memo)
Since they can be over 255 characters. Then I continue on bible verse1,
remarks1, Etc. clear up to 40.

Now, If I set up another table and have subjectid, (number) I linked the 2
tables in a query. *Now I know I can add A BibleVerseID (autonumber) bible
verse(memo), Remarks (memo). *Here is my question?

When Im in the form based on the query that links the 2 forms, How would I
go to the next record in the subform For the bible Verse and remarks, without
specifying again that it is tied to the Subject. *I was told I could have
unlimited bible verses and remarks and wouldn't be wasting space. *Then once
that soulution is figured out, then if I go to a new subject it wouldnt put
the ID number in for the old subject. *I know there has to be a code on after
update on the record it would automatically would put the last ID stored in
for tieing the bible Verse and remarks with the correct subject. *Thanks, I
know others had to go through the same problem that I had also.

Just a point of clarification. When you say, "Then I continue on bible
verse1, remarks1, Etc. clear up to 40." do you mean you have 40
different verses on a single record, or that you have 40 records, each
with a different verse?

Keven Denen

I have 40 fields set up for bible verses and 40 for remarks, That shouldn't
bet that way. However, I need a separate table, My problem is I have to
enter the subject again so that the verse and remark goes with the subject.
I need it to store the value the SubjectID in that table so the verse and
remarks goes along with the subject. I mean it works, However, pain to keep
entering the subject number again then go to the next bible verse and remark.

"GM Leonric" wrote:

On Jul 12, 3:15 pm, BrianPaul
wrote:
I have set subforms up for records but, I don't know how I could do this one
without using a union Query which I have successfully accomplished but I know
its not the way it should be done.

I have a database I am doing for bible study.

I have a table, SubjectId (autonumber Primary Key), Subject (title of the
study set to txt field). Then, I have bible verse (memo), Remarks (memo)
Since they can be over 255 characters. Then I continue on bible verse1,
remarks1, Etc. clear up to 40.

Now, If I set up another table and have subjectid, (number) I linked the 2
tables in a query. Now I know I can add A BibleVerseID (autonumber) bible
verse(memo), Remarks (memo). Here is my question?

When Im in the form based on the query that links the 2 forms, How would I
go to the next record in the subform For the bible Verse and remarks, without
specifying again that it is tied to the Subject. I was told I could have
unlimited bible verses and remarks and wouldn't be wasting space. Then once
that soulution is figured out, then if I go to a new subject it wouldnt put
the ID number in for the old subject. I know there has to be a code on after
update on the record it would automatically would put the last ID stored in
for tieing the bible Verse and remarks with the correct subject. Thanks, I
know others had to go through the same problem that I had also.

Just a point of clarification. When you say, "Then I continue on bible
verse1, remarks1, Etc. clear up to 40." do you mean you have 40
different verses on a single record, or that you have 40 records, each
with a different verse?

Keven Denen

On Jul 12, 4:28*pm, BrianPaul
wrote:
I have 40 fields set up for bible verses and 40 for remarks, *That shouldn't
bet that way. *However, I need a separate table, My problem is I have to
enter the subject again so that the verse and remark goes with the subject. *
I need it to store the value the SubjectID in that table so the verse and
remarks goes along with the subject. *I mean it works, However, pain to keep
entering the subject number again then go to the next bible verse and remark.

"GM Leonric" wrote:
On Jul 12, 3:15 pm, BrianPaul
wrote:
I have set subforms up for records but, I don't know how I could do this one
without using a union Query which I have successfully accomplished but I know
its not the way it should be done.

I have a database I am doing for bible study.

I have a table, SubjectId (autonumber Primary Key), Subject (title of the
study set to txt field). *Then, I have bible verse (memo), Remarks (memo)
Since they can be over 255 characters. Then I continue on bible verse1,
remarks1, Etc. clear up to 40.

Now, If I set up another table and have subjectid, (number) I linked the 2
tables in a query. *Now I know I can add A BibleVerseID (autonumber) bible
verse(memo), Remarks (memo). *Here is my question?

When Im in the form based on the query that links the 2 forms, How would I
go to the next record in the subform For the bible Verse and remarks, without
specifying again that it is tied to the Subject. *I was told I could have
unlimited bible verses and remarks and wouldn't be wasting space. *Then once
that soulution is figured out, then if I go to a new subject it wouldnt put
the ID number in for the old subject. *I know there has to be a code on after
update on the record it would automatically would put the last ID stored in
for tieing the bible Verse and remarks with the correct subject. *Thanks, I
know others had to go through the same problem that I had also.

Just a point of clarification. When you say, "Then I continue on bible
verse1, remarks1, Etc. clear up to 40." do you mean you have 40
different verses on a single record, or that you have 40 records, each
with a different verse?

Keven Denen

Setting your data up like this is going to make it very difficult to
get anything to work properly. Normalize your data properly and
setting up what you need is much easier.

On Jul 12, 6:54*pm, CompGeek78 wrote:
On Jul 12, 4:28*pm, BrianPaul
wrote:



I have 40 fields set up for bible verses and 40 for remarks, *That shouldn't
bet that way. *However, I need a separate table, My problem is I have to
enter the subject again so that the verse and remark goes with the subject. *
I need it to store the value the SubjectID in that table so the verse and
remarks goes along with the subject. *I mean it works, However, pain to keep
entering the subject number again then go to the next bible verse and remark.

"GM Leonric" wrote:
On Jul 12, 3:15 pm, BrianPaul
wrote:
I have set subforms up for records but, I don't know how I could do this one
without using a union Query which I have successfully accomplished but I know
its not the way it should be done.

I have a database I am doing for bible study.

I have a table, SubjectId (autonumber Primary Key), Subject (title of the
study set to txt field). *Then, I have bible verse (memo), Remarks (memo)
Since they can be over 255 characters. Then I continue on bible verse1,
remarks1, Etc. clear up to 40.

Now, If I set up another table and have subjectid, (number) I linked the 2
tables in a query. *Now I know I can add A BibleVerseID (autonumber) bible
verse(memo), Remarks (memo). *Here is my question?

When Im in the form based on the query that links the 2 forms, How would I
go to the next record in the subform For the bible Verse and remarks, without
specifying again that it is tied to the Subject. *I was told I could have
unlimited bible verses and remarks and wouldn't be wasting space. *Then once
that soulution is figured out, then if I go to a new subject it wouldnt put
the ID number in for the old subject. *I know there has to be a code on after
update on the record it would automatically would put the last ID stored in
for tieing the bible Verse and remarks with the correct subject. *Thanks, I
know others had to go through the same problem that I had also.

Just a point of clarification. When you say, "Then I continue on bible
verse1, remarks1, Etc. clear up to 40." do you mean you have 40
different verses on a single record, or that you have 40 records, each
with a different verse?

Keven Denen

Setting your data up like this is going to make it very difficult to
get anything to work properly. Normalize your data properly and
setting up what you need is much easier.

If you do set your data up properly, your whole problem is solved. You
can then make a main form with the Subject and a subform with the
verse/remark. When you enter data into the subform, it will
automatically fill in the subject info into the verse table (as long
as you set your master/child fields up properly).

Keven Denen

Hello Brian,

See my response to your previous post regarding searching and your problem
will disappear.

Steve



"BrianPaul" wrote in message
...
I have set subforms up for records but, I don't know how I could do this
one
without using a union Query which I have successfully accomplished but I
know
its not the way it should be done.

I have a database I am doing for bible study.

I have a table, SubjectId (autonumber Primary Key), Subject (title of the
study set to txt field). Then, I have bible verse (memo), Remarks (memo)
Since they can be over 255 characters. Then I continue on bible verse1,
remarks1, Etc. clear up to 40.

Now, If I set up another table and have subjectid, (number) I linked the 2
tables in a query. Now I know I can add A BibleVerseID (autonumber) bible
verse(memo), Remarks (memo). Here is my question?

When Im in the form based on the query that links the 2 forms, How would I
go to the next record in the subform For the bible Verse and remarks,
without
specifying again that it is tied to the Subject. I was told I could have
unlimited bible verses and remarks and wouldn't be wasting space. Then
once
that soulution is figured out, then if I go to a new subject it wouldnt
put
the ID number in for the old subject. I know there has to be a code on
after
update on the record it would automatically would put the last ID stored
in
for tieing the bible Verse and remarks with the correct subject. Thanks,
I
know others had to go through the same problem that I had also.