AutoNumbering Question?

You basically do not need Categor and Year fields in your table. And this for two reasons:
1) they obviously belong into tblOrders
2) they can be calculated from OrderNo:

   Category: Left(OrderNo,3)
   Year: "20" & Right(OrderNo,2)             <- string
   Year: 2000 + Val(Right(OrderNo,2))     <- integer

As for the second question, I think you will need VB code. Use the AfterUpdate event of the control txtJobNo, and perhaps the Form_BeforeUpdate event as well. Tell us if you need help with that.

Cheers

Thanks for your help.
Any Examples on how to implement these in a QUERY?:

  Category: Left(OrderNo,3)
   Year: "20" & Right(OrderNo,2)             <- string
   Year: 2000 + Val(Right(OrderNo,2))     <- integer

The reason  why I asked for autofill FORM bound to a Table (tblOrderDetails) is becauae of the following:

(1a). There are many occassions when USERS meant to type SDQ but typed SCQ instead when adding new records in a FORM, this can store the OrderNo wrongly in the tblOrderDetails permanently and such a mistake may be udetectable or unnoticed while handling large amount of Data. (New Suggestions welcomed).

Please note that the OrderNo can either begin with SDQ or SCQ !!!

(1b).  I used the SDQ and SCQ to sort Query reports into Categories thereby
         disntinguishing the number of records for each category. (Also suggestions
         will be appreciated).

(1c). The "Year" is also used in sorting out the SDQ and SCQ by year in a query report.
       (I also welcome new ideas).

(2). I will appreciate VBA Code suggestion for Question #2

With regard to billcute's response;

I think you can type into the category field (default value property) the following statement: =left(OrderNo,3) or similar
and the same with the year ="20" & Right(OrderNo,2) (depending on whether the field is a string or integer as billcute as quite rightly displayed both scenarios.

the default look of the order no can be captured using an input mask of lll-000-00;     (the first three charachters requiring letters and then 3 digits and then two digits.

Similarly with the Job number you can use an input mask with a 401 prefixing the value that you enter.

hope this helps

Jez

jezskill ,
Thanks for your help,

I noticed the following after applying your suggestions:L

(1). lll-000-00 (is good but it cut off the last of my records)
      e.g my old data indicated SDQ-001-04    (after applying the above,
                                                                the data now read SDQ-001-0
                                                                the last digit was cut off.

(2).
Defaulting Value of 401 simply create the value of 401 all the time when new records are generated. The problem is that there are some new records that does not provide the 4011111. In this case , the 401 is automatically entered into the record without the remaining data.

(3). When I applied the ="20" & Right(OrderNo,2). I got this error:
   "The expression you entered  has an invalid string...A string can be up to 2048 characters long, including opening and closing quotation marks."

I dont know what this meanss.

(4). When I applied this also the "Year"  =2000 + Val(Right(OrderNo,2)). I recieved the same error:
 "The expression you entered  has an invalid string...A string can be up to 2048 characters long, including opening and closing quotation marks."

Thanks for trying.

Expressions In Access:

1) In a query, you should use the syntax "name: expression". Example:
    Year: "20" & Right(OrderNo,2)
2) As source of an unbound text box in a form/query, you use "= expression". Example:
    = "20" & Right(OrderNo,2)
3) In Visual Basic, you assign the expression to a variable as in "variable = expression". Example:
    intYear = "20" & Right(OrderNo,2)

True, in this forum, we tend to mix these syntaxes freely. Sorry for the confusion...

For the management of the default "401" in JobNo, we will need to know whether it is a text field or a long integer. The management will differ significantly according  to that.

For the input mask, I tried ">LLL-000-00" without problems (">" converts to upper case).


But just a general comment: the OrderNo is basically a three field construct: <category>-<counter>-<year>. As you found out, it is always a problem to manage such constructed  fields. You will spend all your time finding ways to switch between a three-field view and a single field view. Normally, you will store only one view in your database, and in most cases, the three-field view is the best:

OrderCategoryID, OrderYear, OrderCounter, etc...
"SDQ", 2004, 12, etc...
"SDQ", 2004, 15, etc...

That way, when *entering* a new record, the user will select "SDQ", "SCQ", or any other valid category from a list, enter the full year (default: this year?), add a counter (or have the program suggest a counter). Everywhere else, the full OrderNo is built through an expression. E.g. in a query:
   OrderNo: OrderCategoryID & "-" & Right(Str(OrderYear),2) & "-" & Format(OrderCounter, "000")
For efficiency, you will not, of course, sort according to a caclulated field, but rather sort on:
   CrderCategoryID: display (no): sort (ascending)
   OrderCounter: display (no): sort (ascending)
   OrderYear: display (no): sort (ascending)
(or first year, then conter, depending on what you need).

Hope this helps!

Correction to the VB expression sample. "intYear" implies an integer, so the code is more readable as:
   intYear = 2000 & Val(Right(OrderNo,2))
The syntax was for a string variable, but then it should be called "strYear"...
   strYear = "20" & Right(OrderNo,2)

It is always useful to distinguish between numeric and string expression, sometimes it is vital :)

Cheers!

Harfang,
Thank you for your effort, here was the result of your suggestion:

(1). strYear = "20" & Right(OrderNo,2)             - displayed 20No ....nothing more
(2)  intYear = = 2000 & Val(Right(OrderNo,2)) -  displayed 2000....and nothing else change

Hmm. I bet that the expressions were changed to:
(1). strYear = "20" & Right("OrderNo",2)             - displayed 20No ....nothing more
(2)  intYear = = 2000 & Val(Right("OrderNo",2)) -  displayed 2000....and nothing else change

I.e. not field names, but strings. Thus "Richt("OrderNo",2) returns "No", the last two characters... In (2), the value of "No" is zero...
Try enclosing the field names with brackets, as in [OrderNo]. This way, Access will know that you want the content of that field (if it exists) and take the last two characters from the *field*, not the *name*

Good Luck!

I just tried it agin, it's not working. I pasted this in the SDYear field ...default Value. Is this right?

I din't know you had a SDYear field... :)
What value do you want as default for this field?

For a default value, you should use the same syntax as for controls: "= <expression>"

I'm sorry, but this thread is starting to "pedal in yoghurt", as we say around here. You have had several suggestions: what do you want to do? Do you want to change the table structure? create controls on a form? design a query? Do something with VB?

harfang.
Thanks for stepping inside: All I simply need are:

 From:
tblOrderDetails:
  OrderNo        Category        Year      JOBNo          
SDQ-001-04        SDQ           2004      40132345
SCQ-123-03        SCQ           2003      40152344

I have among others, the following fields in the table tblOrderDetails. When I start to enter new data into form (frmOrders) bound to tblOrderDetails; the fields: Category, and Year depends on the data I enter into field OrderNo.

From OrderNo, the category and Year will be autofilled. For example:

SDQ-007-02....will autofill  "SDQ" in the Category field
                    ..it will also autofill 2002 in the Year field.

Similarly:
SCQ-043-04        will autofill  "SCQ" in the Category field
                    ..it will also autofill 2004 in the Year field.

NOTE: The Order No is a text field with field size 50 - Allow Zero Length.

By the way, your previous suggestion on ">LLL-000-00" works but when I applied it, it cuts off all last digit of my prior records. But I suppose it will work if it's anew database with nothing in it.


For the issue of 401_____.  
(this is also a text field with field size 50 - Allow Zero Length. It's a field on it's own .

Defaulting Value of 401 simply create the value of 401 all the time when new records are generated.

The problem is that there are some new records that does not require entering the  401_____ number. In this case , because the  401 is defaulted in the field property, new record will simply be filled into the JOBNo when it is NOT required.

I need some kind of a check, that will not insert the 401 if no other data is entered into it.
Bill

Ok, I still have doubts, such as:

OrderNo is the number of the Order. Thus Category and Year belong also in the table tblOrders, even if they can be deduced easily. But, OK, I might have missed something...

The first three characters of OrderNo are Left([OrderNo], 3). This expression can be used in an event handler for instance. The year can be constructed by concatenating (adding) the string "20" to the last two characters of the OrderNo. These are Right([OrderNo],2). If the name of the control displaying OrderNo is txtOrderNo, you can have the following event handler (select the "After Update" property, select [Event handler] and press the [...] button, and complete the code):

Private Sub txtOrderNo_AfterUpdate()

    If IsNull(Category) Then Category = Left(OrderNo, 3)
    If IsNull(Year) Then Year = "20" & Right(OrderNo, 2)

End Sub

This will place the three first characters of OrderNo into the field Category and the string "20" with the last two characters of OrderNo int the field Year, the first time that txtOrderNo is updated, when these fields are still Null (empty).

For the Input Mask, it should not cut off any digits. Could you try to cut-and-paste three cells from the OrderNo column? This is a strange behavior.

For the default 401, say that the controls displaying the JobNo is called txtJobNo. You can set the default property for that control to "401" (open the properties of the text box, it's close to the top). To remove that value if it was not needed, you will need another event procedure.
This time, go to the Form properties and find "Before Update". Create the Event Procedure and adjust to:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If txtJobNo = "401" Then txtJobNo = Null

End Sub

This will test the control "txtJobNo", and set it to Null if it still contains the string "401".


I hope this is clearer.


The comments above where all mainly concerned with the fact that you probably do not need these fields, because you can calculate them "on the fly", e.g. in a query or a form, whenever needed. What's more, even if you do need them, they are probably in the wrong table (they belong to Orders, not to OrderDetails).

But heck, that was not the question, was it? :)


Cheers!

You seems to understand the question:

I tested your codes and here are the result:
(1) SDQ-112-03  - Result
                            "Category" -displays - Ord
                              "Year" -   - displays - 20No

The code for "401" - worked perfectly, however I want to be able to limit users input to 7 digits in order to contain errors.
                                 
I later change the AfterUpdate code to:

Private Sub txtOrderNo_AfterUpdate()

    If IsNull(Category) Then Category = Left(OrderNo, 3)
    If IsNull(Year) Then Year = 2000 & Val(Right("OrderNo",2))

End Sub

Here is the result:
 (1) SDQ-112-03  - Result
                            "Category" -displays - Ord
                              "Year" -   - displays - 20No

As you can see the result is the same.

As a matter of fact, as soon as I click "add new record" the the Category and Year automatically display "Ord" and "20No" respectively even without inputting data into the OrderNo field.

When I finally input data the category and year does not change from the original default.

I am going to be gone for about 9 hours...that will probably give you some time to perfect it. Thanks for your help.
Bill

I'll probably check mail from a cyber cafe in about 4 hours. Just to see if you've come up with a new suggestion. I will have my Notebook with Application loaded in it.
Thanks

> As a matter of fact, as soon as I click "add new record" the the Category and Year automatically display "Ord" and "20No" respectively even without inputting data into the OrderNo field.

This means that txtOrderNo_AfterUpdate is not responsible. Make sure that your form does not contain other earlier tests and trials. If you already set the values before, txtOrderNo_AfterUpdate will not change them. This is the reason for: If IsNull(Category) Then...
If you want, remove the test for now and just go:

    Category = Left(OrderNo, 3)
    Year = 2000 & Val(Right(OrderNo,2))

And Again: NO QUOTES around OrderNo. This would mean "the name 'OrderNo'", of which the first three characters are indeed "Ord", right?

Good Luck again!

I have tried your latest, stil no result.

Sorry,

The point was not to test anything new, but to remove all *other* tests. Perhaps you could simply create an AutoForm on your table and just rename the control OrderNo to txtOrderNo and create the AfterUpdate event like above:

Private Sub txtOrderNo_AfterUpdate()

    If IsNull(Category) Then Category = Left(OrderNo, 3)
    If IsNull(Year) Then Year = "20" & Right(OrderNo, 2)

End Sub

Maybe adding the keyword Me. so that you will see errors when compiling:

Private Sub txtOrderNo_AfterUpdate()

    If IsNull(Me.Category) Then Category = Left(Me.OrderNo, 3)
    If IsNull(Me.Year) Then Year = "20" & Right(Me.OrderNo, 2)

End Sub

Good Luck.

harfang,
You've done it again. I only tied the Me. part and I am satisfied. This is great. It's working. I know you dont give up easy. Thanks again.
Bill

harfang,
I still have five more posters open. Click on my username and see which ones you can handle. Thanks
Bill

harfang,
Try and solve these 2 listed posters of mine, if you can. They are:

(1). How do I Update Existing Table from a Query

(2). Help with "Generating Reports Every 30 days.


I am requesting your because I believe you can help. Just click on my username (billcute) to get there.
Thanks
Bill

harfang,
Hi, Just a small pointer:

On the "AutoNumbering" issue, the 401 is a default value, How do I set the cursor to begin after the last digit. i.e 401_

I will prefer that the cursor is placed after the last digit. this way it leads users to begin typing the next digit after the 401. How do I set the input in the JobNo field?

Your help will be appreciated.

For a text box called txtBox:

Private Sub txtBox_Enter()
    With txtBox: .SelStart = Len(.Text): End With
End Sub

If  you like this behavior, look at "Tools / Options", Keyboard tab, "go to end of field"

Cheers!

I dont understand what you want me to do but here is what I did:

I added a field called Box to my tblPermits and then add the field to my form frmPermits and then name it "txtBox".

In the "On Enter" properties of the txtBox, I pasted your code.

But I dont know what else you want me to do from there.

OK, sorry.

What is the name of the text box used to display [JOBNo]? If it is, say, "txtJobNo", then create an event procedure for "On Enter" and insert the following line:
    With txtJobNo: .SelStart = Len(.Text): End With

As I stated elsewhere, I provide code with generic names (in this case "txtBox"), which you replace with your actual names...

I'm sorry if you took the time to create a new field and a new text box. It was only meant as a template (just like in a help file).

Cheers!

Okay,
It works thanks again. I have just add a new Poster..."Help With DLookUp Addresses..." Check it out.

hmmm "just added"...sorry for the last typo.

harfang,
Is it possible to add a code that check the field to ensure that digits entered  is NOT less that 9 digits (this 9 digits MUST include the 401 as well).

Note:
My field Size for JobNo is "defined" as 9

Your current code is:

With txtJobNo: .SelStart = Len(.Text): End With
Thanks.
Bill

The easy way is to use an input mask. In the properties of the text box for JobNo, set "000000000" as input mask (nine zeroes without the quotes), and the user will only be allowed to provide a 9-digit number in that field.

In that case, however, you need to change the Enter event a little bit:

Private Sub txtJobNo_Enter()
    With txtJobNo: .SelStart = Len(Nz(.Value)): End With
End Sub

OK, this does not make sure that the entry starts with "401", but it's really easy.
If you want to force all numbers to start with "401", try this input mask:
   "401"000000;0
(and make sure you press F1 while you are there :)

Cheers!

harfang,
I actually used the "40"0000000; (defaulting the new JobNo to begin from 40_________.

I also made adjustment to  your code:

With txtJobNo: .SelStart = Len(Nz(.Value)): End With

and I also tried the .Text example. Both worked but couldn't get to place the cursor or Tab stops to start the default "40".

Do you think that this may have anything to do with adjusting the Tab stop or Cursor setting?

What do you see to that?

To make everything work, you will need several things (all above somewhere, but let's regroup them here)

You want
* A default prefix number of "40" to your txtJobNo control for new records
* The curser to jump to after the default prefix when entering the control
* To force entry of exactly 9 digits
* To remove the default prefix when nothing  else has been entered in the fied.

This is done with two properties of txtJobNo, and two event procedures.
txtJobNo should have:
Default Value: 40
Input Mask: "40"0000000;0

Event procedures should include:

Private Sub txtJobNo_Enter()
' Automatically place the cursor at the end of the current value:
    With txtJobNo: .SelStart = Len(Nz(.Value)): End With
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

    ' remove any imcomplete data from txtJobNo
    If Len(txtJobNo & "") < 9 Then txtJobNo = Null

    ' more validation???
    ' [...]

End Sub


Note how code behid a form always needs to check for Null. Since txtJobNo can be Null (for existing records), we use:
* Nz(.Value) : convert a Null string to ""
* txtJobNo & "" : another way to convert a Null value to ""


Good Luck :)

This is a good summary,
In the OrderNo Example
we have SDQ-012-03

but since the Category is either SDQ or SDQ, Is there a way to first default

 S  Q _________ (thus allowng me to enter either C or D between the S and Q first before jumping to "after" the Q cursor to complete the rest of the numbers. This is to avoid typing the S and Q all the time. The same task can be accomplished by just entering one letter (which can be either C or D) between the letter S and Q.

This looks like another question...

Having a text box txtOrderNo:
Input Mask: >\SL"Q-"000\-000;0

And before you ask:

Private Sub txtOrderNo_Enter()
    If IsNull(txtOrderNo) Then txtOrderNo.SelStart = 1
End Sub

Cheers!

(I really shouldn't answer more question on a spent 50 pt topic... :)

harfang,
I thought about the 50 points award sometimes back and was convinced even before you asked that you deserve more points. I have already asked the Community Support the same question sometimes back now, and I am waiting response.  I like practical 'Gurus" like you who enjoy helping others without thinking about the points and I for one appreciates good service. So, I knew well ahead that you deserve more points

Click on the link below to view my earlier question to Community Support regarding the additional points that relates to this topic.
https://www.experts-exchange.com/questions/21195404/Suggestion-on-awarding-points-to-a-PAQ-due-to-extra-work.html

Cheers !!!

What should I put as the "default value" of the "txtOrderNo"

This is the input Mask that default the Cursor for C or D input, it will jump to the next field after the letter (Q) and it works great but I dont know if this is the right setting. All other settings I tried besides this one simply overrides the S and Q fields:

>"S"L"Q-"000\-00;

But, the above input mask now throws the Category Field calculation off aligment. It begins its' counts from the first keystroke input of C or D to the right, and skips the letter "S and Q" in the process.

What I now think is that I need a new Code that will default the "SDQ" or "SCQ" into the Category field.

Again :)

Having a text box txtOrderNo:
Input Mask: >\SL"Q-"000\-00;0
Default Value: Null

This will work, provided all your data is and will be like "S?Q-???-??"

And please, do not forget the ";0" at the end of the input mask. (select input mask property, press F1, and read what it does)

Good Luck

I am very greatful for the help. I need to point out a comment however. This is the scenereo.

After inputting the SDQ -012-02 and got a value of:

Category = SDQ
Year        =02.

I discovered that going back to correct the a mistake and re-enters let say SCQ-123-04. The correct does not "Requery" and it did not change the previous value in Category and Year

e.g. the field was changed from SDQ-012-02 to SCQ-123-04. The result after exit from the OrderNo field did not change. the old value remain:

Category = SDQ
Year      = 02

Why it's behaving like that I dont know but I'll leave it to the experts.

Well, you've been very helpful as soon as I hear from the Community Support, I'll do what I have to do to issue additional points. Thanks

harfang,
I did increase the points from 50 to 250. You deserve it.

I have tested your current code but beofre I closed this question. I want to the final one.
Is there any way to limit the last two digits entry of the orderNo to control mistake.

Suppose a user wanted to enter:
SDQ-234-04 but enters SDQ-234-44

and did not discover the mistake and move to the next field.

Is there a way to control the "first of the last two digits" of the "txtOrderNo" to be between 0 and 1 such that if the user enters any digit outside the range of 0 and 1 he gets a "message warning to correct it" and at best prevents the Update to the "Category" and "Year" fields.

I am ONLY curious.
Bill

OK, one last time, but this is left as an excercise... I will neither write it for you nor debug. You are on your own for this one, good luck...

Pointers:
* Use BeforeUpdate to validate fields... Set Cancel to true if you refuse the entry
* You already know how to extract the last two characters of the OrderNo
* Create the test, provide a message box... easy!

Cheers and bye for now.

I dont quite follow