Katrina said:
I have fields named [PN01], [PN02].... to [PN30]
What I would like to do it have access determine which
field is the first empty field and put some info into it.
Any suggestions?
This can be done by building a string index into the Controls collection
(of a form) or the Fields collection (of a recordset). I should point
out first that having fields named in that fashion suggests a flaw in
your table design, and it would probably be better to correct that flaw
than to program around it. It sounds as though you should have a
related table to store the data vertically (in multiple records) that
you're currently trying to store horizontally in these fields. That
way, you would simply add a new record to the related table rather than
searching for the first empty field and sticking a value in it.
"Records are cheap, fields are expensive" -- and storing data the way
you are trying to makes it very hard to query and maintain. Please let
me know if you want further explanation.
That said, here's an example of how to do the specific job you asked
about. I'm not sure whether you're using the word "field" literally
(referring to a field in a table or recordset) or whether you're talking
about controls on a form that are bound to these fields. Suppose you're
talking about a form, on which you have controls [PN01] ... [PN30].
Here's some example code:
'----- start of example code -----
Dim intI As Integer
' Set first empty "PNxx" control to SomeInfo:
For intI = 1 To 30
With Me.Controls("PN" & Format(intI, "00"))
If IsNull(.Value) Then
.Value = SomeInfo
Exit For
End If
End With
Next intI
'----- end of example code -----
