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 the input Marshall, I had tried that already but it hadn't worked.
While trying again I found that even though the subform was suppposed to be
set to allow additions, when I had it open this was set to no. Seems that it
always reverts back to no...

The reason for storing 'double' the data rather than the key is that the key
will never change but the underlying data will - and I need to keep what it
was at that point (I doubt that makes sense). Maybe a little more of an
expaination:

There is a parts list - including stuff like a 'standard desktop' - and the
part number for this must never change even thought the actual machine type /
componenets change frequently. So when they select the item from the list -
I capture all of the details at that point in time. So later when the
underlying desktop model is changed, all of my old orders still show what was
actually selected.

Another problem I'm having (other than the form changing to not allow me to
add items) is that I wanted this to be a continuous form, and the user could
click the add button many times (for different parts of course). How do I
make sure that the process moves to the next record before doing the insert?
I had been trying to use the .Form.recordset.addnew but that didn't work.

 

[Marshall Barton]

Comments inline below.
--
Marsh
MVP [MS Access]

Thanks for the input Marshall, I had tried that already but it hadn't worked.
While trying again I found that even though the subform was suppposed to be
set to allow additions, when I had it open this was set to no. Seems that it
always reverts back to no...

I don't know what is causing that. Check to make sure the
subform's record source table/query is updatable. Also make
sure you do not have any code that is changing the
AllowAdditions or AllowEdits properties. Maybe some other
form or recordset is using the same data in an exclusive
mode??

The reason for storing 'double' the data rather than the key is that the key
will never change but the underlying data will - and I need to keep what it
was at that point (I doubt that makes sense). Maybe a little more of an
expaination:

There is a parts list - including stuff like a 'standard desktop' - and the
part number for this must never change even thought the actual machine type /
componenets change frequently. So when they select the item from the list -
I capture all of the details at that point in time. So later when the
underlying desktop model is changed, all of my old orders still show what was
actually selected.

Ahh, I understand now and that is the right thing to do
then. Sorry I jumped on it without knowing your reasons.

Another problem I'm having (other than the form changing to not allow me to
add items) is that I wanted this to be a continuous form, and the user could
click the add button many times (for different parts of course). How do I
make sure that the process moves to the next record before doing the insert?
I had been trying to use the .Form.recordset.addnew but that didn't work.

I think I need more details about what you are trying to do
with this. What "Add" button? And what does it do? What
does "next record" mean? How are you doing an "insert"?

 

[Corey-g via AccessMonster.com]

Hi Marshall, I have tried to address your suggestions below:

1) Check to make sure the subform's record source table/query is updatable.
- Yes it is - straight linked table - nothing special

2) Also make sure you do not have any code that is changing the
AllowAdditions or AllowEdits properties.
- Didn't, but with the form 'changing' this between design view and opening
I did decide to try:

IF Forms![frmMain]![Subform].form.allowadditions <> true then
Forms![frmMain]![Subform].form.allowadditions = True
End If

and now I'm getting another strange error -
runtime error '-2146500594' ( 800f000e):
Method 'Form' of object '_Subform' failed"

3) Maybe some other form or recordset is using the same data in an exclusive
mode??
- That shouldn't be the case - and I'm building this as a test so I don't
have anything else open

Basically I have the main form bound to an order info table. Once all of the
info is entered, the user then uses a combo box to select parts (items), set
the quantity, and click an 'Add' button (All on the main form). This would
then add the selected part info from the combo box to the first empty line in
the subform (hopefully in continuous form view). Then they can pick another
part, set the qty, and click add - to add to the next line in the subform.

I figured this was the way to go because of the data issue discussed earlier
with storing the data twice - the query for the combo box was easy and gave
the user everything they needed to see, and allowed me to insert that data
into the bound subform (to the order_items table).

4) What "Add" button? And what does it do?
- This was the button from the original post

5) What does "next record" mean?
- A new line in the continuous form

6) How are you doing an "insert"?
- I was hoping not to 'do' the insert - rather use the bound forms and let
Access take care of that

Thanks for the guidance,

Corey

 

[Marshall Barton]

Hi Marshall, I have tried to address your suggestions below:

1) Check to make sure the subform's record source table/query is updatable.
- Yes it is - straight linked table - nothing special

2) Also make sure you do not have any code that is changing the
AllowAdditions or AllowEdits properties.
- Didn't, but with the form 'changing' this between design view and opening
I did decide to try:

IF Forms![frmMain]![Subform].form.allowadditions <> true then
Forms![frmMain]![Subform].form.allowadditions = True
End If

and now I'm getting another strange error -
runtime error '-2146500594' ( 800f000e):
Method 'Form' of object '_Subform' failed"

3) Maybe some other form or recordset is using the same data in an exclusive
mode??
- That shouldn't be the case - and I'm building this as a test so I don't
have anything else open

Basically I have the main form bound to an order info table. Once all of the
info is entered, the user then uses a combo box to select parts (items), set
the quantity, and click an 'Add' button (All on the main form). This would
then add the selected part info from the combo box to the first empty line in
the subform (hopefully in continuous form view). Then they can pick another
part, set the qty, and click add - to add to the next line in the subform.

I figured this was the way to go because of the data issue discussed earlier
with storing the data twice - the query for the combo box was easy and gave
the user everything they needed to see, and allowed me to insert that data
into the bound subform (to the order_items table).

4) What "Add" button? And what does it do?
- This was the button from the original post

5) What does "next record" mean?
- A new line in the continuous form

6) How are you doing an "insert"?
- I was hoping not to 'do' the insert - rather use the bound forms and let
Access take care of that

I still have no idea why the subform has AllowAdditions set
to No. The error message when you try to change it back
suggests that the name of the subform **control** is not
[Subform]. Double check the control name, which may be
different from the name of the form object it is displaying.

As for the code in the Add button's Click event, I would use
something more like:

With Me.[Subform].Form.RecordsetClone
.AddNew
!PRODUCT_NUMBER = Me.cboProducts.Column(1)
!PRODUCT_DESCRIPTION = Me.cboProducts.Column(2)
.Update
Me.[Subform].Form.Bookmark = .LastModified
End With

Note that the **fields** (not controls) are being set.

I also want to pass on a warning. It is potentially
dangerous to edit a form's VBA module when the form is open
in any view other than design view. I.e. always switch back
to design view before editing code.

 

[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.