Problems with subform

[Guest]

I'm pretty new to access, so I'm using the Northwind db as a guide.

I have modeled my form/subform by the Orders/Orders Subform in the Northwind
db. A little history on the Northwind form, the orders form has a combo box
that you can pick products to order. You can pick one and then move to the
next row in the combo box and pick a second product for that order. It looks
like to the user a multi value field.

I have a subform that has one combo box field on it. I use this sql to
populate it
SELECT [Ministry Types].[MinistryID], [Ministry Types].[MinistryName] FROM
[Ministry Types] ORDER BY [Ministry Types].[MinistryName];

Table- Ministry Types looks like this
MinistryID - (autonumber)Primary Key
MinistryName- (text)

My form is, Contacts
ContactID autonumber
other sutff(address, phone, etc..)

Corosponding Table- Contact
ContactID (autonumber)Primary key
the other stuff

My third table is
Ministry Involvement
ContactID- Number Primary key
MinistryID- Number Primary key
MinistryName- (Text)

The list come up just fine but I get an error saying you cannot add or
change a record because a related record is required in the table 'Contacts'

I know I have missed something that the Northwind db is doing and I'm not
doing, I just don't know what it is. It feels like I have check my properties
and sql and anyother check box that I can find to see where the differences
my lie, but I can't find it. Can any one help with this error. Basicly I'm
creating a multi-value field on my contacts form. When I open my contacts
form the combo box list show up fine. I can even pick one value but when I go
to pick a second or save is throws me the error.

Please help,
Thanks

Lori

 

[Steve Schapel]

Lori,

I gather that the subform is based on the Ministry Involvement table...
am I right? And the combobox is on the subform, right? If so, have a
look at the properties of the subform, and see if the Link Master Fields
and Link Child Fileds properties are set to ContactID.

It appears to me that it is not correct to have a MinistryName field in
the Ministry Involvement table.

 

[Guest]

Steve, I'm educated as a Lotus Notes programer, very new to Access. I'm not
sure of the lengo of access. Thank you for responding and please be patience J

Yes, the combobox is on the subform. When I enter data in the subform and I
want it to show up in the Ministry involvements table, if that is what you
mean by “the subform is based on the Ministry Involvement table...†then yes
its based on the Ministry involvements table. Is that the record source
property of the form on the data tab?

I have the subform in the design view, I click to bring the form properties
up but can’t find the Link Master Fields and Link Child Fields properties.
Which tabs are they in, format, data, event or other?

With what I’m familur with, I trying to make a field that can have multiple
vales. Maybe I’m not arranging the data they way it should be, can you help
me with that. My database is a list of woman who is involved in may
ministries at my church. We just want to track how many are involved in what
ministry and the next phase will be attendance. This is what I have;

My forms:
Contacts form

Ministry Types form- a form where you can enter new ministries, it has two
fields, autohnumber field called MinistryID,
text field called MinistryName.

Contacts Subform -this has the combobox that does a query for the MinistryID
and the MinistryName, the sql is (SELECT [Ministry Types].[MinistryID],
[Ministry Types].[MinistryName] FROM [Ministry Types] ORDER BY [Ministry
Types].[MinistryName]


My Tables:
Contacts – list of contacts with the little + sign next to it and when I
click it, it show up another table the involvement ministries(is what I want)
but it just shows Ministry ID – the number that was assigned by the
autonumber from the form, it doesn’t show any name. On the contact I have
been playing with it shows
1 – then nothing
2 - then nothing
autonumber field called ContactsID- Primary key
other fields….

Ministry Types – the list of ministry types I want to pick from the the
combobox
Autonumber field called Ministry ID- Primary key
Text field call Ministry Name just like the form

Ministry Involvements – the list of Ministry that woman are involved in at
church, my thought was it was suppose to have a + so I could see the contacts
that where under the ministries. But its not there???
Number field ContactsID- Primary key
Number field MinistryID- Dual primary key
Text field MinistryName

All it has is, at the top its captions are ContactID, MinistryID, MinistryName
1 1 nothing
1 2 nothing

In the relationship view I have
A many to one from Contacts to Ministry Involvement
A many to one from Ministry Types to Ministry Involvement

To recap, my questions are, from the top
1. When you say, “the subform is based on the Ministry Involvement table...â€
Is that the record source property of the form on the data tab?
2. Which tabs are the Link Master Fields and Link Child Fields properties
in, format, data, event or other?
3. Do I have the data arranged correctly to do a multi-value field?

I’ll be glad to email you the database its only 2.8 mg, if that would help.
My email is (e-mail address removed).

Thanks so much,


--
Lori

Lori,

I gather that the subform is based on the Ministry Involvement table...
am I right? And the combobox is on the subform, right? If so, have a
look at the properties of the subform, and see if the Link Master Fields
and Link Child Fileds properties are set to ContactID.

It appears to me that it is not correct to have a MinistryName field in
the Ministry Involvement table.

--
Steve Schapel, Microsoft Access MVP

I'm pretty new to access, so I'm using the Northwind db as a guide.

I have modeled my form/subform by the Orders/Orders Subform in the Northwind
db. A little history on the Northwind form, the orders form has a combo box
that you can pick products to order. You can pick one and then move to the
next row in the combo box and pick a second product for that order. It looks
like to the user a multi value field.

I have a subform that has one combo box field on it. I use this sql to
populate it
SELECT [Ministry Types].[MinistryID], [Ministry Types].[MinistryName] FROM
[Ministry Types] ORDER BY [Ministry Types].[MinistryName];

Table- Ministry Types looks like this
MinistryID - (autonumber)Primary Key
MinistryName- (text)

My form is, Contacts
ContactID autonumber
other sutff(address, phone, etc..)

Corosponding Table- Contact
ContactID (autonumber)Primary key
the other stuff

My third table is
Ministry Involvement
ContactID- Number Primary key
MinistryID- Number Primary key
MinistryName- (Text)

The list come up just fine but I get an error saying you cannot add or
change a record because a related record is required in the table 'Contacts'

I know I have missed something that the Northwind db is doing and I'm not
doing, I just don't know what it is. It feels like I have check my properties
and sql and anyother check box that I can find to see where the differences
my lie, but I can't find it. Can any one help with this error. Basicly I'm
creating a multi-value field on my contacts form. When I open my contacts
form the combo box list show up fine. I can even pick one value but when I go
to pick a second or save is throws me the error.

Please help,
Thanks

Lori