Can't assign a value?

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I have a subform with a combo box which has a source of Company Names and Company ID's

Col 1 Col 2
CompanyID CompanyName
1 Home Depot
2 Lowe's

When I select a company name from the combo box I want to store the company ID in a textbox on the subform which is named txtCompanyID. My code is:

txtCompanyID.Value = Me!cboCompanyName.Column(0)

and I have this code attached to the combobox's 'AfterUpdate' event.

I get an error 'You can't assign a value to this object'. I just don't understand why this would be a problem. Because I can't get this to work I keep adding the same company over and over again when what I want to do is knowing which company I can fill other fields on the subform with the company data. Any help on this would be appreciated.
 
Karen said:
I have a subform with a combo box which has a source of Company Names and Company ID's

Col 1 Col 2
CompanyID CompanyName
1 Home Depot
2 Lowe's

When I select a company name from the combo box I want to store the company ID in a textbox on the subform which is named txtCompanyID. My code is:

txtCompanyID.Value = Me!cboCompanyName.Column(0)

and I have this code attached to the combobox's 'AfterUpdate' event.

I get an error 'You can't assign a value to this object'. I just don't understand why this would be a problem. Because I can't get this to work I keep adding the same company over and over again when what I want to do is knowing which company I can fill other fields on the subform with the company data. Any help on this would be appreciated.
Click to expand...


I'm not sure this explains that error, but you do have to
tell Access where that text box is:

Me.subformcontrol.Form.txtCompanyID=Me!cboCompanyName.Column(0)
 
Karen said:
Thanks. I tried your suggestion and problem remains the same.
Click to expand...

Check the text box and make sure it dowsn't have anything in
its ControlSource property. Make sure you changed
subformcontrol to the appropriate name.

Not sure it matters, but I think you have a text box named
txtCompanyID on the main form too.
 
Hi Marshall,

Well, that does present a problem. The textbox is not repeated on the main
form but it does have a control source. This error message is generated
when a new record is being added---at that point the value in the textbox is
actually null as the record has not been saved.

Karen
 
Hi Karen,

Thank you for using MSDN Newsgroup! My name is Billy and I was reviewing your thread. I
will be assisting you on this issue.

Marshall has pointed out possible causes of your issue, however, the problem still exists on
your side. I performed a testing on my side and it works fine. Till now, I'm not able to
address the problem. Could you provide us with detailed steps so that we will not guess the
settings on your side? Thanks in advance!

If there is anything we can do to help you, please feel free to let us know. I'm here to be of
assistance.

Best regards,

Billy Yao
Microsoft Online Support
 
Karen said:
Well, that does present a problem. The textbox is not repeated on the main
form but it does have a control source. This error message is generated
when a new record is being added---at that point the value in the textbox is
actually null as the record has not been saved.
Click to expand...

Well, it the text box is a bound control, then you should
not be trying to set it from the main form. It's not at all
clear what you're trying to do, but if this is a foreign key
kind of field in the subform's table, then you should
probably be using the subform control's Link Master/Child
properties to tie the subform records to the current record
on the main form. Doing this will allow the subform to fill
in the field (and text box) automatically when a new record
is created.
--
Marsh
MVP [MS Access]


 
Hi Billy,

Here is best detailed explanation I can provide.

I have a main form which has information related to a person; e.g. name,
address, phone numbers.

On the main form I have a subform to list businesses that the person on the
main form might be associated with.

The person info is in a PersonTable, the businesses are in a CompanyTable.
I have a third LinkTable which has records that have the PersonID, the
BusinessID of all relationships.

On the main form the primary ID is the PersonID; on the subform the primary
ID is the CompanyID; the record source of the subform is a query that pulls
the info from the PersonTable and CompanyTable based on what is stored in
the LinkTable.

When I want to add another business that an individual may be linked to I
add a new record in the subform. I wanted the first control on the subform
to be a combobox which had the CompanyID's and CompanyNames. On the
'AfterUpdate' of the combobox I wanted to set the value of a textbox on the
subform which is named txtCompanyID; that is when I get the error I had
reported in the first post of this thread.

I hope this helps explain my confusion, why would I have trouble setting the
value of textbox based on a selection in a combobox.

Karen
 
Hi Karen,

Thanks for your detailed information! Your efforts make things clearer and now I can perform
a further testing to reproduce your issue under the most similar scenario of yours.

Based on my further testing, I find that I can successfully assign a value to the CompanyID
with the Update code until I re-define the CompanyID as a Read-Only type fields. In this
case, if I want to update the field, I got the Run-time error '2448' and the error message is the
same as yours.

I notice that CompanyID is the Primary Key in your CompanyTable, so I'd appreciate you
checking if this field is a read-only one (such as AutoNumber). If the CompanyID is an
AutoNumber field, we cannot update that column manually.

Thanks to Marshall's help, we've known that the text box is a bound control, which may
update that field and cause the error "Can't assign a value". A workaround is to change the
field type to a not read-only one (such as Number). However, you should make sure it will
not decrease the advantages AutoNumber brings to you.

Thank you for your cooperation Karen. If there is anything more I can do for you, please feel
free to let me know. I'm here willing to be of assistance.

Best regards,

Billy Yao
Microsoft Online Support
 
Karen said:
I have a main form which has information related to a person; e.g. name,
address, phone numbers.

On the main form I have a subform to list businesses that the person on the
main form might be associated with.

The person info is in a PersonTable, the businesses are in a CompanyTable.
I have a third LinkTable which has records that have the PersonID, the
BusinessID of all relationships.

On the main form the primary ID is the PersonID; on the subform the primary
ID is the CompanyID; the record source of the subform is a query that pulls
the info from the PersonTable and CompanyTable based on what is stored in
the LinkTable.

When I want to add another business that an individual may be linked to I
add a new record in the subform. I wanted the first control on the subform
to be a combobox which had the CompanyID's and CompanyNames. On the
'AfterUpdate' of the combobox I wanted to set the value of a textbox on the
subform which is named txtCompanyID; that is when I get the error I had
reported in the first post of this thread.

I hope this helps explain my confusion, why would I have trouble setting the
value of textbox based on a selection in a combobox.
Click to expand...


Ah ha! This is a many to many relationship. We've rellay
been wandering down the wrong path with this issue.

The core of your troubles is that the subform's record
source needs to be the Link table.

The subform's Link Master/Child properties would be the
PersonID field in the persons table and the PersonID in the
Link table.

The subform would then just have a combo box with its
RowSource set to
SELECT BusinessID, BusinessName
FROM CompanyTable
ORDER BY BusinessName

Set the combo's ColumnCount to 2, BoundColumn to 1 and
ColumnWidths to 0;2

That should be all it takes, get rid of the extra text box,
the complicated query for the subform and all that code
we've been trying to make work.
 
Marshall,

That did it BUT if I also want to pull a phone number from the Company table
don't I need the subform query to be based on all three tables, the Person
table, the Company table and the Link table?

Thanks a lot for tracking this down.

Karen
 
Karen said:
That did it BUT if I also want to pull a phone number from the Company table
don't I need the subform query to be based on all three tables, the Person
table, the Company table and the Link table?
Click to expand...

No!

The subform is there to link a person and a company, not to
edit data in the company table.

If you want to see some data from the company table, add the
desired fields (phone) to the combo box (and change its
ColumnCount to 3). Then you can add a text box to the
subform with the expression =cboCompany.Column(2) to
display the phone no. I suggest that you disable this text
box or do something so it looks different from the editable
textboxes.
 
Back
Top