Error 2448 from 2nd level Subform

[BlakeD]

If you've seen this on one of the more specialized groups, I
appologize. Just searching for an answer.

To head off the warnings:
Yes, I am aware of Access's "issues" with GUID's. Unfortunately, I
have need of using them. Changing to an autonumber integer is not a
valid solution in this scenario.

Form names and control names have been altered for simplicity.


Scenario:
Main_Form is an unbound form containing Main_Listbox.
Main_Listbox is unbound, with it's record source being a query pulling
data from tbl_Clients. It exposes the table's PK (an autonumber GUID).

First_Subform is an unbound subform containing First_Listbox. There is

no Master/Child relationship established.
First_Listbox is unbound, with it's record source being a query
pulling data from tbl_Life_Factors where Client_GUID =
[forms]![Main_Form].[Main_Listbox]. It exposes the table's PK
(RECORD_ID, also an autonumber GUID).
The tbl_Clients and tbl_Life_Factors are in a one-to-many with
tbl_Client's PK being an FK in tbl_Life_Factors.
Second_Subform is a bound subform inside First_Subform. There is a
Master (First_Listbox) / Child (RECORD_ID) relationship between the
two. It is a single-form form-view of all fields in the record.


Additions and Edits are turned off by default at all levels.


Problem:
Once a client record is selected on Main_Listbox, First_Listbox
populates automatically with all the associated life-factor records.
The first record is automatically selected and displayed on
Second_Subform. I am adding a button on the Main_Form that will allow
a user to add a new Life_Factors record. The subform opens to a blank
record, with the foreign key already carried over (the field on the
form has a default value of
"=Guidfromstring([Forms]![Main_Form]![Main_Listbox])") and the
RECORD_ID showing "(Autonumber)".
It works flawlessly. Except - When you first enter a value into in the

Second_Subform (doesn't matter what field), it throws an error 2448:
"You can't assign a value to this object." It is coming from the
(Autonumber) placeholder being replaced by an actual GUID as far as I
can tell.


Help Needed:
I either need to find out why it's objecting to the creation of the
record's autonumber, or I need to trap the error and shut it the hell
up. That's the kicker - once you click on the error, the record can be

SAVED! It works. It just fusses. =P


Code:
Main_Form
Add_Record_Button_Click()


Forms!Main_Form.First_Subform.Form.Second_Subform.Form.AddLifeFactor


Second_Subform
Public Sub AddLifeFactor()
On Error GoTo Error_Handler_AddLifeFactor


'Make Form Add-able
With Me
.Parent.Form.AllowAdditions = True
.Parent.Form.AllowEdits = True
.Form.AllowAdditions = True
.Form.AllowEdits = True
.Form.DataEntry = True
End With


'Set Focus to Life Factors Listbox, set it to no record selected
Me.Parent.Controls("First_Listbox").SetFocus
Me.Parent.Controls("First_Listbox").ListIndex = -1


'Give user visual feedback of change in editability of record
'Change background color of form detail area
Me.Detail.BackColor = 12624058
'Change background color of labels
Newcolor = 8212851
ToggleLabels (Newcolor)


'Disable Master Client list to prevent record changing during
addition
Me.Parent.Controls("Second_Subform").SetFocus
Me.Parent.Parent.Controls("Main_Listbox").Enabled = False


Me.Parent.Controls("First_Listbox").Enabled = False


Exit_AddLifeFactor:
Exit Sub


Error_Handler_AddLifeFactor:
MsgBox Err.Description
End Sub

 

[Douglas J Steele]

You don't assign values to Autonumber fields: Access generates them for you.

If the value is supposed to be a foreign key to the field that's got the
GUID as its ID, it shouldn't be an Autonumber field.

Or have I misinterpretted what you're doing?

You do know that Autonumbers are capable of generation GUIDs I hope. I also
assume you've read what MichKa has to say at
http://www.trigeminal.com/usenet/usenet011.asp

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If you've seen this on one of the more specialized groups, I
appologize. Just searching for an answer.

To head off the warnings:
Yes, I am aware of Access's "issues" with GUID's. Unfortunately, I
have need of using them. Changing to an autonumber integer is not a
valid solution in this scenario.

Form names and control names have been altered for simplicity.


Scenario:
Main_Form is an unbound form containing Main_Listbox.
Main_Listbox is unbound, with it's record source being a query pulling
data from tbl_Clients. It exposes the table's PK (an autonumber GUID).

First_Subform is an unbound subform containing First_Listbox. There is

no Master/Child relationship established.
First_Listbox is unbound, with it's record source being a query
pulling data from tbl_Life_Factors where Client_GUID =
[forms]![Main_Form].[Main_Listbox]. It exposes the table's PK
(RECORD_ID, also an autonumber GUID).
The tbl_Clients and tbl_Life_Factors are in a one-to-many with
tbl_Client's PK being an FK in tbl_Life_Factors.
Second_Subform is a bound subform inside First_Subform. There is a
Master (First_Listbox) / Child (RECORD_ID) relationship between the
two. It is a single-form form-view of all fields in the record.


Additions and Edits are turned off by default at all levels.


Problem:
Once a client record is selected on Main_Listbox, First_Listbox
populates automatically with all the associated life-factor records.
The first record is automatically selected and displayed on
Second_Subform. I am adding a button on the Main_Form that will allow
a user to add a new Life_Factors record. The subform opens to a blank
record, with the foreign key already carried over (the field on the
form has a default value of
"=Guidfromstring([Forms]![Main_Form]![Main_Listbox])") and the
RECORD_ID showing "(Autonumber)".
It works flawlessly. Except - When you first enter a value into in the

Second_Subform (doesn't matter what field), it throws an error 2448:
"You can't assign a value to this object." It is coming from the
(Autonumber) placeholder being replaced by an actual GUID as far as I
can tell.


Help Needed:
I either need to find out why it's objecting to the creation of the
record's autonumber, or I need to trap the error and shut it the hell
up. That's the kicker - once you click on the error, the record can be

SAVED! It works. It just fusses. =P


Code:
Main_Form
Add_Record_Button_Click()


Forms!Main_Form.First_Subform.Form.Second_Subform.Form.AddLifeFactor


Second_Subform
Public Sub AddLifeFactor()
On Error GoTo Error_Handler_AddLifeFactor


'Make Form Add-able
With Me
.Parent.Form.AllowAdditions = True
.Parent.Form.AllowEdits = True
.Form.AllowAdditions = True
.Form.AllowEdits = True
.Form.DataEntry = True
End With


'Set Focus to Life Factors Listbox, set it to no record selected
Me.Parent.Controls("First_Listbox").SetFocus
Me.Parent.Controls("First_Listbox").ListIndex = -1


'Give user visual feedback of change in editability of record
'Change background color of form detail area
Me.Detail.BackColor = 12624058
'Change background color of labels
Newcolor = 8212851
ToggleLabels (Newcolor)


'Disable Master Client list to prevent record changing during
addition
Me.Parent.Controls("Second_Subform").SetFocus
Me.Parent.Parent.Controls("Main_Listbox").Enabled = False


Me.Parent.Controls("First_Listbox").Enabled = False


Exit_AddLifeFactor:
Exit Sub


Error_Handler_AddLifeFactor:
MsgBox Err.Description
End Sub

 

[BlakeD]

I'm not assigning any values to the autonumber fields via code or via
user input. I'm allowing Access to do it.

The tbl_Life_Factors will have 2 GUIDS per record - One (RECORD_ID) is
an autonumber and the PK of the table. The second is the foreign key
CLIENT_ID that comes from tbl_CLIENTS. It is simply a number field in
tbl_Life_Factors, and is an autonumber in tbl_CLIENTS.

And yes, I've read what MichKA has to say on replication id's numerous
times. Let's just say that my choice about their usage is dictated
from "on high".

 

[Douglas J. Steele]

You said you've provided a Default Value for the field. That attempts to
assign a value to it.

 

[BlakeD]

It's providing a default value for the Client_ID field that will be
entered into tbl_Life_Factors. It is a foreign key from tbl_Clients.
On tbl_Clients, it's an autonumber, but where I'm giving a default
value for it, it's just a standard number field (type rep.id). The
value I'm assigning to it I am explicitly getting a value form
tbl_Clients.

The error does not appear to be stemming from the default value being
entered into the client_ID (I've removed the default value and left it
a combo box with no resulting change in behavior). Rather, it appears
to stem from when the new record is actually instantiated by entering
in data. Access doesn't generate the new record GUID when you tell it
to create a new record. Instead, Access is automatically generating
the autonumber GUID for the new record on tbl_Life_Factors when I enter
my first piece of data onto the record. You can see this behavior if
you open a table with an autonumber PK and begin to fill out a new
record. It's this auto-generation that appears to be the problem.
While the autogeneration of a GUID is EXACTLY what Access is supposed
to do, it's also what is causing the error and why I'm seeking help
here. I don't know why it's objecting to this process since it's
default behavior.

 

[BlakeD]

Bump - Anyone?

 

[BlakeD]

For anyone who reads back this far, or who finds it via search:

I solved the problem by setting the subform.LinkChildField and
subform.LinkMasterField to a blank.

Once this was done, the record could be saved without error, and after
the save, you should be able to reassign the linking values.