How do I link two subforms together?

[Dustin]

Hello,

I am trying to make a form that records service work on customers units. End
goal for me is to be able to locate the customer using his units serial
number in combo box locator. Then in the first subform the service order of
can be made of what was done. And then finally go to the parts used subform
and select the parts used.

I am stuck trying to get the parts used subform to link with the service
order subform? Access will only let me connect the the Parts Used sub to the
Main. How do I get the Parts Used Sub to follow the Service Order Sub?

Any suggestions would be great! Thanks Dustin

 

[Dustin]

Hi Allen,

Thanks for responding, I tried what you told me but I am having problems
getting them to connect. It says name?? in the part used child link record
when I am in form view. I think I might have mislead on what I want. So I
will explain myself and you tell me whether or not I have the right idea.

I want to have the main form based on the customer table and use a Find
combo box to pull up the customer and his information. I want to attach the
Service Order Form as the first subform. I want it to be Continuous so I can
see all of the service that customer has had in the past. Then I want to be
able to select a certain service order and click on the next tab over and
look at the Parts Used on that service order.

Is there a better way? Should I include the parts used on the Service Order
form and forget about a second subform? I ve heard of being able to expand a
record in continuous to a full single record so you can see the whole thing
if interested. Would this be better.

Your opinion would be greatly appreciated. Thanks Dustin

So you have3 tables:
- ServiceWork, with ServiceWorkID primary key
- ServiceOrder, with ServiceOrderID primary key, and ServiceWork foreign
key.
- PartsUsed, with ID primary key, and ServiceOrderID foreign key.

Your main form is bound to ServiceWork, and it has 2 subforms on it (not a
subform within a subform.)

Add a text box to the main form, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
Set its Name property to:
txtServiceOrderID
(Be sure to use the name of your subform control for ServiceOrder, and the
name of the text box bound to ServiceOrderID for that name.)

Now set the Master Link Fields property of the PartsUsed subform to:
txtServiceOrderID

When you move record in the first subform, Access will update the text box
on the main form, and will then reload the 2nd subform.

There is an example of this kind of form in the Northwind sample database:
the Customer Orders form.

Oh, and if you have difficulty in filtering the main form by a field in the
subform, this may help:
http://allenbrowne.com/ser-28.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to make a form that records service work on customers units.
End
goal for me is to be able to locate the customer using his units serial
number in combo box locator. Then in the first subform the service order
of
can be made of what was done. And then finally go to the parts used
subform
and select the parts used.

I am stuck trying to get the parts used subform to link with the service
order subform? Access will only let me connect the the Parts Used sub to
the
Main. How do I get the Parts Used Sub to follow the Service Order Sub?

Any suggestions would be great! Thanks Dustin

 

[Allen Browne]

So you have 3 tables:
- Customer, with CustomerID primary key
- ServiceOrder, with ServiceOrderID primary key, and CustomerID foreign key;
- PartsUsed, with ServiceOrderID foreign key.

That is, you have a one to many relation:
- between Customer and ServiceOrder (based on CustomerID)
- between ServiceOrder and PartsUsed (based on ServiceOrderID.)

If that is so, you can have:
- main form bound to Customer table.
- a subform bound to ServiceOrder table.
- another subform bound to the PartsUsed table.

On the Customer form, place a text box, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
If you still get #Name, you need to change these name to match. Instead of
ServiceOrder, use the name of your subform control. Also, make sure the is a
text box in the subform for ServiceOrderID (even if you set its Visible
property to No.)

Once you get that working, you can then use the name of that text box in the
Link Master Fields property of the 2nd subform control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for responding, I tried what you told me but I am having problems
getting them to connect. It says name?? in the part used child link record
when I am in form view. I think I might have mislead on what I want. So I
will explain myself and you tell me whether or not I have the right idea.

I want to have the main form based on the customer table and use a Find
combo box to pull up the customer and his information. I want to attach
the
Service Order Form as the first subform. I want it to be Continuous so I
can
see all of the service that customer has had in the past. Then I want to
be
able to select a certain service order and click on the next tab over and
look at the Parts Used on that service order.

Is there a better way? Should I include the parts used on the Service
Order
form and forget about a second subform? I ve heard of being able to expand
a
record in continuous to a full single record so you can see the whole
thing
if interested. Would this be better.

Your opinion would be greatly appreciated. Thanks Dustin

So you have3 tables:
- ServiceWork, with ServiceWorkID primary key
- ServiceOrder, with ServiceOrderID primary key, and ServiceWork foreign
key.
- PartsUsed, with ID primary key, and ServiceOrderID foreign key.

Your main form is bound to ServiceWork, and it has 2 subforms on it (not
a
subform within a subform.)

Add a text box to the main form, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
Set its Name property to:
txtServiceOrderID
(Be sure to use the name of your subform control for ServiceOrder, and
the
name of the text box bound to ServiceOrderID for that name.)

Now set the Master Link Fields property of the PartsUsed subform to:
txtServiceOrderID

When you move record in the first subform, Access will update the text
box
on the main form, and will then reload the 2nd subform.

There is an example of this kind of form in the Northwind sample
database:
the Customer Orders form.

Oh, and if you have difficulty in filtering the main form by a field in
the
subform, this may help:
http://allenbrowne.com/ser-28.html

I am trying to make a form that records service work on customers
units.
End
goal for me is to be able to locate the customer using his units serial
number in combo box locator. Then in the first subform the service
order
of
can be made of what was done. And then finally go to the parts used
subform
and select the parts used.

I am stuck trying to get the parts used subform to link with the
service
order subform? Access will only let me connect the the Parts Used sub
to
the
Main. How do I get the Parts Used Sub to follow the Service Order Sub?

Any suggestions would be great! Thanks Dustin

 

[Dustin]

Hey I got it to work! But I have one problem. My Parts Used subform is a
datasheet and the first time I select a part it works but when I go and
select from the next drop down, it only gives me a split second to select a
part. Then the cursor goes immeadately to the top left of the first record
of the datasheet. Which happens to be the Child Link record.

How do I fix that? Thanks so much for your help!

So you have 3 tables:
- Customer, with CustomerID primary key
- ServiceOrder, with ServiceOrderID primary key, and CustomerID foreign key;
- PartsUsed, with ServiceOrderID foreign key.

That is, you have a one to many relation:
- between Customer and ServiceOrder (based on CustomerID)
- between ServiceOrder and PartsUsed (based on ServiceOrderID.)

If that is so, you can have:
- main form bound to Customer table.
- a subform bound to ServiceOrder table.
- another subform bound to the PartsUsed table.

On the Customer form, place a text box, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
If you still get #Name, you need to change these name to match. Instead of
ServiceOrder, use the name of your subform control. Also, make sure the is a
text box in the subform for ServiceOrderID (even if you set its Visible
property to No.)

Once you get that working, you can then use the name of that text box in the
Link Master Fields property of the 2nd subform control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for responding, I tried what you told me but I am having problems
getting them to connect. It says name?? in the part used child link record
when I am in form view. I think I might have mislead on what I want. So I
will explain myself and you tell me whether or not I have the right idea.

I want to have the main form based on the customer table and use a Find
combo box to pull up the customer and his information. I want to attach
the
Service Order Form as the first subform. I want it to be Continuous so I
can
see all of the service that customer has had in the past. Then I want to
be
able to select a certain service order and click on the next tab over and
look at the Parts Used on that service order.

Is there a better way? Should I include the parts used on the Service
Order
form and forget about a second subform? I ve heard of being able to expand
a
record in continuous to a full single record so you can see the whole
thing
if interested. Would this be better.

Your opinion would be greatly appreciated. Thanks Dustin

So you have3 tables:
- ServiceWork, with ServiceWorkID primary key
- ServiceOrder, with ServiceOrderID primary key, and ServiceWork foreign
key.
- PartsUsed, with ID primary key, and ServiceOrderID foreign key.

Your main form is bound to ServiceWork, and it has 2 subforms on it (not
a
subform within a subform.)

Add a text box to the main form, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
Set its Name property to:
txtServiceOrderID
(Be sure to use the name of your subform control for ServiceOrder, and
the
name of the text box bound to ServiceOrderID for that name.)

Now set the Master Link Fields property of the PartsUsed subform to:
txtServiceOrderID

When you move record in the first subform, Access will update the text
box
on the main form, and will then reload the 2nd subform.

There is an example of this kind of form in the Northwind sample
database:
the Customer Orders form.

Oh, and if you have difficulty in filtering the main form by a field in
the
subform, this may help:
http://allenbrowne.com/ser-28.html


I am trying to make a form that records service work on customers
units.
End
goal for me is to be able to locate the customer using his units serial
number in combo box locator. Then in the first subform the service
order
of
can be made of what was done. And then finally go to the parts used
subform
and select the parts used.

I am stuck trying to get the parts used subform to link with the
service
order subform? Access will only let me connect the the Parts Used sub
to
the
Main. How do I get the Parts Used Sub to follow the Service Order Sub?

Any suggestions would be great! Thanks Dustin

 

[Allen Browne]

To fix it, you will have to track down what's causing this unusual behavior.

It could be the timer event of the form.
It could be code or a macro in one of the control's events.

If the subform is based on a query that involves multiple tables, it could
be due to using a field from the wrong table, or a Default Value in one of
the fields in the wrong table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hey I got it to work! But I have one problem. My Parts Used subform is a
datasheet and the first time I select a part it works but when I go and
select from the next drop down, it only gives me a split second to select
a
part. Then the cursor goes immeadately to the top left of the first
record
of the datasheet. Which happens to be the Child Link record.

How do I fix that? Thanks so much for your help!

So you have 3 tables:
- Customer, with CustomerID primary key
- ServiceOrder, with ServiceOrderID primary key, and CustomerID foreign
key;
- PartsUsed, with ServiceOrderID foreign key.

That is, you have a one to many relation:
- between Customer and ServiceOrder (based on CustomerID)
- between ServiceOrder and PartsUsed (based on ServiceOrderID.)

If that is so, you can have:
- main form bound to Customer table.
- a subform bound to ServiceOrder table.
- another subform bound to the PartsUsed table.

On the Customer form, place a text box, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
If you still get #Name, you need to change these name to match. Instead
of
ServiceOrder, use the name of your subform control. Also, make sure the
is a
text box in the subform for ServiceOrderID (even if you set its Visible
property to No.)

Once you get that working, you can then use the name of that text box in
the
Link Master Fields property of the 2nd subform control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for responding, I tried what you told me but I am having
problems
getting them to connect. It says name?? in the part used child link
record
when I am in form view. I think I might have mislead on what I want. So
I
will explain myself and you tell me whether or not I have the right
idea.

I want to have the main form based on the customer table and use a Find
combo box to pull up the customer and his information. I want to attach
the
Service Order Form as the first subform. I want it to be Continuous so
I
can
see all of the service that customer has had in the past. Then I want
to
be
able to select a certain service order and click on the next tab over
and
look at the Parts Used on that service order.

Is there a better way? Should I include the parts used on the Service
Order
form and forget about a second subform? I ve heard of being able to
expand
a
record in continuous to a full single record so you can see the whole
thing
if interested. Would this be better.

Your opinion would be greatly appreciated. Thanks Dustin

:

So you have3 tables:
- ServiceWork, with ServiceWorkID primary key
- ServiceOrder, with ServiceOrderID primary key, and ServiceWork
foreign
key.
- PartsUsed, with ID primary key, and ServiceOrderID foreign key.

Your main form is bound to ServiceWork, and it has 2 subforms on it
(not
a
subform within a subform.)

Add a text box to the main form, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
Set its Name property to:
txtServiceOrderID
(Be sure to use the name of your subform control for ServiceOrder, and
the
name of the text box bound to ServiceOrderID for that name.)

Now set the Master Link Fields property of the PartsUsed subform to:
txtServiceOrderID

When you move record in the first subform, Access will update the text
box
on the main form, and will then reload the 2nd subform.

There is an example of this kind of form in the Northwind sample
database:
the Customer Orders form.

Oh, and if you have difficulty in filtering the main form by a field
in
the
subform, this may help:
http://allenbrowne.com/ser-28.html


I am trying to make a form that records service work on customers
units.
End
goal for me is to be able to locate the customer using his units
serial
number in combo box locator. Then in the first subform the service
order
of
can be made of what was done. And then finally go to the parts used
subform
and select the parts used.

I am stuck trying to get the parts used subform to link with the
service
order subform? Access will only let me connect the the Parts Used
sub
to
the
Main. How do I get the Parts Used Sub to follow the Service Order
Sub?

Any suggestions would be great! Thanks Dustin

 

[Dustin]

Ok, will try to find the problem. But while I am figuring out what happened I
have another post others don't know the answer to. The post is called Problem
with Toggle Button in General Questions. If you could read through what I am
trying to accomplish maybe you could show me how to do it or give me an idea
of a better way.

Thanks so much. Dustin

To fix it, you will have to track down what's causing this unusual behavior.

It could be the timer event of the form.
It could be code or a macro in one of the control's events.

If the subform is based on a query that involves multiple tables, it could
be due to using a field from the wrong table, or a Default Value in one of
the fields in the wrong table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hey I got it to work! But I have one problem. My Parts Used subform is a
datasheet and the first time I select a part it works but when I go and
select from the next drop down, it only gives me a split second to select
a
part. Then the cursor goes immeadately to the top left of the first
record
of the datasheet. Which happens to be the Child Link record.

How do I fix that? Thanks so much for your help!

So you have 3 tables:
- Customer, with CustomerID primary key
- ServiceOrder, with ServiceOrderID primary key, and CustomerID foreign
key;
- PartsUsed, with ServiceOrderID foreign key.

That is, you have a one to many relation:
- between Customer and ServiceOrder (based on CustomerID)
- between ServiceOrder and PartsUsed (based on ServiceOrderID.)

If that is so, you can have:
- main form bound to Customer table.
- a subform bound to ServiceOrder table.
- another subform bound to the PartsUsed table.

On the Customer form, place a text box, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
If you still get #Name, you need to change these name to match. Instead
of
ServiceOrder, use the name of your subform control. Also, make sure the
is a
text box in the subform for ServiceOrderID (even if you set its Visible
property to No.)

Once you get that working, you can then use the name of that text box in
the
Link Master Fields property of the 2nd subform control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Thanks for responding, I tried what you told me but I am having
problems
getting them to connect. It says name?? in the part used child link
record
when I am in form view. I think I might have mislead on what I want. So
I
will explain myself and you tell me whether or not I have the right
idea.

I want to have the main form based on the customer table and use a Find
combo box to pull up the customer and his information. I want to attach
the
Service Order Form as the first subform. I want it to be Continuous so
I
can
see all of the service that customer has had in the past. Then I want
to
be
able to select a certain service order and click on the next tab over
and
look at the Parts Used on that service order.

Is there a better way? Should I include the parts used on the Service
Order
form and forget about a second subform? I ve heard of being able to
expand
a
record in continuous to a full single record so you can see the whole
thing
if interested. Would this be better.

Your opinion would be greatly appreciated. Thanks Dustin

:

So you have3 tables:
- ServiceWork, with ServiceWorkID primary key
- ServiceOrder, with ServiceOrderID primary key, and ServiceWork
foreign
key.
- PartsUsed, with ID primary key, and ServiceOrderID foreign key.

Your main form is bound to ServiceWork, and it has 2 subforms on it
(not
a
subform within a subform.)

Add a text box to the main form, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
Set its Name property to:
txtServiceOrderID
(Be sure to use the name of your subform control for ServiceOrder, and
the
name of the text box bound to ServiceOrderID for that name.)

Now set the Master Link Fields property of the PartsUsed subform to:
txtServiceOrderID

When you move record in the first subform, Access will update the text
box
on the main form, and will then reload the 2nd subform.

There is an example of this kind of form in the Northwind sample
database:
the Customer Orders form.

Oh, and if you have difficulty in filtering the main form by a field
in
the
subform, this may help:
http://allenbrowne.com/ser-28.html


I am trying to make a form that records service work on customers
units.
End
goal for me is to be able to locate the customer using his units
serial
number in combo box locator. Then in the first subform the service
order
of
can be made of what was done. And then finally go to the parts used
subform
and select the parts used.

I am stuck trying to get the parts used subform to link with the
service
order subform? Access will only let me connect the the Parts Used
sub
to
the
Main. How do I get the Parts Used Sub to follow the Service Order
Sub?

Any suggestions would be great! Thanks Dustin

 

[SeaRox]

I used this method to link my subforms and it worked. Is there a way to make
it so that I can edit the text in these fields? I made sure all the forms
were set to allow edits.

So you have3 tables:
- ServiceWork, with ServiceWorkID primary key
- ServiceOrder, with ServiceOrderID primary key, and ServiceWork foreign
key.
- PartsUsed, with ID primary key, and ServiceOrderID foreign key.

Your main form is bound to ServiceWork, and it has 2 subforms on it (not a
subform within a subform.)

Add a text box to the main form, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
Set its Name property to:
txtServiceOrderID
(Be sure to use the name of your subform control for ServiceOrder, and the
name of the text box bound to ServiceOrderID for that name.)

Now set the Master Link Fields property of the PartsUsed subform to:
txtServiceOrderID

When you move record in the first subform, Access will update the text box
on the main form, and will then reload the 2nd subform.

There is an example of this kind of form in the Northwind sample database:
the Customer Orders form.

Oh, and if you have difficulty in filtering the main form by a field in the
subform, this may help:
http://allenbrowne.com/ser-28.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to make a form that records service work on customers units.
End
goal for me is to be able to locate the customer using his units serial
number in combo box locator. Then in the first subform the service order
of
can be made of what was done. And then finally go to the parts used
subform
and select the parts used.

I am stuck trying to get the parts used subform to link with the service
order subform? Access will only let me connect the the Parts Used sub to
the
Main. How do I get the Parts Used Sub to follow the Service Order Sub?

Any suggestions would be great! Thanks Dustin

 

[Ken Snell \(MVP\)]

Is your question about how you can edit the data in the subforms? If yes,
that is determined by using an updatable query as a subform's RecordSource.
What are you using as the subforms' RecordSource queries?

--

Ken Snell
<MS ACCESS MVP>

I used this method to link my subforms and it worked. Is there a way to
make
it so that I can edit the text in these fields? I made sure all the forms
were set to allow edits.

So you have3 tables:
- ServiceWork, with ServiceWorkID primary key
- ServiceOrder, with ServiceOrderID primary key, and ServiceWork foreign
key.
- PartsUsed, with ID primary key, and ServiceOrderID foreign key.

Your main form is bound to ServiceWork, and it has 2 subforms on it (not
a
subform within a subform.)

Add a text box to the main form, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
Set its Name property to:
txtServiceOrderID
(Be sure to use the name of your subform control for ServiceOrder, and
the
name of the text box bound to ServiceOrderID for that name.)

Now set the Master Link Fields property of the PartsUsed subform to:
txtServiceOrderID

When you move record in the first subform, Access will update the text
box
on the main form, and will then reload the 2nd subform.

There is an example of this kind of form in the Northwind sample
database:
the Customer Orders form.

Oh, and if you have difficulty in filtering the main form by a field in
the
subform, this may help:
http://allenbrowne.com/ser-28.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to make a form that records service work on customers
units.
End
goal for me is to be able to locate the customer using his units serial
number in combo box locator. Then in the first subform the service
order
of
can be made of what was done. And then finally go to the parts used
subform
and select the parts used.

I am stuck trying to get the parts used subform to link with the
service
order subform? Access will only let me connect the the Parts Used sub
to
the
Main. How do I get the Parts Used Sub to follow the Service Order Sub?

Any suggestions would be great! Thanks Dustin

 

[SeaRox]

Thanks Ken. I was trying to use the text box to edit the data in the subform
which I had no luck with. I created a second subform and linked them, making
sure they were using an updatable query and I can now edit the data in the
subforms. Now I have another problem. I created a button to add a new
record. When I click on the button a new record comes up just fine but when
I try to enter data into any of the fields I get the error message below.
(The key is an autonumbered field.)

"You can't assign a value to this object.
*The object may be a control on a read only form.
*The object may be on a form that is open in design view.
*The value may be too large for the field."

None of the three possibilities listed are true. I've checked the
properties on all the forms (main and subs) and they are all set to allow
adds, edits, etc. It does it even when it is the only window open. About
half of the fields are drop downs that I've been using for months so I know
the values are not too large.

Thanks for the help.
SeaRox

Is your question about how you can edit the data in the subforms? If yes,
that is determined by using an updatable query as a subform's RecordSource.
What are you using as the subforms' RecordSource queries?

--

Ken Snell
<MS ACCESS MVP>

I used this method to link my subforms and it worked. Is there a way to
make
it so that I can edit the text in these fields? I made sure all the forms
were set to allow edits.

So you have3 tables:
- ServiceWork, with ServiceWorkID primary key
- ServiceOrder, with ServiceOrderID primary key, and ServiceWork foreign
key.
- PartsUsed, with ID primary key, and ServiceOrderID foreign key.

Your main form is bound to ServiceWork, and it has 2 subforms on it (not
a
subform within a subform.)

Add a text box to the main form, and set its Control Source to:
=[ServiceOrder].[Form].[ServiceOrderID]
Set its Name property to:
txtServiceOrderID
(Be sure to use the name of your subform control for ServiceOrder, and
the
name of the text box bound to ServiceOrderID for that name.)

Now set the Master Link Fields property of the PartsUsed subform to:
txtServiceOrderID

When you move record in the first subform, Access will update the text
box
on the main form, and will then reload the 2nd subform.

There is an example of this kind of form in the Northwind sample
database:
the Customer Orders form.

Oh, and if you have difficulty in filtering the main form by a field in
the
subform, this may help:
http://allenbrowne.com/ser-28.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I am trying to make a form that records service work on customers
units.
End
goal for me is to be able to locate the customer using his units serial
number in combo box locator. Then in the first subform the service
order
of
can be made of what was done. And then finally go to the parts used
subform
and select the parts used.

I am stuck trying to get the parts used subform to link with the
service
order subform? Access will only let me connect the the Parts Used sub
to
the
Main. How do I get the Parts Used Sub to follow the Service Order Sub?

Any suggestions would be great! Thanks Dustin

 

[Ken Snell \(MVP\)]

We don't know what the subform's RecordSource query statement is, so it's
hard to give any suggestions. Post more details about the subform's design.
Do you get this same error if you use the Navigation button to move to a new
record, or does the error occur just when you click your "new record"
button? What code is running in the subform form's Current and BeforeInsert
events? What is the code that runs in the Click event of the button?

 

[SeaRox]

Here is a little more info on the whole picture. I have a main form and
two subforms, linked as described in this discussion. One subform is a
Single Form view. The other subform is a datasheet view. They both
have the same query as their RecordSource. The RecordSource is a just a
query. The query is a simple select query that has one condition (an
employee's name). It selects all the time card data for that employee.
The top subform (Single Form) is used to edit the data. The bottom
subform (Datasheet View) is basically a view/select screen. I did it
this way because the datasheet view is easier to navigate between
records but there is too much information for one screen in the
datasheetview. Even having the bottom scroll wasn't good enough because
there are memo fields that have long entries. Also since we track hours
by project, customer, account number, etc., etc. Basically our needs
were too complex for the Time and Billing example database. To be able
to pull the data for all those different things, the data has to be
exactly the same, so the majority of the fields are drop downs. Some
were extremely long so I added some code to the Single Form view form to
limit the number of entries they would see in the drop downs. (This
didn't seem to function properly in Datasheet view. When you would make
a selection it would change every entry in that column, not just in the
record you were working in.) Because there is so much overlap the
primary key for this data is a random autonumber.

To answer your questions:
**Do you get this same error if you use the Navigation button to move to
a new record, or does the error occur just when you click your "new
record" button? Yes

**What code is running in the subform form's Current and BeforeInsert
events?
Main - None.
Single Form - BeforeInsert: None
OnCurrent: (This is the code to limit the number of selections in their
combo box drop downs)

Private Sub Form_Current()
Me!cboCustomerSelect.Requery
Me!cboCodeSelect.Requery
End Sub

Datasheet View - None.

**What is the code that runs in the Click event of the button?

Private Sub AddHours_Click()
On Error GoTo Err_AddHours_Click


DoCmd.GoToRecord , , acNewRec

Exit_AddHours_Click:
Exit Sub

Err_AddHours_Click:
MsgBox Err.Description
Resume Exit_AddHours_Click

End Sub

I hope this helps clarify things.

SeaRox

 

[SeaRox]

** Clarification **
Do you get this same error if you use the Navigation button to move to
a new record, or does the error occur just when you click your "new
record" button?

It occurs no matter which method is used to create the new record.

 

[Ken Snell \(MVP\)]

My initial thought is that you don't have the correct field names in the
LinkChildFields and LinkMasterFields properties for the subform control that
holds the subform in which you're trying to do the editing, and thus the
linking field (the foreign key field) that would get its value from the main
form (the foreign key typically is the one that you would list in the
LinkChildFields property of the subform control) is not getting a value
when the record is being inserted, hence ACCESS displays the error that
you're seeing. Or, you've used the autonumber field as the "linking" field
in the subform, and your form is trying to assign a value to that field,
which is not allowed either.
--

Ken Snell
<MS ACCESS MVP>

Here is a little more info on the whole picture. I have a main form and
two subforms, linked as described in this discussion. One subform is a
Single Form view. The other subform is a datasheet view. They both
have the same query as their RecordSource. The RecordSource is a just a
query. The query is a simple select query that has one condition (an
employee's name). It selects all the time card data for that employee.
The top subform (Single Form) is used to edit the data. The bottom
subform (Datasheet View) is basically a view/select screen. I did it
this way because the datasheet view is easier to navigate between
records but there is too much information for one screen in the
datasheetview. Even having the bottom scroll wasn't good enough because
there are memo fields that have long entries. Also since we track hours
by project, customer, account number, etc., etc. Basically our needs
were too complex for the Time and Billing example database. To be able
to pull the data for all those different things, the data has to be
exactly the same, so the majority of the fields are drop downs. Some
were extremely long so I added some code to the Single Form view form to
limit the number of entries they would see in the drop downs. (This
didn't seem to function properly in Datasheet view. When you would make
a selection it would change every entry in that column, not just in the
record you were working in.) Because there is so much overlap the
primary key for this data is a random autonumber.

To answer your questions:
**Do you get this same error if you use the Navigation button to move to
a new record, or does the error occur just when you click your "new
record" button? Yes

**What code is running in the subform form's Current and BeforeInsert
events?
Main - None.
Single Form - BeforeInsert: None
OnCurrent: (This is the code to limit the number of selections in their
combo box drop downs)

Private Sub Form_Current()
Me!cboCustomerSelect.Requery
Me!cboCodeSelect.Requery
End Sub

Datasheet View - None.

**What is the code that runs in the Click event of the button?

Private Sub AddHours_Click()
On Error GoTo Err_AddHours_Click


DoCmd.GoToRecord , , acNewRec

Exit_AddHours_Click:
Exit Sub

Err_AddHours_Click:
MsgBox Err.Description
Resume Exit_AddHours_Click

End Sub

I hope this helps clarify things.

SeaRox

 

[SeaRox]

Based on what you said, I'm in a bind.

I did use the autonumber as the linking field. It is the only field that is
unique. If I use one of the other fields that is not unique as the linking
field only the first record with that entry is displayed/editable. Our
fields are so repetitive that even two to three of the fields may be exactly
the same in different records.

Can you propose another method to either link the forms or create new
records that would avoid this problem? Is there a VBA solution?

My initial thought is that you don't have the correct field names in the
LinkChildFields and LinkMasterFields properties for the subform control that
holds the subform in which you're trying to do the editing, and thus the
linking field (the foreign key field) that would get its value from the main
form (the foreign key typically is the one that you would list in the
LinkChildFields property of the subform control) is not getting a value
when the record is being inserted, hence ACCESS displays the error that
you're seeing. Or, you've used the autonumber field as the "linking" field
in the subform, and your form is trying to assign a value to that field,
which is not allowed either.
--

Ken Snell
<MS ACCESS MVP>

 

[John W. Vinson]

Based on what you said, I'm in a bind.

I did use the autonumber as the linking field. It is the only field that is
unique. If I use one of the other fields that is not unique as the linking
field only the first record with that entry is displayed/editable. Our
fields are so repetitive that even two to three of the fields may be exactly
the same in different records.

Can you propose another method to either link the forms or create new
records that would avoid this problem? Is there a VBA solution?

It's perfectly reasonable to use an autonumber as the Master Link Field - it's
done all the time.

The Child Link Field cannot be an autonumber, but it can be a Long Integer.
You can define a one to one relationship by making that field the child
table's Primary Key; the child record will be created with the long integer
inherited from the mainform's autonumber at the moment that data is entered
into the subform.

John W. Vinson [MVP]

 

[SeaRox]

John,
Thank you for taking the time to read this and respond.

I don't think I have a child table because both sub forms are displaying the
same information from the same table. The difference is that one is in
datasheet view and one is single form view. The datasheet view is easier to
navigate. The single form view is easier to use for making entries/editing.
Can I still do what you are proposing?

 

[John W. Vinson]

John,
Thank you for taking the time to read this and respond.

I don't think I have a child table because both sub forms are displaying the
same information from the same table. The difference is that one is in
datasheet view and one is single form view. The datasheet view is easier to
navigate. The single form view is easier to use for making entries/editing.
Can I still do what you are proposing?

The problem you're likely to have is that Access will disallow editing on one
form or the other - since both forms are (in principle) editable you have a
conflict, in that there are two forms simultaneously editing the same record.

I'd suggest using a Continuous Form (rather than a datasheet; you can make it
look very much like a datasheet if you wish) with a command button to pop open
a single form on the selected record.

John W. Vinson [MVP]