I hope someone can help. I am creating a database wherein I have several
different meeting types. Each meeting has it's own agenda wherein each agenda
item (different table) is listed as item 1, item 1 name; item 2, item two
name; etc. on continuous subforms for each meeting record.  My supervisor
requested it this way so that if we choose to rearrange or duplicate agenda
items for different meetings, we would have that option; however, this has
posed a problem in that for every meeting, each agenda item has to be
selected all over again.  Therefore, I am trying to write an update query or
a code (I'm fairly new to VBA) that will reference a specific RecordID based
on the name of the meeting and input the agenda items for that record in
every new record with that meeting name.  I have been unsuccessful because I
haven't found a way (and don't know how) to make a reference to that specific
ID in the query or code because I don't want the code to update all the
fields on the continuous subforms because each agenda item also has a minutes
field so each meeting record for instance under a meeting titled Team Meeting
would have the same agenda items but each meeting date would have different
minutes in response to the agenda items. Any assistance, opinion or otherwise
would be greatly appreciated. I've wasted enough time trying to figure it out
on my own. Have a good day.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

On Jan 27, 11:26=A0am, "Cynde via AccessMonster.com" <u57671@uwe> wrote:
> I hope someone can help. I am creating a database wherein I have several
> different meeting types. Each meeting has it's own agenda wherein each ag=
enda
> item (different table) is listed as item 1, item 1 name; item 2, item two
> name; etc. on continuous subforms for each meeting record. =A0My supervis=
or
> requested it this way so that if we choose to rearrange or duplicate agen=
da
> items for different meetings, we would have that option; however, this ha=
s
> posed a problem in that for every meeting, each agenda item has to be
> selected all over again. =A0Therefore, I am trying to write an update que=
ry or
> a code (I'm fairly new to VBA) that will reference a specific RecordID ba=
sed
> on the name of the meeting and input the agenda items for that record in
> every new record with that meeting name. =A0I have been unsuccessful beca=
use I
> haven't found a way (and don't know how) to make a reference to that spec=
ific
> ID in the query or code because I don't want the code to update all the
> fields on the continuous subforms because each agenda item also has a min=
utes
> field so each meeting record for instance under a meeting titled Team Mee=
ting
> would have the same agenda items but each meeting date would have differe=
nt
> minutes in response to the agenda items. Any assistance, opinion or other=
wise
> would be greatly appreciated. I've wasted enough time trying to figure it=
 out
> on my own. Have a good day.
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Foru=
ms.aspx/access-formscoding/201001/1

That seems messy to me. If I understand your environment you'd best be
served with:
1. Table for Mettings with a primary key ID.
2. Table for Agendas in a one-to-many relationship with Meetings.

Is this what you have or something else?

Cynde -

You didn't give any details on your table structure, so I'll just take a 
stab at this.   

I will assume you have tables something like this:

tblMeeting
MeetingID   (autonumber, primary key - is this your RecordID?)
MeetingName
MeetingDate
(maybe some other fields)

tblAgenda
AgendaID    (autonumber, primary key)
MeetingID   (foreign key to tblMeeting)
AgendaItem
AgendaItemName
AgendaItemMinutes

It sounds like you need to add certain agenda items whenever you add a new 
meeting, and those agenda items are based on the meeting name and a 
particular meeting with that meeting name.  You may want to create a small 
table of default meetings by name to drive this, with the MeetingName as the 
primary key, and a second field containing the MeetingID of the meeting you 
want to use as a template.  This way, if the MeetingName is "Team Meeting", 
and of all the current "Team Meeting" meetings, you chose MeetingID 128 to be 
the one with the correct agenda items for new "Team Meeting", then the record 
in this new table would be "Team Meeting" and 128.  The beauty of this is 
that if you decide later than you want future new "Team Meeting"s to have 
agenda items that are associated with MeetingID 328, you can just update the 
record in this table.   You would end up having one record for each 
MeetingName in this new table:

tblDefaultMeeting
MeetingName   (primary key)
MeetingID     (foreign key to tblMeeting)

Now you can use this to add the correct agenda items when you add a new 
Meeting.   On the form where a new meeting is entered, the user will add the 
MeetingName (the MeetingID should be autonumber), MeetingDate, and any other 
fields in that table.   Have them click a button to save this record.  In the 
code behind the button you can run a query to automatically populate the new 
agenda items based on the MeetingName.  This query will look something like 
this (you will need to use your table, field, form, and control names):

INSERT INTO tblAgenda (MeetingID, AgendaItem, AgendaItemName)
(SELECT Forms!MyFormName!MeetingID, tblAgenda.AgendaItem, 
tblAgenda.AgendaItemName FROM tblDefaultMeeting INNER JOIN tblAgenda ON 
tblDefaultMeeting.MeetingID = tblAgenda.MeetingID WHERE 
tblDefaultMeeting.MeetingName = Forms!MyFormName!MeetingName);

Check it out.  If you need more help, then please post your table 
structures, the control names on your forms for these fields, and any SQL you 
have started.

-- 
Daryl S


"Cynde via AccessMonster.com" wrote:

> I hope someone can help. I am creating a database wherein I have several
> different meeting types. Each meeting has it's own agenda wherein each agenda
> item (different table) is listed as item 1, item 1 name; item 2, item two
> name; etc. on continuous subforms for each meeting record.  My supervisor
> requested it this way so that if we choose to rearrange or duplicate agenda
> items for different meetings, we would have that option; however, this has
> posed a problem in that for every meeting, each agenda item has to be
> selected all over again.  Therefore, I am trying to write an update query or
> a code (I'm fairly new to VBA) that will reference a specific RecordID based
> on the name of the meeting and input the agenda items for that record in
> every new record with that meeting name.  I have been unsuccessful because I
> haven't found a way (and don't know how) to make a reference to that specific
> ID in the query or code because I don't want the code to update all the
> fields on the continuous subforms because each agenda item also has a minutes
> field so each meeting record for instance under a meeting titled Team Meeting
> would have the same agenda items but each meeting date would have different
> minutes in response to the agenda items. Any assistance, opinion or otherwise
> would be greatly appreciated. I've wasted enough time trying to figure it out
> on my own. Have a good day.
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
> 
> .
> 

You need to be more specific about what tables you have and how they are 
related to each other.
Without that, we could only guess at what the problem is.
It could very well be that your table design is incorrect but without more 
information, we cannot tell.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they 
eat for a lifetime".


"Cynde via AccessMonster.com" wrote:

> I hope someone can help. I am creating a database wherein I have several
> different meeting types. Each meeting has it's own agenda wherein each agenda
> item (different table) is listed as item 1, item 1 name; item 2, item two
> name; etc. on continuous subforms for each meeting record.  My supervisor
> requested it this way so that if we choose to rearrange or duplicate agenda
> items for different meetings, we would have that option; however, this has
> posed a problem in that for every meeting, each agenda item has to be
> selected all over again.  Therefore, I am trying to write an update query or
> a code (I'm fairly new to VBA) that will reference a specific RecordID based
> on the name of the meeting and input the agenda items for that record in
> every new record with that meeting name.  I have been unsuccessful because I
> haven't found a way (and don't know how) to make a reference to that specific
> ID in the query or code because I don't want the code to update all the
> fields on the continuous subforms because each agenda item also has a minutes
> field so each meeting record for instance under a meeting titled Team Meeting
> would have the same agenda items but each meeting date would have different
> minutes in response to the agenda items. Any assistance, opinion or otherwise
> would be greatly appreciated. I've wasted enough time trying to figure it out
> on my own. Have a good day.
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
> 
> .
> 

Thanks John. Yes, that's what I have. I will take a look at the next answer
also and respond so that you will both know what I have and if I can get this
to work. Thanks so much for responding.

johnlute wrote:
>> I hope someone can help. I am creating a database wherein I have several
>> different meeting types. Each meeting has it's own agenda wherein each agenda
>[quoted text clipped - 18 lines]
>> --
>> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
>
>That seems messy to me. If I understand your environment you'd best be
>served with:
>1. Table for Mettings with a primary key ID.
>2. Table for Agendas in a one-to-many relationship with Meetings.
>
>Is this what you have or something else?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

Thank you, Daryl.  I do have Meeting Names table also; however, I don't have
primary key on it. I'm not real familiar with relationships with more than
one table having a primary key but I'm definitely going to give your response
a try.  What I have so far is:

tblMeetingTable
MtgID as the Primary Key 
MeetingName
other fields

tblAgendaItems
MtgID as a Number
3 fields for AgendaItemNumber (keeps the items in order), AgendaItem and
AgendaMinutes

tblMeetingNames 
MeetingNames

I will add the fields as you have suggested and create the appropriate
relationships and work on creating the code you have shown me.   I am also
just learning the SQL feature as I've had no issues of needing it in the past
as macros and queries have worked very well for me and I have done some
coding but certainly don't consider myself exceptionally good at it...yet:).
With both of those types of programming, I haven't been able to figure out
how to reference a particular field via the ID (syntax can be confusing) but
I can see from what you've written that a lightbulb just came on.  I will
post after I've worked on this and let you know if I need further assistance.
I'm very thankful for your response. Thank you, Daryl.

Daryl S wrote:
>Cynde -
>
>You didn't give any details on your table structure, so I'll just take a 
>stab at this.   
>
>I will assume you have tables something like this:
>
>tblMeeting
>MeetingID   (autonumber, primary key - is this your RecordID?)
>MeetingName
>MeetingDate
>(maybe some other fields)
>
>tblAgenda
>AgendaID    (autonumber, primary key)
>MeetingID   (foreign key to tblMeeting)
>AgendaItem
>AgendaItemName
>AgendaItemMinutes
>
>It sounds like you need to add certain agenda items whenever you add a new 
>meeting, and those agenda items are based on the meeting name and a 
>particular meeting with that meeting name.  You may want to create a small 
>table of default meetings by name to drive this, with the MeetingName as the 
>primary key, and a second field containing the MeetingID of the meeting you 
>want to use as a template.  This way, if the MeetingName is "Team Meeting", 
>and of all the current "Team Meeting" meetings, you chose MeetingID 128 to be 
>the one with the correct agenda items for new "Team Meeting", then the record 
>in this new table would be "Team Meeting" and 128.  The beauty of this is 
>that if you decide later than you want future new "Team Meeting"s to have 
>agenda items that are associated with MeetingID 328, you can just update the 
>record in this table.   You would end up having one record for each 
>MeetingName in this new table:
>
>tblDefaultMeeting
>MeetingName   (primary key)
>MeetingID     (foreign key to tblMeeting)
>
>Now you can use this to add the correct agenda items when you add a new 
>Meeting.   On the form where a new meeting is entered, the user will add the 
>MeetingName (the MeetingID should be autonumber), MeetingDate, and any other 
>fields in that table.   Have them click a button to save this record.  In the 
>code behind the button you can run a query to automatically populate the new 
>agenda items based on the MeetingName.  This query will look something like 
>this (you will need to use your table, field, form, and control names):
>
>INSERT INTO tblAgenda (MeetingID, AgendaItem, AgendaItemName)
>(SELECT Forms!MyFormName!MeetingID, tblAgenda.AgendaItem, 
>tblAgenda.AgendaItemName FROM tblDefaultMeeting INNER JOIN tblAgenda ON 
>tblDefaultMeeting.MeetingID = tblAgenda.MeetingID WHERE 
>tblDefaultMeeting.MeetingName = Forms!MyFormName!MeetingName);
>
>Check it out.  If you need more help, then please post your table 
>structures, the control names on your forms for these fields, and any SQL you 
>have started.
>
>> I hope someone can help. I am creating a database wherein I have several
>> different meeting types. Each meeting has it's own agenda wherein each agenda
>[quoted text clipped - 15 lines]
>> would be greatly appreciated. I've wasted enough time trying to figure it out
>> on my own. Have a good day.

-- 
Message posted via http://www.accessmonster.com

Hi Daryl.  I just want to thank you so much. I created the sql query per your
instructions and although I had to experiment with it a little because, not
being real familiar with how to write sql, I wasn't real sure what form to
reference in the SELECT statement, it is working beautifully.  I did, however,
streamline the process so that I don't have to use the Save button because
the button would only work if I went back and forth from previous to next
record first; otherwise, I would get an update violation. So I created a
macro to go to previous then next record and within the macro I put an Open
Query action and have the sql query run from within the macro on after update
event of the Meeting Name, then when I select the Agenda Item Subform, the
items are all there with no violation errors.  I do have other tables to tie
into this such as Action Item table but I think with what you've shown me, I
should be able to accomplish it relatively easily.  But I do have one other
question that haunts me every time I have to use a previous, next macro to
refresh a record...is there an easier way because of course, if I'm at the
first or last record, I always have an error and although I can do an error
handler for it (which I haven't), I just wondered what the pros do for issues
like that. Thank you so very much again. Have a wonderful evening!

Daryl S wrote:
>Cynde -
>
>You didn't give any details on your table structure, so I'll just take a 
>stab at this.   
>
>I will assume you have tables something like this:
>
>tblMeeting
>MeetingID   (autonumber, primary key - is this your RecordID?)
>MeetingName
>MeetingDate
>(maybe some other fields)
>
>tblAgenda
>AgendaID    (autonumber, primary key)
>MeetingID   (foreign key to tblMeeting)
>AgendaItem
>AgendaItemName
>AgendaItemMinutes
>
>It sounds like you need to add certain agenda items whenever you add a new 
>meeting, and those agenda items are based on the meeting name and a 
>particular meeting with that meeting name.  You may want to create a small 
>table of default meetings by name to drive this, with the MeetingName as the 
>primary key, and a second field containing the MeetingID of the meeting you 
>want to use as a template.  This way, if the MeetingName is "Team Meeting", 
>and of all the current "Team Meeting" meetings, you chose MeetingID 128 to be 
>the one with the correct agenda items for new "Team Meeting", then the record 
>in this new table would be "Team Meeting" and 128.  The beauty of this is 
>that if you decide later than you want future new "Team Meeting"s to have 
>agenda items that are associated with MeetingID 328, you can just update the 
>record in this table.   You would end up having one record for each 
>MeetingName in this new table:
>
>tblDefaultMeeting
>MeetingName   (primary key)
>MeetingID     (foreign key to tblMeeting)
>
>Now you can use this to add the correct agenda items when you add a new 
>Meeting.   On the form where a new meeting is entered, the user will add the 
>MeetingName (the MeetingID should be autonumber), MeetingDate, and any other 
>fields in that table.   Have them click a button to save this record.  In the 
>code behind the button you can run a query to automatically populate the new 
>agenda items based on the MeetingName.  This query will look something like 
>this (you will need to use your table, field, form, and control names):
>
>INSERT INTO tblAgenda (MeetingID, AgendaItem, AgendaItemName)
>(SELECT Forms!MyFormName!MeetingID, tblAgenda.AgendaItem, 
>tblAgenda.AgendaItemName FROM tblDefaultMeeting INNER JOIN tblAgenda ON 
>tblDefaultMeeting.MeetingID = tblAgenda.MeetingID WHERE 
>tblDefaultMeeting.MeetingName = Forms!MyFormName!MeetingName);
>
>Check it out.  If you need more help, then please post your table 
>structures, the control names on your forms for these fields, and any SQL you 
>have started.
>
>> I hope someone can help. I am creating a database wherein I have several
>> different meeting types. Each meeting has it's own agenda wherein each agenda
>[quoted text clipped - 15 lines]
>> would be greatly appreciated. I've wasted enough time trying to figure it out
>> on my own. Have a good day.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

Cynde -

A simpler way to save the current record if needed is this:
If Me.Dirty = True Then
   Me.Dirty = False
End If

No need to move to other records.

-- 
Daryl S


"Cynde via AccessMonster.com" wrote:

> Hi Daryl.  I just want to thank you so much. I created the sql query per your
> instructions and although I had to experiment with it a little because, not
> being real familiar with how to write sql, I wasn't real sure what form to
> reference in the SELECT statement, it is working beautifully.  I did, however,
> streamline the process so that I don't have to use the Save button because
> the button would only work if I went back and forth from previous to next
> record first; otherwise, I would get an update violation. So I created a
> macro to go to previous then next record and within the macro I put an Open
> Query action and have the sql query run from within the macro on after update
> event of the Meeting Name, then when I select the Agenda Item Subform, the
> items are all there with no violation errors.  I do have other tables to tie
> into this such as Action Item table but I think with what you've shown me, I
> should be able to accomplish it relatively easily.  But I do have one other
> question that haunts me every time I have to use a previous, next macro to
> refresh a record...is there an easier way because of course, if I'm at the
> first or last record, I always have an error and although I can do an error
> handler for it (which I haven't), I just wondered what the pros do for issues
> like that. Thank you so very much again. Have a wonderful evening!
> 
> Daryl S wrote:
> >Cynde -
> >
> >You didn't give any details on your table structure, so I'll just take a 
> >stab at this.   
> >
> >I will assume you have tables something like this:
> >
> >tblMeeting
> >MeetingID   (autonumber, primary key - is this your RecordID?)
> >MeetingName
> >MeetingDate
> >(maybe some other fields)
> >
> >tblAgenda
> >AgendaID    (autonumber, primary key)
> >MeetingID   (foreign key to tblMeeting)
> >AgendaItem
> >AgendaItemName
> >AgendaItemMinutes
> >
> >It sounds like you need to add certain agenda items whenever you add a new 
> >meeting, and those agenda items are based on the meeting name and a 
> >particular meeting with that meeting name.  You may want to create a small 
> >table of default meetings by name to drive this, with the MeetingName as the 
> >primary key, and a second field containing the MeetingID of the meeting you 
> >want to use as a template.  This way, if the MeetingName is "Team Meeting", 
> >and of all the current "Team Meeting" meetings, you chose MeetingID 128 to be 
> >the one with the correct agenda items for new "Team Meeting", then the record 
> >in this new table would be "Team Meeting" and 128.  The beauty of this is 
> >that if you decide later than you want future new "Team Meeting"s to have 
> >agenda items that are associated with MeetingID 328, you can just update the 
> >record in this table.   You would end up having one record for each 
> >MeetingName in this new table:
> >
> >tblDefaultMeeting
> >MeetingName   (primary key)
> >MeetingID     (foreign key to tblMeeting)
> >
> >Now you can use this to add the correct agenda items when you add a new 
> >Meeting.   On the form where a new meeting is entered, the user will add the 
> >MeetingName (the MeetingID should be autonumber), MeetingDate, and any other 
> >fields in that table.   Have them click a button to save this record.  In the 
> >code behind the button you can run a query to automatically populate the new 
> >agenda items based on the MeetingName.  This query will look something like 
> >this (you will need to use your table, field, form, and control names):
> >
> >INSERT INTO tblAgenda (MeetingID, AgendaItem, AgendaItemName)
> >(SELECT Forms!MyFormName!MeetingID, tblAgenda.AgendaItem, 
> >tblAgenda.AgendaItemName FROM tblDefaultMeeting INNER JOIN tblAgenda ON 
> >tblDefaultMeeting.MeetingID = tblAgenda.MeetingID WHERE 
> >tblDefaultMeeting.MeetingName = Forms!MyFormName!MeetingName);
> >
> >Check it out.  If you need more help, then please post your table 
> >structures, the control names on your forms for these fields, and any SQL you 
> >have started.
> >
> >> I hope someone can help. I am creating a database wherein I have several
> >> different meeting types. Each meeting has it's own agenda wherein each agenda
> >[quoted text clipped - 15 lines]
> >> would be greatly appreciated. I've wasted enough time trying to figure it out
> >> on my own. Have a good day.
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
> 
> .
> 

Thanks Daryl.  Where would I put this code, in the close event?

Daryl S wrote:
>Cynde -
>
>A simpler way to save the current record if needed is this:
>If Me.Dirty = True Then
>   Me.Dirty = False
>End If
>
>No need to move to other records.
>
>> Hi Daryl.  I just want to thank you so much. I created the sql query per your
>> instructions and although I had to experiment with it a little because, not
>[quoted text clipped - 76 lines]
>> >> would be greatly appreciated. I've wasted enough time trying to figure it out
>> >> on my own. Have a good day.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

 silly me...it probably goes in the On Dirty event correct?

Cynde wrote:
>Thanks Daryl.  Where would I put this code, in the close event?
>
>>Cynde -
>>
>[quoted text clipped - 10 lines]
>>> >> would be greatly appreciated. I've wasted enough time trying to figure it out
>>> >> on my own. Have a good day.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

ok got it.  I put it it the OnDirty event of the MeetingName and it seems to
be working. It didn't work in the OnDirty event of the whole form.   Guess I
had to think that one through a little better.  Thanks so much for everything.
..I am good to go!

Cynde wrote:
> silly me...it probably goes in the On Dirty event correct?
>
>>Thanks Daryl.  Where would I put this code, in the close event?
>>
>[quoted text clipped - 3 lines]
>>>> >> would be greatly appreciated. I've wasted enough time trying to figure it out
>>>> >> on my own. Have a good day.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

Nope I thought it worked but apparently I haven't got it right yet.  It's
still giving me an error.  I'll keep trying different places.

Cynde wrote:
>ok got it.  I put it it the OnDirty event of the MeetingName and it seems to
>be working. It didn't work in the OnDirty event of the whole form.   Guess I
>had to think that one through a little better.  Thanks so much for everything.
>..I am good to go!
>
>> silly me...it probably goes in the On Dirty event correct?
>>
>[quoted text clipped - 3 lines]
>>>>> >> would be greatly appreciated. I've wasted enough time trying to figure it out
>>>>> >> on my own. Have a good day.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

Ok I converted my update macro to code and put the syntax you provided inside
of that code before the query runs and put it in the AfterUpdate event of the
MeetingName field. I do believe it's working now.  If I did this incorrectly,
please let me know. Thanks so much.

Cynde wrote:
>Nope I thought it worked but apparently I haven't got it right yet.  It's
>still giving me an error.  I'll keep trying different places.
>
>>ok got it.  I put it it the OnDirty event of the MeetingName and it seems to
>>be working. It didn't work in the OnDirty event of the whole form.   Guess I
>[quoted text clipped - 6 lines]
>>>>>> >> would be greatly appreciated. I've wasted enough time trying to figure it out
>>>>>> >> on my own. Have a good day.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

Cynde -

Yes, it should go in with your other code to force a record save.  Glad it 
is working.  Let us know if you have other issues.
-- 
Daryl S


"Cynde via AccessMonster.com" wrote:

> Ok I converted my update macro to code and put the syntax you provided inside
> of that code before the query runs and put it in the AfterUpdate event of the
> MeetingName field. I do believe it's working now.  If I did this incorrectly,
> please let me know. Thanks so much.
> 
> Cynde wrote:
> >Nope I thought it worked but apparently I haven't got it right yet.  It's
> >still giving me an error.  I'll keep trying different places.
> >
> >>ok got it.  I put it it the OnDirty event of the MeetingName and it seems to
> >>be working. It didn't work in the OnDirty event of the whole form.   Guess I
> >[quoted text clipped - 6 lines]
> >>>>>> >> would be greatly appreciated. I've wasted enough time trying to figure it out
> >>>>>> >> on my own. Have a good day.
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
> 
> .
> 

Thank you.  I may need your assistance one more time for another type of
issue in this db; however, I want to give it my best shot before I request
help.  If I can't figure it out, I may be contacting you again sometime on
Monday.  Thank you again, Daryl.

Daryl S wrote:
>Cynde -
>
>Yes, it should go in with your other code to force a record save.  Glad it 
>is working.  Let us know if you have other issues.
>> Ok I converted my update macro to code and put the syntax you provided inside
>> of that code before the query runs and put it in the AfterUpdate event of the
>[quoted text clipped - 9 lines]
>> >>>>>> >> would be greatly appreciated. I've wasted enough time trying to figure it out
>> >>>>>> >> on my own. Have a good day.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

Hi Daryl.  I am requesting some more direction please.  Due to the intricacy
of some agendas, I have had to add levels so to speak with more agenda tables.
I have 5 levels total so far. Each of the agenda level tables are set up the
same as the original agenda table but with identifying level names.

tblAgendaItems
AgendaID (autonumber, primary key)
MtgID (foreign key)
AgendaItem
AgendaItemName
AgendaItemMinutes

tblAgendaItemsLevel2
AgendaIDLevel2 (foreign key to AgendaID)
MtgID (Number)
AgendaItemLevel2
AgendaItemNameLevel2
AgendaItemMinLevel2

each of the tables after that up to level 4 are set up the same way.
In the relationships, I have the tblAgendaItems related to the
tblMeetingTable in a one-many via MtgID.  I have the tblAgendaItems related
via the AgendaID in one-one with tblAgendaItemsLevel2 and then
tbleAgendaItemsLevel2 as a one-one with the level 3 table and level 3 table
as a one-one with level 4 table.  I have tried several different
relationships as well as different syntaxes for the SQL and I can't get it to
work.  I'm sure my syntax is incorrect but I can't figure out how to get to
the subsubform of level 2 (haven't got beyond level 2 yet).  Each subform of
agendaitems (level 2, 3, 4) are subforms of the level above and I have them
set as continuous forms (although I did try single form and that didn't work
either).  Below is my current syntax for the level 2 sql which I have edited
many times over in the last few days with no luck. I have also included my
syntax (drawn from yours) that I used to connect the meeting names with
agendas (which works very well).  I am trying to get the AgendaIDs to flow
down to level 2 as well as the AgendaItem (text as number).  I've tried
putting the sql into different forms and into different events and still am
without a clue as to what I'm doing incorrectly.  Perhaps I'm tackling this
all wrong.  Any suggestions or direction would be greatly appreciated. Also,
not really related direclty to this issue, my employer might let me take a 5
day course in either VBA or SQL and I wanted to ask your opinion on what
might be most beneficial to start with.Over the last few years, I've become
more familiar with VBA, but not proficient and it's not been until very
recently that I've been trying my hand at SQL and not quite getting the jist
of it. Your opinion would be appreciated. Thank you, Daryl.

This one directly below doesn't work.  This is to a subform (level2) of the
subform (AgendaItems) of the mainform (Meetings).

INSERT INTO AgendaItemsLevel2 ( AgendaItemLevel2, AgendaIDLevel2 )
SELECT AgendaItems.AgendaItem, AgendaItems.AgendaID
FROM AgendaItems INNER JOIN AgendaItemsLevel2 ON AgendaItems.AgendaID =
AgendaItemsLevel2.AgendaIDLevel2
WHERE (((AgendaItems.MtgID)=[AgendaItemsLevel2].[MtgID]));

Below is the one adapted from your example and it works. This is for a direct
subform to the meeting form.

INSERT INTO AgendaItems ( MtgID, AgendaItem, AgendaItemName )
SELECT Forms!MeetingForm!MtgID, AgendaItems.AgendaItem, AgendaItems.
AgendaItemName
FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
MtgID=AgendaItems.MtgID
WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;




Daryl S wrote:
>Cynde -
>
>You didn't give any details on your table structure, so I'll just take a 
>stab at this.   
>
>I will assume you have tables something like this:
>
>tblMeeting
>MeetingID   (autonumber, primary key - is this your RecordID?)
>MeetingName
>MeetingDate
>(maybe some other fields)
>
>tblAgenda
>AgendaID    (autonumber, primary key)
>MeetingID   (foreign key to tblMeeting)
>AgendaItem
>AgendaItemName
>AgendaItemMinutes
>
>It sounds like you need to add certain agenda items whenever you add a new 
>meeting, and those agenda items are based on the meeting name and a 
>particular meeting with that meeting name.  You may want to create a small 
>table of default meetings by name to drive this, with the MeetingName as the 
>primary key, and a second field containing the MeetingID of the meeting you 
>want to use as a template.  This way, if the MeetingName is "Team Meeting", 
>and of all the current "Team Meeting" meetings, you chose MeetingID 128 to be 
>the one with the correct agenda items for new "Team Meeting", then the record 
>in this new table would be "Team Meeting" and 128.  The beauty of this is 
>that if you decide later than you want future new "Team Meeting"s to have 
>agenda items that are associated with MeetingID 328, you can just update the 
>record in this table.   You would end up having one record for each 
>MeetingName in this new table:
>
>tblDefaultMeeting
>MeetingName   (primary key)
>MeetingID     (foreign key to tblMeeting)
>
>Now you can use this to add the correct agenda items when you add a new 
>Meeting.   On the form where a new meeting is entered, the user will add the 
>MeetingName (the MeetingID should be autonumber), MeetingDate, and any other 
>fields in that table.   Have them click a button to save this record.  In the 
>code behind the button you can run a query to automatically populate the new 
>agenda items based on the MeetingName.  This query will look something like 
>this (you will need to use your table, field, form, and control names):
>
>INSERT INTO tblAgenda (MeetingID, AgendaItem, AgendaItemName)
>(SELECT Forms!MyFormName!MeetingID, tblAgenda.AgendaItem, 
>tblAgenda.AgendaItemName FROM tblDefaultMeeting INNER JOIN tblAgenda ON 
>tblDefaultMeeting.MeetingID = tblAgenda.MeetingID WHERE 
>tblDefaultMeeting.MeetingName = Forms!MyFormName!MeetingName);
>
>Check it out.  If you need more help, then please post your table 
>structures, the control names on your forms for these fields, and any SQL you 
>have started.
>
>> I hope someone can help. I am creating a database wherein I have several
>> different meeting types. Each meeting has it's own agenda wherein each agenda
>[quoted text clipped - 15 lines]
>> would be greatly appreciated. I've wasted enough time trying to figure it out
>> on my own. Have a good day.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201002/1

Just wondering if what I'm trying to do is possible.

Cynde wrote:
>Hi Daryl.  I am requesting some more direction please.  Due to the intricacy
>of some agendas, I have had to add levels so to speak with more agenda tables.
>I have 5 levels total so far. Each of the agenda level tables are set up the
>same as the original agenda table but with identifying level names.
>
>tblAgendaItems
>AgendaID (autonumber, primary key)
>MtgID (foreign key)
>AgendaItem
>AgendaItemName
>AgendaItemMinutes
>
>tblAgendaItemsLevel2
>AgendaIDLevel2 (foreign key to AgendaID)
>MtgID (Number)
>AgendaItemLevel2
>AgendaItemNameLevel2
>AgendaItemMinLevel2
>
>each of the tables after that up to level 4 are set up the same way.
>In the relationships, I have the tblAgendaItems related to the
>tblMeetingTable in a one-many via MtgID.  I have the tblAgendaItems related
>via the AgendaID in one-one with tblAgendaItemsLevel2 and then
>tbleAgendaItemsLevel2 as a one-one with the level 3 table and level 3 table
>as a one-one with level 4 table.  I have tried several different
>relationships as well as different syntaxes for the SQL and I can't get it to
>work.  I'm sure my syntax is incorrect but I can't figure out how to get to
>the subsubform of level 2 (haven't got beyond level 2 yet).  Each subform of
>agendaitems (level 2, 3, 4) are subforms of the level above and I have them
>set as continuous forms (although I did try single form and that didn't work
>either).  Below is my current syntax for the level 2 sql which I have edited
>many times over in the last few days with no luck. I have also included my
>syntax (drawn from yours) that I used to connect the meeting names with
>agendas (which works very well).  I am trying to get the AgendaIDs to flow
>down to level 2 as well as the AgendaItem (text as number).  I've tried
>putting the sql into different forms and into different events and still am
>without a clue as to what I'm doing incorrectly.  Perhaps I'm tackling this
>all wrong.  Any suggestions or direction would be greatly appreciated. Also,
>not really related direclty to this issue, my employer might let me take a 5
>day course in either VBA or SQL and I wanted to ask your opinion on what
>might be most beneficial to start with.Over the last few years, I've become
>more familiar with VBA, but not proficient and it's not been until very
>recently that I've been trying my hand at SQL and not quite getting the jist
>of it. Your opinion would be appreciated. Thank you, Daryl.
>
>This one directly below doesn't work.  This is to a subform (level2) of the
>subform (AgendaItems) of the mainform (Meetings).
>
>INSERT INTO AgendaItemsLevel2 ( AgendaItemLevel2, AgendaIDLevel2 )
>SELECT AgendaItems.AgendaItem, AgendaItems.AgendaID
>FROM AgendaItems INNER JOIN AgendaItemsLevel2 ON AgendaItems.AgendaID =
>AgendaItemsLevel2.AgendaIDLevel2
>WHERE (((AgendaItems.MtgID)=[AgendaItemsLevel2].[MtgID]));
>
>Below is the one adapted from your example and it works. This is for a direct
>subform to the meeting form.
>
>INSERT INTO AgendaItems ( MtgID, AgendaItem, AgendaItemName )
>SELECT Forms!MeetingForm!MtgID, AgendaItems.AgendaItem, AgendaItems.
>AgendaItemName
>FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
>MtgID=AgendaItems.MtgID
>WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;
>
>>Cynde -
>>
>[quoted text clipped - 57 lines]
>>> would be greatly appreciated. I've wasted enough time trying to figure it out
>>> on my own. Have a good day.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201002/1