Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Question Unanswered: Update a record your not currently viewing...

    Hello DBForums,
    This is my first post. I wish it was helping someone, but on this subject I'm pretty far from being able to help others.

    I have a database with a table with owners [Owners], a table with property [Lots] and a table linking owners to the table [Lot Ownership]. Each table has an ID, the [Lot Ownership] table has the [Owners] and [Lots] linked using their IDs, and also has information on when the lot was purchased and sold. I use the fields "Owned from" and "Owned to" to decipher when it was bought and when it was sold. So if the "Owned to" field is NULL then that entry IS the current owner.

    I need a quick way to update a lot with a new owner.

    I was assuming I would have a form showing lot information with a subform of previous owners and the current owner. This form would have a button on it to change ownership. This button would look through the tables (using a query i guess) for the CURRENT OWNER and put today as the "Owned to" date. and then maybe pop up a box asking for the new owner, with a list out of the owners table (with maybe a search box at the top) and if needed be able to add an owner for this property.

    This problem seems easy in my head, but when figuring out how to do this in access, something (if not everything) is evading me.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Welcome to the forum!

    You can easily update a row in a table using a SQL statement:
    Code:
    Dim strSQL as String
    strSQL = "UPDATE <TableName> SET <Column_1_Name> = <Value_1>, ..., <Column_x_Name> = <Value_x> WHERE <Row_Id> = <Id>"
    CurrentDb.Execute strSQL
    In the same way, you can insert a new row into a table:
    Code:
    Dim strSQL as String
    strSQL = "INSERT INTO <TableName> ( <Column_1_Name> , ...,   Column_x_Name> ) VALUES ( <Value_1>, ..., <Value_x> )
    CurrentDb.Execute strSQL
    In this case, you can get the values directly from a SELECT statement:
    Code:
    Dim strSQL as String
    strSQL = "INSERT INTO <TableName> ( <Column_1_Name> , ...,   Column_x_Name> ) SELECT <Col1>, ... <Colx> FROM <Some table> WHERE <Conditions> )
    CurrentDb.Execute strSQL
    In both cases, the number of columns in the VALUES or SELECT part of the statement must match the number of columns in the column list.

    For "picking" a value from a column in a table, you can use a domain function, such as:
    Code:
    Dim SomeValue as Variant
    SomeValue = DLookUp("<Column_Name>", "<Table or Query Name>", "<Conditions>")
    If you do not define the variable as variant, you have to care for Null values in the table. This will yield an error if DLookup returns Null:
    Code:
    Dim strValue as String
    SomeValue = DLookUp("<Column_Name>", "<Table or Query Name>", "<Conditions>")
    A solution consists in using the Nz function:
    Code:
    Dim lngValue as Long
    Dim strValue as String
    lngValue = Nz(DLookUp("<Column_Name>", "<Table or Query Name>", "<Conditions>"), 0)
    strValue = Nz(DLookUp("<Column_Name>", "<Table or Query Name>", "<Conditions>"), "")
    If you have several values to be fetched from a table (or query), opening a recordset is probably more efficient:
    Code:
    Dim Value1 as Variant
    Dim Value2 as Variant
    Dim Value3 as Variant
    Dim Value4 as Variant
    Dim Value5 as Variant
    Dim rst As DAO.RecordSet
    Dim strCriteria as String
    
    Set rst = CurrentDb.OpenRecordSet("Table1", DBOpenSnapshot)
    strCriteria = "ColumnId = 122"
    rst.FindFirst strCriteria
    If rst.NoMatch = False Then
        Value1 = rst!Column1
        Value2 = rst!Column2
        Value3 = rst!Column3
        Value4 = rst!Column4
        Value5 = rst!Column5
    Else
        ' No record found
    End If
    rst.Close
    Set rst = Nothing
    Notice that you can also open the recordset on a SQL statement with the condition in it:
    Code:
    Dim Value1 as Variant
    Dim Value2 as Variant
    Dim Value3 as Variant
    Dim Value4 as Variant
    Dim Value5 as Variant
    Dim rst As DAO.RecordSet
    Dim strSQL as String
    
    strSQL = "SELECT * FROM Table1 WHERE ColumnId = 122"
    Set rst = CurrentDb.OpenRecordSet(strSQL, DBOpenSnapshot)
    If rst.BOF = False Then
        Value1 = rst!Column1
        Value2 = rst!Column2
        Value3 = rst!Column3
        Value4 = rst!Column4
        Value5 = rst!Column5
    Else
        ' No record found
    End If
    rst.Close
    Set rst = Nothing
    Have a nice day!

  3. #3
    Join Date
    Apr 2010
    Posts
    2

    Thumbs up Thanks!

    Its going to take me a while to read through this code to understand it, but this is EXACTLY what I wanted. Ill post back with any questions I find. Thanks again!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •