Opening another form by double-clicking on sub-form field

[topgeek]

I have a sub form on my main form and I want to double click on a field (Site ID) and open up another form.

I have written a piece of code which opens up a dumy form but I cannot work out how to open the form at the particular record I have double-clicked on.

I'd like to be able to show the SiteID in the new form with the records related to it on a sub-form.

Can anyone give me some help please?

 

[Remou]

Hi
Here is a snippet from one I made earlier:

    vFind = "[Title Code]=" _
        & Forms![Members]![Jnlsmem subform].Form![Title Code]

    DoCmd.OpenForm "Journal Titles"

    Set rst = Forms![Journal Titles].RecordsetClone
    With rst
        .MoveLast
        .FindFirst vFind
    End With
    Forms![Journal Titles].Bookmark = rst.Bookmark

 

[TheAceMan1]

How are ya topgeek . . . . .

Try this ([blue]You[/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim frm As Form, rst As Recordset
   
   DoCmd.OpenForm "[purple][b]OpenedFormName[/b][/purple]"
   DoEvents
   Set frm = Forms![[purple][b]OpenedFormName[/b][/purple]]
   Set rst = frm.RecordsetClone
   
   If rst.BOF Then
      MsgBox "No Records!"
   Else
      rst.FindFirst "[SiteID] = [red][b]'[/b][/red]" & Me!TextboxName & "[red][b]'[/b][/red]"
      
      If rst.NoMatch Then
         MsgBox "Record Not Found!"
      Else
         frm.Bookmark = rst.Bookmark
      End If
   End If
   
   Set rst = Nothing
   Set frm = Nothing[/blue]

If [purple]SiteID[/purple] is numeric, remove the two single quotes [red]'[/red] . . .

See Ya! . . . . . .

 

[topgeek]

Thank You AceMan1

I tried your code, but don't understand what the:

& Me!TextboxName & "'"

Line is doing?

Many Thanks

 

[ProgramError]

hi topgeek

It's looking at the contents of the textbox called 'textboxname' on the current form.



Program Error
Programmers do it one finger at a time!

 

[BSman]

Here's another approach:

First, set up a module for your application that defines one (or more) public variables of the appropriate type to pass along needed data. Let's say, for example, the module contains:

Public lngValue as Long

This now means that the variable lngValue is available in your entire application.

Assuming that SiteID is a long integer, when you double click on that control (I'm assuming it's called txtSiteID) on the subform, you should have the following code:

lngValue = me.txtSiteID
docmd.OpenForm ("MyOtherForm", [SiteID] = lngValue)

Or, when MyOtherForm is opened, you could set the filter property to on, and set the filter to properly filter the data using the value of lngValue.

Bob

 

[TheAceMan1]

topgeek . . . .

[purple]TextboxName[/purple] should've been Ne![purple]SiteID[/purple] . . . . its the SiteID on the calling form that [blue]identifies the current record[/blue].

See Ya! . . . . . .