Open Continuous form to edit datasheet subform record 1

[fishtek]

Hello:
I have a subform in datasheet view that displays some (but not all) data in a table. I would like to have the user browse the records in the subform and then click on the date field in the subform to have a continuous form open for editing all data for that record. Thanks for any help.

 

[dhookom]

How far have you gotten?

I would try use the command button wizard in the design view of the datasheet form. Use the wizard to write the code to open your continuous form based on a value in your datasheet subform. When the wizard is finished, modify the code to be called in the Double-Click event of the date field.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya fishtek . . .

You could also use the forms [blue]On Dbl Click[/blue] event and simply double click the [blue]record selector[/blue] of the desired record.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[fishtek]

Great!.. thanks I got the continuous form to open, but it opens filtered to all records on the subform. How do I get it to open to just the record (date record) I double clicked in the subform.
Thanks

 

[dhookom]

Did you use the "wizard to write the code to open your continuous form based on a value in your datasheet subform"?

This uses the Where Condition of the DoCmd.OpenForm to apply a filter to the opened form.

Duane
Hook'D on Access
MS Access MVP

 

[fishtek]

Yes the continuous form opens based on a value on the subform that is not visible. Thats fine, but I also want to further filter by the record I dbl click on that is visible in the datasheet view. In other words i would like the user to be able to go to the specific record (line) in the datasheet subform using the continuous form. Thanks again for talking me through this.

 

[dhookom]

Can you:
1) share the code that opens the continuous form
2) share the field names in the subform and continuous form that should be the same
3) provide the data type of the field names from 2)


Duane
Hook'D on Access
MS Access MVP

 

[fishtek]

Thanks for your continued help dhookom:

The form opens with an embedded macro. I converted the macro to a module and the code is as follows:


Function mcrOpenTest()
On Error GoTo mcrOpenTest_Err

With CodeContextObject
DoCmd.OpenForm "fmTesRec", acNormal, "", "[NPDES]=" & "'" & .NPDES & "'", , acNormal
End With


mcrOpenTest_Exit:
Exit Function

mcrOpenTest_Err:
MsgBox Error$
Resume mcrOpenTest_Exit

End Function

End of code


The continuous form uses [NPDES] which is a text field but opens to too many records. I would like the user to click on a date in the subform and have the continuous form open the the NPDES record for that specific date. The field in the subform for date is [Test Date]. So I guess I'm looking to filter the continuous form by [NPDES] and [Test Date] off the subbform.
Thanks Again

 

[dhookom]

Assuming the code is run from your subform and your Test Date field is bound to a control named txtTestDate.

Function mcrOpenTest()
On Error GoTo mcrOpenTest_Err
    Dim strWhere as String
    With CodeContextObject

        strWhere = "[NPDES]=" & "'" & .NPDES & "' " & _
           "AND [Test Date] = #" & .txtTestDate & "# "
        DoCmd.OpenForm "fmTesRec", acNormal, "", strWhere, , acNormal
    End With


mcrOpenTest_Exit:
    Exit Function

mcrOpenTest_Err:
    MsgBox Error$
    Resume mcrOpenTest_Exit

End Function

Duane
Hook'D on Access
MS Access MVP

 

[fishtek]

Thanks so much Dhookom! That worked fantastic! I appreciate all your help.