Copy cell contents to access form

[MM]

Hi folks, I hope someone can help me....

I'm working on a macro to do the following from excel:

i) open an access database if it is not already open
ii) copy the contents of the active cell ("job number") in a worksheet into
an access form and then run a macro to view the related job details.

I am trying to use the Transfer Spreadsheet method but cannot get it to
work. I keep getting eror 3011.

Here's the code:
Sub RunAccessMacro()
Dim appAcc As Access.Application


'Open Access or use it if already running
Set appAcc = New Access.Application

'open desired database
With appAcc
.Visible = False
.OpenCurrentDatabase "C:\Documents and Settings\User\My
Documents\Works in progress\database stuff\Copy of job list.mdb"
.DoCmd.RunMacro "GenSearch" 'opens a search form
.DoCmd.TransferSpreadsheet acImport, , "Search",
"C:\data\control\stations.xls", , ActiveCell

^^^
This is where it goes wrong. Error 3011 says Jet Database Engine could not
find the object "011524A" (i.e. the contents of the ActiveCell)


.DoCmd.RunMacro "Search" 'macro to view job details
.Visible = True
End With
End Sub

Apologies for the scrappy code - it's been scavenged from all over the place
.

Yours hopefully,

Marcel

 

[jasontferrell]

When your line for the transferspreadsheet says "ActiveCell", it's
referring to the active cell of the spreadsheet that's currently
open. This parameter of the TransferSpreadsheet method is looking for
the range that you'd like to import from the Excel file ("A1:B100" or
something like that). The way it's written, it's like you're trying
to move the data from the Excel file into a table in Access called
"Search".

Reading what you actually want to accomplish, it sounds like you might
need to dynamically change the parameters of a query, then display the
result in Access.
Without fully testing, that might look something like this:
With appAcc
.Visible = False
.OpenCurrentDatabase "C:\Documents and Settings\User\My
Documents\Works in progress\database stuff\Copy of job list.mdb"
.CurrentDb.QueryDefs("SearchQuery").Sql = "select * from
SearchTable where Job='" & ActiveCell.Value & "'"
.DoCmd.RunMacro "Search"
.Visible = True
End With

 

[MM]

Hi Jason,

thanks for the reply. (I didn't receive any notification but hey ho)

That looks like the kind of thing I'm after. I'm not familiar with QueryDefs
but I'll have a go at it and see what I can achieve.

Thanks, Marcel

 

[MM]

I finally sussed it out. It was only a matter of time before I stumbled
across the right approach...

For the record, what I have used is the following:

Sub RunAccessMacro()
Dim appAcc As Access.Application
Dim ac As Object

On Error Resume Next 'Open Access or use it if already running
Set ac = GetObject(, "Access.Application")
If ac Is Nothing Then
Set ac = GetObject("", "Access.Application")
ac.OpenCurrentDatabase "C:\Documents and Settings\Me\My Documents\Works in
progress\database stuff\Copy of job list.mdb"
ac.UserControl = True
End If

AppActivate "Microsoft Access"
Set appAcc = GetObject(, "Access.Application")
With appAcc
.Visible = False
.DoCmd.OpenForm "Job details query", , , , , , "" & ActiveCell.Value
& ""
.DoCmd.FindRecord "" & ActiveCell.Value & ""
.Visible = True
End With

End Sub

Thanks for your help all the same