Re: copy value of field to find a record in another form

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: George Nicholson (JunkGeorgeN_at_msn.com)
Date: 02/25/05 

Date: Fri, 25 Feb 2005 11:39:12 -0600

The button code looks fine.

In the Open event of your addressbook form:

Private Sub Form_Open(Cancel As Integer)
    Dim strRef As String
    Dim rst As DAO.Recordset

    If IsNull(Me.OpenArgs) Then
        ' Open normally. Do nothing.
    Else
        'OpenArgs has been passed. Find 1st record that matches criteria
        strRef = Me.OpenArgs
        Set rst = Me.RecordsetClone
        rst.FindFirst "Reference = '" & strRef & "'"
        If rst.NoMatch Then
            ' No match found. Do nothing (form opens as if no OpenArgs was
passed)
        Else
            ' Match found. Set form to same record/bookmark as
RecordsetClone
            Me.Bookmark = rst.Bookmark
        End If
    End If
End Sub

This is patterned after the code in the Help file example, but I've expanded
it a bit in hopes that it's a little clearer as to what is going on. There
is another, maybe simpler, example in the Help file that uses
DoCmd.FindRecord, but I never use that method (no particular reason, I just
don't), so I can't vouch for it. No reason it wouldn't work though:

Private Sub Form_Open(Cancel As Integer)
    Dim strRef As String

    If IsNull(Me.OpenArgs) Then
        ' Open normally. Do nothing.
    Else
        'OpenArgs has been passed. Find 1st record that matches criteria
        strRef = Me.OpenArgs
        DoCmd.GoToControl "Reference" 'Note: if different, this should be
the *control* name, not the field name
        DoCmd.FindRecord strRef, , True, , True, , True
    End If
End Sub

HTH, 

-- 
George Nicholson
Remove 'Junk' from return address.
"Caroline" <Caroline@discussions.microsoft.com> wrote in message 
news:96B4D986-785E-4DB9-899C-CEC6CD0F603F@microsoft.com...
> Thanks, the logic makes sense but I don't completely understand the 
> process -
> I'm a complete VB novice.
> What I'm trying is to create a command button and used the build button -
> code builder. This is what I've got:
>
> Private Sub Command33_Click()
>   DoCmd.OpenForm "address_book", , , Me.txtreference
>
> End Sub
> Private Sub Form_Open(Cancel As Integer)
>
> End Sub
>
> (I've renamed my ref # field as 'reference' as I was concerned about 
> spaces
> & symbols)
> This is obviously incomplete because I get an error message saying compile
> error: method or data member not found.
> I looked up the help on OpenArgs - but wasn't really sure where it was to 
> go
> or how to use it to find the exact record.
> You've probably worked out that I've got very little idea about this, so
> would appreciate an access for dummies type answer.
>
> thanks again
>
>
>
> "George Nicholson" wrote:
>
>> I think you are looking for the OpenForm method.
>>
>> with SearchResultsForm.CmdButton_Click, if RefNum is a text field in
>> AddressBook:
>>     DoCmd.OpenForm "AddressBook", , , Me.txtRefNum
>> The last argument of the OpenForm method is the OpenArgs argument. It is
>> passed to the form you are opening.
>> You can then place code in the Open event of the AddressBook form that 
>> takes
>> that value and either goes to that record or filters on that value.  See 
>> the
>> Help file entry for OpenArgs for examples.
>>
>> -- 
>> George Nicholson
>>
>> Remove 'Junk' from return address.
>>
>>
>> "Caroline" <Caroline@discussions.microsoft.com> wrote in message
>> news:F8700D9B-AC35-4526-AE64-F822AE5486F2@microsoft.com...
>> > I'm a relatively new user of Access 2003 and haven't been able to find 
>> > a
>> > solution to this problem.
>> > My database consists of one table (addresses) that contains numerous
>> > contact
>> > and personal details for clients. I have developed a parameter query
>> > (search_query) loaded through a form (search_form) that enables users 
>> > to
>> > identify records based on name and/or organisation etc. The results of 
>> > the
>> > query are then displayed on a new form (search_results_form).
>> > What I want to do is add a command button on this form that will allow 
>> > the
>> > person to go to the complete address record in the address_book form. 
>> > The
>> > process that I've been thinking through is:
>> > - copy the ref # of the active record in the search_results_form (a 
>> > text
>> > box
>> > bound to the field ref # in the query)
>> > - open the address book form
>> > - search in the field ref # for the corresponding number
>> > - at some stage close the search_results_form
>> >
>> > I've spent ages trying different functions and expressions but have got
>> > absolutely nowhere. I'm hoping that there's some relatively simple
>> > solution
>> > as this is a critical part of the whole database.
>> >
>> > Thanks very much in anticipation of your help.
>>
>>
>>
Quantcast