Open form on double click

[Dr Poot]

Hello, I would like some help to open up a form on a double click on an item
in a list (called list0). My relationships are:
one Product_ID - many Job_ID
one Job_ID - many Tape_ID
one Tape_ID - many Segment_ID
All ID's are autonumber. The main form has a subform, which has a subform,
which has a subform (ie, nested subforms - confirming table relationships).
The list contains ID's - Product_ID as column(0), and the second subform -
Tape_ID as column(1). When the user double clicks on an entry in the list ,
I would like the form to open (called frmProduct) with the corresponding data
in the second subform, being the subform relating to Tape_ID. So far I've
guessed this:

If Me.List0.Column(6) = "" Then
DoCmd.OpenForm "frmProduct", acNormal, , "frmSubTape!Tape_ID = " &
Me.List0.Column(1)

Else
DoCmd.OpenForm "frmProduct", acNormal, , "Product_ID = " & Me.List0.Column(0)

End If

The ELSE statement works fine, however the first IF statement doesn't *work*
in the way I've wanted, described above. I'm just not sure how to refer to
the sub-subform ID called Tape_ID.

Any help would be appreciated - and I can easily submit more information if
required. Many thanks,

- Matthew

 

[Svetlana]

Hey Matthew try this
If Me.List0.Column(6) = "" Or IsNull(Me.List0.Column(6))=True Then

 

[Douglas J. Steele]

Probably better to use:

If Len(Me.List0.Column(6) & vbNullString) = 0 Then

or

If Len(Me.List0.Column(6) & "") = 0 Then

 

[Dr Poot]

Thanks for both your feedback, however the IF condition was never a problem.
In my previous post, I should have said that my THEN statement is the one I
need to rectify to get the result I want.

Basically when the user double clicks the entry in the list (each entry has
multiple columns, column(1) being the Tape_ID), I want frmProduct to open
such that the value in text box named Tape_ID located in subform frmSubTape
which is nested in the subform frmSubJob which is nested in the form
frmProduct to match the Tape_ID as in column(1) in the entry the user double
clicked on. Just so you don't have to go back, my current code again is here:

If Me.List0.Column(6) = "" Then
DoCmd.OpenForm "frmProduct", acNormal, , "frmSubTape!Tape_ID = " &
Me.List0.Column(1)

Else
DoCmd.OpenForm "frmProduct", acNormal, , "Product_ID = " & Me.List0.Column(0)

End If


Thanks again,
- Matthew

 

[Svetlana]

You may need the full reference for your forms and controls as like
Forms!frmProduct!frmSubTape!TapeID=" &
Forms!frmProduct!frmSubJob!frmSubTape!TapeID.Column(1) or you could
pass the value of TapeID.Column(1) into a public variable.

 

[Dr Poot]

Ok after much trial and error, I have come up with another question - in the
line of code:

DoCmd.OpenForm "frmProduct", acNormal, , "frmSubTape!Tape_ID = " &
Me.List0.Column(1)

where I've written frmSubTape!Tape_ID in the WhereCondition part, I think
this is meant to be a reference to a field in the table, rather than a
textbox in the form. Is this true?

Svetlana, I tried fully qualifying the name but with no luck.. Thanks anyway.

- Matthew

 

[Douglas J Steele]

What you currently have as frmSubTape!Tape_ID needs to be a reference to a
field that's contained in the recordset that's bound to the form. As
written, it definitely will not work.

 

[Dr Poot]

That's right, it'll never work. So what I did was base frmProduct on a query
rather than the table tblProduct. The query is:

SELECT tblProduct.*
FROM tblProduct INNER JOIN (tblJob INNER JOIN tblTape ON tblJob.Job_ID =
tblTape.Job_ID) ON tblProduct.Product_ID = tblJob.Product_ID;

which puts Tape_ID in the recordset that is bound to frmProduct. In fact I
put a textbox(txtTapeID) in frmProduct bound to Tape_ID just to show it was
really happening. However, when I scroll through frmSubTape, the value in
txtTapeID doesn't update. So I try put a So now, my code under double
click on list0 looks like:

DoCmd.OpenForm "frmProduct", acNormal, , "Tape_ID = " & Me.List0.Column(1)
'Column(1) relates to the Tape_ID for that record

This appears encouraging with clear reference to Tape_ID and Column(1).
However upon execution, I get a runtime 2501 error.

Will keep looking, hope that by process of trying everything, I will hit the
nail's head soon enough.

Thanks for your feedback.
- Matthew