Page 4 of 5 FirstFirst 12345 LastLast
Results 46 to 60 of 64
  1. #46
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Why don't you just have them type directly into the UDT field? It is in the same table.

  2. #47
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Because all codes doesn't appear in the UDT field ! It's annoying I know but I have codes that can go from A to W (see table all stoppage codes). In my form, it's only data for 2 days but on other days further in the futur, there can be other stoppage codes not mentionned in the VBA code but that you can see in all stoppage codes (because they don't take part in UDT)

  3. #48
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Take a look at your code in the DB I uploaded. The CASE statement is not differentiating what to do between one code and another. It might as well just be

    Me.UDT_ProOther2 = Me.Timing in the current event.

    One line. It is doing the same thing as your two dozen lines of code.

    So why don't you just have the user type the timing into the UDT_ProOther2 field directly?

  4. #49
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Because as I told you, some stoppage codes will not have their timings in the UDT_ProOther2 field because they are not included in it. See in my previous db, with the table called All stoppages codes : the codes P, S and U for example CAN exist because there can be one day timings for training, paperwork, or for maintenance. However, in my days 26.08.13 and 28.08.13, none of these codes are used because there wasn't training, paperwork or maintenance. These codes are not included in UDT_ProOther2, so if one day I put them and their timings, I don't want that their timings appear in UDT_ProOther2 field (only in Timings field). But if an other day, the employee enter codes such as A,B,C1...included in UDT_ProOther2, I want them to appear in timings AND in UDT_ProOther2. Is it clearer for you?

  5. #50
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Well, maybe you do understand what the code in the CASE statement is doing. I didn't think you understood the code very well at all.

    So what you want is to call on that code you created in another event. You identified something the user is doing. They are updating a field. The field name is Coding I velieve. In the Coding's properties there is an "Afterupdate" event.
    Place your CASE statement there. It will fire after the field has been updated by the user.

    If I may make a suggestion. It is good that you are coming up with ideas how to use the form, very good. However, it is very easy to wotk you way into a corner. Try to dedicate some time to studying the tables and how Primary Key matter in their relationships. You want to avoid duplicating fields from one table into another unless it is a Key.

  6. #51
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If I may make a suggestion....

    Looking at the "Select Case" code in Post #43, it can be written as:

    Code:
    Private Sub Command43_Click()
      Select Case Me.StopCode
        Case "A", "B", "C1", "C2","D" to "I", "N"
            Me.UDT_ProOther2 = Me.Timing
        Case Else
            Me.UDT_ProOther2 = 9000
        End Select
    'End StopCode
    End Sub
    ".Value" is not needed because the value property is the default property.

    I'm just saying....


    I haven't been able to look at the db because I have Access 2000.

  7. #52
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    It sure is a nicer way to do it. Knelou is very new to all of this so spelling things out may be an advantage at this point.

    At least there has been some progress moving from an If Then statement to a Case statement.

  8. #53
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Okay thanks, I tried to put my code in the event procedure for "After Update" in my field StopCode (is it this one you calling Coding?) So there is no need to add a button for this, right? But then why it doesn't run after updating??

    About the Primary Key, I understand that it can be important but I am not sure I can deal with this because I have 2 tables where some fields are named the same (like Actual Date) and I cannot put everything in only one table, it would be too big (and as I want it to be as clear as possible... a bit hard for sure with all those fields !)

  9. #54
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I am not sure which field is best to use. At what point does Timing get updated? Timing has the value you want to transfer to your UDT field.

    Perhaps the afterupdate for Timing. See which one works. Actually, it should not hurt to place it in more than one event behind the form.

    There is a way you can comment out code. If you don't want code to execute you can place ' on the far left side of that line of code. Copy the below text an place it in your VB editor, somewhere behind your form as a demonstration.

    ' this text is commented out
    This is not and will cause your program to stop


    There is a tool in the ribbon at the top of your screen to comment code you don't want to use. Let me know when you get this first step working.




    .

  10. #55
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    I don't unsertand your question about Timing getting updated... I just enter data in my table in the field Timing... Tried also After Update for Timing but it's the same thing, nothing happens. I don't undertand either your other suggestions... I DO want Code to execute, what's the point using this comment?? I placed your text in my VBA editor, under my Code but haven't found your tool in the ribbon, maybe it's in an other version of Access... I am really sorry for all this, it has been more that the 50th post for this thread, I know it is anoying but this automatic updating thing for my column UDT_ProOther2 is still not solved for me...

  11. #56
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    UDT_Sample2.zip

    This DB has the code in the AfterUpdate event for the field named "Timing"

    After you update the field the code will execute. Make sure you exit the field by placing focus somewhere outside the field named Timing, such as clicking another control or using the tab key.

    If you look at code behind the form you will see examples of code that will not execute becuase it is commented out. The point of demonstrating this tool to you is that I feel it is a useful tool. You can use it to help you determine where you want your CASE statement to reside, among other things.

    If you do not see the "Comment Block" tool in your tool bar you can show it by adjusting the tool bar properties. It should be available in your Edit Ribbon. In Access 2010 it may be dificult to find but I use it in that version also.

  12. #57
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Oh my god it's working !!! Whenever I enter a StopCode and a Timing, it displays the Timing in my field UDT_ProOther2 if it's in the code and otherwise it displays 0, exactly what I wanted ! Now the user will have to enter everything in the Form directly, not in the table, right? The tbl is only a support now if I understood well? That's so great, my problem is solved, finally ! I thought I will never succeed... I don't know how I could do without your help ! Hope I won't need help anymore, I will try to study everything about PrimaryKey and so on. Thank you so much for having spending time on this !

  13. #58
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    This is good news.

    You had a question about adding the date so.......

    Here is some sample code. I will give you a few hints and if you can't figure it out by searching Google or the forums then you can start a new thread.

    Hint = You need to change "Me.txtTest" to match the name of a text box on your form. The desirable data type is Date. You can adjust the Date Format within the code to your needs.

    Find a suitable event to place the following code.

    Dim strDate As String
    strDate = Format(Date(), "yyyy/mm/dd")
    Me.txtTest = strDate

  14. #59
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Ok thanks looks good !
    I have an other question if I can bother you an other time (sorry!) Actually, I would like all my fields to have a total and I found how to do in a PivotTable form. It works good with all fields (selecting AutoCalc then Sum for each field) except that it shows 2 lines for each record. For example, I have put actual date and stopcode in rows, at the very left side of the PivotTable and it appears 2 lines for code A, 2 lines for B...and so on. One line has only the Timing appearing, the other has all the details filled in all fields. See screen shot. Also, and that's more a problem, when I try to do the sum (autocalc) for my famous field UDT_ProOther2, Autocalc only displays "Count". The Sum is grayed out. How can I do this?

  15. #60
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Click image for larger version. 

Name:	Screen shot PivTable.jpg 
Views:	4 
Size:	103.3 KB 
ID:	13884

    Here is the screen shot, sorry

Page 4 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
  •  
Other Forums: Microsoft Office Forums