multiple recordset for forms

[NickW]

I have two tables, one is contacts (with ID # as key), second is Events (with
eventID # as key). I currently have them set up as a relationship one to
many. From ID on caontact table to ID on events table. I also have a query
that does what it's supposed to, as long as I enter the information in the
table instead of a form. I would like to be able to enter into a form, the
contact ID #, then have it automatically create a new EventID, then I fill in
the rest of the information which needs to go on the events table. It
works... a little bit. I have a form, based on the query, but when I enter a
contact ID, it says that it's trying to create a duplicate key value and it
can't do it. I don't want it to create anything in the contact table, just
use information from it. Hopefully someone can help

 

[John W. Vinson]

I have two tables, one is contacts (with ID # as key), second is Events (with
eventID # as key). I currently have them set up as a relationship one to
many. From ID on caontact table to ID on events table. I also have a query
that does what it's supposed to, as long as I enter the information in the
table instead of a form. I would like to be able to enter into a form, the
contact ID #, then have it automatically create a new EventID, then I fill in
the rest of the information which needs to go on the events table. It
works... a little bit. I have a form, based on the query, but when I enter a
contact ID, it says that it's trying to create a duplicate key value and it
can't do it. I don't want it to create anything in the contact table, just
use information from it. Hopefully someone can help

Typically in this kind of one to many relationship one would use a Form with a
Subform. Base hte mainform on the Contacts table, and the subform on the
Events table, using the contact ID as the master/child link field. Creating a
new record in the subform will both automatically create an EventID (if it is,
as I am guessing, an Autonumber), and also inherit the mainform's current
contact ID.

 

[NickW]

So I tried the subform deal and it looks like a normal form in design view,
but then when I open to form to edit, it looks like a datasheet. That's not
what I want at all! I want to arrange text boxes that are bound to different
tables in any way i want them. I don't want them to be subforms. Is there
some kind of coding i can do to make that work? btw, subforms suck

 

[John Spencer]

If the subform is opening in datasheet view, then you need to go back to the
design of the subform and set the Default View (on the format tab of the
form's properties) to Continuous forms or Single form. Also make sure that
Allow Form View property is Yes.

And, BTW, subforms do not suck. They are very valuable in many situations.
Don't let your frustration with climbing the learning curve on programming in
Access get to you. It is a powerful tool and complex to fully learn.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[NickW]

but it still has the record selector bar at the bottom of the subform. Is
there really no way to change the recordset for each text box to the table I
want? I don't want to make multiple subforms for each text box that is in a
different table.

 

[John Spencer]

If you don't want the record selector to show, then open the form (subform) in
design view and set the property to No.

You normally show a subform when you need to link multiple related records to
a record in the main form.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County