Page 1 of 5 12345 LastLast
Results 1 to 15 of 64
  1. #1
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45

    VBA If Then function - problem in code

    Hi again everyone,
    I tried to create a calculated field in my table with a lot of IIF statements but I think there are too many caracters because I have an error message saying "expression too complex". So i tried to do it in in a Module (VBA). In the main window, under Option Compare Database, I created a new Sub procedure (I think it's the way to do it) and under, I put my formulas. Looks like this (actually it's longer than that but it's just the beginning of it so you can understand):

    Public Sub Code()

    If [Code] = "A" Then
    [UDT Pro - other2] = [Time]
    ElseIf [Code] = "B" Then
    [UDT Pro - other2] = [Time]
    ElseIf [Code] = "C1" Then
    [UDT Pro - other2] = [Time]
    ElseIf [Code] = "C2" Then
    [UDT Pro - other2] = [Time]
    ElseIf [Code] = "C3" Then
    [UDT Pro - other2] = [Time]
    End If


    End Sub

    My problem is that when I want to run it, it says: "Complie Error : External Name not defined"... I tried also with "Public Sub Code_AfterUpdate()" as it saw on forums but still does not work. Any idea? Thanks !

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    If you want to do some math from anywhere within your application you need to place your code within a Public module verses a form Module. If you put it in a public module you will need a public FUNCTION within said module that you can CALL.

    What does this represent?
    [UDT Pro - other2]

  3. #3
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Okay but how do I do the form module then?
    UDT Pro- other2 is just the name where I want the [Time] for each code to go. I used this name in my table in a field name and tried the IIF function but it said it is too complex...

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    You might start by changing the name of your field UDT Pro- other2. Within your table's design view you should give each column a name that is unique and does not contain spaces or symbols. The shorter the better. Any name you dream up should not be something reserved by the Access Program. You don't want to use Key as a field name. You don't want to use the word Call as a field name. Renaming to UDTProOther2 would be better. Or UDT_ProOther2 should be acceptable but I find underscores confusing when I am reviewing VBA.

    Using a conventional standard to name objects is helpful too. I use tbl in front of my table names. frm in front of forms. qry in front of querries, tblMain, frmFormOne, etc.

    Any form you create will have a VBA module behind it that acts as a container for VBA code relative to that form. It is accessible via the built in Visual Basic Editor.

  5. #5
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Okay makes sense. I tried this beginning with : Public Sub Code(UDT_ProOther2) and changed underneath with "UDT_ProOther2". When I run it, it ask me to give a macro name, I entered "UnplannedProcessOther" and it appears under the If Then End If :

    Sub UnplannedProcessOther()
    End Sub


    But it doesn't seem to do anything or return to data... Where is it going? I would like a column to return all the Time when the Code is A and B and C1...and so on. Is this possible?
    Sorry I am new in VBA...

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    anything is possible.

    Well not really, but damn near anyway. And when I mention column I am referring to a (column of fields) that represents many rows. Where one row represents one record which contains many fields.

    Moving on to your VBA. It simply does not mean anything to me. You need to decide if you want a function. If you are building a sub routine it needs to be associated to something. You mentioned that you wanted to go the route of a form. Good, forget functions for now.

    Build a form using the wizard and get the fields you want on your form. Your form's RecordSource will be your table. Don't worry about a query just yet.

    Once you get all of that. Place an unbound textbox and a Control Button on your form. You can create an unbound textbox by canceling the wizard. Within VBA you now have something to associate a SUB to and you can start with your new Control Button's click event. Put your VBA into your Control Button's click event.

    It still won't work but at least you will be typing VBA in the correct area.
    Last edited by ItsMe; 09-18-2013 at 10:28 AM. Reason: incorrect statement

  7. #7
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Table or Form I don't mind, I juste want that when I modify data in table, it also modify in form but it will so it's okay. I created my form and in design view created a text box in design tab. It says unbound inside, I guess it's what you mean. In this textbox, as you said, I created a Control Button and selected click, Event Procedure, [...] and typed my VBA. I undertand it's in the right area now (and thanks for that!) but then now?

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    So if you followed all of the directions you should have several text boxes and a Button. One TextBox is unbound and the other textboxes should represent fields in you table. This is how access should look to the user that is going to view and manipulate data within tables. Users will do it from a form. Forms have a place to store VBA and tables really do not. This is why you are now looking at a form.

    The click event will trigger when the user clicks the button. it will trigger you VBA to do "Something". Now I will ask you to please tell me what that something is. I believe it has something to do with your fields that are now on your form and calculating a sum. Maybe after a user clicks the button it will trigger some VBA to do some math and display the result in your unbound textbox. It can do whatever but I really don't understand your question. Also how many textboxes are on your form and do some of them represent fields on your table?

  9. #9
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    It doesn't do something, that's the point. When I click on the button it doesn't do anything, just the properties appear. Am I doing something wrong in the VBA? Should I put it into the brackets after "Click", like this?

    Private Sub Command12_Click(
    If [Code] = "A" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "B" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "C1" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "C2" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "C3" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "D" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "E" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "F" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "G" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "H" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "O" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "P" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "S" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "U" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "N" Then
    [UDT_ProOther2] = [Time]
    ElseIf [Code] = "V" Then
    [UDT_ProOther2] = [Time]
    End If
    )
    End Sub


    Because there is still an error message and "Private Sub Command12_Click(" is in red.... I tried to put the VBA after the brackets but does not work either...

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Give me a minute.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    In your new form create another Control Button. You have an unbound TextBox. Give it a name by accessing its properties and clicking the "Other Tab". Call it txtTest.

    In your VB Editor, insert the following code into your NEW Control Button's Click Event.

    Dim strTime As String
    strTime = Format(Now(), "yyyy/mm/dd_hh:mm:ss")
    Me.txtTest = strTime
    msgbox"This is the Current time."
    'EndCode

    Save your form and click your NEW Button. Your new button will now do "something"

    Tell me what you would like the first button to do. Do you want it to some up all of your fields? Do you want it to take an average? Do you want "UDT_ProOther2" to match another field named "Time"

    how many textboxes are on your form and do some of them represent fields on your table?

  12. #12
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Okay so the "Something" is that it finds the good Time of the 1st Code ("A") and put it in my field called UDT_ProOther2 (I can see it in datasheet view). That's good ! But I would like it to find all the Time for every Code I have (A,B, C1... written in my VBA). It's not a sum or average, I want it to find the Time when the Code is A and the Time when the Code is B...and so on.
    In my Form I have only selected 3 fields for now : Code, Time and the famous UDT_ProOther2. But in reality, I have also all my other fields in the Table from which I created my Form (I juste don't let them appear in the form for now so it is simpler to see how it goes)

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    What you have writen here is changing the value of your field named "UDT_ProOther2" to the current value of the field named "Time" every time your field named "Code" = Any letter in the alphabet. With only a few exceptions, i.e. "Z".

    In other words, Time will ALWAYS = UDT_ProOther2 unless Code meets the criteria of one of the few exceptions. i.e. "Z". In the case of the exception, UDT_ProOther2 will remain unchanged.

  14. #14
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Okay looks good, it doesn't go untill Z so no worries for that. Works weel now thanks ! Problem is that it won't do it automatically, only when I click on my Button to provide it for each Code that is mentionned in my VBA. But I guess it cannot be automatic? I would like if possible to do the same thing as, for example, when you enter a calculated expression in a table and it calculates directly when you change the view...

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Like I said earlier, anything is possible. I could not figure out you needed to loop through all of the records until now.

    It still is not what you probably need but...... Your form has a "Current" event. You set focus on your form and display its properties by double clicking the upper right hand corner of the form. Insert the following in the form's current event. It is like a click event bit fires your VBA everytime you move to the NEXT record.

    If Me.Code = "A" Then
    Me.UDT_ProOther2.Value = Me.Time.Value
    ElseIf Me.Code = "B" Then
    Me.UDT_ProOther2.Value = Me.Time.Value
    Else
    MsgBox "There was not a match."
    End If

    'end code


    It is just another way to illustrate some things for you. And also you should consider renaming your field "Time" to something else. Access 2010 reserves "Time" for its own use.

Page 1 of 5 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem with IIF function
    By donnysl in forum Queries
    Replies: 3
    Last Post: 08-12-2013, 10:11 AM
  2. Replies: 10
    Last Post: 02-09-2012, 04:42 PM
  3. Compile error: code or function not defined
    By GeorgeBrown in forum Access
    Replies: 1
    Last Post: 09-19-2011, 10:25 AM
  4. Problem with IIF function
    By Hulk in forum Forms
    Replies: 3
    Last Post: 03-20-2011, 12:59 PM
  5. VBA Function problem
    By smikkelsen in forum Programming
    Replies: 5
    Last Post: 07-16-2010, 07:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums