openForm at resultRecord from searchForm

[zSplash]

Okay, my searchForm finds a bunch of records ("resultList"). I want to be
able to select one of the records in the resultList to open the MainForm at
that record. On the left-hand part of the list, I have the pk_ID of the
record, and want a double-click on the pk_ID of the desired record will open
the MainForm at that record.

Here's the code I'm using on the double-click event:

Private Sub pk_ID_DblClick(Cancel As Integer)
Dim foundID 'As Long 'String
foundID = ActiveControl.OldValue
Form_1MainForm.getResult foundID
End Sub

I've declared foundID in the 1MainForm module (Public foundID as Long), but
the variable isn't recognized in the Form_Search module, so I declared it
there, too. Then, when I get to the 1MainForm module, I set
foundID=foundID, but it is lost when I get into another sub. (This must be
a basic programming error I've never encountered before, eh?)

Anyway, when I get to the 1MainForm module, here's my code:

Sub getResult(foundID)
foundID = foundID
DoCmd.OpenForm "1MainForm", acNormal, , , , , foundID
Form_Load
End Sub
Private Sub Form_Load()
foundID = "115" 'set it because I lost it
foundID = CLng(foundID)
strWhere = "[1MainForm].[pk_ID]=" & foundID
DoCmd.OpenForm "1Mainform", WhereCondition:=strWhere

End Sub

I get an error asking for the parameter "1MainForm.pkID". So, seems like it
doesn't recognize 1MainForm.pk_ID as the field which I've related from the
Form_Search module.

UffDah. Can anybody lead me back?

TIA

 

[George Nicholson]

1) A variable declared at the top of a form module doesn't exist until the
Form is opened and it is destroyed as soon as the form closes. There are
several ways to pass values between forms, but the simplest would be to
declare foundID in a general module (one that isn't attached to a Form or
Report). In the VBE: Insert>Module. This way the variable will be available
to the entire app & you won't "lose" it until you make a new assignment.
Remove redundant declarations from your Form modules. Also remove any
foundID assignments from both forms other than:
foundID = ActiveControl.OldValue (why OldValue??...no matter)

2) strWhere = "[1MainForm].[pk_ID]=" & foundID
As long as [pk_ID] is a field name in the recordset of the underlying the
form, [1MainForm] is unnecessary & misunderstood (hence the prompt). Change
to:
strWhere = "[pk_ID]=" & foundID

HTH,

 

[Guest]

Hi zSplash,

Here's an example of how I do it for records displayed in a subform of a QBF
(Query By Form) search form. Each field in the subform as the following event
procedure. This way, the user can double-click anywhere within the selected
record:

On Dbl Click........ =OpenRecordForEditing()


The subform includes this code:

Option Compare Database
Option Explicit

Function OpenRecordForEditing()
On Error GoTo ProcError

If Not IsNull([TitleNo]) Then
DoCmd.OpenForm "frmMovieTitles", _
OpenArgs:=1, datamode:=acFormEdit, _
WhereCondition:="[TitleNo] = " & [TitleNo] & ""
End If

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in OpenRecordForEditing event procedure..."
Resume ExitProc
End Function


Note: TitleNo is the primary key, in this case.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

You are trying to open 1MainForm with code in its own module, which doesn’t
make a lot of sense. I can see how you are trying to do it, but you can do
it very much more simply with a single line of code in the DblClick event
procedure of the control on the search form, and none at all in 1MainForm's
module:

DoCmd.OpenForm "1MainForm", _
WhereCondition:="pk_ID = " & Me.ActiveControl

I'm assuming the control on the search form is a list box and the pk_ID
column in the list is the bound column, which it is if it’s the leftmost
column, whether visible or hidden, and the list box's BoundColumn property is
1. I'm also assuming pk_ID is a number data type, not text. If it’s text
data type then wrap the value in quotes:

DoCmd.OpenForm "1MainForm", _
WhereCondition:="pk_ID = """ & Me.ActiveControl & """"

Even if 1MainForm is already open at another record when you double click
the row in the list box it will be filtered to the record you select.

Ken Sheridan
Stafford, England