FindRecord

[alex]

Please help:

I'm trying to create a FindRecord macro to find records from/within a
Form.

When I select a record from the combo box (search field), I get the
following error:

You can't use Find or Replace now.

The fields are controls...
The fields have no data.
There are no fields to search.

What I'm trying to do is create a searchable field in my Form.

E.g., the user selects the records primary key from the combo box, and
the record (in its entirety) appears on the Form (in Form format).

A macro may not be the best way to do this...I'm open to suggestions.

Keep in mind that this Form and Table are locked tight. I only want
users to be able to enter a new record. Therefore, if they select an
ID in the combo box and the record comes up (should I get it to work);
I only want the user to be able to view the record and nothing else.

Thanks,
alex

 

[Guest]

Alex:

I'd use VBA code rather than a macro. Lets assume you are searching on
ContactID using a combo box cboFindContact. Put the following code in the
combo box's AfterUpdate event procedure:

Dim rst As Object
Dim ctrl As Control

Set ctrl = Me.ActiveControl

Set rst = Me.Recordset.Clone

rst.FindFirst "ContactID = " & ctrl
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If

In the form's Current event procedure put the following to lock and disable
all controls other than the combo box if the form is at any record other than
a new one:

Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.Name <> "cboFindContact" Then
On Error Resume Next
ctrl.Locked = Not Me.NewRecord
ctrl.Enabled = Me.NewRecord
On Error GoTo 0
End If
Next ctrl


You could exempt other controls from the locking/disabling if you wished,
e.g. if you have a button for going to a new record for instance which you
want to leave enabled the set the Tag property of each control you want to
leave enabled to DontLock and inserted of examining the Name property of each
control examine its Tag property:

If ctrl.Tag <> "DontLock" Then

The error handling is to cope with controls which don't have Locked or
Enabled properties. It simply turns the default system error handling on and
off, though this is rather crude and it would be better to incorporate your
own error handling routine in the code.

Ken Sheridan
Stafford, England

 

[alex]

Alex:

I'd use VBA code rather than a macro. Lets assume you are searching on
ContactID using a combo box cboFindContact. Put the following code in the
combo box's AfterUpdate event procedure:

Dim rst As Object
Dim ctrl As Control

Set ctrl = Me.ActiveControl

Set rst = Me.Recordset.Clone

rst.FindFirst "ContactID = " & ctrl
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If

In the form's Current event procedure put the following to lock and disable
all controls other than the combo box if the form is at any record other than
a new one:

Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.Name <> "cboFindContact" Then
On Error Resume Next
ctrl.Locked = Not Me.NewRecord
ctrl.Enabled = Me.NewRecord
On Error GoTo 0
End If
Next ctrl

You could exempt other controls from the locking/disabling if you wished,
e.g. if you have a button for going to a new record for instance which you
want to leave enabled the set the Tag property of each control you want to
leave enabled to DontLock and inserted of examining the Name property of each
control examine its Tag property:

If ctrl.Tag <> "DontLock" Then

The error handling is to cope with controls which don't have Locked or
Enabled properties. It simply turns the default system error handling on and
off, though this is rather crude and it would be better to incorporate your
own error handling routine in the code.

Ken Sheridan
Stafford, England














- Show quoted text -

Thanks Ken; I'll try it.

alex