Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    help with simple IF...Then (A2002)

    This will be my first IF...Then.

    What I want to set up is, if control1 is null, give it the value of control2. Both controls on the same form. Here's what I came up with on the OnCurrent event for the form:

    If [Control1] = Null Then
    [Control2] = [Control1]
    End IF

    How do I make this work?

    elizabeth

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

    Re: help with simple IF...Then (A2002)

    To check is something is null in code, you can use the IsNull function:

    If IsNull([Control1]) Then
    ' Make value of Control1 equal to that of Control2
    [Control1] = [Control2]
    End If

    Note the order of the assignment statement. A shorter alternative is to replace the above code by

    [Control1] = Nz([Control1], [Control2])

    The Nz function returns the first argument, but if that is null, it returns the second argument.

    Is Control1 bound to a field in the record source of the form? What should happen if the user updates Control2? Should the value of Control1 be updated too?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: help with simple IF...Then (A2002)

    Thanks for your help. Re your question, it's one way only. Like First Name and Badge Name. Usually it's the same, Wendy and Wendy, but it could be William and Bill. I'm doing this after the fact. I didn't anticipate needing the second field when I originally set up the table and form.

    e

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

    Re: help with simple IF...Then (A2002)

    Hi Elizabeth,

    I would put the code in the BeforeUpdate event of the form. Using the BeforeUpdate event ensures that when the user creates a new record and leaves Badge Name empty, it will be filled when the user leaves the record.

    I would also create an update query to populate the Badge Name field for existing records.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: help with simple IF...Then (A2002)

    <P ID="edit" class=small>(Edited by ppem on 05-Oct-03 00:07. )</P>I was thinking of setting the default value of Badge Name to First Name. Is the Before Update a better bet than the default value?

    I did try putting =[FirstName] in the default value, but that's obviously not it.

    e

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

    Re: help with simple IF...Then (A2002)

    The Default Value property is applied when you create a new record, before you as user have filled in anything. So the First Name field is still empty; therefore Badge Name remains empty too.

    The Before Update event occurs after you have entered data in the record or modified existing data, so it is better suited for what you want.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: help with simple IF...Then (A2002)

    Thanks. Really appreciate your expertise.

    E

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: help with simple IF...Then (A2002)

    I would also create an update query to populate the Badge Name field for existing records.

    Hans: I'm trying to do something similar, but I'm having trouble writing an update query to populate my existing records. I've written update queries before, where I'm updating a field in one table with the data in a field from another table. In this case, I'm working with just the one table. My table, tblStudents, has fields for FirstName and Nickname. In almost all cases, Nickname will equal FirstName. Here's what I've done so far:

    I've created a new query with the tblStudents, and I've added FirstName and Nickname to the design grid. Then I made the query into an Update query and keyed in the following in the Update To: line under Nickname:

    If IsNull ([Nickname]), [Nickname] = ([tblStudentsCopy]. [FirstName])

    I'm just taking a shot at how to write this, but so far I'm getting error messages about operators and operands.... <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22> Clearly, I need some professional help!

    Also, I tried following the advice about code in the Before Update event, but my efforts haven't worked. After I wrote the event procedure, I tried entering a new bogus record and left Nickname field blank. To my dismay, the form failed to populate the Nickname field with the FirstName. Here's my code--anything wrong with it?

    Private Sub Nickname_BeforeUpdate(Cancel As Integer)

    If IsNull([Nickname]) Then
    'Make Nickname equal the First Name
    [Nickname] = [FirstName]
    End If
    End Sub


    Thanks!

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

    Re: help with simple IF...Then (A2002)

    You can't put a VBA statement in a query.

    If I understand you correctly, you want to set Nickname to FirstName, but only if it hasn't been filled in yet.
    - Create a query based on tblStudents.
    - Add ONLY the Nickname field to the design grid.
    - Enter Is Null in the Criteria: line. This makes the query use only those records whose Nickname is empty.
    - Change the query to an Update query.
    - Enter [FirstName] in the Update to: line
    - Execute the query.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: help with simple IF...Then (A2002)

    Hans: You understood my intentions perfectly, and now I've got the query problem fixed. Thanks again!

    There's still the problem with the Event Procedure and the form. I've looked again and again at my code, and my amateur's eyes can't see any problem with it. Nonetheless, when I enter a bogus student and leave the Nickname field empty, the form does not display the FirstName in the Nickname field. What could be the problem?

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

    Re: help with simple IF...Then (A2002)

    Put the code in the Form_BeforeUpdate event instead of in the NickName_BeforeUpdate event. NickName_BeforeUpdate only occurs if the user changes NickName, but that is precisely what (s)he didn't do.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: help with simple IF...Then (A2002)

    OK...I put the code into the Form_BeforeUpdate event, and still the form refuses to display FirstName in the Nickname field when Nickname is null. I've tried both versions of the code (the long version and the shorter version using the Nz function) without success.

    The form gets it data straight from the tblStudents table. I'd started out using a query as the datasource, and I suspected there was something in my query that was causing this behavior. So I switched the datasource to the table, but the problem persists.

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

    Re: help with simple IF...Then (A2002)

    Lucas,

    It is important to keep in mind when events occur.
    <UL><LI>The Before Update and After Update events of a control occur only if the value of the control has been changed by the user.
    For a text box, the events occur when the user moves to another control after changing the text.
    For a check box, whenever the user clicks the check box.
    <LI>The Before Update and After Update events of the form occur only if the record as a whole has been changed by the user, when the user moves to another record or closes the form.[/list]Neither of these events will modify an existing record if the user looks at it without modifying anything.

    You should run the update query once. This will copy the first name to the nickname field for all records in which nickname is empty. After that, the Before Update event of the form will ensure that the nickname field will be kept up-to-date.

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: help with simple IF...Then (A2002)

    Lucas
    I think you'll find the code should be applied to the event procedure of [FirstName] not [Nickname]. Nickname never gets updated if it remains null.
    Peter

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: help with simple IF...Then (A2002)

    Thank you, Hans. Here's what I did a while ago (if memory serves): First, I ran the update query as you recommended. That did in fact plug the first name into the Nickname field wherever Nickname was empty. Now all 600-plus records had something in the Nickname field. Next I entered a bogus student record, but I left the Nickname field blank. I expected to see the form pick up the FirstName and fill the void in Nickname, but that didn't happen. It didn't happen after I closed and reopened the form, nor did it happen after I ran Compact & Repair. I remain confused....

Page 1 of 2 12 LastLast

Posting Permissions

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