Field Names and VBA issues

[autiger58]

I seem to have a bit of an issue that I hope someone can point me in the right direction. I need to carry values in a field on a form. Here's the code I want to use.

Private Sub StateAndCountyCombined_AfterUpdate()
Dim ctl As Control
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Carry" Then
ctl.DefaultValue = """" & ctl.Value & """"
End If


Next
End Sub

But because I am being forced to use Numbers as field names, the code will not work when I for example.....

Private Sub 5_AfterUpdate()
Dim ctl As Control
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Carry" Then
ctl.DefaultValue = """" & ctl.Value & """"
End If
Next
End Sub

5 is the field name in the data base and VBA. I get "Compile error.....Expected Identifier". This code works perfectly in another db where I can use field names. But changing the field name to the number 5 causes this error message in the db with all field numbers instead of names. I know using numbers as field names is a bad idea but like I said, I am being forced to use someone else's db. Anyone have a suggestion on what I am doing wrong in the second code?


Thanks, Michael

[Micron]

Let's distinguish between fields and controls. In many posts when the word field is used to refer to form objects, the difference is not worth pointing out. Your case is different.
I'm presuming your table fields are text data type and that your table field names are numbers, which does not mean your controls have to be. In older versions of Access, you cannot even create an event for a control named 5, so I'm surprised that you can do this from the property sheet event list in your db. Access want's to preface 5 with Ctl (Ctl5) and won't recognize 5 as the object name for the event. Or are you typing the event name yourself?

Not only do the controls not have to be the same name as the fields they're bound to, they shouldn't have the same name as a matter of better design practice. Can you not rename the control Ctl5, or is that what you mean by having to use someone else's db?

[autiger58]

I'm really a novice at programming Access db's although I've been using Access since 2000. As for your question, I sorry to say I don't know how to answer other than I am forced to use someone eles linear DB to input data from the field. That data base does not use field Names, it uses Field Numbers..ie 1,2,3,4,5,6,7. For Field 5 in this DB, I want to enter the state and county into a field and have it "CARRY" forward for each new record until I need to change to a new county. I have a similar DB we use and in that DB, all the field names are NAMES. The first VBA code work perfectly to accomplish the same thing in our own DB. But when I want to do the same thing in the other DB (the one with numbers for field names), it will not run and gives me the Compile error: Expected identifier. We do a number other things, like Update queries in SQL and the numbers as field names do not present any issues. Only in VBA. My question is this, is there any way for me to use the existing field NAMES as numbers in VBA code as stated above?

Michael

[Micron]

I want to enter the state and county into a field

I take this to mean you want to concatenate state and county into ONE field, which is generally considered bad practice. Calculations and concatenations should be done via unbound controls on forms and reports.
I understand that the first code works - because you're not violating any object naming rules.

My question is this, is there any way for me to use the existing field NAMES as numbers in VBA code as stated above?

In spite of your effort, I still don't understand if the 'other person's db' is the one with the tables and yours has the forms or not. As I said, the control name does not have to be the same as the field name (and generally should not be), so if you have design control over the forms, don't use those field names (numbers) as control names and you should be fine. The issue is that vba is interpreted differently than sql, so it doesn't relate to "5" as an object name, thus it cannot relate the object name to any table field. Sql does not present this "layer" problem. If you don't have design control over the forms and cannot rename control "5" to Ctl5 because it is not your db, then I fail to see how they are getting it to work, unless it is not an Access database. In that case, I can only suggest that you run an Update query afterwards, rather than try to do this from code. Again, the two values should not be concatenated into one field AFAIC.

[autiger58]

OK. Now I understand. I do have full control over the form and I just fixed the problem with your help. Some where along the way, I never learned that a form control could/should be named something different than the field name. Thanks a million for your help (and your time!!).

Michael

[Micron]

U r welcome. Glad you solved it. Access form/report wizards will name controls the same as the fields they are bound to, but you can and should rename them using a standard naming convention.
Please mark your post as solved if you've got a solution.
Thanks.