Solved OpenArge error "You can't assign a value to this object" (1 Viewer)

[nashaz]

I have seen another post where deleting the target control fixed the issue, but it hasn't worked for me unfortunately so here goes.

I have a combo box with PositionID, PositionName on a form with a listbox below which shows related training record to that PositionID. I have another form, a target form, which can be used to add further records in the listbox for that PositionID, if AddBtn is clicked. I have placed a textbox called PositionID in the form footer in the form footer and a combobox called TrainingName. This is so that I can add multiple trainings at once, to one PositionID. I have the following codes:

On the source form:

Private Sub AddBtn_Click()
    docmd.openform "frmTraining" , , , , , acFormAdd, Me.PositionID
End Sub

On the target form I have:

Private Sub  Form_Open(Cancel As Integer)
    Me.PositionID = Me.OpenArgs
End Sub

I get the following error when I click on AddBtn:

***Edit: I have just figured out that if I move to a new record in the target form, the PositionID in the footer goes back to 0. Is there a way to keep that same?*** Thanks

 

[bob fitz]

I'm having trouble trying to visualise your form. Can you post a copy of the db.

 

[Gasman]

Do you have an expression on the control source of the target form control?
Generally I use the Load event for setting form controls.

 

[nashaz]

I'm having trouble trying to visualise your form. Can you post a copy of the db.

I will have to edit a lot of things to post the db, unfortunately, but here are the images.

Source form:

Target form:

the textbox in the footer showing 103 is the PositionID from source form i.e., First Aider.

 

[nashaz]

Do you have an expression on the control source of the target form control?
Generally I use the Load event for setting form controls.

Yes. Its bound to the table where the record for role vs training will be saved

 

[nashaz]

You are trying to save to an auto number. Not what you want to do.

113 is not the PK for the table behind these forms. 113 is rather an FK

 

[bastanu]

Try to use the DefaultValue property instead:
Me.PositionID.DefaultValue = Me.OpenArgs
That should also work for subsequent new records in the same session.
Cheers,

 

[Pat Hartman]

The Open event happens before the Load event so there is no data in the form at this point. I think if the field you are trying to populate is bound, that is what causes this error.

You can use the Default property as @bastanu suggested OR you can use the form's BeforeInsert event rather than the Open event. The BeforeInsert event runs ONCE for each new record so all new records will end up with the correct FK (which would NOT happen if you used the Open event or the load event since they run only ONCE ever, rather than for each new record added). The other advantage of the BeforeInsert event is that it doesn't run until the user types something into the for so your code doesn't dirty the form as it would if you put the code in a different event.

 

[Cronk]

in #1, the code used
docmd.openform "frmTraining" , , , , , acFormAdd, Me.PositionID
has incorrect syntax

Use
docmd.openform "frmTraining" , , , , acFormAdd , , Me.PositionID

That is 4 commas before and 2 commas after, the acFormAdd rather than 5 and one

 

[nashaz]

Try to use the DefaultValue property instead:
Me.PositionID.DefaultValue = Me.OpenArgs
That should also work for subsequent new records in the same session.
Cheers,

I get compile error that .DefaultValue method method or data member not found

 

[nashaz]

The Open event happens before the Load event so there is no data in the form at this point. I think if the field you are trying to populate is bound, that is what causes this error.

You can use the Default property as @bastanu suggested OR you can use the form's BeforeInsert event rather than the Open event. The BeforeInsert event runs ONCE for each new record so all new records will end up with the correct FK (which would NOT happen if you used the Open event or the load event since they run only ONCE ever, rather than for each new record added). The other advantage of the BeforeInsert event is that it doesn't run until the user types something into the for so your code doesn't dirty the form as it would if you put the code in a different event.

Ahaa! BeforeInsert works perfect! I still got the same error as in the original post whether I tried Open or Load event, nor the default property worked. Thanks as ever, Pat!

 

[nashaz]

in #1, the code used
docmd.openform "frmTraining" , , , , , acFormAdd, Me.PositionID
has incorrect syntax

Use
docmd.openform "frmTraining" , , , , acFormAdd , , Me.PositionID

That is 4 commas before and 2 commas after, the acFormAdd rather than 5 and one

yes you were right about that! thanks, Ive fixed that

 

[Pat Hartman]

You're welcome Understanding the purpose of form events is very difficult since there is little documentation regarding when you should use one rather than another. Sometimes knowing what triggers them can help you decide but not always.

I created two videos a while back and decided to post the database I created to use with them. The videos concentrate on the Before and AfterUpdate events of the form as well as a couple of others so you might want to view them before you jump in to play with the database but the database has a form that shows you what events are running in the form you are working with. You can modify the forms I included or you can add your own. Just make sure to put the ONE line of code that logs each event as the first line of any event that you want to see fire. The syntax for the control events is slightly different from the form level events.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[bastanu]

Glad to hear you solved your issue with BeforeInsert. I don't know how relevant the comment in your edit line in post #1 is now that you have it working, but if you want to give a visual clue to the user about the value about to get inserted (might be better to actually have a combo just like on the source form to show the description and not the ID) you would need to use the DefaultValue property of the control.

Is the name of the textbox in the footer PositionId or something else. In this context the DefaultValue property applies to a control not a field in the form's record source.

TextBox.DefaultValue property (Access)

Office VBA reference topic

learn.microsoft.com

Cheers,

 

[nashaz]

You're welcome Understanding the purpose of form events is very difficult since there is little documentation regarding when you should use one rather than another. Sometimes knowing what triggers them can help you decide but not always.

I created two videos a while back and decided to post the database I created to use with them. The videos concentrate on the Before and AfterUpdate events of the form as well as a couple of others so you might want to view them before you jump in to play with the database but the database has a form that shows you what events are running in the form you are working with. You can modify the forms I included or you can add your own. Just make sure to put the ONE line of code that logs each event as the first line of any event that you want to see fire. The syntax for the control events is slightly different from the form level events.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

Thanks, Pat. I have seen these videos before and they are great help in clearing up quite a few concepts!

 

[nashaz]

Glad to hear you solved your issue with BeforeInsert. I don't know how relevant the comment in your edit line in post #1 is now that you have it working, but if you want to give a visual clue to the user about the value about to get inserted (might be better to actually have a combo just like on the source form to show the description and not the ID) you would need to use the DefaultValue property of the control.

Is the name of the textbox in the footer PositionId or something else. In this context the DefaultValue property applies to a control not a field in the form's record source.

TextBox.DefaultValue property (Access)

Office VBA reference topic

learn.microsoft.com

Cheers,

That actually resolves my ick as to why .defaultvalue was not working for me. Cheers, bastanu