Pass Variable to Access Textbox via excel dbl click event

I am probably dreaming but thought I would ask to see if it's possible.

The database contains all the reports, data etc. for the excel sheet. What I am looking to see if it can happen, is in Column B of my tracking sheet is a RCN number.. it is basically the report number of the data in the database pertaining to that report.. so kinda the key if you will.

In the database we take this number type it into the text box that is named RCN and hit enter, it opens a word document with the report...

What I am wondering.. is there a way to double click the RCN number in Column B and it autofill the RCN text box of the database.

The database will be open....

Ok.. am I dreaming or is this possible? I know I can get a number *from* access and populate a excel field, I am just not sure about populating the textbox via excell

Excel Gods... thank you...

Thanks in advance.

Re: Pass Variable to Access Textbox via excel dbl click event

Hi Rory,

Thanks for the reply... the report is ran by having the report number typed into the text box, then when you would hit enter, it runs the Macro "ExportToReport"

That Macro is:

ACTION                          ARGUMENTS
-------                           ------------
Echo                               No,
RunSavedImportExport       Export-vRCN REPORT

The Export-vRCN REPORT is the name of the saved steps to open the report (I am an excel guy, so I will take their word for it)

So.. recap...

I type in the report number from my tracking sheet to pull the report.. 5851 ... I type that in the textbox on the form and hit enter, it runs the report and opens a .rtf word file with the report.. I save the word document and then.. next number...

so what I am trying to do is pass the RCN # ie 5851 to the form, and then either I hit enter, or even better, it runs the report.. I would like to "pass it" via double clicking in the RCN # in Column B to pass or run the report.. if it's possible.

Thanks again.. I will tinker with your code..

Joe

Re: Pass Variable to Access Textbox via excel dbl click event

Rory,

Thanks that works perfectly.. now I am off to find out how to run a macro in access from the same double click function... Here is the code I used, it pulls from the cell I am double clicking in, incase anyone is looking for this type of code...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
Dim REPORTFILENAME, TRACKNUMBER, TYPEINSP As String
Dim DataObj As New MSForms.DataObject
Dim objAcc As Object
If Target.Column <> 2 Then Exit Sub
   
' This is Rory's code on passing the double click'd cell contents to access
' My Textbox name is RCN
     
   Set objAcc = GetObject(, "Access.Application")
   objAcc.Screen.ActiveForm.Controls("RCN").Value = Target.Value
        


'This part collects variable information for a file name
 
    TRACKNUMBER = ActiveCell.Offset(0, -1).Value
    TYPEINSP = ActiveCell.Offset(0, 5).Value
    
' This combines the type of inspection puts in a dash and then adds the tracking number
    REPORTFILENAME = TYPEINSP & "-" & TRACKNUMBER
    
' This part puts the combined variable to the clip board, so
' on the saveas dialog from the word report that I am saving
' all I have to do is Ctrl V to paste the file name over the default
' report name
 
    DataObj.SetText REPORTFILENAME
    DataObj.PutInClipboard
   
' This selects 6 cells right of the target (where I double click)
' this is the next area I need filled in on the tracking sheet.
 
    ActiveCell.Offset(0, 6).Select
End Sub