Page 3 of 5 FirstFirst 12345 LastLast
Results 31 to 45 of 64

VBA If Then function - problem in code

  1. #31
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45

    Okay thank you very much for have been working with my data, it will helps and helps others for sure. What I don't understand on your sample is that you created forms weird for me. I mean all of this work with the forms is only for my VBA, for one field (UDT_ProOther2)! Maybe it's an other type of form I don't know but every field doesn't appear, it only shows the records one by one... Yeah you are true, it looks like an Excel work and indeed, I have to adapt Excel files to Access, that's why I have a lot of fields in my table with an IIF formula...because in excel in how it is. I NEED those fields to appear everytime I oppen my db because employees will fill only the first data (start time, finish time, code and time) and then everything has to be calculated directly when they enter it - and every data has to be displays in one sheet... I created a query from my table to count and especillay sum every column and I have used the Pivot table view to displays the subtotals for EACH date (that's why it's also important for me that the data are classified from the oldest to the newest date. because I want results (sums) of each column for each day. Maybe you would like to see my Excel file to see what I would like in Access? I know I am asking a lot for this but I really cannot find help elsewhere and I am alone on this project...

  2. #32
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    The forms in the DB I provided look weird to you because what I created is not the final solution to the task you need to accomplish. I did not do that for you in the example and I will not in the future either.

    If all you want to do is update that one field, the solution is right here in this thread. All you have to do is go back over the thread and find out where your mistake is.

    As for not every field appearing in the weird form; It is because not every field appears in the table I provided. As I mentioned before, you want to do your calculations somewhere other than your table. You might try in a form or in a query or maybe a report. The sample data base is there to illustrate how an application works to provide a user interface to query and update data that resides in data tables. Access is a tool to build applications.

    In most all of my posts I have asked you to take a look at WHY Access does not help you. You need to take a step back and look at the resources provided to you here. Reverse engineer the DB provided. Go back over this thread to discover how to update that one field. If it does not rapidly become evident why Excel and Access are very different then I doubt Access is the right tool for you. Access can benefit most any business environment. It just needs to be employed correctly.

    You may, in the end, be better off using Excel exclusively or consulting with a developer.

  3. #33
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    I undertand what you saying and other persons told me that it was better for me to use Excel but my boss want everything to be in Access, so... I will try what you did and try to fix my problem, basically it's a problem in my VBA code as I told you. I will contact you later if I am still stuck but anyway thank you for everything ! It helped me understand many things !

  4. #34
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Hi, sorry again, tried everything, syntax is all good but still the message: "Compile error : method or data member not found" when I click on my event button in my form. Have been searching on a lot of forums ans it seems that the 2nd line is not good : Select Case Me.StopCode (I renamed Code into StopCode because it can be a problem). Really don't know what is going on ... Any help would be appreciated. My code is still :

    Private Sub Command43_Click()
    Select Case Me.StopCode
    Case "A"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case "B"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case "C1"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case "C2"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case "D"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case "E"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case "F"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case "G"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case "H"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case "I"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case "N"
    Me.UDT_ProOther2.Value = Me.Timing.Value
    Case Else
    Me.UDT_ProOther2.Value = 9000
    End Select
    'End StopCode
    End Sub

  5. #35
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    When you name your objects within Access you need to stay away from reserved words. CASE is one of those words. Naming your field case is something you should avoid. For some reason, it worked when I tested it.

    What you need to do is take a look at your field names within your form. Using the "Other" tab within the field's properties check the names of your fields.

    Now that you have renamed some of your fields you want to make sure that the field that has your codes like C2 and A is named

    StopCode

    In the case StopCode equals "A" , as one of the first lines of codes suggests, your field named

    Timing

    is going to update the field named

    UDT_ProOther2

    If you do not have fields with these names in the 'Other" tab within the properties settings, your code will not work.

  6. #36
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Okay yes there as a problem in the name in Other Tab, sorry about that... Now no more error message, that's good ! But still, it does what it did a few days ago, the Timing appears in the column UDT_ProOther2 when in datasheet view I am on a line and then click on the event button in the form view. BUT it only displays THIS timing, the timing of the line I was on, not the other ones ! It should displays every timing of every code directly, why it doesn't?

  7. #37
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Place your code in the form's current event.

    In Form view and nor DS view use the navigation buttuns to cycle through all of the records. The code will fire every time it moves to a new record. After you have cycled through all of your records, close your form. look at the table with your UDT_ProOther2 field and view the results of running your code.

    Like I said at the very beginning. I am not sure what calculation your trying to make but your code copies the value of one field to a field in a neighboring column.

  8. #38
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    I cannot find how to cycle all records because I think I chose the wrong button... I should choose this :
    Navigation= Yes
    Cycle = All records

    But it's not in the properties... Is it the Button with xxx inside anc called "Button" or the "Navigation control" (icon looks like a form)? Or something else because none of them have Navigation and Cycle in their properties...

  9. #39
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    at the very bottom of the form are the navigation buttons. You have the properties where they need to be. Now you should see a series of buttons that move to the next record, forward, back, beginning, end, and new record.

  10. #40
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Open your form in Form View

    Not Edit View

    Not data sheet view

  11. #41
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Okay found it, however when I go to the next record, I have to click on my event button (contains the code), otherwise it doesn't display the timing in the column UDT_ProOther2... And it's the same for each record... I want it to do it automatically when I add a record... But when I try to add a new record, it doesn't display the timings either....

  12. #42
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    If you placed the code in the current event the code will update your UDT_ProOther2 field every time you navigate to the next record.

    It will be updated with whatever value is in your Timing field for the same record.

    AFTER you navigate passed all of your records, close your form.

    Open your table and look at your UDT_ProOther2 field. It will be populated with the value of your timing field.

    Forget about adding records right now. That is what the sample DB is an example of.

  13. #43
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Yes I placed my code in the current event and I did all what you said. Navigating all my records, closed the form, opnned my table but still nothing in the UDT_ProOther2 field... I don't know what's wrong but it's driving me crazy..

  14. #44
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    UDT_Sample.zip

    Here is a DB. It has one form and one table. When you open the DB the form will automaticaly open.

    Behind this form is the code that you posted in post # 34. It is in the form's current event.

    Right now, that one table in the DB does not have any data in the UDT field.

    When the form opens it will place a value into the UDT field. It will be the same value as the Timing field.

    When you use the navigation buttons and click to the next record, the UDT field will again be popolated with data. This is because of the code in the form's current event.

  15. #45
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Okay I understand how it works, thanks. But what I would like is that when a user open my db and add a code and a timing as a new record, the timing is diplayed automatically in UDT_ProOther2 in the table, the person doesn't need to click somewhere to have it appearing. Is this possible or am I going too far?

Page 3 of 5 FirstFirst 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
  •  
Tech Forums: Microsoft Office Forums