copy value of field to find a record in another form

[Guest]

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.

 

[George Nicholson]

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.

 

[Guest]

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]

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,

 

[Guest]

Thanks again for your help George. I tried both solutions but with no success.
With the second one, the address book form would open, but just normally not
to the corresponding record that was displayed on the search results form.

With the former, I get a Microsoft VB compile error message "User defined
type not defined". In the code the second line containing rst As
DAO.Recordset is highlighted.

The only thing that has been changed is that the command on the 'go to
record' button in the search results form is now controlled by a macro
(essentially closes active window and then opens address book form). However
I don't think this is related because the address book opens with the same
error message when I try to open it directly.

Another point that I should make, which I think is covered off in the code,
is that there are commands to open the address book form which are unrelated
to search results eg. to enter a new record. In this case I don't need a
particular record to be found and I've got the macros working okay off other
command buttons.

Any more ideas?

Caroline

 

[George Nicholson]

1) in the AddressBook *table*, is Reference a text field or a numeric field?
If it is a numeric field, try:
Dim lngRef as Long
.....
lngRef = clng(Me.OpenArgs)
DoCmd.GoToControl "Reference"
DoCmd.FindRecord lngRef, , True, , True, , True
Could be we've been using a string to identify a record when we needed to
use a number if we want a match.

2) The error message is due to no DAO reference being set.
In the VBE (VisualBasicEditor): Tools>References>Scroll to "Microsoft DAO
x.x Object Library" & put a check in the box.
You would also need to adjust this code for the string/number issue:

lngRef = clng(Me.OpenArgs)
........
rst.FindFirst "Reference = " & lngRef

 

[Guest]

Reference is a numberic field (autonumber). I tried the code that you wrote,
but wasn't entirely sure how to integrate it into what I already had (I tried
a number of variations, but none worked)
Here's the complete code - maybe you can work out what I've done wrong:

Private Sub Form_Open(Cancel As Integer)
Dim IngRef As Long

IngRef = CLng(Me.OpenArgs)
DoCmd.GoToControl "Reference"
DoCmd.FindRecord IngRef, , True, , True
If IsNull(Me.OpenArgs) Then
'Open normally. Do nothing.
Else
'OpenArgs has been passed. Find 1st record that matches criteria
IngRef = Me.OpenArgs
Set rst = Me.RecordsetClone
rst.FindFirst "Reference = " & IngRef
If rstNoMatch 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

The message that I'm getting is Run time error '94' - invalid use of Null.
The line IngRef = CLng(Me.OpenArgs) is highlighted.

This occurs whether I try to open the form directly or through the button on
the search results form.

I also ticked the check box on DAO object library.

thanks again (and for your patience!)

 

[George Nicholson]

You've combined 2 methods of doing 1 thing rather than choosing one or the
other. Use one of the 2 below. I tend to use the 2nd approach myself, but
the choice is yours.

Private Sub Form_Open(Cancel As Integer)
Dim IngRef As Long
If IsNull(Me.OpenArgs) then
'Open normally. Do nothing.
Else
IngRef = CLng(Me.OpenArgs)
DoCmd.GoToControl "Reference"
DoCmd.FindRecord IngRef, , True, , True
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
' Requires a reference to "Microsoft DAO x.x" be checked under
Tools>References in the VisualBasicEditor.
Dim IngRef As Long
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
IngRef = Clng(Me.OpenArgs)
Set rst = Me.RecordsetClone
rst.FindFirst "Reference = " & IngRef
If rstNoMatch Then
'No match found in recordset. Do nothing (form opens as if no
OpenArgs was passed)
Else
'Match found in recordset. Set form to the same record.
Me.Bookmark = rst.Bookmark
End If
End If
End Sub

HTH,

 

[Guest]

Thanks George. I played around with it again and tidied everything up and
I've got things working as I intended.