Argument not optional error 4

[puforee]

I am getting the "Argument not optional" error when this code is run from my form. Code is run on the After update event of a Drop Down select control. Note the first line of the code runs fine and both controles I am tryig to update are on the same form from the same table. No retrictons on the form or controls.

Private Sub EnterATA_AfterUpdate()
    Me.Chapter = Me.EnterATA.Column(3)
    Me.Section = Me.Text32
    
End Sub

The second line Me.Section = Me.Text32 is the failing line with the "Argumen not optional" Error. When commented out the first line runs fine and updates the Chapter control. The Text32 control is temporary. It was Me.EnterATA.Column(4) when the first failures occured.

And, what does "Argument not Optional" actually mean?
.

 

[dhookom]

I expect Section is a reserved word. Check out Allen Browne's list of reserved words.

You may need to wrap section like [Section].

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

OK Duane, I tried Me.[Section] and I also tried with DoCmd Set Properties Value. It still does not work.

Any other ideas?

 

[dhookom]

Try change the name of the Section control to txtSection.

Which exact line throws the error?

What is Text32? Is this an unbound text box for displaying the 4th column of the combo box EnterATA?

Do you understand columns in VBA are 0 based?

If you only need to display the value from another column, you can do this without code. Just set the Control Source to:

=MyComboBoxName.Column(X)

X is a number of the column and it is 1-based outside of VBA.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Howdy puforee ...

Duane is correct in his reference to [blue]Section[/blue] as a reserved word. You can easily prove this by typing [blue]Me.Section[/blue] in any vba window ... and when you next type a period ([blue].[/blue]) ... form/report properties should pop-up.

An alternate syntax for you to try would be [blue]Me("Section")[/blue] = Me.Text32

In any case I highly suggest you [blue]replace the reserved word![/blue] (I expect it will work when you do) ... [blue]reserved words[/blue] can be more trouble than you might anticipate ... and as you program, [blue]always ask yourself if your naming convention is true![/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[puforee]

Thanks to both of you. I used TheAceMan1's suggestion of Me("Section") in the code and it worked. Also of note: When I type Me.Section in the VB window it does NOT warn me this is a reserved word. Also, at Duane's suggestion, I went to “Allen Browne's list of reserved words” link. SECTION and sections is listed but not Section. Note the capitalization and the plural of Section. Does that matter? Anyway, it now works and I learned there are several ways to use the ME syntax... Like Me.(control), Me.[Control] and the one that worked Me("control") without the Dot after Me.


Confusing at best but you both of you gave excellent suggestions and information.

 

[strongm]

VBA is not case-sensitive

 

[MajP]

learned there are several ways to use the ME syntax... Like Me.(control), Me.[Control] and the one that worked Me("control") without the Dot after Me.

If interested here is the explanation. It is not related just to the "Me" syntax but general VBA syntax. VBA and VB tried to be too flexible that it actually causes more confusion than helps IMO.

1) There are two types of syntax Dot and Bang when working with collections. So for example the forms collection can be referenced like this using Dot
Forms(1) using the numbered index
or
Forms("YourFormName") using the named index
or with a variable
dim frmName as string
frmName = "MyForm"
Forms(frmName)

Using Bang you can just do this
Forms!YourFormName
can not use a variable and do not use ""

2) If there is a space or reserved word you need to wrap it in [], and you can always do it if you want.
dot
Forms("My Form") no need for brackets since in ""
Forms![My Form] need brackets
me.[txt box1] need brackets
me.controls("txt box1") no need for brackets
me.[txtBxOne] no need for brackets but still works

3) To add to the confusion there are vba/vb deault methods and properties. If it is a default you do not have to do use it, but you can
For the controls collection on a form the full syntax is
me.controls.item("controlName")
however the "item" property is the default property for any collection so you can just drop it
me.controls("controlName")
and the controls collection is the default property of a form so you can drop that
me("controlName")

4) Access has a couple of even more confusing things
When you add a control to a form it makes it both a property of a form and adds it to the controls collection
so you can reference the control directly as a property
me.TxtBoxOne
or through the controls collection
me.controls("txtBoxOne")
Also a lot of times you have a field and a control with the same name and Access does not care.
Me.UserID can either be the control or the field, but figures it out by context


So for your specific issue a form has Sections such as the Detail Section. I could write the following and all are the same
Me.Section.item("Detail").name
Me.Section("Detail").name
Me.Section!Detail.name
Me.Section(1).name
if Your section was named "My Section"
Me.Section("My Section")
Me.Section![My Section]

But since you were using the reserved word section as a name of a control that was not a section object
Me.Controls.item("Section")
me.controls("section")
me("Section")

But you cannot do what you tried because
Me.Section expects an argument to follow such as Me.Section(1) or me.Section("Detail")

But bottom line never use spaces and use a naming convention to avoid using reserved words. You will not get any warning in vba that the word is reserved. So instead of Section use something like txtBxSection for the text box.

 

[dhookom]

MajP,
Thanks for the great posting!

Duane
Hook'D on Access
MS Access MVP

 

[strongm]

For a slightly more in depth look at the bang operator, have a look at

http://www.tek-tips.com/viewthread.cfm?qid=717031

 

[MajP]

@strongm,
Good detail, but there another strange caveat in Access. And this is very confusing. When you have a bound form all the fields can be referenced as properties of the form. Even if you have no controls on the form you can do something like this, which makes sense.
Me.ID
For some undocumented reason you can also do this
Me!ID

Unlike the controls collection, the form does not even have a fields collection. So no idea why that works and I know of no where else in vba where you get similar behavior.

Second, in your post you said

Indeed, if a field in a table has the same name as an ADO method on an ADO Recordset object then you have to refer to the corresponding field in the recordset with the ! syntax to avoid an error

Not sure exactly what you meant, but there is never a reason you have to use bang in VBA. (You may in a query or expression.)

RS.fields("FieldWithSameNameAsMethod")
but since the fields collection is the default
RS("FieldWithSameNameAsMethod")

If it is "" it is always treated as a string and never a problem.

 

[strongm]

>Not sure exactly what you meant

I wrote it 12 years ago, so I'm not sure either now ...

 

[jrbarnett]

MajP,

Could you turn that posting into an FAQ in one of the Access fora here please. I am sure something like that is bound to come up again.

John