I guess I would begin by saying avoid using Macros at all costs, simply because VBA is so much more flexible, and once you start using it, you will find it to be rather easy for your basic activities in your db.
Second, I would advise you to rename all of your tables, forms, fields and objects, so that there are no spaces included in the names. This makes things easier overall (no need to constantly include square brackets for your form and field names).
Third, I would very highly encourage you to make sure you rename all of the controls on your forms so that they do not match the names of your fields. For example, if you have Form bound to the Table tblInformation, which has a Field InfoID, and you have a Text Box Control on your Form that is bound to the Field InfoID, you would not want to name that Control "InfoID," as this automatically creates an ambiguous naming situation. In most cases, Access is able to figure it out, but in some cases, it can create problems (which could be your situation here). For example, on the above mentioned form, the term
Me.InfoID could refer to
either the field in the table
or the text box.
So, find an adequate naming convention for your DB controls and stick with it. For example, all my tables begin with "tbl", queries "qry", text boxes "txt", etc. This prevents ambiguity and helps others to troubleshoot your work. Let's use your example:
- Item: [Forms]![Violation Add]![FirstName]
-
Expression: [Add Charge Violator].[column](2)
Because you use [Forms], I know that you have a form names "Violation Add." However, I don't know if it is a main form or a subform (I name my subforms "fsub"). Next, does "FirstName" refer to the
Field underlying the form or to some sort of
control on the form? What type of control it is? Is it a text box? A Check box? A Combo Box? List Box? Option Group?
Finally, you say you have a three-column combo box, but you refer to Column(3). That implies you have a four-column combo box, as column numbers begin at 0.
Below, an example of the simplicity of VBA (I've renamed your controls to make it easier to understand):
- Private Sub cboViolator_AfterUpdate()
-
-
Me.txtFirstName = Me.cboViolator.Column(1)
-
Me.txtLastName = Me.cboViolator.Column(2)
-
-
End Sub
Even if you know absolutely nothing about VBA at this point (I hope you know at least a little bit), you should be able to see how simple and straightforward this code is: After you select someone from the Combo Box, it updates the First and Last Names to the values in the columns from the Combo Box.
Plus, once you learn more about VBA, you will be able to manipulate the data in whatever way you like. This makes your projects much more powerful, flexible and robust.
I admit that this is not solving the problem of your error, but I suggest that there may be other minor things that could be adjusted and improved which may avoid future errors like this.
Hope this hepps!
3
