Results 1 to 12 of 12
  1. #1
    Join Date
    May 2009
    Posts
    104

    Unanswered: Setting Field Values with a button

    I have a form that i'm using to fill out a table, but there are certain times when i want to just copy a record and change one field then gotonew. I used the duplicate record function to build the start of my button, select the record, gotonew, and paste but what command should i use change the value of the copied field to one i specify. This will be the same word everytime, so as you can imagine my button will be named the same. FYI i'm not good at writing macros, i use the macro builder to help me.

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    You have said; "i want to just copy a record and change one field...", it means that your tables not normalized well. Learn something about DATABASE NORMALIZATION.

  3. #3
    Join Date
    May 2009
    Posts
    104
    Actually it is normalized, what i have is a kind of sign in/sign out form for when employees in our company use gages or equipment they sign them out on this form, there is a combo box for Item number, Sign Out Location, Employee, and Transaction Type. The transaction type it going to either be Sign In or Sign Out. I'm trying to make my Sign In/Sign Out process as few clicks as possible for the person signing an item in or out. I tried to keep my question as simple as possible but obviously it was too simple, i apologize. What i am trying to do is when a person Signs out something they obviously have to still select all the fields and save the record in the transaction table. But when they return to sign an item back in, i have a combo box that allows them to easily find the items last record in the transaction list and copy it so they don't have to fill out the same information again, all they have to do is, copy the record, change the transaction type drop down field to Sign In and they are done. What i want to do is take a few steps out of this process. I would like to use a buttons that are labeled Sign In and Sign Out and have these buttons write Sign In and Sign Out to the transaction type field. I tried modifying my buttons macro through the macro builder, and there is an selection for "setvalue", but i cant seem to make this work. Any thoughts????

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Wouldn't it be better to have DateReturned field in the same table as the one you are using to "signout"?? Then you wouldn't have to copy records about.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2009
    Posts
    104
    The gist of it is i have a table that tracks the gages that are checked out. When a gage is checked in a query makes a copy of the records and puts them into the history table, then deletes them out of the main transaction table so that only gages that are checked out appear in the main table. I also have a table that keeps records of every transaction made. In the transaction table there is a field for Gage Serial Number,Employee, Location where its checked out to, Date of transaction, and the transaction type (check in or check out). Really if this can't be done or no one here knows how to do it i can either look to another forum for help or figure out another way to get to this done. This, I thought, would be the easy way to accomplish what i wanted to do.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by MStef-ZG
    You have said; "i want to just copy a record and change one field...", it means that your tables not normalized well. Learn something about DATABASE NORMALIZATION.

    That seems like a harsh statement given that we don't know anything about his table structure setup or the purpose. Normalization is not a rule you ALWAYS have to follow. For example, I'll break normalization rules in certain cases when dealing with 5 million records so my query returns totals within seconds versus 15-20 minutes (or hours if I were to completely follow the rules of Normalization). There are many situations where you break the normalization rules which you learn when you're dealing with lots of business rule situations (and especially with extremely large recordsets.)

    I knew someone once who thought the City, State, and Zip should all be broken out into separate tables. Trying to retrieve the full address for everyone with 5 million records would be a chore.

    In this situation, I might question why he wants to do this (which he explained in his last post) but not necessarily chastize him on normalization without even asking him why he wants to do what he's doing!! Writing something like "Learn something about DATABASE NORMALIZATION" in the way I read it in your post doesn't sound very dbforums friendly.

    We're here to help and guide, not chastize! (or write stuff like "Learn something about THIS!") But maybe I took your post wrong - it seemed LOUD and non-helpful.
    Last edited by pkstormy; 06-25-09 at 02:09.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You may want to design a simple "append" query and use criteria for the autonumber field (ie. = Forms!MyFormName!MyAutoNumberField.) You can also use expressions in the query to change certain values or use IIF statements to set values depending on situations.

    Then when you want to "clone" the record and go to the cloned record, in code on whatever event, use the docmd.openquery "MyAppendQuery" to run the append query (before you go to a new record so you're appending the current record to the same table). (in code, you'll want to docmd.setwarnings false before running the query and docmd.setwarnings true after running the query in code.) - (and may also need to issue a docmd.gotorecord,,aclast to go to the new cloned record.)

    Note: You could use the same technique to also delete the record.

    Another option on setting values for a new record is to simply set the default value of that field based on what the user last entered....for example:

    (in the AfterUpdate event of the City field)
    me.City.defaultvalue = me!City

    (or maybe it's me.City.defaultvalue = " & me!City & " (I can't recall the exact syntax from memory).
    Last edited by pkstormy; 06-25-09 at 02:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    May 2009
    Posts
    104
    I've tried to use the default but it wont work on a copied record, or at least i couldn't get it to. I also made an an update query that will take the very last record and change the field "transactiontype" to "checked in". This works but it doesn't seem to agree with the other 2 query's im running right after that one. It seems the more i go down a road that uses anything other than what i wanted to do in the first place the more problems start cropping up. Everything i have done up till this point works perfectly, When someone checks out a gage they find the gage number from the drop down list or just type it in. They also have to select other fields, location, employee, and transaction type. When they come to check in a gage, i tried to make it easier by making a drop down list of all the gages that are currently "checked out", all they have to do is select the gage and the previous record when they checked the gage out is pulled up. All they have to do is click the "COPY" button, change the transaction type to "check in", save and done. I would like to further make it easier by taking out the field transaction type and replacing it with two buttons, one for Sign Out and one for Sign In. The help in access says you can change field values with the command SetValue but i cant seem to make it work. Maybe this is not what its for.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If you copy a record, then of course the defaults don't work. It would be a logical paradox -- when you copy a record, do you want the "defaults" to apply or do you want to copy the fields from the source record?

    One is create new record (apply defaults).

    One is copy a record (copy the fields from an existing record -- ignore defaults).

    You simply assign the fields their "default" during the copy:

    IE

    dim rs as Recordset
    Set rs = CurrentDB.OpenRecordset("YourTableOrSQL")
    rs.AddNew
    rs!Fieldname1 = Value1
    rs!Fieldname2 = Value2
    rs!Fieldname3 = Value3
    ...etc...
    rs.Update

    That kind of thing.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    May 2009
    Posts
    104
    Right now all i want to do is copy a record then change the value in one field. I'll change this to the same thing every time. What i would like to do is set the field value with my button. If you think this is possible i can upload what ever you need so you know what my database looks like. I had trouble a week or two ago and had to upload screen shots for the guys to help me, which they did beautifully. Keep in mind i'm don't know anything about writing VB code, i use the macro builder to make my macros. I plan to learn as much VB and SQL as i can but right now i'm still just trying to get the simple stuff down. I assume i can look at the VB code that the macro builder produces but i haven't even attempted this because at this point it all a foreign language to me.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Time to get your hands dirty then.

    I have no idea how to do this with macros. I avoid macros like the plague. I would not recommend learning much about them tbh.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    May 2009
    Posts
    104
    If anyone is interested i figured out how to do this. I used the macro builder to do all the work. Use the Action "Set Value" and in the action arguments;
    Item = [TheFieldYouWishToSetTheValueTo]
    Expression = "Word You Wish To Write To This Field" in my case "CHECKED OUT"
    This worked like a charm.
    FYI i never said anything about using macros, the macro build is my friend.

Posting Permissions

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