Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If Then ElseIF (Access03)

    I want to computer to create three different ID numbers and the fourth type is entered by the user. The number should be based on the selection entered in txtClassification.

    Working this up I get the following to work
    Private Sub txtClassification_AfterUpdate()
    Dim NewID As String
    Dim OldID As String
    OldID = DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='M'")
    NewID = Format("M-") & Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    End Sub

    So I know it builds the NewID correctly

    Then I try to create an If Then ElseIf statement

    Private Sub txtClassification_AfterUpdate()
    Dim NewID As String
    Dim OldID As String
    If txtClassification = Manager Then
    OldID = DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='M'")
    NewID = Format("M-") & Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    ElseIf txtClassification = Agency Then
    OldID = DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='A'")
    NewID = Format("A-") & Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    ElseIf txtClassification = Outsider Then
    OldID = DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='O'")
    NewID = Format("O-") & Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    End If
    End Sub

    Nothing happens I've looked at various books and the online help and I don't see what is wrong. Nor do I get any error messages.

    Thank you for your help.
    Fay

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then ElseIF (Access03)

    Is txtClassification a textbox that can contain the words Manager, Agency or Outsider ?
    Then you have to surround them by quotes :
    If txtClassification = "Manager" Then
    ...
    ElseIf txtClassification = "Agency" Then
    ...
    ElseIf txtClassification = "Outsider" Then

    To avoid this kind of mistake, enter the following line just under Option Compare Database at the top of each module in your application :
    Option Explicit

    In the VBE editor, under Tools / Option select the tab Editor and check the Require Variable Declaration. Option Explicit will now be added automatically for each new module.
    Francois

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then ElseIF (Access03)

    Thank you Francois. It is always the "little" things that is missed when you keep staring at something. Fay

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then ElseIF (Access03)

    Second question. If you are starting a new database and there are no Outsiders or Agency people at this time when I add the first person I get the following error message "Invalid use of Null". When debugging it goes to the following line.

    OldID = (DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='A'"))

    How do I test for no ID number starting with A, O, or M? The earlier code works and I am thinking that I need to nest another If Then statement in each section. If have tried IsNull and Nz but get an error message saying that I have the wrong number of auguments or invalid property assignments.

    Thank you.

    Fay

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

    Re: If Then ElseIF (Access03)

    Try something like

    OldID = Nz(DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='A'"),"0000")

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then ElseIF (Access03)

    Thanks Hans. The lesson learned here is that I didn't check if there was an extra argument to be added to the original code. I had tried the Nz part, but was unaware of the 0000 addition at the end.

    Fay

Posting Permissions

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