Results 1 to 12 of 12
  1. #1
    emilyrogers is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2010
    Posts
    46

    Textbox Control Source As An Expression help!

    I am working on improving a previously made database and I would really like to know how to get an expression as the control source of a textbox on a form to save into the table.
    I have drawing numbers and each time something gets changed the issue goes from a to b etc... and each change is recorded in the database.
    I have a main form that shows the current drawing number and then a subform showing all previous Issue changes. eg -date, why and who by..etc.
    on my main form there is a textbox for "Issue" and "new Issue" I would like the main form to automatically input themost recent issue change into the "issue" textbox so that the user doesnt have to- which avoids mistakes.
    I have put-
    Code:
     
    =DMax("newIssue","ECN Master List","[DrawingnNumber]=[Forms]![dataentryform]![Combo50]")
    into the control source for "issue" on the main form

    new issue- textbox on subform with most recent issue on it
    ECN Master List- table with all data stored in
    Combo50- the combobox with the current drawing number stored in it.
    dataentryform- the name of my main form.

    I have go the code to work by implementing a requery macro into the drawing number combox so that everytime it is changed the issue textbox updates with most recent issue.



    however because the control source of the macro is an expression the value wont save into the table.

    How would i go about getting this value saved to the table?? is there a way?

    Thanks!

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I have drawing numbers and each time something gets changed the issue goes from a to b etc... and each change is recorded in the database.
    Can you provide more info on the table structure that underlies the forms? I assume that you have a corresponding field that holds the "issue". What is the datatype of that field? If it is text, you cannot increment it. Only a numerical value can be incremented technically speaking.

  3. #3
    emilyrogers is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2010
    Posts
    46
    Well, whilst the record is being composed in the form it is saved in temp ECN, then when "save" is clicked it is saved to ECN Master List.

    The ECN Master List holds a seperate record for each issue change. eg if a drawing number changed from rev a- b and then b-c there would be 2 records for that drawing number.
    the data type of issue is "text" I have used the code =Chr(Asc([Issue])+1)
    in the control source of "new issue" on my main form to automatically update the issue by one place from its previous issue.

    Do you think its best just to let the user look at the records and see which is the next issue?? rather than using expressions to generate textbox entries?

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    What happens when you have the next issue after z? Where I work, after z comes aa, ab, ac...ay and then bb, bc, bd... Eventually it will go to a three letter designation and so on. Just to add more complexity some letters such as i, o, q, s, x and z are never used. That will get hard to code.

    The other option is to set up a table that holds the various issues and a corresponding sequential numerical value. Something like this:

    tblIssues
    -pkIssueID primary key, autonumber
    -txtIssueLetter
    -longIssueSeqNo

    You could just use the autonumber, but then you would have to enter your issue letter in sequence when you set up the table initially. If you happen to make a mistake then the autonumber may not yield the correct sequence (that is why I included the sequential number field). You can have the seqno field increment by 10, that way you can add back missed issue letter with a sequence number in between.

    For example, if you have this

    txtIssueletter|longissueSeqNo
    a|10
    b|20
    c|30
    e|40
    f|50

    Then you realized that you forgot d, you can add the following to correct

    d|35

  5. #5
    emilyrogers is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2010
    Posts
    46
    Ah ha! That is a good idea! I think I shall do that!
    Yes in theory the issues would hit z and then go to aa ab ac etc....however in practice it never gets that high!!
    thank you for your help! (again!)

  6. #6
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Your organization must do things right the first time, I've seen drawings here that have gone to bb!

  7. #7
    emilyrogers is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2010
    Posts
    46
    haha! hardly! i think its just they hold back on officially making the changes for a while so they can make a whole bunch at a time in the same issue number!
    I must say on some of the databses I have worked on my issues have got up to ag!

  8. #8
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I just heard today about a case where there were two ECN's in the system to revise 1 drawing. The first ECN corrected an error on the drawing and was completed, then the second ECN undid the correction, so now the drawing is wrong again. It just goes to show that when you don't have a defined procedure with good checks and balances in place, anything can happen.

  9. #9
    emilyrogers is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2010
    Posts
    46
    Haha! I can see that happening- we have the problem that sometimes 2 people are in the database at the same time and dont realise and they both enter ECNs but only one of them gets saved- and they still both think that they have saved their entry!
    So easy to cause errors with multi user access!

  10. #10
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    It sounds like you need some VBA code to prevent a user from tying a drawing to a new ECN when it is tied to an ECN that is not yet complete.

    Our system is purchased package on an Oracle platform and the front end has been "adapted" to meet our needs. Unfortunately, all of the adaptations just open up more ways for us to mess up everything!

  11. #11
    emilyrogers is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2010
    Posts
    46
    This is how i solved my problem.
    I put this in the after update of the drawing number combobox.

    [Issue].Value = DMax("[newIssue]", "[ECN Master List]", "[DrawingnNumber]=[Forms]![dataentryform]![Combo50]")
    If Not IsNull([Issue]) Then
    [newIssue].Value = Chr(Asc([Issue]) + 1)
    Else
    [newIssue].Value = Null
    End If


    And it works a dream!
    thanks

  12. #12
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Glad to hear that you got it worked out and thank you for posting the solution for others on the forum.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-07-2010, 09:42 AM
  2. Control Source for Text Box (#error)
    By km8415 in forum Forms
    Replies: 3
    Last Post: 06-27-2010, 10:45 AM
  3. one form two sources - invalid control source msg
    By techexpressinc in forum Forms
    Replies: 1
    Last Post: 05-28-2010, 02:11 PM
  4. Replies: 2
    Last Post: 07-30-2009, 08:02 AM
  5. Control source and calculated values
    By meistersteff in forum Forms
    Replies: 0
    Last Post: 11-23-2007, 07:04 PM

Tags for this Thread

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 - Senior Forums