Textbox Control Source As An Expression help!

[emilyrogers]

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-

 
=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!

[jzwp11]

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.

[emilyrogers]

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?

[jzwp11]

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

[emilyrogers]

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!)

[jzwp11]

Your organization must do things right the first time, I've seen drawings here that have gone to bb!

[emilyrogers]

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!

[jzwp11]

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.

[emilyrogers]

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!

[jzwp11]

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!

[emilyrogers]

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

[jzwp11]

Glad to hear that you got it worked out and thank you for posting the solution for others on the forum.