Hello;
Access 2000. I have two forms, each form has one listbox. Form1 opens
and the listbox displays all the data from a table (it is based on a
query called query1).

The user double clicks on the record they want to see details for. On
that event the second form (Form2) opens up and only the selected
record (from Form1) should show up. The Form2 is based on a query
that has a parameter for the id field.

I have to this point been able to code an event that captures the
value of the id field of the selected row and pass it into a
variable. I just can't get that value to automatically pass into the
parameter of query2 in Form2. What happens is the parameter input
opens up and I still have to manually type in the value. How do I make
happen programatically?

Here is the code:

Private Sub List0_DblClick(Cancel As Integer)

'open a form and pass whatever baseid was double clicked
'to the parameter of the query the form is built off of
Dim ptid As Integer
ptid = Forms!Form1!List0.Column("0")

MsgBox ("the value is" & ptid)
DoCmd.OpenForm "Form2", acNormal

End Sub

On Feb 23, 4:01*pm, Tony_E <bluestealt... (AT) hotmail (DOT) com> wrote:
Hi Tony,

You can assign ptid to OpenArgs in the OpenForm statement, and use the
value inside Form2.

Imb.

On Feb 23, 4:01*pm, Tony_E <bluestealt... (AT) hotmail (DOT) com> wrote:
Hi Tony,

You can assign ptid to OpenArgs in the OpenForm statement, and use the
value inside Form2.

Imb.

imb wrote:

Or do a
Docmd.Openform "YourFormName",,,"Id = " & Me.YourListBoxName.Column(0)

The above assumes the first column (index starts at 0) is the key. The
Column property and ListIndex property are worth knowing.

Tony_E wrote:

Queries know nothing about VBA variables.

You could use a hidden text box instead of the VBA variable,
in which case the query parameter would look like
Forms!Form1.[hidden text box]

But, it's usually better to remove the criteria with the
parameter from the query and use the OpenForm method's
WhereCondition the filter the form:

'open a form and pass whatever baseid was double
clicked
'to the parameter of the query the form is built off
of
Dim strWhere As String
strWhere = "[id field]=" & Me!List0.Column("0")

MsgBox ("the value is" & ptid)
DoCmd.OpenForm "Form2", acNormal, , strWhere

--
Marsh

On Feb 23, 11:25*am, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Hi ; thanks for your ideas. I am sure they are correct and I am sure
I am doing something wrong. I tried both methods:

DoCmd.OpenForm "Form2", , , , , , ptid --> this opened up the
parameter input from query2 (from the second listbox)
I am trying to pass the value without the parameter input
opening.

I also tried:

DoCmd.OpenForm "Form2", , , "CustomerID = " & Forms!Form1!
List0.Column("0")
the parameter input opened twice this time

I pointed the Form2 to the query as well because earlier I got a
message that the program couldn't work because the form was not built
off of a table or query. I don't quite understand why I had to do
that but I did it. If you can think of something else I missed please
let me know.

thanks so much
Tony

Tony_E wrote:

I have no idea what your recordsource is for Form2.

My earlier example simply opens the form and filters for a specific
record.

If you use an OpenArgs approach you can do something like this example
on the OnOpen event.
If Not Isnull(Me.OpenArgs) then
Me.Filter = "YourID = " & Me.OpenArgs
Me.FilterOn = True
endif

Hi Tony,

There are several ways.

For bound forms Marsh’ suggestion is probably the best in using
strWhere.

An example of using OpenArgs in an unbound form is, by including this
line in the OnOpen event of Form2:
Me.RecordSource = “SELECT * FROM This_table WHERE CustomerId = “ &
Me.OpenArgs


Imb.

On Feb 23, 12:34*pm, imb <im... (AT) onsmail (DOT) nl> wrote:
Hello;
I programmed the onopen event of form2 and what happens is the listbox
on form2 ignores it: It still lists all the records from the table as
opposed to just the one that was double-clicked on in the listbox on
form1. Should I remove the recordsouce info from the listbox in form2?

On 23/02/2011 17:34:22, imb wrote:
Try

Private Sub List0_DblClick(Cancel As Integer)

If Not IsLoaded("Form2") Then
DoCmd.OpenForm "Form2"
End If

DoCmd.SelectObject acForm, "Form2"
If Nz(List0) = 0 Then
DoCmd.GoToRecord acForm, "Form2", acNewRec
Else
DoCmd.GoToControl "CustomerID"
DoCmd.FindRecord List0
End If

End Sub

This is a useful function to find if a form is already open

Function IsLoaded(ByVal strFormName As String) As Integer
' ' Returns True if the specified form is open in Form view or Datasheet
view.

Const conObjStateClosed = 0
Const conDesignView = 0

If If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then If Forms(strFormName).CurrentView <> conDesignView
Then IsLoaded = True
End If
End If

End Function

This can be useful after updating Form2 for with a new record to get the lis
or combo box in form1 to show the new details Private Sub Form_AfterUpdate()

If IsLoaded("Form1") Then
If Nz(Forms!Form1!List0) = 0 Then ' Empty
Forms!Form1!List0 = 0
Forms!Form1!List0.Requery
Forms!Form1!List0 = CustomerID
End If
End If

End Sub

Phil