passing variables to a field

S

sjk153

I have a function that returns the value of the last autonumber from a table
and want to pass that number into another table? Any thoughts on how to do
it?
 
O

Ofer

Hi Scott
If you want the function to return the last record enterd, you need to make
few changes to your function.
1. You need to open the table using order by, by the field that indicate
which record was the last to be entered
2. If you want the function to return the value, so you can use it outside
this function, you need to either assign the value returned to the function
name, Or declare the varLastRecord as Global

Option Compare Database
Option Explicit
Global varLastRecord As Double
Function GetTask()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("Select * From tblTask Order By [FieldName]")

If rec.RecordCount > 0 Then
rec.MoveLast
varLastRecord = rec(0)
GetTask = varLastRecord
End If
End Function
=================================
And to insert the value in a table, you can do it outside the function, and
that incase you want to use this function in other places, just to return the
max value

Docmd.RunSQL "INSERT INTO tblNewTasks ( TaskID ) VALUES (" & GetTask() & ")"

-
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck
 
T

Tom Wickerath

PS.

Change:
Dim varLastRecord As Integer

to

Dim varLastRecord As Long

The reason being that an autonumber is a long integer, and can easily exceed
the upper limit allowed as an integer. I think I'd rename the variable as
well to lngLastRecord.

Tom
__________________________________________
 
S

sjk153

Tom:
Here is the function I wrote:
Function GetTask()
Dim db As Database
Dim rec As Recordset
Dim varLastRecord As Integer
Dim tbl As TableDef

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblTask")
rec.MoveLast

varLastRecord = rec(0)
Debug.Print varLastRecord
rec.Close
End Function

I used the Debug.Print to confirm that varLastRecord has the value I want
but I am not sure how to take the next step to get that value into a table.
I played around with append queries but could not get anything but blank
records to add.
Thank you in advance for any help you may be able to provide.

Scott
 
T

Tom Wickerath

One more thing....

The procedure you wrote, and I modified, will work only if the TaskID
autonumber field in tblTasks is set to Increment. If it is set to Random,
then the last record entered will not have the highest numeric value.

Tom
____________________________________________
 
T

Tom Wickerath

Hi Scott,
I played around with append queries but could not get anything but
blank records to add.
Click to expand...

Try this:

*********************Begin Code*******************
Option Compare Database
Option Explicit

Function GetTask()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim varLastRecord As Integer
Dim strSQL As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblTask")

If rec.RecordCount > 0 Then
rec.MoveLast
varLastRecord = rec(0)

strSQL = "INSERT INTO tblNewTasks ( TaskID ) " _
& "VALUES (" & varLastRecord & ");"

db.Execute strSQL, dbFailOnError

End If

ExitProc:
On Error Resume Next 'Cleanup
rec.Close: Set rec = Nothing
db.Close: Set db = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure GetTask..."
Resume ExitProc
End Function
*********************End Code******************

Notes:

varLastRecord = rec("FieldName") might be a bit safer than
varLastRecord = rec(0)

It would certainly improve the readability of your code.

The line of code above that includes:
"INSERT INTO tblNewTasks ( TaskID ) "

should be modified to insert into the proper table name and field name for
your new table.

Your code, as it was written, is dependent on the priority of references if
you have both the DAO and ADO libraries included as checked references. I
always recommend explicit declarations. For example, use:

Dim rec As DAO.Recordset instead of Dim rec As Recordset

You'll notice that I used this form in the code that I presented above.
The reason is that both libraries (DAO & ADO) include Recordset. The code
will compile fine, as long as one of these libraries is included. However,
you can get a run-time error if the ADO library has a higher priority (listed
before the DAO library). Here is an article where you can read more about
this issue:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/gem_tips1.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom:
Here is the function I wrote:
Function GetTask()
Dim db As Database
Dim rec As Recordset
Dim varLastRecord As Integer
Dim tbl As TableDef

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblTask")
rec.MoveLast

varLastRecord = rec(0)
Debug.Print varLastRecord
rec.Close
End Function

I used the Debug.Print to confirm that varLastRecord has the value I want
but I am not sure how to take the next step to get that value into a table.
I played around with append queries but could not get anything but blank
records to add.
Thank you in advance for any help you may be able to provide.

Scott
__________________________________________

:

By "pass that number into another table", do you mean that you want to add
that number to another table? If so, an append query should work.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a function that returns the value of the last autonumber from a table
and want to pass that number into another table? Any thoughts on how to do
it?
 
Top