Current Row in the Datasheet triggers a new form

[aBill]

Pretty Basic.

1.) I am new to making these things in Access, so bear that in mind.

I have a form that will be displayed in a datasheet view. The form's datasource selects about 30 fields from two tables. Only 4 of these fields are displayed on the form.

I need to bring up a a new form to display the entire 30 or so fields for a record when the row is selected(double clicked or whatnot) in the datasheet view.

How can i acheive this? Basically I am looking to pass the record selected (including all of the 30 fields not displayed in the datasheet) to the other detailed form.

Thanks.

 

[jbehrne]

Hi,

In your field's "On Click" event run some code like this:


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form2Name"

stLinkCriteria = "[UniqueID]=" & Me![UniqueID2]
DoCmd.OpenForm stDocName, , , stLinkCriteria

- Basically, you will be passing the record ID to the other form and the other form will open itself up to that record ID and display all values.

HTH,

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[psemianonymous]

There are a few parts to this:

1. Figure out which record is currently selected
2. Figure out how to trigger a form open
3. Figure out how to pull up selected record in new form



Answers:

1. There may be a better way to do this (i.e. accessing the recordset directly), but I usually have a textbox control bound to a data field. So I just reference "txtControlName.Value" and it retreives the info of the currently highlighted record. *this works even in datasheet mode*.

2. If you're in datasheet mode in the subform, add a button or something to the main form with caption "open new form". On the event, do something like:

Sub btnOpenForm_Click()
    DoCmd.OpenForm "NewForm"  'also have options
End Sub

3. There are different ways to handle this. My favorite: using the OpenArgs parameter to pass in information. For example, you pass in "102395" (the unique ID number) as the OpenArgs in the DoCmd.OpenForm line. THEN, in the Form_Open() event:

Sub Form_Open(Cancel as Integer)
    If IsNull(Me.OpenArgs) Then
        Cancel = True
        Exit Sub
    End If

    Me.RecordSource = "SELECT * FROM [[[[[tblName]]]]] WHERE ID = " & Me.OpenArgs
End Sub

Something like that. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[aBill]

Thanks jbehrne,

What is the UniqueID and UniqueID2?

 

[jbehrne]

These are the key fields between the two forms. UniqueID would be the primary key of your first record and UniqueID2 would be the primary/foreign key of the second record (of course you don't have to use key fields, the two fields just have to have the same value). Here is an example that I am using in one of my forms:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PaymentVoucherMain" 'My form name

stLinkCriteria = "[PVAssignedNumber]=" & Me![PVAssignedNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

- PVAssignedNumber is a user input field that is being used in both forms (this is the same value - but it is on both forms). So you need to pass a value to the second form that is equal to the first form,

HTH,

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations