Appending in a sub form

[SFleming]

I am creating a venue ticket tracking DB in Access '07. tblVenueEvent has
info about Venue, date, time, event with a VenueEventID (key field) which is
linked to tblTicketInfo which has section, seat, row, etc.
I set up an entry form completing VenueEvent info with a sub form for ticket
info.
Some of the venues have a standard set of seats that I want to "auto
populate" into the sub fom.
I have set up a Venue table with the standard seats for the venue.
I can set up an Append Query on the sub form, which does append the
tblTicketInfo, but does not capture the VenueEventID.
Any ideas?

 

[John W. Vinson]

I am creating a venue ticket tracking DB in Access '07. tblVenueEvent has
info about Venue, date, time, event with a VenueEventID (key field) which is
linked to tblTicketInfo which has section, seat, row, etc.
I set up an entry form completing VenueEvent info with a sub form for ticket
info.
Some of the venues have a standard set of seats that I want to "auto
populate" into the sub fom.
I have set up a Venue table with the standard seats for the venue.
I can set up an Append Query on the sub form, which does append the
tblTicketInfo, but does not capture the VenueEventID.
Any ideas?

Just bear in mind you're not appending into the Subform - forms are just
windows, not data repositories - but into tblTicketInfo. The Append query can
use

VenueEventID: [Forms]![YourMainForm]![VenueEventID]

as a calculated field to append into tblTicketInfo. You will need to save the
mainform record to disk with either RunCommand acCmdSaveRecord or code like

If Me.Dirty Then Me.Dirty = False

before running the query, so there is an ID in the table to link to.

 

[SFleming]

Thanks for the info, the VenueEventID shows up in the tblTicketInfo.
How can I get that info to show up on the subform after they invoke the
append query?
The ticket information shows up on the sub form if I go to the next record
and then go back. I tried to hit refresh and it does not show up until I
move out and then back.

I am creating a venue ticket tracking DB in Access '07. tblVenueEvent has
info about Venue, date, time, event with a VenueEventID (key field) which is

[quoted text clipped - 7 lines]

tblTicketInfo, but does not capture the VenueEventID.
Any ideas?

Just bear in mind you're not appending into the Subform - forms are just
windows, not data repositories - but into tblTicketInfo. The Append query can
use

VenueEventID: [Forms]![YourMainForm]![VenueEventID]

as a calculated field to append into tblTicketInfo. You will need to save the
mainform record to disk with either RunCommand acCmdSaveRecord or code like

If Me.Dirty Then Me.Dirty = False

before running the query, so there is an ID in the table to link to.

 

[John W. Vinson]

Thanks for the info, the VenueEventID shows up in the tblTicketInfo.
How can I get that info to show up on the subform after they invoke the
append query?
The ticket information shows up on the sub form if I go to the next record
and then go back. I tried to hit refresh and it does not show up until I
move out and then back.

Requery the subform rather than refreshing it:

Me!mysubformname.Form.Requery

 

[SFleming via AccessMonster.com]

John - Thanks for your help...I hate to ask an elementary question, but would
you place this event proceedure on the subform attached to one of the field
or some type of an update button?

 

[John W. Vinson]

John - Thanks for your help...I hate to ask an elementary question, but would
you place this event proceedure on the subform attached to one of the field
or some type of an update button?

That's up to you. You know better than I do how you want your user interface
to work!

It should be in the same code that executes the append query. I'd assume that
would be a command button on the mainform, but there are other ways you could
do it (the AfterUpdate event of the mainform, or some control on the
mainform).