Cross Form Focus 1

[OzSupra]

I have two open forms. They both use the same table. The first form is a straight data entry form and the second is a continuous form showing all records.
I would like to click on one of the records on the second form and the first form displays the same record even when I close the second form.

Ta, in advance, Adrian.

 

[RoyVidar]

Hi!

You could probably use some alterations of the code generated by the wizard for combos and lists ("Find a record on my form...&quot.

Using form1 and form2:

First question would be what event should one use to trigger this. One would like to ensure that only clicking within the "record" part of the form should invoke this routine, so I would probably rule out the forms on click event. One could use one of the controls, but in stead I would use them all. How:

I'd create a sub within the form module, which will be called from all of the controls. Here it would probably be wise to check whether the form is open, which can be done with the IsLoaded routine provided by Rohdem in faq181-320.

[tt]private sub ShowCurrentRecord()
dim rs as dao.recordset
if isloaded("form1&quot then
set rs=forms!form1.recordsetclone
rs.findfirst "IDfield = " & me!txtIDControl
if not rs.bof then forms!form1.bookmark=rs.bookmark
set rs=nothing
forms!form1.setfocus
end if
end sub[/tt]

I've also added a setfocus. Next, how to call it. Since you want it on click, then on each of the controls in form2's on click event, add:

[tt]ShowCurrentRecord[/tt]

Note - I've used [tt]dim rs as dao.recordset[/tt], because form recordsets are dao by default also in access 2000 and XP. If you're using Access 97, "dao." might be ommited. In later versions, the library "Microsoft DAO 3.N" must be checked (or use the Access Wizard version [tt]dim rs as object[/tt] (late binding)

HTH Roy-Vidar

 

[OzSupra]

Followed your instructions and posted the code into a module.
Problem is on click the ShowCurrentRecord event is looking for a macro not a module.

Please make the rely REAL easy to understand.
Your help is really appreciated.
Thanks,

 

[RoyVidar]

Hi again!

From your response, I'm not really sure what goes wrong.

Trying a few things:

* the ShowCurrentRecord sub (above) is pasted within the forms module and not a new module?
* calling the sub is done thru the on click event of each of the controls on form2 should look something like this:
[tt]Private sub txtID_Click
ShowCurrentRecord
End Sub[/tt]?
* you have substituted the names of form and controls?
* if you wan't to use the IsLoaded function (as I am in the example) you need to paste that (copy it from the faq I mentioned) into a new module (best way is to create a new module and paste it there). If you're not using it, remove the if statement and the end if?

If none of this helps, please post where it halts, any errormessage, the code you're using.

Roy-Vidar

 

[OzSupra]

Sorry for being a bit thick.

Here's what I have done. On form 2 there are two fields "Code" and "Description". Description is the one I want to use. On the "On Click" event I have

Private Sub Description_Click()

ShowCurrentRecord

End Sub

Then I have more code as follows (PS dao won't work with my Access 2000)


Private Sub ShowCurrentRecord()
Dim rs As Recordset
If IsLoaded("Status_Form&quot Then
Set rs = Forms!Status_Form.RecordsetClone
rs.findfirst "Description = " & Me!Description
If Not rs.BOF Then Forms!Status_Form.Bookmark = rs.Bookmark
Set rs = Nothing
Forms!Status_Form.SetFocus
End If
End Sub

This gives me an error stating "Compile Error, Method or Data Member not Found" and highlights the !Description area in the code. I have tried substituting this for all possible alternatives, without sucess.

Thanks,
Adrian.

 

[RoyVidar]

Hi!

The one, if someone, is thick, it's me for not being able to provide you with help!

Dao will work on a2k if you, in any module, use Tools | References and select the Microsoft DAO 3.N Object Library (N = numeric, probably 6).

So either use the above and
[tt]dim rs as dao.recordset[/tt]
or
[tt]dim rs as object[/tt]

(litle explanation; declaring rs as a recordset in a2k and using recordsetclone will produce an error, because the forms recordset are DAO (unless you're using adp/SQL server backend) and the default recordset in a2k is ADO. So the error might also be cause .findfirst is a DAO method and rs is declared as default (ADO))

Highlighting the !Description, ususally means there isn't a text control on your form called Description. Here I suspect there's 'nother issue -> description being a text, which means text qualifiers are necessary:

[tt]rs.findfirst "Description = '" & Me!Description & "'"[/tt]

Note the single quotes (')

I've only tested this on XP (should be the same as 2000), so I'm firing up my old a2k machine and test there too.

Roy-Vidar

 

[RoyVidar]

...works as it should in 2000 too.

Either
[tt]Private Sub ShowCurrentRecord()
Dim rs As dao.recordset
' if the Microsoft DAO 3.N Object Library is checked
If IsLoaded("Status_Form&quot Then
Set rs = Forms!Status_Form.RecordsetClone
rs.findfirst "Description = '" & Me!Description & "'"
If Not rs.BOF Then Forms!Status_Form.Bookmark = rs.Bookmark
Set rs = Nothing
Forms!Status_Form.SetFocus
End If
End Sub[/tt]

or
[tt]Private Sub ShowCurrentRecord()
Dim rs As object
If IsLoaded("Status_Form&quot Then
Set rs = Forms!Status_Form.RecordsetClone
rs.findfirst "Description = '" & Me!Description & "'"
If Not rs.BOF Then Forms!Status_Form.Bookmark = rs.Bookmark
Set rs = Nothing
Forms!Status_Form.SetFocus
End If
End Sub[/tt]

Roy-Vidar

 

[OzSupra]

Fantastic! Adding those single quotes did the trick.
Many Thanks for your time and effort. Much appreciated.
Given you a star, wish it was more.
Adrian.

 

[RoyVidar]

Thank you, thank you! You're to kind! It's a pleasure to assist, specially when offered tips provide solutions!

Roy-Vidar