Issue with adding a new record to a bound subform

[Corey-g via AccessMonster.com]

Hi All,

I have a form (frmMain) and subform (sfrmSub), both are bound to the
appropriate tables, and they are linked on the key field "OrderID" (subform
control is named Subform). In the main form I have a combo box (bound to a
query) and button that I would like to use to have the user select the
desired item - and click the button. This would then add the item to the
subform using the following code:

Forms![frmMain]![Subform]!txtPRODUCT_NUMBER = Me.cboProducts.Column(1)
Forms![frmMain]![Subform]!txtPRODUCT_DESCRIPTION = Me.cboProducts.Column(2)

The problem is that I get a runtime error '2448' You can't assign a value to
this object. I have the allow additions and the data entry properties of the
subform set to Yes.

Any ideas on what I'm doing wrong?

TIA,

Corey

 

[Marshall Barton]

I have a form (frmMain) and subform (sfrmSub), both are bound to the
appropriate tables, and they are linked on the key field "OrderID" (subform
control is named Subform). In the main form I have a combo box (bound to a
query) and button that I would like to use to have the user select the
desired item - and click the button. This would then add the item to the
subform using the following code:

Forms![frmMain]![Subform]!txtPRODUCT_NUMBER = Me.cboProducts.Column(1)
Forms![frmMain]![Subform]!txtPRODUCT_DESCRIPTION = Me.cboProducts.Column(2)

The problem is that I get a runtime error '2448' You can't assign a value to
this object. I have the allow additions and the data entry properties of the
subform set to Yes.

Two things.

One, you should not normally save all those value from one
table to records in another table. The primary key is
sufficient to retrieve the values from the original table.

Two, you need to use the Form property to get to the
controls on a subform:

Forms!frmMain!Subform.Form!txtPRODUCT_NUMBER = ...

 

[Regan via AccessMonster.com]

Hi All,

Forms![frmMain]![Subform]!txtPRODUCT_NUMBER = Me.cboProducts.Column(1)
Forms![frmMain]![Subform]!txtPRODUCT_DESCRIPTION = Me.cboProducts.Column(2)

The problem is that I get a runtime error '2448' You can't assign a value to
this object. I have the allow additions and the data entry properties of the
subform set to Yes.

Any ideas on what I'm doing wrong?

Hi, What is the rowsource for the Combo Box, Is is Table/query or value
list? also the first column in a combo Box is Column(0). If you source is a
table and the product number is already in their you don't have to add
Product description. IF your combo box is a value list you'ld have to enter
product number and description through there

dim str as string
str = me.combobox.rowsource

Me.combobox.RowSource = str & ";" & Forms![frmMain]![Subform]!
txtPRODUCT_NUMBER & ";" & Forms![frmMain]![Subform]!txtPRODUCT_DESCRIPTION

end sub

Else if it is a table you'll have to insert it into there

Hope this helps

 

[Corey-g via AccessMonster.com]

Thanks for all the insight Marshall - it's now doing what I had wanted...

I think I needed your warning earlier (about altering code during debugging),
as I did have to create a new database and import everything from the old one
to get rid of the "Method 'Form' of object '_Subform' failed" error. So I
guess I had some corruption happening as well.

The code you gave me works great, and it will help alot more now that I see
what you did - because of the oracle tables being linked, I can use DAO
methods to work with the underlying data rather than ADO.

One question though - I am using ADO(X) to do the linking through code, does
it matter if I use both throught the app? I know I can, and I know I need to
typecast all the time so that I don't start getting mixed up, but other than
that is it pretty common?

Thanks again for the help, I appreciate it!

Corey
PS: Sorry for the delay, but I just got over a week long flu that shut down
my liver - scary...

 

[Marshall Barton]

Thanks for all the insight Marshall - it's now doing what I had wanted...

I think I needed your warning earlier (about altering code during debugging),
as I did have to create a new database and import everything from the old one
to get rid of the "Method 'Form' of object '_Subform' failed" error. So I
guess I had some corruption happening as well.

The code you gave me works great, and it will help alot more now that I see
what you did - because of the oracle tables being linked, I can use DAO
methods to work with the underlying data rather than ADO.

One question though - I am using ADO(X) to do the linking through code, does
it matter if I use both throught the app? I know I can, and I know I need to
typecast all the time so that I don't start getting mixed up, but other than
that is it pretty common?

Thanks again for the help, I appreciate it!

Corey
PS: Sorry for the delay, but I just got over a week long flu that shut down
my liver - scary...

I don't use ADO, so my comments are just general concepts.

I think(?) you can use either library for this kind of thing
(taking object model differences into account) and I don't
see where you have said anything that indicates a need for
you to use both. Linking tables certainly can be done using
DAO so, without advocating that you change existing, working
code, I would not use ADOX if linking were the only reason
for the reference.

Using both libraries is not rare, but not what I would call
common either. I'm not sure what you mean by "typecast all
the time". If you do want to use both libraries, I'm pretty
sure that you just need to disambiguate the objects in the
Dim statements:
Dim rs As ADODB.Recordset
Set rs = ...
or
Dim rs As DAO.Recordset
Set rs = ...
as needed.

Sorry to hear about your nasty illness, but It's good to
hear you are recovering from it.