A record number is not a reliable means to identify a record; if you delete
one or more records, the record number of those "after" it will change. Use
the primary key to unambiguously identify the record.
I'm not sure of the context in which you want to set the field's value, but
you may want to consider making these changes with an Update query.
I have no idea how to do this in a macro, but it is quite easy in VBA. Many
here do not use macro's for anything more than the simplest tasks or during
development because they offer no error-handling. I'll describe the process
if you've not done it before.
To set a property of a control on the current record of a form, the VBA
syntax is:
Me![YourControlName].PropertyName = YourValue
To choose based on the value of another control's value, such as the primary
key, use a Select Case statement:
Select Case Me![YourPrimaryKey]
Case 1
Me![SomeControl].Visible = True
Case 2
Me![SomeOtherControl].Visible = True
... other cases
Case 10
Me![YetAnotherControl].Visible = True
End Select
Again, not understanding the context under which you're doing this, if you
truly want to loop through the first 10 records, you can use the GoToControl
method of the DoCmd object:
Dim i as Integer
For i = 1 to 10
DoCmd.GoToRecord acDataForm, "YourFormName", acGoTo, i
<Insert Select Case block from above here>
Next i
To create the VBA procedure, open the form in Design View. You could run
this code in a variety of ways; one easy way is by attaching it to a command
button. Choose the command button icon from the Toolbox toolbar (if it is
not displayed, choose View, Toolbox from the main menu), and click and drag
on the form to size the button. If you have the Wizards enabled, the command
button wizard will launch to write code for a number of common processes.
Since you want to use custom code, Cancel out of the wizard.
Show the button's properties by View, Properties, click the Event tab, and
click into the OnClick field. An ellipsis will appear to the right; click on
it. If prompted with a list of selections, choose Code Builder to launch the
VBA Editor. Access will create the shell of an event procedure which will
run when you press the button. Between the Sub and End Sub lines, paste the
code from above, and change "YourFormName", "YourPrimaryKey",
"YourControlName", etc. to the actual names of your form, field, and controls.
To add basic error handling that will display a message during an
unanticipated event rather than crashing and displaying the dreaded End/Debug
dialog box, place the following code directly under the Sub statement:
On Error GoTo ErrHandler
Then just above the End Sub line, place the code:
ErrExit:
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ErrExit
You can customize the message if you wish. I typically use something like:
ErrHandler:
MsgBox "An unanticipated event has occurred." & _
" Please contact the system administrator with the following
information." & _
& vbCRLF & vbCRLF & _
Err.Number & vbCRLF & Err.Description
Resume ErrExit
The & characters concatenate the pieces of the string together, the
underscores tell Access to continue the code on the next line, and the
vbCRLFs are a Visual Basic predefined constant that inserts a new line.
Hope that helps.
Sprinks
