Results 1 to 9 of 9

Thread: Clearing Fields

  1. #1
    Join Date
    Jul 2011
    Posts
    16

    Unanswered: Clearing Fields

    Hello There i recently set up a database and forms to go with it.
    however on the form i want it so that when i press the close macro it clears certain fields of that record is this possible>

    Many Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes it is.
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    16
    how is it?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Without concrete information it's hard to supply a concrete answer. See:http://www.dbforums.com/microsoft-ac...ml#post4534485
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    16
    Well i have a database table:
    with four fields so when a item is signed out all 5 fields are filled in on a form but when there signed back in the user only needs to fill in 3 fields and the other fields need to revert back to blank.

    For example:

    Item No: This is a combo box
    Name Of User:
    Job No:
    Date Signed Out:
    Date In:

    These are the four fields.

    So when the item is signed back in the Date In and item number would only be present on the form.

    Once the entry is entered, I want it to save and then delete the name of user, job no and date in records??

    Thanks

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    On a form:
    Code:
    Me.Text_Name_Of_User.Value = Null
    Where Text_Name_Of_User is the Name of a TextBox bound to the corresponding column into the table. If the table definition prohibits Null values, use:
    Code:
    Me.Text_Name_Of_User.Value = ""
    Directly into the table:
    Code:
    UPDATE <TableName> SET [Name Of User] = Null WHERE [Item No] = <Item ID>;
    Where <TableName> is the name of the table and <Item ID> is a unique value identifying the row you want to update. This can be used in a Query or in VBA:
    Code:
    Dim strSQL As String
    strSQL = "UPDATE <TableName> SET [Name Of User] = Null WHERE [Item No] = <Item ID>;"
    CurrentDb.Execute strSQL, dbFailOnError
    It's also possible to open a recordset on the table and edit then update the values.

    Generally speaking, I'm not sure that I would remove data from a table the way you do it. I would keep a history table recording every movement.
    Have a nice day!

  7. #7
    Join Date
    Jul 2011
    Posts
    16
    Mate Ideally i would love a history table where every change user etc is recorded i tried to enter a audit trail and it didn't like it.

    Ideally I would love to be able when stock comes back in it moves the information to the history table making the equipment table blank ready for queries etc to run of it as well as keep it ready for when the next user needs to sign out that particular equipment?

    Thanks

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Usually you don't change the values of the base table (let's call it Tbl_Items), and possibly of a second base table (Tbl_Users). This second table is useful if the set of users is known, is rather constant, does not vary much.

    A third table (Tbl_Movements or Tbl_Transactions) stores the ID from Tbl_Items, the ID of Tbl_Users if this table exists (or the user's data if it does not) and data related to the movement: date of movement, type of movement, other data specific to the movement (possibly an ID from a third base table).

    Queries are used to retrieve available items, "engaged" items, etc. They also indicate what kind of movements are allowed at a given time for a given item.

    The principle is very similar to those used for stock management systems.
    Have a nice day!

  9. #9
    Join Date
    Jul 2011
    Posts
    16
    Yer thats exactly what i want something to manage our stock.
    That soloution sounds perfect but i havent set up a user table here.But its something im now looking into.

    well at the moment i have everything placed into one table.
    For e.g.
    Item ID Current User Location ETC.


    But i would love to create the tbltransactions though.
    I'm guessing i need to create a relationship between the items and transactions items?
    Thanks
    Last edited by niravbhuva; 08-03-11 at 07:35.

Posting Permissions

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