Page 2 of 5 FirstFirst 12345 LastLast
Results 16 to 30 of 64
  1. #16
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Anyway, I need to get to bed. I am going to leave you with another code snipit and a couple more points you should consider. First the code. You can replace the other code within your form's current event. It does the same thing.

    Select Case Me.Code
    Case "A"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "B"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "C1"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "C2"
    Me.UDT_ProOther2.Value = Me.Time.Value

    Case Else
    MsgBox "No Match Found"
    End Select

    'End Code


    Some advice. Anytime you are hard coding data that could be in a table stop and ask why. For example, maybe you only plan on having two cars. Why bother making a table that will only store the data of two cars? So every time you reference them you type "Ford" and "Chevy" in your VBA. Well, that goes against convention. Convention is there for a reason. It's there for us who are learning to follow the convention so we don't start screaming at our pets because they want fresh water.

    You are hard coding what is to be considered a variable. Maybe its "A" maybe it varies and is sometimes "C2". Put it in a table. You can draw a relation to it and query the relative data from another table. After all, we are working within a relational database.

  2. #17
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Sorry, have been away for a while... I tried your last advice but it displays of course: "There was not a match." and the Time does not appear for all stoppage codes... (I tried with "Timing" instead of Time) Am I missing something?

  3. #18
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Quote Originally Posted by Knelou View Post
    Sorry, have been away for a while... I tried your last advice but it displays of course: "There was not a match." and the Time does not appear for all stoppage codes... (I tried with "Timing" instead of Time) Am I missing something?
    Post the code you have in your form's current event. If it is in anther event like the click event of a button post that. Tell me which event you got it from.

  4. #19
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    I created a new button for that and I tried your first code, "there was not a match" does not appear anymore but my column UDT_ProOther2 is still not filled directly... Tried also your second code but same result, nothing has changed in datasheet view for my column...

  5. #20
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    I think we should square away a couple things. First, I really don't understand what your end goal is. I think it has something to do with copy data from one column to another. Like I first mentioned. Your VBA makes no sense to me. It really does not accomplish much.

    Second thing I would like to clarify; You need to understand what the code you wrote is doing.

    On the point of me trying to explain what your code is doing, I asked for you to show me the code you are currently using. So I will guess it is the same code as in post #15.

    I offered this code to you so you can see something happen with your click event and you can start to look at and determine WHY it did something. I did not offer the code as a solution to your problem because I don't know what that may be.

    take a look at
    ElseIf Me.Code = "B" Then

    If on the current record your field named Code = B then "Something Happens"

    Take a look at the next line
    Else

    If the current record's field named Code does not equal B or any other "IF" statement then the "ELSE" part of the statement takes over. The field named Code did not equal A or B so the msgbox displayed because something ELSE equaled the field named Code.

    Does this look familiar?
    MsgBox "There was not a match."

    It happens when there is not a match to "A" or "B"

  6. #21
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Okay, I understand all what you said. Actually my problem is that I wanted to apply a IIf formula to a field called UDT_ProOther2. In my first table, in data type, I put Calculated and I tried my expression with the IIf formula, beginning like this :

    IIf([Code]="A",[Time],IIf([Code]="B",[Time],IIf([Code]="C1",[Time] ....
    and finishing with :
    IIf([Code]="W",[Time],0))))))))))))))

    But my expression seems too complex for Access, probably because of the number of caracters, that's why I was asking for VBA... With your advice, I almost succeeded but I would like that the field is calculated automatically when I enter a new "Code" (A,B, C1...and so on). Let's take an example. On the 22.09.13, the production team has filled the database with the data of the day. They start with "B" code (=starting machine) and enter the time it took (25mn for example). Then there has been changeover on the machine (C1 to C3) so they enter for each code of changeoverm the time it took. And so on... So for each day there won't be ALL the codes possible (from A to W) but SOME of them. One time is associated to each but it can change from one day to another (for example, starting of the machine can take 30mn an other day). But the Codes cannot become others as you were wondering (B will always be starting machine and C1 will always be one time of changeover). What I would like is that when I enter a new data, UDT_ProOther2 (=Others Unplanned DownTime) is calculated automatically as I said. It should returns the time of each code included in it. Because I have other fields such as UDT_Pro_idle_time for the Code I (=idle time). But these fields are calculated with a simple IIF formula in my table because there is only a few caracters.... Hope you understand more like this...?

    For now, I am using this :

    Private Sub Command38_Click()
    Select Case Me.Code
    Case "A"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "B"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "C1"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "C2"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "D"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "E"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "F"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "G"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "H"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "I"
    Me.UDT_ProOther2.Value = Me.Time.Value
    Case "N"
    Me.UDT_ProOther2.Value = Me.Time.Value

    Case Else
    MsgBox "No Match Found"
    End Select

    'End Code
    End Sub

  7. #22
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    OK give me a couple min to look at this

  8. #23
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    BTW, do you understand why some records were giving you the message box. It is important for me to understand what you do and don't understand within the code

  9. #24
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Actually it was giving me the message box because first, I didn't enter all the Codes that figured in my data but I found what was missing and now there is not this msg box appearing anymore. But my field is still not filled with the Timings I want... Ok I'm waiting !

  10. #25
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    I am understanding a little better now. In the beginning, the thing that threw me off is why you are updating information to the table that contains the field named UDT_ProOther2. Rather than write the code and simply hand it to you I wanted to make sure you understood how to use it.

    Now I do understand a little more clearley. I may be able to offer you a better solution. Can yu delete any private data from the DB and upload it or just some samples of the data tables?

    If not, can you tell me what the table name and the field names are for the table(s) that hold the Code and Time fields? Does the field named UDT_ProOther2 exist within the same table as either of these other fields? The reason I ask is that you may be able to query the information you need without updating the field UDT_ProOther2. It might sound crazy, but updating the field sounds unecessary to me still.

    If the Code and the Time are being inputed then you may already have the relationsship needed to draw up a report or view the results in a form.

  11. #26
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Quote Originally Posted by Knelou View Post
    Actually it was giving me the message box because first, I didn't enter all the Codes that figured in my data but I found what was missing and now there is not this msg box appearing anymore. But my field is still not filled with the Timings I want... Ok I'm waiting !
    In your form, go to your field with the name Time. You mentioned that you changed the name of the field. I suppose you changed it at the table. We should update it at the form too. Go to the field's properties and on the "Other" tab change the name to "Timing" the code below is revised with this name.

    You can paste the following code in your form's "Current" event. It is a lazy way to do things but I think it is a good exercise to learn how some of this works. Also, in the case one of the parameters are not met, I changed the message box. I typed in the value of 9,000. Now, in the case there is no match it should update your field to 9,000 or whatever literal value you type after =.




    Code:
     
    Select Case Me.Code
    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

  12. #27
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Now when you navigate to the next record, the field UDT_ProOther2 will update with the appropriate code. This is a long and ugly way to get the job done. However, it is code that is more easy to interpret.

  13. #28
    Knelou is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    45
    Ok looks good but there still a problem for me here... When I want to run it, it displays an error message : "Compile Error : Method or Data member not found" and highlights in yellow my first lign beginning with "Private Sub". Here is my db, please let me know what I am doing wrong... The only things you have to focus on in my db is the table "Bindler timings & DT" and the form with the same name. All the others are trial or other things I still need.

    Database1 copy2.zip

  14. #29
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    I am looking at your DB. I am working on an example to provide to you. The example is an entirely different approach than you are taking. I am trying to make the example as simple and intuitive as possible. Because it is so far removed from your techniques so far, it will be difficult for you to interpret.

    The example will illustrate basic fundamentals of Access and how applications work with relational databases. You should focus on forms as an interface (GUI) for you and your users. Forms are supported by queries. Avoid using tables to perform calculations. Keep your tables as dumb as possible. Assign primary keys (number data types) to your tables so Access knows how to query your tables.

    In order for you to use this approach to accomplish the tasks needed you will need to devote your time to learning other techniques. I suggest you take a look at this thread and read it again. It is an exercise in using a form to update information within a table. Focus on the field name issue. Focus on what different events (Sub Routines are examples here) do in relation to the user's actions. Focus on how VBA will execute line by line, top to bottom. I feel your current approach to
    Access is similar to one's approach to Excel. You are not to blame for trying to use functionality provided to you at the table level. You are only misguided that this is a good approach for some of the tasks you need to accomplish.

    The (temporary) answers to your current hurdle ARE within the previous posts of this thread.

  15. #30
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    I uploaded the sample DB I built in the Data Base Sample area of this form in a new thread. It may be able to help others too.

    https://www.accessforums.net/sample-...tml#post190509

Page 2 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