If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
=?Utf-8?B?QnJ1Y2VN?= wrote in
: I had imagined originally that when a new year started RPT_Sequence would restart from 01. So was I: that's what I suggested. I have implemented another suggestion that seems to work (from Allen Browne), but I am interested in your approach too. It's not very different -- we mortals seldom disagree with the MVPs and especially so with the very experienced people like Allen. What I did suggest was a slightly more rigorously-normalised design, but either can work quite happily. need to design a similar database except with the department code in the number (D-05-01, D-05-02, S-05-01, D-05-03, Q-05-01) so maybe your suggestion will come into play yet, OK: this would need three separate fields: one for the department code, one for the year number, and one for the sequence number. As I understand your suggestion, CreateDate is a field containing the date the report was created. Only assuming that you are already storing this date: if you aren't, then you just need the YearNumber field on its own. That would have an advantage over Format(Date(),"yy") in that a handwritten report created on the last day of the year but entered into the system in the new year would contain the correct report date. That is an example; another one is where some applications' years start in April or September, etc. How does MOD 100 fit in? This year is 2005; you wanted 05 on its own. The mathematics says that the conversion function is Modulo-100. It's up to you how you code the hangovers from 1998; and you just have to hope it's not running in 2101. On another matter, I usually use autonumber PKs, but in this case I wonder if there is any advantage (or disadvantage) to using the concatenated number (RPT_Number) as the PK. Well, AFAICS, RPT_Number is not in first normal form and shouldn't be in the table at all. Since you have gone to all this trouble create a unique combination (YearNumber, SequenceNumber) then it seems a bit of a waste to maintain another unique key based on Access' autonumber system. Then again, I don't intend to get involved in another autonumber war over it! Hope that helps Tim F |
#12
|
|||
|
|||
I had imagined originally that when a new year started RPT_Sequence
would restart from 01. So was I: that's what I suggested. Trouble is, I only imagined it. I had no idea how to bring it about. On another matter, I usually use autonumber PKs, but in this case I wonder if there is any advantage (or disadvantage) to using the concatenated number (RPT_Number) as the PK. Well, AFAICS, RPT_Number is not in first normal form and shouldn't be in the table at all. Since you have gone to all this trouble create a unique combination (YearNumber, SequenceNumber) then it seems a bit of a waste to maintain another unique key based on Access' autonumber system. Then again, I don't intend to get involved in another autonumber war over it! I really must learn more about VBA. I understand most of what you and Allen suggested, but I have not learned VBA systematically, so things like: "Year(CreateDate)=" & Year(Me!CreateDate) continue to puzzle me. For instance, I've never quite sorted out when to use Me. and when to use Me!, and I only understand & as a concatenation operator (which it doesn't seem to be in the code snippet above). Sometimes field names are in brackets and sometimes in quotes (as in DMax), and field name syntax is different in Format and in DMax, and so forth to my continual bewilderment, but I'm picking up a bit more each day. I'm still working on "Like", as in: strWhere = "RPT_Sequence Like """ & Format(Date, "yy") & "*""" and maybe someday I will figure out why there are so many quotation marks. Until I attain such wisdom I am very appreciative of the explanations and suggestions offered on this forum. Thanks again. |
#13
|
|||
|
|||
=?Utf-8?B?QnJ1Y2VN?= wrote in
: I really must learn more about VBA. I understand most of what you and Allen suggested, but I have not learned VBA systematically, so things like: "Year(CreateDate)=" & Year(Me!CreateDate) continue to puzzle me. If I remember correctly, this is a string that is going to be passed to the DMax() function. Going from the right-hand-end: Me is the form that "owns" the currently running code, so Me!CreateDate refers to a control on the current form ... and since it does not mention any particular property of that control, we'll assume it's meant to be the .Value property. Year(Me!CreateDate) means that the value must be interpreted as a date value, and then the year part of that date is to be used as a number, like 2005 for example & quite right, this is a string concatenation. In this example it also forces the number 2005 into a string like "2005". "Year(CreateDate) = " goes on the front of what we calculated just now, so the whole thing looks like: "Year(CreateDate) = 2005" What happens next is that DMax passes the whole lot to the database engine to interpret as a filter. The engine will check all the records and carry out the "Year()" function on the CreateDate field for each of them, and remember only the records where the Year() function returned 2005. Then it works out and returns the maximum whatever, which is what you asked for; in this case, the largest SequenceNumber for the appropriate year so far. (An extra point to note: it's good practice to rename all the controls on the form so that they are not the same as their fields. It would have been much less confusing to have strWhere = "Year(CreateDate) = " & Year(Me!txtCreateDate) because it immediately reminds you one is a control and the other one is a field in a record. On the other hand, not all of us always follow our own advice...) Sometimes field names are in brackets and sometimes in quotes (as in DMax) That's because DMax is expecting strings as arguments. The data types of all functions are always listed in the help files. but I'm picking up a bit more each day. Aren't we all? :-) I'm still working on "Like", as in: strWhere = "RPT_Sequence Like """ & Format(Date, "yy") & "*""" and maybe someday I will figure out why there are so many quotation marks. VBA has to know when you want _it_ to see a quote mark, and when you want to keep a quote mark inside the string. The rule turns out to be that if there's one, then VBA uses it; if there's two then VBA passes one of them on. In this expression: "RPT_Sequence LIKE """ The first quote starts a string; The second one could be used by VBA to close the string, but The third one makes a pair, so one of them stays inside the string The fourth one is used by VBA to close the string, so we get the value RPT_Sequence LIKE " and so on. If you parse out the entire expression, you get RPT_Sequence LIKE "05*" which is what you want: compare and find all strings that begin with zero-five. Until I attain such wisdom I am very appreciative of the explanations and suggestions offered on this forum. Thanks again. No problem. If you have any specific questions then come back here again. In the meantime, you might find a step-by-step teach yourself book handy, or else a friendly mentor who can watch over you shoulder in a somewhat more systematic way. All the best Tim F |
#14
|
|||
|
|||
Thanks again. The part about quotes is especially helpful. I am going to
try putting it to use in another database in which I am using SendObject to send an e-mail containing various fields and text strings combined ('Tim Ferguson has initiated Report 05-03 on the subject "VBA Tips" ', where name, report number, and subject are fields; single quotes are for this posting only). I have put the string together OK, but it would be good to have quotes within it. Thanks too for the explanation about values to be considered as strings being in quotes, such as in DMax (and quite a few other things, now that I am aware of it). By the way, I invariably use naming conventions (txtTextBox, lblLabel, cboComboBox, cmdCommandButton, etc.) for controls, to the extent of naming labels, lines, graphics (which I use sparingly, and usually just for company logos and such), and even detail sections and headers. When I review the list of controls I can easily spot anything that is named differently. It also makes VBA a lot easier in terms of the autocomplete feature. In the example you gave I was unsure where CreateDate came from, but it does raise the larger issue of when to refer to a field and when to refer to a control. I'm glad you specified which is the field and which the control in DMax. I think I see now that as the criterium for the DMax expression it is looking for records in which the field matches the control for that field in the current record. Makes perfect sense. I have been doing some investigating on Me!Something vs. Me.Something. As I understand it the dot refers to a property and the exclamation mark refers to a collection, but somehow Access is able to regard a control as a property of the form in many cases, even though it is also a collection. I tend to use the dot because the choices show up automatically, then change it to an exclamation mark as needed. Some people say that using the dot when referring to a control leads to corruption, but they were not specific about that, so I don't know if they are in effect pumping the brake pedal on a car with antilock brakes. I have noticed that when using the full reference such as [Forms]![frmMain]![StartDate] the exclamation mark is needed in all cases. Apparently the control can be a property of the form, but the individual form cannot be a property of Forms, and use of the exclamation mark must be continued once started. It can get to be a bit bewildering when in one instance the syntax is [Field] Is Null and in another IsNull[Field], and sometimes it's Is Null and at other IsNull. I have gotten a lot out of this exchange. I will keep an eye out for your postings as I scan the newsgroup. Much of what I have learned has been by reading other people's questions. "Tim Ferguson" wrote: =?Utf-8?B?QnJ1Y2VN?= wrote in : I really must learn more about VBA. I understand most of what you and Allen suggested, but I have not learned VBA systematically, so things like: "Year(CreateDate)=" & Year(Me!CreateDate) continue to puzzle me. If I remember correctly, this is a string that is going to be passed to the DMax() function. Going from the right-hand-end: Me is the form that "owns" the currently running code, so Me!CreateDate refers to a control on the current form ... and since it does not mention any particular property of that control, we'll assume it's meant to be the .Value property. Year(Me!CreateDate) means that the value must be interpreted as a date value, and then the year part of that date is to be used as a number, like 2005 for example & quite right, this is a string concatenation. In this example it also forces the number 2005 into a string like "2005". "Year(CreateDate) = " goes on the front of what we calculated just now, so the whole thing looks like: "Year(CreateDate) = 2005" What happens next is that DMax passes the whole lot to the database engine to interpret as a filter. The engine will check all the records and carry out the "Year()" function on the CreateDate field for each of them, and remember only the records where the Year() function returned 2005. Then it works out and returns the maximum whatever, which is what you asked for; in this case, the largest SequenceNumber for the appropriate year so far. (An extra point to note: it's good practice to rename all the controls on the form so that they are not the same as their fields. It would have been much less confusing to have strWhere = "Year(CreateDate) = " & Year(Me!txtCreateDate) because it immediately reminds you one is a control and the other one is a field in a record. On the other hand, not all of us always follow our own advice...) Sometimes field names are in brackets and sometimes in quotes (as in DMax) That's because DMax is expecting strings as arguments. The data types of all functions are always listed in the help files. but I'm picking up a bit more each day. Aren't we all? :-) I'm still working on "Like", as in: strWhere = "RPT_Sequence Like """ & Format(Date, "yy") & "*""" and maybe someday I will figure out why there are so many quotation marks. VBA has to know when you want _it_ to see a quote mark, and when you want to keep a quote mark inside the string. The rule turns out to be that if there's one, then VBA uses it; if there's two then VBA passes one of them on. In this expression: "RPT_Sequence LIKE """ The first quote starts a string; The second one could be used by VBA to close the string, but The third one makes a pair, so one of them stays inside the string The fourth one is used by VBA to close the string, so we get the value RPT_Sequence LIKE " and so on. If you parse out the entire expression, you get RPT_Sequence LIKE "05*" which is what you want: compare and find all strings that begin with zero-five. Until I attain such wisdom I am very appreciative of the explanations and suggestions offered on this forum. Thanks again. No problem. If you have any specific questions then come back here again. In the meantime, you might find a step-by-step teach yourself book handy, or else a friendly mentor who can watch over you shoulder in a somewhat more systematic way. All the best Tim F |
#15
|
|||
|
|||
=?Utf-8?B?QnJ1Y2VN?= wrote in
: I have been doing some investigating on Me!Something vs. Me.Something. As I understand it the dot refers to a property and the exclamation mark refers to a collection, The easy part: a dot _always_ refers to a property. Examples: Application.Visible, Me.Caption, ctl.Value etc etc (Nearly) every object has a default property, i.e the one that is used if you don't specify which property you actually want. Therefore, strSomething=ctl is shorthand for the same thing: strSomething=ctl.Value Most objects have collections, which are accessed a bit like properties, so, set colControls = Me.Controls ' the controls collection and you can get at any one of them using the Item property of the collection so: Set ctl = Me.Controls.Item("txtFirstName") Now, because the Item is the default property for the collection object, it can be omitted using the same shorthand as above: Set ctl = Me.Controls("txtFirstName") (and in fact you very rarely see the Item() property spelled out). Now, a second useful shorthand is where one of the object's collections is declared as the Default Collection - and that is where the ! notation comes in. The default collection for the Form object is the Controls collection, so the above expression could be rewritten using Set ctl = Me!txtFirstName Yes, this time the quotes are unneeded because it's going straight to the actual namespace. Final complication: a quirk of the Form object is that it exposes some things that are not really properties as if they were. In the old days, there was no access to the underlying recordset, so you could not do Forms("Teachers").Recordset.Fields("FirstName") (note: we are going for the field, not the control! Therefore, the form exposed all the underlying fields as pseudo-properties, so that you could read instead Forms("Teachers").FirstName Somewhere along the line, though, this all changed to the controls rather than the fields, which I find confusing. I tend to stick to the longhand methods so I stand some chance of reading the code some months later!! brakes. I have noticed that when using the full reference such as [Forms]![frmMain]![StartDate] the exclamation mark is needed in all cases. It would not make much sense, but Forms("frmMain)!StartDate is pefectly legal... when in one instance the syntax is [Field] Is Null and in another IsNull[Field], and sometimes it's Is Null and at other IsNull. 'Something IS NULL' is SQL, 'IsNull(Something)' is VBA I have gotten a lot out of this exchange. I will keep an eye out for your postings as I scan the newsgroup. Much of what I have learned has been by reading other people's questions. That's what we're here for... All the best Tim F |
#16
|
|||
|
|||
Thanks once again. Maybe when some organizational changes are complete where
I work I will be able to use a newsreader such as Outlook Express, but for now I can't get permission to connect to anything other than the default servers. If that change comes about I will have a much easier time with individual message threads, but for now I am limited to the web-based newsreader, so finding threads from several days ago can become a bit of a chore. Therefore I will probably not be returning to this thread, but I thank you again for your generosity with your time and expertise. "Tim Ferguson" wrote: =?Utf-8?B?QnJ1Y2VN?= wrote in : I have been doing some investigating on Me!Something vs. Me.Something. As I understand it the dot refers to a property and the exclamation mark refers to a collection, The easy part: a dot _always_ refers to a property. Examples: Application.Visible, Me.Caption, ctl.Value etc etc (Nearly) every object has a default property, i.e the one that is used if you don't specify which property you actually want. Therefore, strSomething=ctl is shorthand for the same thing: strSomething=ctl.Value Most objects have collections, which are accessed a bit like properties, so, set colControls = Me.Controls ' the controls collection and you can get at any one of them using the Item property of the collection so: Set ctl = Me.Controls.Item("txtFirstName") Now, because the Item is the default property for the collection object, it can be omitted using the same shorthand as above: Set ctl = Me.Controls("txtFirstName") (and in fact you very rarely see the Item() property spelled out). Now, a second useful shorthand is where one of the object's collections is declared as the Default Collection - and that is where the ! notation comes in. The default collection for the Form object is the Controls collection, so the above expression could be rewritten using Set ctl = Me!txtFirstName Yes, this time the quotes are unneeded because it's going straight to the actual namespace. Final complication: a quirk of the Form object is that it exposes some things that are not really properties as if they were. In the old days, there was no access to the underlying recordset, so you could not do Forms("Teachers").Recordset.Fields("FirstName") (note: we are going for the field, not the control! Therefore, the form exposed all the underlying fields as pseudo-properties, so that you could read instead Forms("Teachers").FirstName Somewhere along the line, though, this all changed to the controls rather than the fields, which I find confusing. I tend to stick to the longhand methods so I stand some chance of reading the code some months later!! brakes. I have noticed that when using the full reference such as [Forms]![frmMain]![StartDate] the exclamation mark is needed in all cases. It would not make much sense, but Forms("frmMain)!StartDate is pefectly legal... when in one instance the syntax is [Field] Is Null and in another IsNull[Field], and sometimes it's Is Null and at other IsNull. 'Something IS NULL' is SQL, 'IsNull(Something)' is VBA I have gotten a lot out of this exchange. I will keep an eye out for your postings as I scan the newsgroup. Much of what I have learned has been by reading other people's questions. That's what we're here for... All the best Tim F |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
automatic sequential numbering in excel or word | greg2 | General Discussion | 1 | January 15th, 2005 05:35 PM |
Automatic numbering of documents shared on server | Graham Gobell | General Discussion | 1 | September 1st, 2004 03:40 PM |
Automatic paragraph numbering | Don Zalkin | Formatting Long Documents | 4 | June 21st, 2004 02:00 PM |
Need help with (1) automatic paragraph numbering; (2) page __ of ____ | [email protected] | New Users | 3 | May 10th, 2004 06:47 PM |
Automatic row numbering | Judy | Setting up and Configuration | 1 | December 12th, 2003 07:01 PM |