Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2002
    Location
    Frederick, MD USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Beginner: changing values (2002)

    Here's a question from an Access beginniner: I want to change the value of a Yes/No field when other fields in the record are updated. Can I do it w/o VBA knowledge?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Beginner: changing values (2002)

    Can you be a tad more specific?
    - Do you want the Yes/No field to be set to Yes whenever any other field is updated, or when some specific fields are updated? Or something else altogether?
    - Will it ever be reset?

  3. #3
    Lounger
    Join Date
    Jan 2002
    Location
    Frederick, MD USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Beginner: changing values (2002)

    Sure ... and thanks for the response.

    When the record is created the "Yes/No" field is set to "yes". Later, when the record is edited, the "Yes/No" field needs to be changed to "No" when the edits are saved ... and I would prefer to make that happen automatically rather than have the user check or uncheck a box.

    I could get even more specific, but I'm always concerned it would be a whole lot more than you want to know.

    Thanks ... again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Beginner: changing values (2002)

    You must do this in a form based on the table, and you need a little bit of Visual Basic. It cannot be done directly in the table itself. so you must not let the end users edit records directly in tables or queries, only in forms.

    Here are detailed instructions:
    <UL><LI>Open the form bound to the table in design view.
    <LI>Activate the Event tab of the Properties window.
    <LI>Click in the Before Update event.
    <LI>Select [Event Procedure] from the dropdown list.
    <LI>Click the builder button (i.e. the button with the three dots ...) to the right of the dropdown arrow.
    <LI>The Visual Basic Editor will be started, with the first and last line of the event procedure already created for you. The insertion point is in the blank line between them.
    <LI>Make it look like this:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord = False Then
    Me.[NameOfYesNoField] = False
    End If
    End Sub

    where NameOfYesNoField must be replaced with the actual name of the field in your table.
    <LI>Quit the Visual Basic Editor (Alt+F4)
    <LI>Close and save the form.[/list]Note: this code will leave the Yes/No field as Yes when you save a new record for the first time, but it will be set to No the first time you modify and save the record after that.

  5. #5
    Lounger
    Join Date
    Jan 2002
    Location
    Frederick, MD USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Beginner: changing values (2002)

    Hans ... I readily admit to being a slow learner ... but I can't begin to figure out what is going on ... So here's the detail ...

    For my first Access project, I wanted something at least psuedo practical, so I'm creating a coupon management program for my wife -- you know, the little 25-cent clippings you take to the store.

    Physically, I've prepared 100 envelopes and each envelope will someday contain the coupons of each number 1 - 500 in 100 increments, i.e., envelope 25 will hold coupons numbered 025, 125, 225, etc. The numbers are assigned in the data entry process.

    In my database coupon table, I have created 500 blank coupon records, The fields include a field named "available" intended to indicate if the record is already in use. In the coupon entry process I want to only available records to be offered to my wife to enter new coupons clipped from the Sunday paper. So the query includes the [available] field with the criteria set to "Yes".

    In the design mode, everything is fine, and when I run the query and uncheck the [available] box and run the query again, those newly unchecked records don't display.

    On the "enter new coupons" form, the unchecked records are not presented - great. But, even after entering the few lines of VBA code you sent me, the [available] field does not change on the records that are presented ... AND ... this is the part that really confused me ... when in the form design mode I click to view the field list, the [available] field is not included in the list.

    I don't know what to try next ... except here on the east coast of the U.S. it is after 11o pm, so I think I sleep on it!!

    Thanks for any help you are able to give.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Beginner: changing values (2002)

    If the 'available' field is not in the field list, it is not a field that is shown in the query that acts as record source for the form. Either add the 'available' field to the query, or if it already there, make sure that the 'Show' check box is ticked.

    The event procedure I posted will only run if the user modifies a record. Apparently, you want something else, but I don't understand your setup. When should the 'available' field become unchecked?

  7. #7
    Lounger
    Join Date
    Jan 2002
    Location
    Frederick, MD USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Beginner: changing values (2002)

    Let me try again ... There are 500 records when my wife starts using the program and 100 numbered envelopes (which hold the physical coupons in 100 increments, i.e., envelope #5 will eventually hold coupon #5 and coupon #105, and coupon #205, etc.) ... at this point all the fields in all the records are blank and all are available to receive data about the coupons (except the coupon number, which corresponds to the record number and the envelope number).

    1. She selects "Enter new coupons" form the menu ... and the first blank record appears in the form ... it's [available] to receive the data. She enters the data and clicks on continue and the new data is saved and the next available blank record appears. She does this for whatever supply of coupons she just clipped from the Sunday paper. For each record that receives data, the [available] field is unchecked, i.e., it is not available to receive new coupon info.

    2. Next Wednesday she goes shopping, and uses the coupons that have data stored in record #5, and #8, and #12 and maybe five others. She makes her selection of coupons to use from a report sorted by date, or type of coupon, or value of coupon, and in a maintenance screen indicates the record (coupon) has been selected for use. She prints a report of the selected coupons ... removes them from the numbered envelopes and goes shopping. At this point the [available] field is still "No" ... and another "Yes/No" field -- [selected] is set to "Yes."

    3. When she gets back from the store another maintenance screen allows her to indicate which of the eight coupons she used and which she didn't. At this point, the fields in the coupons she actually used at the store are erased, [available] becomes "Yes" and [selected] becomes "No." The coupons which she took to the store and didn't use remain [available] = "No" and [selected] becomes "No."

    4. Next Sunday, after reading the paper the enters new coupons again, the first record that is presented is one of the now blank records which used to hold the info about the coupons she used the preceding Wednesday and is now available. So if on Wednesday she used coupon #5, but not #1 - #4, the first [available] record into which data is recorded is record #5 ... envelope #5 ...

    These steps keep the envelope number and the record number synchronized

    At least that is the plan and design as I began working on the project. There are other steps -- maintaining the tables, reports, etc. -- that are part of the plan, but it all centers on the recording and maintenance of the coupon file itself.

    I realize this effort will probably never be put to practical use, and therefore is a lot of work for nothing, but it has a lot of interesting aspects that will help me understand the Access process.

    I appreciate your patience and your help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Beginner: changing values (2002)

    I see little point in creating 500 records most of which may be unused a lot of the time. I wouldn't create the records in advance, but create and delete them as needed. One of the fields would be the envelope number; this would be assigned automatically (using code) when a new record is created.

  9. #9
    Lounger
    Join Date
    Jan 2002
    Location
    Frederick, MD USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Beginner: changing values (2002)

    Thanks, Hans, for taking time to read through all that.

    Okay ... I'll change my plan of attack. What worried me was synchronizing between the envelopes and the records in the table. But this is just an experiment, after all.

    Thanks!

Posting Permissions

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