convert ac2

[richard luft]

Hi: I'm trying to convert an AC2 Db into AC2000, and am having a bit of trouble with the following code. This is to serve to get numbers from a 'counter Db".
>>>>>>>
Dim wrkCurrent As WorkSpace
Dim dbCounter As DAO.Database (I've added the 'DAO." for AC2000"--not present in the original)
Dim recCounter As DAO.Recordset
Dim lngNextCounter As Long
Dim lngWait As Long
Dim lngX As Long
Dim intLockCount As Integer
DoCmd.Hourglass True

intLockCount = 0

' Open a recordset on the approrpriate table in the counters database,
' denying all reads to others while it is open

Set wrkCurrent = CurrentDb AT THIS LINE I GET ERRMSG = TYPE MISMATCH!
Set dbCounter = wrkCurrent.OpenDatabase(strCounterDbName, False)

As mentioned above, at next-to-last line of code, I get errmsg as indicated. Any suggestions as to what corrections are necessary for AC2000 compiler?

 

[WayneRyan]

Richard,

Never used WorkSpace ... how 'bout DAO.WorkSpace???

Wayne

 

[richard luft]

No ,but problem more confusing than I first wrote. The following code comes right after the code I originally wrote:
>>>>>>>>>>>>>
'EDIT FOR COUNTER TABLES IN SAME COUNTER.DB

Set recCounter = dbCounter.OpenRecordset(strtablename & "_EntryNum", DB_OPEN_TABLE, DB_DENYREAD)

' Increment and return the counter value
recCounter.MoveFirst

recCounter.Edit
>>>>>>
If I include DAO with workspace, recordset, and database dims, then when I run code it stops at line "wrkcurrent = Current DB" with errmsg of "Type Mismatch"
However, if I eliminate DAO from these Dims, then the code stops at " recCounter.Edit" with errmsg "Compile error: method or data member not found.
Any suggestion what this means & how to correct?

 

[Bat17]

Long time since I used A2 but try using
Dim wrkCurrent As Database

Peter

 

[richard luft]

nope, that causes other problems.
Here's the code (complete) as written for Ac2, which might help someone to figure a solution:
>>>>>>>>
Function GetNextCounter (ByVal strtablename As String) As Long

' Returns the next custom counter value for a particular
' table. Counters are stored in the database COUNTER_DB
' in tables with _EntryNum appended for which they supply counters.
' Returns -1 if a valid counter value cannot be retrieved
' due to locking problems.
' In:
' strTableName: the name of the table for which the counter
' is to be retrieved.
' Out:
' Return value: A Long containing the new counter value,
' or -1 if the routine couldn't provide a counter value.
' Example:
' lngNewEntryNum = GetNextCounter(strTableName)

On Error GoTo glrGetNextCounter_Err

Dim wrkCurrent As WorkSpace
Dim dbCounter As Database
Dim recCounter As Recordset
Dim lngNextCounter As Long
Dim lngWait As Long
Dim lngX As Long
Dim intLockCount As Integer
DoCmd Hourglass True

intLockCount = 0

' Open a recordset on the approrpriate table in the counters database,
Set wrkCurrent = DBEngine.Workspaces(0)
Set dbCounter = wrkCurrent.OpenDatabase(strCounterDbName, False)


'EDIT FOR COUNTER TABLES IN SAME COUNTER.DB

Set recCounter = dbCounter.OpenRecordset(strtablename & "_EntryNum", DB_OPEN_TABLE, DB_DENYREAD)

' Increment and return the counter value
recCounter.MoveFirst
recCounter.Edit
lngNextCounter = recCounter![NextCounter]
recCounter![NextCounter] = lngNextCounter + 1
recCounter.Update

glrGetNextCounter = lngNextCounter

recCounter.Close
dbCounter.Close

 

[Bat17]

This is not complete code either, there appears to be the error handling missing at the end of it. where does the value strCounterDbName come from? is a global variable?
it looks as if the function was originaly "glrGetNextCounter" not "GetNextCounter"

Peter

 

[richard luft]

Yes, I left off the errorhandling at the end to shorten for reader.
"StrCounterDbname" comes from calling function.
Yes, full name is "glrGetNextCounter", which I forgot to change in all places on the submitted code.

 

[Bat17]

Function GetNextCounter (ByVal strtablename As String) As Long

"StrCounterDbname" comes from calling function.

it is not being passed to the function so is it a global variable?

Peter

 

[richard luft]

yes, it's global

 

[Bat17]

As you have posted a couple of versions it is a job to spot the error. try this and see where it bombs out

Function GetNextCounter(ByVal strtablename As String) As Long


' Returns the next custom counter value for a particular
' table. Counters are stored in the database COUNTER_DB
' in tables with _EntryNum appended for which they supply counters.
' Returns -1 if a valid counter value cannot be retrieved
' due to locking problems.
' In:
' strTableName: the name of the table for which the counter
' is to be retrieved.
' Out:
' Return value: A Long containing the new counter value,
' or -1 if the routine couldn't provide a counter value.
' Example:
' lngNewEntryNum = GetNextCounter(strTableName)

'On Error GoTo glrGetNextCounter_Err
Dim 
Dim wrkCurrent As Workspace
Dim dbCounter As dao.Database
Dim recCounter As dao.Recordset
Dim lngNextCounter As Long
Dim lngWait As Long
Dim lngX As Long
Dim intLockCount As Integer
DoCmd.Hourglass True 

intLockCount = 0

' Open a recordset on the approrpriate table in the counters database,
Set wrkCurrent = DBEngine.Workspaces(0)
Set dbCounter = wrkCurrent.OpenDatabase(strCounterDbName, False)


'EDIT FOR COUNTER TABLES IN SAME COUNTER.DB

Set recCounter = dbCounter.OpenRecordset(strtablename & "_EntryNum", DB_OPEN_TABLE, DB_DENYREAD)

' Increment and return the counter value
recCounter.MoveFirst
recCounter.Edit
lngNextCounter = recCounter![NextCounter]
recCounter![NextCounter] = lngNextCounter + 1
recCounter.Update

GetNextCounter = lngNextCounter

recCounter.Close
dbCounter.Close

peter

 

[richard luft]

Perfect! Thanx much