How to jump to a record 2

[HebieBug]

I have a search form which the user types in criteria, selects client and pushes a button.
When they push the button it is meant to take them to the correct record.
I just can't seem to figure out how to take them to the correct record
I am currently using:
(VBA)
-----------------------------
docmd.gotorecord acdataform, "formName", acgoto,
primaryKeyNumber
------------------------------
Anyone know how to jump to the correct record.
It just has me stumped....
Preferably VBA as I use the second form for different actions eg to view and also to add new record

 

[SamDemon]

Could you not you use a combo box on your form, having the user enter all the details into the combo box and searching based on information in the form?

SAM

 

[HebieBug]

the search options are very detailed.
The user can search over 10 different ways to get to their client.
It really has to goto the record based on a primary.

 

[HebieBug]

To add a bit more to it.
I have come up with a very short term solution.
the following code is on the first form:

OpenForm ("secondForm&quot
Do While Form_secondForm.T_Profile_ID.Text <> Me.firstform.Column(0)
DoCmd.GoToRecord acDataForm, &quot;secondForm&quot;, acNext
Loop

It is a very lengthy way of getting to the result. But it might give everyone on the web a better idea of what I am trying to get to.
Should see what it is like as I scan through a thousand records.... and that is just the start of it.

 

[judgehopkins]

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/702/qid/563930

Have a look at that thread.

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[judgehopkins]

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/702/qid/559784

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[HebieBug]

Cool judge,
But already have that part nailed.
The first form is the search they have to push an radio button and then enter the criteria. When they push search it goes into a case statement and then sets a listbox.rowsource to there search criteria.
The rowsource has a column(0) which is the primary key they then push another button called main which takes them to all the clients details.
The clients details (second form) source is then another differnt query which does not have the same order as the query that is set to the listbot.rowource that is why if I say gotorecord command.
The part that I really can't figure out is how to get the second form to open on the correct information relating to the first form (search page).
Now he tells us???????
It's the Kis method gone wrong

 

[judgehopkins]

http://www.tek-tips.com/viewthread.cfm?spid=702&newpid=702&sqid=557149

Ask kappeller for his sample search db.

It's great!

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[HebieBug]

SPOT ON JUDGE!
I got the db and started working my way through the code
the part that I was after is shown below
DoCmd.Openform &quot;FORMNAME&quot;, , , &quot;ID =&quot; & Me.combo.column(0)

 

[danvlas]

That line opens the form filtered
If the condition is wrong, no records are displayed.

I've been using the function below for a long time:

Function SearchRecord(frm As Form, WhatToSearch As String) As Boolean
With frm.RecordsetClone
.Findfirst WhatToSearch
If Not .nomatch Then
frm.Bookmark = .Bookmark
SearchRecord = True
End If
End With
End Function

And your code could be changed to:

DoCmd.Openform "FORMNAME"
Call SearchRecord(Forms![FORMNAME].Form, "ID =" & Me.combo.column(0))

It will open the form and jump to the record without filtering the form.

The good thing is that you can use it for whatever open form from whatever place in the application. And it also works for subforms:
Call SearchRecord(Forms![MainForm]![SubformName].Form, &quot;YourCriteria&quot



HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[judgehopkins]

DoCmd.Openform &quot;FORMNAME&quot;

Daniel, what if the form we want to search on is already opened?

What do we do then?

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[danvlas]

hmmm...just search it?

Call SearchRecord(Forms![FORMNAME].Form, &quot;ID = 15&quot

Or

Call SearchRecord(Forms![FORMNAME].Form, &quot;ID = &quot; & forms!AnotherForm!ATextBox)

Or

Call SearchRecord(Forms![FORMNAME].Form, &quot;ID = &quot; & Screen.ActiveControl.Value)


It works ONLY with (already) open forms (how on earth could we search for a record on a closed form???)

I included the DoCmd.OpenForm in my reply because the poster opened it and applied a filter (which suggested that the form was not open)

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[judgehopkins]

I think that deserves ANOTHER star!

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.