FindRecord

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

0
alex
4/6/2007 3:15:37 PM
access 16762 articles. 2 followers. Follow

2 Replies
803 Views

Similar Articles

[PageSpeed] 30

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" wrote:

> 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
> 
> 

0
Utf
4/6/2007 5:52:00 PM
On Apr 6, 1:52 pm, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -

Thanks Ken; I'll try it.

alex

0
alex
4/6/2007 7:58:11 PM
Reply:

Similar Artilces:

Help with FindRecord
Hello again and thanks in advance.. I've got an orders table with a primary key of OHID (autonum). Ideally the user could type the order they want to pull up in ctlOHID and have that record be called up. Since I'm using an autonumber, I can't have the user enter a value directly into that field, so I have another unbound text box set up called ctlOHGoToRec. In the afterupdate section of this I am trying to put this code: Dim varOHNewRec as Long varOHNewRec = Me.ctlOHGoToRec DoCmd.FindRecord varOHNewRec, , , , , acAll When this code runs I get the following error:...

FindRecord
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 sug...

FindRecord in Macro does not return Not Found error
Hi, I am designing a database where I can input data in a form and the macro searches for that data in a particular field and then moves focus to another field where it inputs pre-defined data. The only problem is, if the particular search fails, there is no notification. Instead, it moves on and inputs the pre-defined data in the last record which had the focus. I want the macro to return a popup box with the error and close the macro. What should I do? Thanks! Zef. On problem with macros (at least the pre-A07 versions) is the lack of error handling. For that you need a function if returin...

docmd.findrecord
I have a form with a textbox that onchange finds the nearest string that is typed into the textbox in a subform that is a list of customers. If the docmd.findrecord is keyed to the first column it works great but I now want it to search a field in the query behind the subform which does not show on the subform as a text box. For instance if I start to type wei in the textbox the subform will immediately go to a record that has wei as the first three letters in the first column of the subform as it should do but on a different form that searches for full name the letters nes sho...

Using DoCmd.FindRecord
Hi! My problem is basically in a search modal window, where the user is allowed to make a selection on which option to take. After selecting an option and then giving the data to search, the user clicks the "OK" button and the user gets taken back to the "Customer" form with the correct searched record. The function works fine except that every now and then it takes 3-5 seconds to close the search modal window and goes back to the "Customer" form after clicking the "OK" button Below is the actual code for the "OK" button: - - - - - - -...

Access 2000 crashes on DoCmd.FindRecord
Any idea what may be causing this: An Access database, 2000 file format, designed using 2003 but running on Access 2000. To open a form and find a specific record, the following code is run: DoCmd.OpenForm "Quote" DoCmd.GoToControl "QuoteID" ' Contains Record ID DoCmd.FindRecord Forms!MyQuotes!MyQuotesList_subform!QuoteID <-- CRASH HAPPENS HERE, DURING SEARCH Access crashes at the point indicated. Is there anything about the development having happened in Access 2003 rather than 2000 that may provide a clue as to the cause of this? This crash occurs on more ...

DoCmd.FindRecord Error
My code has a function to find a record. The function is called by 2 separate search forms, each of which generates SQL for a query that builds a table of record keys of all qualifying recs. When there is only 1 qualifying rec, the function is called to open the primary form (Member Data) to the qualifying rec. The function uses the rec in the new results table ( a long int ) as the argument for the FindRecord. When called by 1 of the forms, it works properly. When called by the second form it fails on the DoCms.FindRecord line with Runtime error 2162: "A macro set to 1 of th...

FindRecord Control Button
i built a find record control button so a user can type in the form number and go there but it only searches the field that the cursor is in and the form # is not on the tab order(purposely). How can i specify where the find record function searches? thanks Ryan, usually people use an unbound combo box or an unbound text box for searching. There is a sample download with code of a search form at http://www.allenbrowne.com/ser-62.html If you need help to get it working, post back. You can also use the wizard to put a combo for searching on your form. On the form in design view, click the...

Filter Records after FindRecord
I created a cboQuickSearch combo box, where I can choose the field I would like to search on. The txtSearchBox text field is where I enter the search data. I have three issues: 1. I would like to filter the records once found. 2. I want to be able to enter a few characters instead of having to enter the whole word. 3. Is there shorter code to do the same thing? Below is the code I created: Private Sub txtSearchBox_AfterUpdate() If Me.cboQuickSearch.Value = 1 Then ' Company ID Me.txtCompanyID.SetFocus If Not I...

DoCmd.FindRecord...How to use to match a field value
When I open a form, I would like Access to go to a specified record that meets the criteria in a certain field using the 'DoCmd.FindRecord' command in VBA. Unfortunately I have not had much success this way. Can you explain how I can find a record based on the contents of a field on the form. Below is my VBA code. Private Sub Workouts_Click() Dim stfilter As String Dim stFormName As String Dim getdateid As Integer stFormName = "Datelog" getdateid = Me!Workouts.Value stfilter = "[athleteid]=" & Me![Athleteid] DoCmd.Close, acform...

FindRecord Macro Won't Work
I am trying to run a macro that takes information from 2 separate fields in one form and then searches a table for the record and then it populates another form. The first form only has 2 fields in it. I have one command button also. For the command button I set the "OnClick" to a macro that opens up another form and also finds the record that was chosen from the 1st form. The FindRecord action is set up like this Find What:=[PARID] Match: Whole Field Match Case: No Search: All Search as Formatted: No Only Current Field: Yes Find First: Yes The search form has two combo boxes th...