 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / April 2008Track_No in sub form
Dear Experts, I have created table1 as main form : SN, NAME Then created table2 as sub-form : TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE The problem is I enter document information for each TRACK_NO. TRACK_NO is auto number. If I press enter key next number will be coming automatically. In case I delete this number one number will be missing. This number missing not beautiful. I dont like this way. I need all numbers (TRACK_NO). In case 2 numbers automaticaly coming that also waste. How to solve this problem? Would appreciate your greate favor in this regard. ak, Instead of using an autonumber, use a LongInteger field. On your subform, set the DefaultValue of TrackNo to... =NZ(DMax("[TrackNo]", "table2")) +1 Every time you create a new record, TrackNo will be the next larger value than is stored in the table. Also, to help insure no sequence gaps... don't allow any record deletions in that table.  Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." > Dear Experts, > [quoted text clipped - 11 lines] > also waste. How to solve this problem? Would appreciate your greate > favor in this regard. I have typed as per you mentioned. But in vein. I get a remark as : Unknown function 'NZ' in validation expression or default value on 'Table2.SN'. Please help. > ak, > Instead of using an autonumber, use a LongInteger field. [quoted text clipped - 19 lines] > > also waste. How to solve this problem? Would appreciate your greate > > favor in this regard. You've almost certainly got a Missing Reference, Ak. Open a Module, Go to Tools, Reference and check what is there. If any are marked as Missing, note their names and Untick them. Run Debug. If you still have a problem, find the missing reference in the list and tick it. Evi > I have typed as per you mentioned. But in vein. > I get a remark as : [quoted text clipped - 33 lines] > > > also waste. How to solve this problem? Would appreciate your greate > > > favor in this regard. I did not got your instruction. Where is that Module to open? > You've almost certainly got a Missing Reference, Ak. > Open a Module, Go to Tools, Reference and check what is there. If any are [quoted text clipped - 39 lines] > > > > also waste. How to solve this problem? Would appreciate your greate > > > > favor in this regard. In your main database window, click on the word Module and choose New so that a new Module Page opens. You will see Tools on the Menu Bar and under that, you will find References. Evi > I did not got your instruction. Where is that Module to open? > [quoted text clipped - 41 lines] > > > > > also waste. How to solve this problem? Would appreciate your greate > > > > > favor in this regard. ak, Whenever you have trouble with code, please cut and paste that code "exactly" into your reply. We need to see your code just as you have it. Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." >I have typed as per you mentioned. But in vein. > I get a remark as : [quoted text clipped - 25 lines] >> > also waste. How to solve this problem? Would appreciate your greate >> > favor in this regard. This is the Code I typed : =NZ(DMax("[TRACK_NO]","table2"))+1 Awaiting your response. > ak, > Whenever you have trouble with code, please cut and paste that code [quoted text clipped - 29 lines] > >> > also waste. How to solve this problem? Would appreciate your greate > >> > favor in this regard. ak, Evi's response (in this thread) would be correct. You have a missing Reference, so Access doesn't know what an NZ function is. Read Evi's reply, and in addition, you can try Doug Steele's excellent write up on the subject. http://www.accessmvp.com/djsteele/AccessReferenceErrors.html While in Design mode for your form, select View/ViewCode from the menubar. That will place yoiu in the code module... follow the instructuions from Evi or Doug to locate the missing reference. Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." > This is the Code I typed : > =NZ(DMax("[TRACK_NO]","table2"))+1 [quoted text clipped - 37 lines] >> >> > greate >> >> > favor in this regard. Dear Expert, I am not satisfied with your response this way. Please Please help me more. The thing is, as I am aware about myself I am not expert in visual basic. But please solve the problem from your side itself in easy way. I have read Evi's reply and Doug Steele's write up too. I am in a big problem of solve the problem. Please help me. > ak, > Evi's response (in this thread) would be correct. You have a missing [quoted text clipped - 47 lines] > >> >> > greate > >> >> > favor in this regard. Which is the bit you don't understand, Ak. We'll try to say it another way. We do understand that you are trying to understand the complications of Access while using a language with which you are not completely familiar and I'm sure that when you write 'I am not satisfied with your response' you are not aware of how inappropriate it sounds. It will make others hesitate to answer your posts. It is the sort of thing you would say to an employee to whom you are paying a salary. In fact, Al's explanation sounds very clear to me. So please explain which bit isn't clear to you. Evi > Dear Expert, > I am not satisfied with your response this way. Please Please help me more. [quoted text clipped - 80 lines] > > >> >> > greate > > >> >> > favor in this regard. Evi, I am so sorry about my language problem and I know you are doing a free service. I need to know why not working once I type the below : NZ(DMax("[TrackNo]", "table2")) +1 Anyway I need to see that properly working. Please help me. Where is the problem? Is it problem of wrong table name? What is NZ? Sorry for disturbance. > Which is the bit you don't understand, Ak. We'll try to say it another way. > We do understand that you are trying to understand the complications of [quoted text clipped - 105 lines] > > > >> >> > greate > > > >> >> > favor in this regard. Hi Ak I've just realized that you are probably using this in your *Table*'s Default Value. I've just tried this on a database table with no missing references for the DMax and NZ functions and I get the same results as you do. What Al said is that you have to use the function in your Subform To do this, open your Subform by itself in Design View, click on your Control. Click on the Properties button, and on the Data Tab, type the formula next to where it says Default Value Replace table2 with the correct name for your table which contains the field TrackNo Evi > Evi, > I am so sorry about my language problem and I know you are doing a free [quoted text clipped - 114 lines] > > > > >> >> > greate > > > > >> >> > favor in this regard. Hi Evi, I did not find any Default Value in Data Tab in control of sub form. What can I do? Please help? > Hi Ak > I've just realized that you are probably using this in your *Table*'s [quoted text clipped - 150 lines] > > > > > >> >> > greate > > > > > >> >> > favor in this regard. I've been looking at the very start of this thread and I think we need to approach this differently I think that what you need is a) keep your Autonumber field - you never have to look at it if you don't want to but they are so easy to maintain. Add a Number field to your table - lets call it SortNum (or whatever you like) To ensure that the next consecutive number is always added, in your subform use the AfterUpdate Event of one the controls which you always use when you want to add a new record The code you will use will say If IsNull(Me.SortNum) then Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" & [Track_No])) + 1 End If Replace table and field names with the real ones. Evi > Hi Evi, > I did not find any Default Value in Data Tab in control of sub form. [quoted text clipped - 154 lines] > > > > > > >> >> > greate > > > > > > >> >> > favor in this regard. Evi, I am worried about I could not do this. You are saying in right way. But once I try to do this I could not make it practicable. The reason is my lack of knowlege, I know. But if I get info more simlified way I believe I can do that. Can you help me please? I created control, I went to properties etc.. etc... > I've been looking at the very start of this thread and I think we need to > approach this differently [quoted text clipped - 197 lines] > > > > > > > >> >> > greate > > > > > > > >> >> > favor in this regard. Ak, give us the name of the table which contains the field SortNum The name of the first control in your form which you always fill in when you are creating a new record. Evi > Evi, > I am worried about I could not do this. You are saying in right way. But [quoted text clipped - 102 lines] > > > > > > excellent > > > > > > > > write up on the subject. http://www.accessmvp.com/djsteele/AccessReferenceErrors.html > > > > > > > > While in Design mode for your form, select View/ViewCode from > > the [quoted text clipped - 32 lines] > > > > > > > > >> > > > > > > > > >> "ak" <akuttym@hotmail.com> wrote in message news:A4BD6897-3C41-4971-9205-9EC026072045@microsoft.com... > > > > > > > > >> >I have typed as per you mentioned. But in vein. > > > > > > > > >> > I get a remark as : [quoted text clipped - 31 lines] > > > > > > > > >> >> > > > > > > > > >> >> "ak" <akuttym@hotmail.com> wrote in message news:C2418C52-09B2-455D-B4B9-62EDC634502C@microsoft.com... > > > > > > > > >> >> > Dear Experts, > > > > > > > > >> >> > [quoted text clipped - 27 lines] > > > > > > > > >> >> > greate > > > > > > > > >> >> > favor in this regard. Evi, I have typed in my database form properties of control as you told. But there is a certain mistake which I made or something else such as I dont know what is Me.SortNum something like that. But I believe you can help me more to enable me to do that. I am sorry for this. Please help. I have more and more worry about disturbing you. I know this is free service. > I've been looking at the very start of this thread and I think we need to > approach this differently [quoted text clipped - 197 lines] > > > > > > > >> >> > greate > > > > > > > >> >> > favor in this regard. Hi ak, sorry about the delay. I fell down the stairs and hurt my arm and haven't been able to write for a couple of days. Me.SortNum In code pages, when you want to write about a control in the form or report which is open, you write Me a dot and the name of the control. Evi > Evi, > I have typed in my database form properties of control as you told. But [quoted text clipped - 102 lines] > > > > > > excellent > > > > > > > > write up on the subject. http://www.accessmvp.com/djsteele/AccessReferenceErrors.html > > > > > > > > While in Design mode for your form, select View/ViewCode from > > the [quoted text clipped - 32 lines] > > > > > > > > >> > > > > > > > > >> "ak" <akuttym@hotmail.com> wrote in message news:A4BD6897-3C41-4971-9205-9EC026072045@microsoft.com... > > > > > > > > >> >I have typed as per you mentioned. But in vein. > > > > > > > > >> > I get a remark as : [quoted text clipped - 31 lines] > > > > > > > > >> >> > > > > > > > > >> >> "ak" <akuttym@hotmail.com> wrote in message news:C2418C52-09B2-455D-B4B9-62EDC634502C@microsoft.com... > > > > > > > > >> >> > Dear Experts, > > > > > > > > >> >> > [quoted text clipped - 27 lines] > > > > > > > > >> >> > greate > > > > > > > > >> >> > favor in this regard. I could not do that. > Hi ak, sorry about the delay. I fell down the stairs and hurt my arm and > haven't been able to write for a couple of days. [quoted text clipped - 257 lines] > > > > > > > > > >> >> > greate > > > > > > > > > >> >> > favor in this regard. Ak, what is the name of a control in your form, not the Autonumber field, but the first one which you fill in when you are adding a new record? Evi > I could not do that. > [quoted text clipped - 159 lines] > > > > > > > > > > > > > > > > > > > > "ak" <akuttym@hotmail.com> wrote in message news:63222FF2-D5ED-4E4C-A23C-B133B1C1B259@microsoft.com... > > > > > > > > > > > This is the Code I typed : > > > > > > > > > > > =NZ(DMax("[TRACK_NO]","table2"))+1 [quoted text clipped - 52 lines] > > > > > > > > > > >> >> Al Campagna > > > > > > > > > > >> >> Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html > > > > > > > > > > >> >> "Find a job that you love... and you'll never work > > a [quoted text clipped - 39 lines] > > > > > > > > > > >> >> > greate > > > > > > > > > > >> >> > favor in this regard. I use one form, created by wizard. This is form made by wizard from Table2. In this form TRACK_NO, DOCUMENT_NAME, PROCESS_FINISHED and so on. > Ak, what is the name of a control in your form, not the Autonumber field, > but the first one which you fill in when you are adding a new record? [quoted text clipped - 268 lines] > > > news:C2418C52-09B2-455D-B4B9-62EDC634502C@microsoft.com... > > > > > > > > > > > >> >> > Dear Experts, See if you can perform the following steps: Tell us which steps you can't do Add a number field called SortNum to the table on which the form is based. It must be a Number field. If your form is based on a query, Open the query in design view and add this field to the query grid In the Form's Design View, click the Field List button and add your new SortNum field to the form by dragging it from the Field List onto the Form. click on the Document_Name control Click on Properties Click on the Events tab Click next to After Update so that you open a code page Just above where it says End Sub, paste the following: If IsNull(Me.SortNum) then Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" & [Track_No]))+1 End If Replace the word table2 with the real name of the table which contains the SortNum field. Now, when you fill in your form, don't bother to fill in SortNum, as soon as you have filled in Document_Name, the SortNum field will fill itself in (hopefully!) Evi > I use one form, created by wizard. This is form made by wizard from Table2. > In this form TRACK_NO, DOCUMENT_NAME, PROCESS_FINISHED and so on. [quoted text clipped - 137 lines] > > > > > > > > > > > > > > > > > > > > "ak" <akuttym@hotmail.com> wrote in message news:BFE61847-BA38-4EAA-A7F6-214599F4EB29@microsoft.com... > > > > > > > > > > > Dear Expert, > > > > > > > > > > > I am not satisfied with your response this way. Please [quoted text clipped - 70 lines] > > > > > > > > > > > > >> Al Campagna > > > > > > > > > > > > >> Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html > > > > > > > > > > > > >> "Find a job that you love... and you'll never > > work a [quoted text clipped - 55 lines] > > > > news:C2418C52-09B2-455D-B4B9-62EDC634502C@microsoft.com... > > > > > > > > > > > > >> >> > Dear Experts, Evi, I have done as you told me. After SortNum field next field is NAME. Instead of Document_Name control 2nd field is NAME. So 1st field is SortNum. Once I enter name in NAME filed I get an error message Run-time error '3075': Syntax-error (missing operator) in query expression '[Track_No]<'. As you told me once I enter name in NAME filed the SortNum have to display new field number. Let me tell you what are the field in this form I created. SortNum NAME TRACK_NO DOCUMETN_NAME PROCESS_FINISHED PROCESS FOR FORWARDED_TO FORWARDED DATE NOTES Please comment. > See if you can perform the following steps: Tell us which steps you can't do > Add a number field called SortNum to the table on which the form is based. [quoted text clipped - 270 lines] > > > > > > > > > > > > > >> > http://home.comcast.net/~cccsolutions/index.html Ak, you need to rename the Name field. Name is a reserved word in Access. At some time, when you least expect it, it will cause an inexplicable problem. I see that you have spelt Document_Name as Documetn_Name - is that just a typo? Paste the actual code which you have on your form's code page. It is possible that the email broke up the message lines. Evi > Evi, > I have done as you told me. After SortNum field next field is NAME. Instead [quoted text clipped - 158 lines] > > > > > > > > > > > > > > > > > > > > "ak" <akuttym@hotmail.com> wrote in message news:289BA7C0-DDE7-4843-A54A-7166F26CB56C@microsoft.com... > > > > > > > > > > > Evi, > > > > > > > > > > > I am so sorry about my language problem and I know you are [quoted text clipped - 100 lines] > > > > > > > > > > > > > > Al Campagna > > > > > > > > > > > > > > Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html > > > > > > > > > > > > > > "Find a job that you love... and you'll never > > work a [quoted text clipped - 25 lines] > > > > > > > > > > > > > > >> > > http://home.comcast.net/~cccsolutions/index.html Once going on this way by lot of question and answer I am confused. This is my problem. Now question arise this way. What will do with track number in Sub form. I guess the sort number is only for sorting records. If there is SortNum I hope no need of Track_No Actually I need a databse to maintain my office documents. For example there are 100 people in a company. Each one have several issues daily such as Passport renewal Driving license renewal need medical card need exit reentry need family visa so on.... Once an employee e.g. serial number 7 bring 5 issues one day this 5 issues I will file as 5 issues physically in five pages. In each page I will write down serial number without duplicate number. In my way this is Track_No. Main form will be keeping SN (serial number of employee) NAME1 (employee name) DESIGNATIONA LOCATION (employee's office location) CONTACT SN will be primary key. Once one employee ask me about his certain date's certain case I will search in database. So I can see his SN and Track_No. I can take that paper according to the Track_No from the physical file which I filed serially. Please help me. > Ak, you need to rename the Name field. Name is a reserved word in Access. At > some time, when you least expect it, it will cause an inexplicable problem. [quoted text clipped - 271 lines] > > > > > > > > > > > read > > > > > > > > > > > > > > Evi's reply and Doug Steele's write up too. I am I know what you mean about the questions and answers! Ak, keep Track_No if it is your Autonumber Primary Key field. You don't have to look at it, you can make it invisible in forms and reports but until you are more accustomed to Access, it would be safer to let it do its work in the background. Currently the DMax uses it to find which records come before the current one. It *sounds* as if your database structure needs to be like this: TblLocation LocID Location (all the different office locations) TblEmployee EmpSN EmpFirstName EmpSurname LocID (Linked from TblLocation TblIssue IssueID Issue (eg Passport renewal, Family Visa) TblDocument Track_No (if that is your Autonumber, primary key) SortNum EmpSN (linked from TblEmployee) IssueID (linked from TblIssue) ReceivedDate (Date you received the document) Other fields which concern that employee with that document I don't know what DesignationA or Contact refer to - whether they concern the document or the employee or both or neither, so I don't know which table they belong to or if they need to be linked from a seperate table. Evi > Once going on this way by lot of question and answer I am confused. This is > my problem. Now question arise this way. What will do with track number in [quoted text clipped - 178 lines] > > > > > > > > > > > > > > > > > > > > "ak" <akuttym@hotmail.com> wrote in message news:52362A54-62E3-4B0B-9F77-7A9203087FB0@microsoft.com... > > > > > > > > > > > Hi Evi, > > > > > > > > > > > I did not find any Default Value in Data Tab in control of [quoted text clipped - 121 lines] > > > > > > > > > > > > read > > > > > > > > > > > > > > > Evi's reply and Doug Steele's write up too. I am I changed the NAME filed to NAME1. Still keep getting the same error. So let me forget about the issue of Document_Name. > Ak, you need to rename the Name field. Name is a reserved word in Access. At > some time, when you least expect it, it will cause an inexplicable problem. [quoted text clipped - 271 lines] > > > > > > > > > > > read > > > > > > > > > > > > > > Evi's reply and Doug Steele's write up too. I am |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.