Do not want subform to update

[JIM]

I keep getting the message: "The changes you requested to the table were not
sucessful because they would create duplicate values in the index, primary
key or relationship.....
I want to use the subform to update a file when it's a on frmCustomer but
when it's on frmWorkOrder I'm using the subform to just collect data. It
works fine if a work order is created for the first time but I get the above
message if I try to go back and change the data in the subform. Here's my
code:

Private Sub Form_Load()
On Error GoTo ErrFormLoad
'If CurrentProject.AllForms("frmClientBuildings").IsLoaded Or
Parent.Name = "frmWorkOrders" Then
Dim ctl As Control
If Parent.Name = "frmWorkOrders" Then 'do not allow edits unless
to check print contact check boxes
For Each ctl In Me.Controls
If ctl.Tag = "Static" Then ctl.Locked = True
Next ctl
Me.AllowAdditions = False
ElseIf Parent.Name = "frmCustomer" Then 'allow edits if subform of
frmCustomers
For Each ctl In Me.Controls
If ctl.Tag = "Static" Then ctl.Locked = False
Next ctl
Me.AllowEdits = True
Me.AllowAdditions = True
End If

ExitFormLoad:
Exit Sub
I thought the line Me.AllowAdditions = False would fix the problem but I'm
still getting the same error. Any thoughts.
TIA

 

[Marshall Barton]

I keep getting the message: "The changes you requested to the table were not
sucessful because they would create duplicate values in the index, primary
key or relationship.....
I want to use the subform to update a file when it's a on frmCustomer but
when it's on frmWorkOrder I'm using the subform to just collect data. It
works fine if a work order is created for the first time but I get the above
message if I try to go back and change the data in the subform. Here's my
code:

Private Sub Form_Load()
On Error GoTo ErrFormLoad
'If CurrentProject.AllForms("frmClientBuildings").IsLoaded Or
Parent.Name = "frmWorkOrders" Then
Dim ctl As Control
If Parent.Name = "frmWorkOrders" Then 'do not allow edits unless
to check print contact check boxes
For Each ctl In Me.Controls
If ctl.Tag = "Static" Then ctl.Locked = True
Next ctl
Me.AllowAdditions = False
ElseIf Parent.Name = "frmCustomer" Then 'allow edits if subform of
frmCustomers
For Each ctl In Me.Controls
If ctl.Tag = "Static" Then ctl.Locked = False
Next ctl
Me.AllowEdits = True
Me.AllowAdditions = True
End If

ExitFormLoad:
Exit Sub
I thought the line Me.AllowAdditions = False would fix the problem but I'm
still getting the same error.

IME, AllowEdits has no effect if the record is dirty so the
first thing I check for is code in the Current event that
sets the value of a bound control.

 

[JIM]

I'm not concerned with AllowEdits, that's working fine when subform is on
frmCustomer. When the subform is on frmWorkOrders I need a way to tell
subform not to try to add a record - I thought AllowAditions = False would do
it. When subform is on frmWorkOrders I click on a listbox and the fields are
populated. If I move to another record then come back to first record and
have to change something I get the error message. Do you have any ideas?
TIA

 

[Marshall Barton]

My comments also apply to AllowAdditions.

If you do not want to save any changes that have already
been made to the currrent record, then you need to undo the
change(s)

If Me.Dirty Then Me.Undo

 

[JIM]

Thanks Marshall for input. Perhaps I'm not describing my problem correctly.
I use a subform on a main Customer Master form to add and update related
records. Then I use the same subform on a Work Order form. I was hoping to
use the same subform to simply collect data to create and print a work order.
The message that comes up seems to indicate that by changing the record in
the subform I am trying to update the table behind the subform. I tried
using your suggestion but I get the same error message. When I change the
record in the subform is it warning me because it thinks I'm trying to
overwrite the record that was there first?
Thanks for any insight you can give.

My comments also apply to AllowAdditions.

If you do not want to save any changes that have already
been made to the currrent record, then you need to undo the
change(s)

If Me.Dirty Then Me.Undo
--
Marsh
MVP [MS Access]

I'm not concerned with AllowEdits, that's working fine when subform is on
frmCustomer. When the subform is on frmWorkOrders I need a way to tell
subform not to try to add a record - I thought AllowAditions = False would do
it. When subform is on frmWorkOrders I click on a listbox and the fields are
populated. If I move to another record then come back to first record and
have to change something I get the error message. Do you have any ideas?
TIA

 

[JIM]

Marshall, I should explain more: on the main form I have a bound list box and
when clicked the subform is populated with the correct record. The listbox
is filled from a combo box on the main form. As stated before this
arrangement works well to create a work the first time. If I go back to made
a change to the subform record then the error message is displayed. Do I
have to have an unbound list box? I've tried some many things now I can't
wrap my mind around this.
TIA

My comments also apply to AllowAdditions.

If you do not want to save any changes that have already
been made to the currrent record, then you need to undo the
change(s)

If Me.Dirty Then Me.Undo
--
Marsh
MVP [MS Access]

I'm not concerned with AllowEdits, that's working fine when subform is on
frmCustomer. When the subform is on frmWorkOrders I need a way to tell
subform not to try to add a record - I thought AllowAditions = False would do
it. When subform is on frmWorkOrders I click on a listbox and the fields are
populated. If I move to another record then come back to first record and
have to change something I get the error message. Do you have any ideas?
TIA

 

[Marshall Barton]

I'm still not following it. Are you saying that the subform
in frmWorkOrders is for searching? If so, it should be
unbound so there is no way for Access to try to save your
search criteria.

OTOH, maybe the frmWorkOrders instance of the subform is
only for selecting/viewing existing records and you do not
want to allow any changes. In this case, setting its
AllowEdits, AllowAdditions and AllowDeletions to False
should do what you want.

 

[JIM]

You got it. I just want to show the user that they have picked the correct
building and I'm updating the work order with the building number, a foreign
key, to reference when work order is printed. Where do you suggest I put the
allow commands? Do I have to use all three in order for it to work? I can
do that and it won't be a problem. Just want to cover all the bases while I
have your expertise.
TIA

I'm still not following it. Are you saying that the subform
in frmWorkOrders is for searching? If so, it should be
unbound so there is no way for Access to try to save your
search criteria.

OTOH, maybe the frmWorkOrders instance of the subform is
only for selecting/viewing existing records and you do not
want to allow any changes. In this case, setting its
AllowEdits, AllowAdditions and AllowDeletions to False
should do what you want.
--
Marsh
MVP [MS Access]

Marshall, I should explain more: on the main form I have a bound list box and
when clicked the subform is populated with the correct record. The listbox
is filled from a combo box on the main form. As stated before this
arrangement works well to create a work the first time. If I go back to made
a change to the subform record then the error message is displayed. Do I
have to have an unbound list box?

 

[Marshall Barton]

You should be able to that just about anywhere (as long as
the current record is not dirty). In your situation, the
first place I would try is the Open event:

Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True

Using all three statements will prevent any changes to the
form's recordset.

 

[JIM]

I'm assuming you meant = False for all the allow commands -which I tried in
the open event. This doesn't work for me because I am updating the subreport
with information from the listbox. And with no additions, edits or adds and
you are trying to create a new record on the main form, the subform goes
blank and when the subform is blank it can't receive info from the list box.
I'm sure there is a work around for this as it seems like a basic design that
many would need. Have I described it well?
TIA

You should be able to that just about anywhere (as long as
the current record is not dirty). In your situation, the
first place I would try is the Open event:

Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True

Using all three statements will prevent any changes to the
form's recordset.
--
Marsh
MVP [MS Access]

You got it. I just want to show the user that they have picked the correct
building and I'm updating the work order with the building number, a foreign
key, to reference when work order is printed. Where do you suggest I put the
allow commands? Do I have to use all three in order for it to work? I can
do that and it won't be a problem. Just want to cover all the bases while I
have your expertise.

 

[Marshall Barton]

I really do not understand what you are trying to do. I
thought you said you did not want to allow any changes, but
then you say you can't update a record that was added.
Somewhere else you said the main form's list box is used to
add data to the subform, but you get an error about
duplicate records. Somehow AllowAdditions comes into the
discussion as a way to prevent the error.

If you want to add a record and then be able to change it, I
don't see how anything we've discussed applies to your
problem. I still do not know how the list box creates a new
subform record or how you are trying to update a record that
was added.

At this point, I suspect that AllowAdditions is the wrong
idea and the problem is in what the list box is doing.
Maybe I can get a useful idea if you posted the code for the
list box???

 

[JIM]

I'm not explaining my situation. The entire pupose for the frmWorkOrders is
to add to and edit the Work Orders table. On frmWorkOrders I have a listbox
which is populated by a combo box on frmWorkOrders. When an entry is clicked
in the listbox then a subform is populated. And this is the subform table
that I don't want added to or edited, the subform is being used to display
the information only. The problem is that this same subform is used by
another form that does add to and edit the table behind it, it's a bound
subform. So when I try to change the item clicked on in the listbox to
change info that populates the subform -that's when I get the error message.
When I tried your suggestion and put code in to not allow additions, edits
and deletes; when I went to add a new Work Order the subform was completely
blank. I researched that and on Allen Browne's site it said if there are no
records for a subform and allow additions, edits and deletes is false then
the subform will be blank. I was just trying to follow best practices by
using as few forms as poosible. In my case, do you suggest another subform
or maybe a pop-up form?
TIA

I really do not understand what you are trying to do. I
thought you said you did not want to allow any changes, but
then you say you can't update a record that was added.
Somewhere else you said the main form's list box is used to
add data to the subform, but you get an error about
duplicate records. Somehow AllowAdditions comes into the
discussion as a way to prevent the error.

If you want to add a record and then be able to change it, I
don't see how anything we've discussed applies to your
problem. I still do not know how the list box creates a new
subform record or how you are trying to update a record that
was added.

At this point, I suspect that AllowAdditions is the wrong
idea and the problem is in what the list box is doing.
Maybe I can get a useful idea if you posted the code for the
list box???
--
Marsh
MVP [MS Access]

I'm assuming you meant = False for all the allow commands -which I tried in
the open event. This doesn't work for me because I am updating the subreport
with information from the listbox. And with no additions, edits or adds and
you are trying to create a new record on the main form, the subform goes
blank and when the subform is blank it can't receive info from the list box.
I'm sure there is a work around for this as it seems like a basic design that
many would need.

 

[JIM]

Hi Marshall, so sorry for the delay. We got slowed by year-end closing and
then I couldn't find the thread. Here's my code from the listbox and sql
statement:
Private Sub cboCustomerName_AfterUpdate()
Dim sql As String 'Fill listbox with Buildings
that apply to Customer when Customer is entered or updated
sql = "SELECT tblCustomer.CustomerName, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2, tblClientBuildings.JobAddress3,
tblClientBuildings.JobAddress4, tblClientBuildings.BuildingNo,
tblClientBuildings.ContactName1, tblClientBuildings.Phone1,
tblClientBuildings.Ext1, tblClientBuildings.Cell1, tblClientBuildings.Email1,
tblClientBuildings.ContactName2, tblClientBuildings.Phone2,
tblClientBuildings.Ext2, tblClientBuildings.Cell2, tblClientBuildings.Email2,
tblClientBuildings.ContactName3,tblClientBuildings.Phone3,
tblClientBuildings.Ext3, tblClientBuildings.Cell3, tblClientBuildings.Email3,
tblClientBuildings.SpecInstNotes, tblClientBuildings.RoofPlanLoc,
tblClientBuildings.PrintAll, tblClientBuildings.Reg FROM tblCustomer INNER
JOIN tblClientBuildings ON tblCustomer.CustomerName =
tblClientBuildings.CustomerName WHERE tblClientBuildings.CustomerName =
Forms!frmWorkOrders2!cboCustomerName ORDER BY tblCustomer.CustomerName,
tblClientBuildings.JobAddress1, tblClientBuildings.JobAddress2;"
Me!lstBuildings.RowSource = sql

End Sub

Private Sub lstBuildings_Click() 'if listbox is
clicked then move all data to subform
If Not IsNull(Me.lstBuildings) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtBuildingNo =
Me.lstBuildings.Column(5) 'Put Building No. in subform
Me.txtBuildNo = Me.lstBuildings.Column(5)
'Put Building No. in main form
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress1 =
Me.lstBuildings.Column(1) 'Put Job Address1 in subform
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtRegion =
Me.lstBuildings.Column(24) 'Put Region in subform
If Not IsNull(Me.lstBuildings.Column(2)) Then
'Put rest of data in subform, check for null field
first
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress2 =
Me.lstBuildings.Column(2)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(3)) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress3 =
Me.lstBuildings.Column(3)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(4)) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress4 =
Me.lstBuildings.Column(4)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress4 = ""
End If
End If
End Sub
The purpose of the form is to add Work Orders to tblWorkOrders but the
purpose of the subform is to simply display info from listbox. When user
clicks on combo box, the listbox fills, then when listbox is clicked, the
subform fills. The record source for the subform is tlbClientBuildings.
When I try to add a new Work Order, that is if I click on the listbox for a
new Work Order, or if I try to change info on a old Work Order by clicking on
the listbox, I get error message "The changes you requested to the table were
not sucessful because they would create duplicate values in the index,
primary key or relationship..... I think the problem is that VB thinks I'm
trying to change the record behind the subform - maybe the recordsource is
the problem but I don't know how to assign on the fly. Any help appreciated.

I really do not understand what you are trying to do. I
thought you said you did not want to allow any changes, but
then you say you can't update a record that was added.
Somewhere else you said the main form's list box is used to
add data to the subform, but you get an error about
duplicate records. Somehow AllowAdditions comes into the
discussion as a way to prevent the error.

If you want to add a record and then be able to change it, I
don't see how anything we've discussed applies to your
problem. I still do not know how the list box creates a new
subform record or how you are trying to update a record that
was added.

At this point, I suspect that AllowAdditions is the wrong
idea and the problem is in what the list box is doing.
Maybe I can get a useful idea if you posted the code for the
list box???
--
Marsh
MVP [MS Access]

I'm assuming you meant = False for all the allow commands -which I tried in
the open event. This doesn't work for me because I am updating the subreport
with information from the listbox. And with no additions, edits or adds and
you are trying to create a new record on the main form, the subform goes
blank and when the subform is blank it can't receive info from the list box.
I'm sure there is a work around for this as it seems like a basic design that
many would need.

 

[Marshall Barton]

I'm still confused. If the subform is only supposed to
display the same information as selected in the list box,
then there are several ways to address the situation. One
is to use code like you have BUT the subform must be unbound
(by setting its RecordSource to "" AND setting each of the
bound controls' ControlSource property to ""). This seems
more than a little clumsy,

Another way is to remove the code that sets the values in
the subform's bound controls and use the LinkMaster/Child
properties to sync the subform with the selected entry in
the list box. The list box's RowSource query only needs
enough fields to identify the existing work order. This may
be a little messy if it takes several fields to identify the
matching record.

A third way is to use the subform's Filter property to
restrict the subform to only the matching record. This
sounds like it would be ideal except that prior to A2007,
the Filter property had several bugs so it's only useful in
simple situations. The safe work around for the Filter
property problems is to set the subform's RecordSource to a
query that only selects the record that matches the list
box's selected record.

On the other hand, if you do not want to limit the subform
to the single matching record, then you can use code with
the FindFirst method to just navigate to the matching
record. With this approach, there is nothing to prevent
users from navigating to a different record and a continuous
or data sheet sunform would display other, unrelated
records.

Regardless of how you do it, the list box must not make the
subform record dirty unless you intend to add/edit the
record.
--
Marsh
MVP [MS Access]

Hi Marshall, so sorry for the delay. We got slowed by year-end closing and
then I couldn't find the thread. Here's my code from the listbox and sql
statement:
Private Sub cboCustomerName_AfterUpdate()
Dim sql As String 'Fill listbox with Buildings
that apply to Customer when Customer is entered or updated
sql = "SELECT tblCustomer.CustomerName, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2, tblClientBuildings.JobAddress3,
tblClientBuildings.JobAddress4, tblClientBuildings.BuildingNo,
tblClientBuildings.ContactName1, tblClientBuildings.Phone1,
tblClientBuildings.Ext1, tblClientBuildings.Cell1, tblClientBuildings.Email1,
tblClientBuildings.ContactName2, tblClientBuildings.Phone2,
tblClientBuildings.Ext2, tblClientBuildings.Cell2, tblClientBuildings.Email2,
tblClientBuildings.ContactName3,tblClientBuildings.Phone3,
tblClientBuildings.Ext3, tblClientBuildings.Cell3, tblClientBuildings.Email3,
tblClientBuildings.SpecInstNotes, tblClientBuildings.RoofPlanLoc,
tblClientBuildings.PrintAll, tblClientBuildings.Reg FROM tblCustomer INNER
JOIN tblClientBuildings ON tblCustomer.CustomerName =
tblClientBuildings.CustomerName WHERE tblClientBuildings.CustomerName =
Forms!frmWorkOrders2!cboCustomerName ORDER BY tblCustomer.CustomerName,
tblClientBuildings.JobAddress1, tblClientBuildings.JobAddress2;"
Me!lstBuildings.RowSource = sql

End Sub

Private Sub lstBuildings_Click() 'if listbox is
clicked then move all data to subform
If Not IsNull(Me.lstBuildings) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtBuildingNo =
Me.lstBuildings.Column(5) 'Put Building No. in subform
Me.txtBuildNo = Me.lstBuildings.Column(5)
'Put Building No. in main form
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress1 =
Me.lstBuildings.Column(1) 'Put Job Address1 in subform
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtRegion =
Me.lstBuildings.Column(24) 'Put Region in subform
If Not IsNull(Me.lstBuildings.Column(2)) Then
'Put rest of data in subform, check for null field
first
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress2 =
Me.lstBuildings.Column(2)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(3)) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress3 =
Me.lstBuildings.Column(3)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(4)) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress4 =
Me.lstBuildings.Column(4)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress4 = ""
End If
End If
End Sub
The purpose of the form is to add Work Orders to tblWorkOrders but the
purpose of the subform is to simply display info from listbox. When user
clicks on combo box, the listbox fills, then when listbox is clicked, the
subform fills. The record source for the subform is tlbClientBuildings.
When I try to add a new Work Order, that is if I click on the listbox for a
new Work Order, or if I try to change info on a old Work Order by clicking on
the listbox, I get error message "The changes you requested to the table were
not sucessful because they would create duplicate values in the index,
primary key or relationship..... I think the problem is that VB thinks I'm
trying to change the record behind the subform - maybe the recordsource is
the problem but I don't know how to assign on the fly. Any help appreciated.

I really do not understand what you are trying to do. I
thought you said you did not want to allow any changes, but
then you say you can't update a record that was added.
Somewhere else you said the main form's list box is used to
add data to the subform, but you get an error about
duplicate records. Somehow AllowAdditions comes into the
discussion as a way to prevent the error.

If you want to add a record and then be able to change it, I
don't see how anything we've discussed applies to your
problem. I still do not know how the list box creates a new
subform record or how you are trying to update a record that
was added.

At this point, I suspect that AllowAdditions is the wrong
idea and the problem is in what the list box is doing.
Maybe I can get a useful idea if you posted the code for the
list box???
--
Marsh
MVP [MS Access]

I'm assuming you meant = False for all the allow commands -which I tried in
the open event. This doesn't work for me because I am updating the subreport
with information from the listbox. And with no additions, edits or adds and
you are trying to create a new record on the main form, the subform goes
blank and when the subform is blank it can't receive info from the list box.
I'm sure there is a work around for this as it seems like a basic design that
many would need.

:
You should be able to that just about anywhere (as long as
the current record is not dirty). In your situation, the
first place I would try is the Open event:

Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True

Using all three statements will prevent any changes to the
form's recordset.


JIM wrote:
You got it. I just want to show the user that they have picked the correct
building and I'm updating the work order with the building number, a foreign
key, to reference when work order is printed. Where do you suggest I put the
allow commands? Do I have to use all three in order for it to work? I can
do that and it won't be a problem. Just want to cover all the bases while I
have your expertise.


:
I'm still not following it. Are you saying that the subform
in frmWorkOrders is for searching? If so, it should be
unbound so there is no way for Access to try to save your
search criteria.

OTOH, maybe the frmWorkOrders instance of the subform is
only for selecting/viewing existing records and you do not
want to allow any changes. In this case, setting its
AllowEdits, AllowAdditions and AllowDeletions to False
should do what you want.


JIM wrote:
Marshall, I should explain more: on the main form I have a bound list box and
when clicked the subform is populated with the correct record. The listbox
is filled from a combo box on the main form. As stated before this
arrangement works well to create a work the first time. If I go back to made
a change to the subform record then the error message is displayed. Do I
have to have an unbound list box?

:
My comments also apply to AllowAdditions.

If you do not want to save any changes that have already
been made to the currrent record, then you need to undo the
change(s)

If Me.Dirty Then Me.Undo


JIM wrote:
I'm not concerned with AllowEdits, that's working fine when subform is on
frmCustomer. When the subform is on frmWorkOrders I need a way to tell
subform not to try to add a record - I thought AllowAditions = False would do
it. When subform is on frmWorkOrders I click on a listbox and the fields are
populated. If I move to another record then come back to first record and
have to change something I get the error message.

JIM wrote:
I keep getting the message: "The changes you requested to the table were not
sucessful because they would create duplicate values in the index, primary
key or relationship.....
I want to use the subform to update a file when it's a on frmCustomer but
when it's on frmWorkOrder I'm using the subform to just collect data. It
works fine if a work order is created for the first time but I get the above
message if I try to go back and change the data in the subform. Here's my
code:

 

[JIM]

Hi Marshall, from your statement,
"Regardless of how you do it, the list box must not make the subform record
dirty unless you intend to add/edit the record." it seems I'm using the
subform incorrectly because I do need to dirty the form in the case of an
error, the info would have to be updated. I've made several attempts to use
unbound controls with some success using DLookup. Do you think that is the
best method?
I really appreciate your expertise.

I'm still confused. If the subform is only supposed to
display the same information as selected in the list box,
then there are several ways to address the situation. One
is to use code like you have BUT the subform must be unbound
(by setting its RecordSource to "" AND setting each of the
bound controls' ControlSource property to ""). This seems
more than a little clumsy,

Another way is to remove the code that sets the values in
the subform's bound controls and use the LinkMaster/Child
properties to sync the subform with the selected entry in
the list box. The list box's RowSource query only needs
enough fields to identify the existing work order. This may
be a little messy if it takes several fields to identify the
matching record.

A third way is to use the subform's Filter property to
restrict the subform to only the matching record. This
sounds like it would be ideal except that prior to A2007,
the Filter property had several bugs so it's only useful in
simple situations. The safe work around for the Filter
property problems is to set the subform's RecordSource to a
query that only selects the record that matches the list
box's selected record.

On the other hand, if you do not want to limit the subform
to the single matching record, then you can use code with
the FindFirst method to just navigate to the matching
record. With this approach, there is nothing to prevent
users from navigating to a different record and a continuous
or data sheet sunform would display other, unrelated
records.

Regardless of how you do it, the list box must not make the
subform record dirty unless you intend to add/edit the
record.
--
Marsh
MVP [MS Access]

Hi Marshall, so sorry for the delay. We got slowed by year-end closing and
then I couldn't find the thread. Here's my code from the listbox and sql
statement:
Private Sub cboCustomerName_AfterUpdate()
Dim sql As String 'Fill listbox with Buildings
that apply to Customer when Customer is entered or updated
sql = "SELECT tblCustomer.CustomerName, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2, tblClientBuildings.JobAddress3,
tblClientBuildings.JobAddress4, tblClientBuildings.BuildingNo,
tblClientBuildings.ContactName1, tblClientBuildings.Phone1,
tblClientBuildings.Ext1, tblClientBuildings.Cell1, tblClientBuildings.Email1,
tblClientBuildings.ContactName2, tblClientBuildings.Phone2,
tblClientBuildings.Ext2, tblClientBuildings.Cell2, tblClientBuildings.Email2,
tblClientBuildings.ContactName3,tblClientBuildings.Phone3,
tblClientBuildings.Ext3, tblClientBuildings.Cell3, tblClientBuildings.Email3,
tblClientBuildings.SpecInstNotes, tblClientBuildings.RoofPlanLoc,
tblClientBuildings.PrintAll, tblClientBuildings.Reg FROM tblCustomer INNER
JOIN tblClientBuildings ON tblCustomer.CustomerName =
tblClientBuildings.CustomerName WHERE tblClientBuildings.CustomerName =
Forms!frmWorkOrders2!cboCustomerName ORDER BY tblCustomer.CustomerName,
tblClientBuildings.JobAddress1, tblClientBuildings.JobAddress2;"
Me!lstBuildings.RowSource = sql

End Sub

Private Sub lstBuildings_Click() 'if listbox is
clicked then move all data to subform
If Not IsNull(Me.lstBuildings) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtBuildingNo =
Me.lstBuildings.Column(5) 'Put Building No. in subform
Me.txtBuildNo = Me.lstBuildings.Column(5)
'Put Building No. in main form
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress1 =
Me.lstBuildings.Column(1) 'Put Job Address1 in subform
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtRegion =
Me.lstBuildings.Column(24) 'Put Region in subform
If Not IsNull(Me.lstBuildings.Column(2)) Then
'Put rest of data in subform, check for null field
first
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress2 =
Me.lstBuildings.Column(2)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(3)) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress3 =
Me.lstBuildings.Column(3)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(4)) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress4 =
Me.lstBuildings.Column(4)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress4 = ""
End If
End If
End Sub
The purpose of the form is to add Work Orders to tblWorkOrders but the
purpose of the subform is to simply display info from listbox. When user
clicks on combo box, the listbox fills, then when listbox is clicked, the
subform fills. The record source for the subform is tlbClientBuildings.
When I try to add a new Work Order, that is if I click on the listbox for a
new Work Order, or if I try to change info on a old Work Order by clicking on
the listbox, I get error message "The changes you requested to the table were
not sucessful because they would create duplicate values in the index,
primary key or relationship..... I think the problem is that VB thinks I'm
trying to change the record behind the subform - maybe the recordsource is
the problem but I don't know how to assign on the fly. Any help appreciated.

I really do not understand what you are trying to do. I
thought you said you did not want to allow any changes, but
then you say you can't update a record that was added.
Somewhere else you said the main form's list box is used to
add data to the subform, but you get an error about
duplicate records. Somehow AllowAdditions comes into the
discussion as a way to prevent the error.

If you want to add a record and then be able to change it, I
don't see how anything we've discussed applies to your
problem. I still do not know how the list box creates a new
subform record or how you are trying to update a record that
was added.

At this point, I suspect that AllowAdditions is the wrong
idea and the problem is in what the list box is doing.
Maybe I can get a useful idea if you posted the code for the
list box???
--
Marsh
MVP [MS Access]


JIM wrote:
I'm assuming you meant = False for all the allow commands -which I tried in
the open event. This doesn't work for me because I am updating the subreport
with information from the listbox. And with no additions, edits or adds and
you are trying to create a new record on the main form, the subform goes
blank and when the subform is blank it can't receive info from the list box.
I'm sure there is a work around for this as it seems like a basic design that
many would need.

:
You should be able to that just about anywhere (as long as
the current record is not dirty). In your situation, the
first place I would try is the Open event:

Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True

Using all three statements will prevent any changes to the
form's recordset.


JIM wrote:
You got it. I just want to show the user that they have picked the correct
building and I'm updating the work order with the building number, a foreign
key, to reference when work order is printed. Where do you suggest I put the
allow commands? Do I have to use all three in order for it to work? I can
do that and it won't be a problem. Just want to cover all the bases while I
have your expertise.


:
I'm still not following it. Are you saying that the subform
in frmWorkOrders is for searching? If so, it should be
unbound so there is no way for Access to try to save your
search criteria.

OTOH, maybe the frmWorkOrders instance of the subform is
only for selecting/viewing existing records and you do not
want to allow any changes. In this case, setting its
AllowEdits, AllowAdditions and AllowDeletions to False
should do what you want.


JIM wrote:
Marshall, I should explain more: on the main form I have a bound list box and
when clicked the subform is populated with the correct record. The listbox
is filled from a combo box on the main form. As stated before this
arrangement works well to create a work the first time. If I go back to made
a change to the subform record then the error message is displayed. Do I
have to have an unbound list box?

:
My comments also apply to AllowAdditions.

If you do not want to save any changes that have already
been made to the currrent record, then you need to undo the
change(s)

If Me.Dirty Then Me.Undo


JIM wrote:
I'm not concerned with AllowEdits, that's working fine when subform is on
frmCustomer. When the subform is on frmWorkOrders I need a way to tell
subform not to try to add a record - I thought AllowAditions = False would do
it. When subform is on frmWorkOrders I click on a listbox and the fields are
populated. If I move to another record then come back to first record and
have to change something I get the error message.

JIM wrote:
I keep getting the message: "The changes you requested to the table were not
sucessful because they would create duplicate values in the index, primary
key or relationship.....
I want to use the subform to update a file when it's a on frmCustomer but
when it's on frmWorkOrder I'm using the subform to just collect data. It
works fine if a work order is created for the first time but I get the above
message if I try to go back and change the data in the subform. Here's my
code:

 

[Marshall Barton]

So, you do want to edit the subform record. Then, I think
you have to remove the code in the list box and replace it
with code to either use the LinkMaster/Child properties or
the Filter/RecordSource approach. Which field(s?) are
needed to uniquely identify the record?
--
Marsh
MVP [MS Access]

Hi Marshall, from your statement,
"Regardless of how you do it, the list box must not make the subform record
dirty unless you intend to add/edit the record." it seems I'm using the
subform incorrectly because I do need to dirty the form in the case of an
error, the info would have to be updated. I've made several attempts to use
unbound controls with some success using DLookup. Do you think that is the
best method?

I'm still confused. If the subform is only supposed to
display the same information as selected in the list box,
then there are several ways to address the situation. One
is to use code like you have BUT the subform must be unbound
(by setting its RecordSource to "" AND setting each of the
bound controls' ControlSource property to ""). This seems
more than a little clumsy,

Another way is to remove the code that sets the values in
the subform's bound controls and use the LinkMaster/Child
properties to sync the subform with the selected entry in
the list box. The list box's RowSource query only needs
enough fields to identify the existing work order. This may
be a little messy if it takes several fields to identify the
matching record.

A third way is to use the subform's Filter property to
restrict the subform to only the matching record. This
sounds like it would be ideal except that prior to A2007,
the Filter property had several bugs so it's only useful in
simple situations. The safe work around for the Filter
property problems is to set the subform's RecordSource to a
query that only selects the record that matches the list
box's selected record.

On the other hand, if you do not want to limit the subform
to the single matching record, then you can use code with
the FindFirst method to just navigate to the matching
record. With this approach, there is nothing to prevent
users from navigating to a different record and a continuous
or data sheet sunform would display other, unrelated
records.

Regardless of how you do it, the list box must not make the
subform record dirty unless you intend to add/edit the
record.
--
Marsh
MVP [MS Access]

Hi Marshall, so sorry for the delay. We got slowed by year-end closing and
then I couldn't find the thread. Here's my code from the listbox and sql
statement:
Private Sub cboCustomerName_AfterUpdate()
Dim sql As String 'Fill listbox with Buildings
that apply to Customer when Customer is entered or updated
sql = "SELECT tblCustomer.CustomerName, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2, tblClientBuildings.JobAddress3,
tblClientBuildings.JobAddress4, tblClientBuildings.BuildingNo,
tblClientBuildings.ContactName1, tblClientBuildings.Phone1,
tblClientBuildings.Ext1, tblClientBuildings.Cell1, tblClientBuildings.Email1,
tblClientBuildings.ContactName2, tblClientBuildings.Phone2,
tblClientBuildings.Ext2, tblClientBuildings.Cell2, tblClientBuildings.Email2,
tblClientBuildings.ContactName3,tblClientBuildings.Phone3,
tblClientBuildings.Ext3, tblClientBuildings.Cell3, tblClientBuildings.Email3,
tblClientBuildings.SpecInstNotes, tblClientBuildings.RoofPlanLoc,
tblClientBuildings.PrintAll, tblClientBuildings.Reg FROM tblCustomer INNER
JOIN tblClientBuildings ON tblCustomer.CustomerName =
tblClientBuildings.CustomerName WHERE tblClientBuildings.CustomerName =
Forms!frmWorkOrders2!cboCustomerName ORDER BY tblCustomer.CustomerName,
tblClientBuildings.JobAddress1, tblClientBuildings.JobAddress2;"
Me!lstBuildings.RowSource = sql

End Sub

Private Sub lstBuildings_Click() 'if listbox is
clicked then move all data to subform
If Not IsNull(Me.lstBuildings) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtBuildingNo =
Me.lstBuildings.Column(5) 'Put Building No. in subform
Me.txtBuildNo = Me.lstBuildings.Column(5)
'Put Building No. in main form
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress1 =
Me.lstBuildings.Column(1) 'Put Job Address1 in subform
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtRegion =
Me.lstBuildings.Column(24) 'Put Region in subform
If Not IsNull(Me.lstBuildings.Column(2)) Then
'Put rest of data in subform, check for null field
first
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress2 =
Me.lstBuildings.Column(2)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(3)) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress3 =
Me.lstBuildings.Column(3)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(4)) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress4 =
Me.lstBuildings.Column(4)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress4 = ""
End If
End If
End Sub
The purpose of the form is to add Work Orders to tblWorkOrders but the
purpose of the subform is to simply display info from listbox. When user
clicks on combo box, the listbox fills, then when listbox is clicked, the
subform fills. The record source for the subform is tlbClientBuildings.
When I try to add a new Work Order, that is if I click on the listbox for a
new Work Order, or if I try to change info on a old Work Order by clicking on
the listbox, I get error message "The changes you requested to the table were
not sucessful because they would create duplicate values in the index,
primary key or relationship..... I think the problem is that VB thinks I'm
trying to change the record behind the subform - maybe the recordsource is
the problem but I don't know how to assign on the fly. Any help appreciated.


:

I really do not understand what you are trying to do. I
thought you said you did not want to allow any changes, but
then you say you can't update a record that was added.
Somewhere else you said the main form's list box is used to
add data to the subform, but you get an error about
duplicate records. Somehow AllowAdditions comes into the
discussion as a way to prevent the error.

If you want to add a record and then be able to change it, I
don't see how anything we've discussed applies to your
problem. I still do not know how the list box creates a new
subform record or how you are trying to update a record that
was added.

At this point, I suspect that AllowAdditions is the wrong
idea and the problem is in what the list box is doing.
Maybe I can get a useful idea if you posted the code for the
list box???
--
Marsh
MVP [MS Access]


JIM wrote:
I'm assuming you meant = False for all the allow commands -which I tried in
the open event. This doesn't work for me because I am updating the subreport
with information from the listbox. And with no additions, edits or adds and
you are trying to create a new record on the main form, the subform goes
blank and when the subform is blank it can't receive info from the list box.
I'm sure there is a work around for this as it seems like a basic design that
many would need.

:
You should be able to that just about anywhere (as long as
the current record is not dirty). In your situation, the
first place I would try is the Open event:

Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True

Using all three statements will prevent any changes to the
form's recordset.


JIM wrote:
You got it. I just want to show the user that they have picked the correct
building and I'm updating the work order with the building number, a foreign
key, to reference when work order is printed. Where do you suggest I put the
allow commands? Do I have to use all three in order for it to work? I can
do that and it won't be a problem. Just want to cover all the bases while I
have your expertise.


:
I'm still not following it. Are you saying that the subform
in frmWorkOrders is for searching? If so, it should be
unbound so there is no way for Access to try to save your
search criteria.

OTOH, maybe the frmWorkOrders instance of the subform is
only for selecting/viewing existing records and you do not
want to allow any changes. In this case, setting its
AllowEdits, AllowAdditions and AllowDeletions to False
should do what you want.


JIM wrote:
Marshall, I should explain more: on the main form I have a bound list box and
when clicked the subform is populated with the correct record. The listbox
is filled from a combo box on the main form. As stated before this
arrangement works well to create a work the first time. If I go back to made
a change to the subform record then the error message is displayed. Do I
have to have an unbound list box?

:
My comments also apply to AllowAdditions.

If you do not want to save any changes that have already
been made to the currrent record, then you need to undo the
change(s)

If Me.Dirty Then Me.Undo


JIM wrote:
I'm not concerned with AllowEdits, that's working fine when subform is on
frmCustomer. When the subform is on frmWorkOrders I need a way to tell
subform not to try to add a record - I thought AllowAditions = False would do
it. When subform is on frmWorkOrders I click on a listbox and the fields are
populated. If I move to another record then come back to first record and
have to change something I get the error message.

JIM wrote:
I keep getting the message: "The changes you requested to the table were not
sucessful because they would create duplicate values in the index, primary
key or relationship.....
I want to use the subform to update a file when it's a on frmCustomer but
when it's on frmWorkOrder I'm using the subform to just collect data. It
works fine if a work order is created for the first time but I get the above
message if I try to go back and change the data in the subform. Here's my
code:

 

[JIM]

Thanks Marshall for staying with me. I found another way to do this. The
subform was removed and all controls that were on subform are now unbound.
The controls in that section are for display only but I needed the ability to
change the displayed info in order to update a bound field on the main form.
It's working perfect. You really did help me a lot-you got me thinking in
the right direction.

For those interested, On Current event of form, if New Record then I null
out all controls, else (if New Record) all controls are assigned a DLookup
result. It's working perfect.
Thanks again.

So, you do want to edit the subform record. Then, I think
you have to remove the code in the list box and replace it
with code to either use the LinkMaster/Child properties or
the Filter/RecordSource approach. Which field(s?) are
needed to uniquely identify the record?
--
Marsh
MVP [MS Access]

Hi Marshall, from your statement,
"Regardless of how you do it, the list box must not make the subform record
dirty unless you intend to add/edit the record." it seems I'm using the
subform incorrectly because I do need to dirty the form in the case of an
error, the info would have to be updated. I've made several attempts to use
unbound controls with some success using DLookup. Do you think that is the
best method?

I'm still confused. If the subform is only supposed to
display the same information as selected in the list box,
then there are several ways to address the situation. One
is to use code like you have BUT the subform must be unbound
(by setting its RecordSource to "" AND setting each of the
bound controls' ControlSource property to ""). This seems
more than a little clumsy,

Another way is to remove the code that sets the values in
the subform's bound controls and use the LinkMaster/Child
properties to sync the subform with the selected entry in
the list box. The list box's RowSource query only needs
enough fields to identify the existing work order. This may
be a little messy if it takes several fields to identify the
matching record.

A third way is to use the subform's Filter property to
restrict the subform to only the matching record. This
sounds like it would be ideal except that prior to A2007,
the Filter property had several bugs so it's only useful in
simple situations. The safe work around for the Filter
property problems is to set the subform's RecordSource to a
query that only selects the record that matches the list
box's selected record.

On the other hand, if you do not want to limit the subform
to the single matching record, then you can use code with
the FindFirst method to just navigate to the matching
record. With this approach, there is nothing to prevent
users from navigating to a different record and a continuous
or data sheet sunform would display other, unrelated
records.

Regardless of how you do it, the list box must not make the
subform record dirty unless you intend to add/edit the
record.
--
Marsh
MVP [MS Access]


JIM wrote:

Hi Marshall, so sorry for the delay. We got slowed by year-end closing and
then I couldn't find the thread. Here's my code from the listbox and sql
statement:
Private Sub cboCustomerName_AfterUpdate()
Dim sql As String 'Fill listbox with Buildings
that apply to Customer when Customer is entered or updated
sql = "SELECT tblCustomer.CustomerName, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2, tblClientBuildings.JobAddress3,
tblClientBuildings.JobAddress4, tblClientBuildings.BuildingNo,
tblClientBuildings.ContactName1, tblClientBuildings.Phone1,
tblClientBuildings.Ext1, tblClientBuildings.Cell1, tblClientBuildings.Email1,
tblClientBuildings.ContactName2, tblClientBuildings.Phone2,
tblClientBuildings.Ext2, tblClientBuildings.Cell2, tblClientBuildings.Email2,
tblClientBuildings.ContactName3,tblClientBuildings.Phone3,
tblClientBuildings.Ext3, tblClientBuildings.Cell3, tblClientBuildings.Email3,
tblClientBuildings.SpecInstNotes, tblClientBuildings.RoofPlanLoc,
tblClientBuildings.PrintAll, tblClientBuildings.Reg FROM tblCustomer INNER
JOIN tblClientBuildings ON tblCustomer.CustomerName =
tblClientBuildings.CustomerName WHERE tblClientBuildings.CustomerName =
Forms!frmWorkOrders2!cboCustomerName ORDER BY tblCustomer.CustomerName,
tblClientBuildings.JobAddress1, tblClientBuildings.JobAddress2;"
Me!lstBuildings.RowSource = sql

End Sub

Private Sub lstBuildings_Click() 'if listbox is
clicked then move all data to subform
If Not IsNull(Me.lstBuildings) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtBuildingNo =
Me.lstBuildings.Column(5) 'Put Building No. in subform
Me.txtBuildNo = Me.lstBuildings.Column(5)
'Put Building No. in main form
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress1 =
Me.lstBuildings.Column(1) 'Put Job Address1 in subform
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtRegion =
Me.lstBuildings.Column(24) 'Put Region in subform
If Not IsNull(Me.lstBuildings.Column(2)) Then
'Put rest of data in subform, check for null field
first
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress2 =
Me.lstBuildings.Column(2)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(3)) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress3 =
Me.lstBuildings.Column(3)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(4)) Then
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress4 =
Me.lstBuildings.Column(4)
Else
Forms.frmWorkOrders2.sfrmClientBuildings.Form.txtJobAddress4 = ""
End If
End If
End Sub
The purpose of the form is to add Work Orders to tblWorkOrders but the
purpose of the subform is to simply display info from listbox. When user
clicks on combo box, the listbox fills, then when listbox is clicked, the
subform fills. The record source for the subform is tlbClientBuildings.
When I try to add a new Work Order, that is if I click on the listbox for a
new Work Order, or if I try to change info on a old Work Order by clicking on
the listbox, I get error message "The changes you requested to the table were
not sucessful because they would create duplicate values in the index,
primary key or relationship..... I think the problem is that VB thinks I'm
trying to change the record behind the subform - maybe the recordsource is
the problem but I don't know how to assign on the fly. Any help appreciated.


:

I really do not understand what you are trying to do. I
thought you said you did not want to allow any changes, but
then you say you can't update a record that was added.
Somewhere else you said the main form's list box is used to
add data to the subform, but you get an error about
duplicate records. Somehow AllowAdditions comes into the
discussion as a way to prevent the error.

If you want to add a record and then be able to change it, I
don't see how anything we've discussed applies to your
problem. I still do not know how the list box creates a new
subform record or how you are trying to update a record that
was added.

At this point, I suspect that AllowAdditions is the wrong
idea and the problem is in what the list box is doing.
Maybe I can get a useful idea if you posted the code for the
list box???
--
Marsh
MVP [MS Access]


JIM wrote:
I'm assuming you meant = False for all the allow commands -which I tried in
the open event. This doesn't work for me because I am updating the subreport
with information from the listbox. And with no additions, edits or adds and
you are trying to create a new record on the main form, the subform goes
blank and when the subform is blank it can't receive info from the list box.
I'm sure there is a work around for this as it seems like a basic design that
many would need.

:
You should be able to that just about anywhere (as long as
the current record is not dirty). In your situation, the
first place I would try is the Open event:

Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True

Using all three statements will prevent any changes to the
form's recordset.


JIM wrote:
You got it. I just want to show the user that they have picked the correct
building and I'm updating the work order with the building number, a foreign
key, to reference when work order is printed. Where do you suggest I put the
allow commands? Do I have to use all three in order for it to work? I can
do that and it won't be a problem. Just want to cover all the bases while I
have your expertise.


:
I'm still not following it. Are you saying that the subform
in frmWorkOrders is for searching? If so, it should be
unbound so there is no way for Access to try to save your
search criteria.

OTOH, maybe the frmWorkOrders instance of the subform is
only for selecting/viewing existing records and you do not
want to allow any changes. In this case, setting its
AllowEdits, AllowAdditions and AllowDeletions to False
should do what you want.


JIM wrote:
Marshall, I should explain more: on the main form I have a bound list box and
when clicked the subform is populated with the correct record. The listbox
is filled from a combo box on the main form. As stated before this
arrangement works well to create a work the first time. If I go back to made
a change to the subform record then the error message is displayed. Do I
have to have an unbound list box?

:
My comments also apply to AllowAdditions.

If you do not want to save any changes that have already
been made to the currrent record, then you need to undo the
change(s)

If Me.Dirty Then Me.Undo


JIM wrote:
I'm not concerned with AllowEdits, that's working fine when subform is on
frmCustomer. When the subform is on frmWorkOrders I need a way to tell
subform not to try to add a record - I thought AllowAditions = False would do
it. When subform is on frmWorkOrders I click on a listbox and the fields are
populated. If I move to another record then come back to first record and
have to change something I get the error message.

JIM wrote:
I keep getting the message: "The changes you requested to the table were not
sucessful because they would create duplicate values in the index, primary
key or relationship.....
I want to use the subform to update a file when it's a on frmCustomer but
when it's on frmWorkOrder I'm using the subform to just collect data. It
works fine if a work order is created for the first time but I get the above
message if I try to go back and change the data in the subform. Here's my
code: