Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

passing variables to a field

Discussion in 'Information Technology' started by sjk153, Nov 8, 2005.

  1. sjk153

    sjk153
    Expand Collapse
    Guest

    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?
     
  2. Loading...


  3. Ofer

    Ofer
    Expand Collapse
    Guest

    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


    "sjk153" wrote:

    > 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
    >
    > "Tom Wickerath" wrote:
    >
    > > 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
    > > __________________________________________
    > >
    > > "sjk153" wrote:
    > >
    > > 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?
    > >
     
  4. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    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
    __________________________________________

    "Tom Wickerath" wrote:

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

    >
    > 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
    > __________________________________________
    >
    > "sjk153" wrote:
    >
    > 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
    > __________________________________________
    >
    > "Tom Wickerath" wrote:
    >
    > 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
    > __________________________________________
    >
    > "sjk153" wrote:
    >
    > 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?
     
  5. sjk153

    sjk153
    Expand Collapse
    Guest

    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

    "Tom Wickerath" wrote:

    > 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
    > __________________________________________
    >
    > "sjk153" wrote:
    >
    > 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?
    >
     
  6. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    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
    ____________________________________________

    "Tom Wickerath" wrote:

    > 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
    > __________________________________________
    >
    > "Tom Wickerath" wrote:
    >
    > > Hi Scott,
    > >
    > > > I played around with append queries but could not get anything but
    > > > blank records to add.

    > >
    > > 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
    > > __________________________________________
    > >
    > > "sjk153" wrote:
    > >
    > > 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
    > > __________________________________________
    > >
    > > "Tom Wickerath" wrote:
    > >
    > > 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
    > > __________________________________________
    > >
    > > "sjk153" wrote:
    > >
    > > 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?
     
  7. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Scott,

    > I played around with append queries but could not get anything but
    > blank records to add.


    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
    __________________________________________

    "sjk153" wrote:

    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
    __________________________________________

    "Tom Wickerath" wrote:

    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
    __________________________________________

    "sjk153" wrote:

    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?
     

Share This Page