HELP with Procedure please!

[MarkNie]

Hi All

I am not good at VB code at all, any help in this would be great.

I got this code from the microsoft website to transpose data in my DB. I get the code in a module and then tested it through the immediate window by using the following:

?Transposer("ytblMISDailyPerformance" , "MISTracking")

But when I run the Function it askes me to select a procedure. I think that has to be the names of the table that is being copied and the name that is being created.

Any help is grand thanks!!!

Here is the Function code I am using now I just need to create a procedure:

Function Transposer(strSource As String, strTarget As String)

Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.Field
Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
Dim i As Integer, j As Integer

On Error GoTo Transposer_Err

Set db = CurrentDb()
Set rstSource = db.OpenRecordset(strSource)
rstSource.MoveLast

' Create a new table to hold the transposed data.
' Create a field for each record in the original table.
Set tdfNewDef = db.CreateTableDef(strTarget)
For i = 0 To rstSource.RecordCount
Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
tdfNewDef.Fields.Append fldNewField
Next i
db.TableDefs.Append tdfNewDef


' Open the new table and fill the first field with
' field names from the original table.
Set rstTarget = db.OpenRecordset(strTarget)
For i = 0 To rstSource.Fields.Count - 1
With rstTarget
.AddNew
.Fields(0) = rstSource.Fields(i).Name
.Update
End With
Next i

rstSource.MoveFirst
rstTarget.MoveFirst
' Fill each column of the new table
' with a record from the original table.
For j = 0 To rstSource.Fields.Count - 1
' Begin with the second field, because the first field
' already contains the field names.
For i = 1 To rstTarget.Fields.Count - 1
With rstTarget
.Edit
.Fields(i) = rstSource.Fields(j)
rstSource.MoveNext
.Update
End With

Next i
rstSource.MoveFirst
rstTarget.MoveNext
Next j

db.Close

Exit Function

Transposer_Err:

Select Case Err
Case 3010
MsgBox "The table " & strTarget & " already exists."
Case 3078
MsgBox "The table " & strSource & " doesn't exist."
Case Else
MsgBox CStr(Err) & " " & Err.Description
End Select

Exit Function

End Function

 

[xlbo]

How are you calling the function ? you need something like

Sub Runner

Call Transposer("ytblMISDailyPerformance" , "MISTracking")

End Sub

If a function takes arguments, you must pass those arguments to it through a sub....


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

This is a function that returns no value. It's really more like a procedure with arguments.

Any function or subroutine with arguments, must be called from another procedure. Could be as simple as...

Sub RunIt()
   dim strSource as string, strTarget as string
   strSource = "???"    'connect string for source
   strTarget = "???"    'connect string for target
   Transposer strSource, strTarget
End Sub

Skip,

_[red][/red]

 

[MarkNie]

Thanks Alot Geoff

That worked like a bomb.

Another question for you though.

Do you know of anywhere that I can get a step by step or similar site to show me how to creat a OLE Automation as I am trying to export data from an Access database into Excel but need the data to be transposed in the process. I can find stuff about OLE Automation but nothing there that I can even begin to understand.

Unexperience in my part.

Thanks for your help again!!!!

 

[xlbo]

Please start a new thread to ask about this

Also, have a search in this forum for "ADO" or "DAO" or "Access to Excel" - there are literally hundreds of examples here





Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question