Retrieve Value From Selected Cell in Continuous Form

[tkepongo]

I have a continuous form with many rows and columns. Is it possible that when a user double-clicks a cell, then it will pass the value in the cell into a textbox?

Thanks for the help!

 

[John Big Booty]

In the field's Double Click event put the following code;

Me.YourTextBoxName = Me.FieldThatWasDoubleClicked

 

[missinglinq]

Databases don't have 'cells,' spreadsheets do! What you're calling a 'cell,' in a database, is a Textbox. Why would you want the same data in two separate Textboxes? This, on the face of it, flys in the face of Normalization!

I think you need to give us a plain English explanation of what you're actually trying to accomplish here.

Linq ;0)>

 

[tkepongo]

Databases don't have 'cells,' spreadsheets do! What you're calling a 'cell,' in a database, is a Textbox. Why would you want the same data in two separate Textboxes? This, on the face of it, flys in the face of Normalization!

I think you need to give us a plain English explanation of what you're actually trying to accomplish here.

Linq ;0)>

I want the user to be able to double-click a textbox in the continuous form and then have a specific piece of string (Audit_ID) from that textbox stored into a variable and then somehow, if possible, open a new form and have the current record set to where it's Audit_ID matches with the variable.

 

[missinglinq]

You don't need to use a variable, you can do this using the OpenArgs argument in the FormOpen Command. Not really sure whether you mean that you want the second Form to open to an Existing Record with the matching Audit_ID or you want to create a New Record with that Audit_ID. Thisd code will look for a Matching Record and if found, will move to that Record. If no match is found, it will move to a New Record and insert the Audit_ID.

In the original Form:

Private Sub Audit_ID_DblClick(Cancel As Integer)
If Not IsNull(Me.Audit_ID) Then
  DoCmd.OpenForm "SecondFormName", , , , , , Me.Audit_ID
 Else
  MsgBox "A Audit ID Must Be Entered First!"
 End If
End Sub

Then, in the called Form:

Private Sub Form_Load()

Dim rst As Recordset

If Not IsNull(Me.OpenArgs) Then
 Set rst = Me.RecordsetClone
 
 [COLOR="Red"]rst.FindFirst "[Audit_ID] = '" & Me.OpenArgs & "'"   ' Use this for a Text Audit_ID
 'rst.FindFirst "[Audit_ID] = " & Me.OpenArgs          ' Use this for a Numeric Audit_ID
 [/COLOR] If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   Else
    DoCmd.GoToRecord , , acNewRec
    Me.Audit_ID = Me.OpenArgs
   End If

rst.Close
Set rst = Nothing
End If

End Sub

Note that you have to use one of the two Red Lines, depending on whether Audit_ID is defined as Text or as a Number. Comment out the unused line but leave it in place. That way, if you need to do something similar in the future, but using a different Datatype, you can refer to this example and be reminded of the difference in syntax.

Linq ;0)>

 

[tkepongo]

You don't need to use a variable, you can do this using the OpenArgs argument in the FormOpen Command. Not really sure whether you mean that you want the second Form to open to an Existing Record with the matching Audit_ID or you want to create a New Record with that Audit_ID. Thisd code will look for a Matching Record and if found, will move to that Record. If no match is found, it will move to a New Record and insert the Audit_ID.

In the original Form:

Private Sub Audit_ID_DblClick(Cancel As Integer)
If Not IsNull(Me.Audit_ID) Then
  DoCmd.OpenForm "SecondFormName", , , , , , Me.Audit_ID
 Else
  MsgBox "A Audit ID Must Be Entered First!"
 End If
End Sub

Then, in the called Form:

Private Sub Form_Load()

Dim rst As Recordset

If Not IsNull(Me.OpenArgs) Then
 Set rst = Me.RecordsetClone
 
 [COLOR="Red"]rst.FindFirst "[Audit_ID] = '" & Me.OpenArgs & "'"   ' Use this for a Text Audit_ID
 'rst.FindFirst "[Audit_ID] = " & Me.OpenArgs          ' Use this for a Numeric Audit_ID
 [/COLOR] If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   Else
    DoCmd.GoToRecord , , acNewRec
    Me.Audit_ID = Me.OpenArgs
   End If

rst.Close
Set rst = Nothing
End If

End Sub

Note that you have to use one of the two Red Lines, depending on whether Audit_ID is defined as Text or as a Number. Comment out the unused line but leave it in place. That way, if you need to do something similar in the future, but using a different Datatype, you can refer to this example and be reminded of the difference in syntax.

Linq ;0)>

I'm sorry, I forgot to mention that when I open the second form, the record set is in a subform. Does this affect anything? I tried your code above but it doesn't go to the specified record with matching Audit_ID. When I debug it, the openargs is correct and the rst.NoMatch = False so that it means it worked right? Why isn't it loading the specified record though?

Thanks for the help!

 

[tkepongo]

The code below works with the OpenArgs. Is there a reason why this solution wouldn't be as good as using the rst.Bookmark?

Private Sub Form_Load()
Me.subfrmPlanAudit.Form.Filter = "[Audit_ID] =" & Me.OpenArgs
Me.subfrmPlanAudit.Form.FilterOn = True
End Sub