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 |
#21
|
|||
|
|||
Formula ?
Private Sub Command85_Click()
DoCmd.GoToRecord , , acNewRec End Sub "Steve Schapel" wrote: Tom, The code we have been discussing is a function that returns a certain value. I believe the modification I suggested before willl be necessary to get it to return the value you want. But here's the main point... when is this value being applied? So far we have seen the function that calculates the value, but we have not seen the procedure for how this is supposed to be inserted into the relevant field in a new record. Can you please let us know how this works? You mentioned a command button... what is the code on the Click event of that button? Thanks. -- Steve Schapel, Microsoft Access MVP Tom wrote: Quite the contrary. When I click on "new record" or use the button I created to do the same, the same number appears, ....001. It needs to be ....002. When I enter data into that record and click on new record, the number that needs to be there is ....003. The first part of the number is working fine. 051122xxx, the date part. It's the last part that is not incrementally changing as a new record is created. Tom |
#22
|
|||
|
|||
Formula ?
Tom,
Ok, thanks. Well, that wasn't what I was looking for! I hope you understand what I was driving at here... You have showed us some code which is a user-defined function. You want a certain value entered to the Run Number field in a new record. But the function does not do this by itself, and the simple existence of the function does not achieve anything at all. Somewhere along the line, something is being "done" in order to use the function in the process of assigning the Run Number value. How this is being done, I still have no idea about, and I just wondered whether the actual method or timing of this procedure may have been contributing to the problem. But anyway, as discussed elsewhere in the thread, it looks like the absence of the leading 0 on the Run Number is a source of at least part of the problem, so needs to be fixed. It has occurred to me that the Run Number is a number data type, in which case a leading 0 will not be possible. You could change it to a Text data type (which is probably more normal for such a field anyway). Or you could change the code in the GetNextNumber() function, where it defines strCurrDate = Format(Date, "yymmdd") I think this would do it... strCurrDate = Format(Val(Format(Date,"yymmdd"))) -- Steve Schapel, Microsoft Access MVP Tom wrote: Private Sub Command85_Click() DoCmd.GoToRecord , , acNewRec End Sub |
#23
|
|||
|
|||
Formula ?
Steve,
I thought this might be a better way to handle the problem. Navigating these discussion logs is time consuming. I'd like to e-mail you an attachment of the file in question. That way you can look at everything. Would that be OK with you? Thanks, Tom Pickett ABLE Medical Transport 614-670-1151 (cell) "Steve Schapel" wrote: Tom, Ok, thanks. Well, that wasn't what I was looking for! I hope you understand what I was driving at here... You have showed us some code which is a user-defined function. You want a certain value entered to the Run Number field in a new record. But the function does not do this by itself, and the simple existence of the function does not achieve anything at all. Somewhere along the line, something is being "done" in order to use the function in the process of assigning the Run Number value. How this is being done, I still have no idea about, and I just wondered whether the actual method or timing of this procedure may have been contributing to the problem. But anyway, as discussed elsewhere in the thread, it looks like the absence of the leading 0 on the Run Number is a source of at least part of the problem, so needs to be fixed. It has occurred to me that the Run Number is a number data type, in which case a leading 0 will not be possible. You could change it to a Text data type (which is probably more normal for such a field anyway). Or you could change the code in the GetNextNumber() function, where it defines strCurrDate = Format(Date, "yymmdd") I think this would do it... strCurrDate = Format(Val(Format(Date,"yymmdd"))) -- Steve Schapel, Microsoft Access MVP Tom wrote: Private Sub Command85_Click() DoCmd.GoToRecord , , acNewRec End Sub |
#24
|
|||
|
|||
Formula ?
Tom,
At this stage I think it would be good to continue liaising via the newsgroup. Especially since we have reached the point of identifying specific actions you could take to help the situation. Is the Run Number field in your table a number or text data type? If number, any reason it shouldn't be changed to text? If text, any reason for not entering the leading 0? -- Steve Schapel, Microsoft Access MVP Tom wrote: Steve, I thought this might be a better way to handle the problem. Navigating these discussion logs is time consuming. I'd like to e-mail you an attachment of the file in question. That way you can look at everything. Would that be OK with you? |
#25
|
|||
|
|||
Formula ?
Steve,
Here is the code in the module. Function GetNextNumber() As String Dim strCurrDate As String Dim varHighValue As Variant strCurrDate = Format(Val(Format(Date, "yymmdd"))) varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", "Left([RUN NUMBER], 6) ='" & strCurrDate & "'") If IsNull(varHighValue) Then GetNextNumber = strCurrDate & "001" Else GetNextNumber = Format(CLng(varHighValue) + 1, "000000000") End If End Function I've decided to make this easy, and it will problably be more benificial when it comes time for relationships, and create a field next to the date, that will be just for the last 3 digits. The finished product should be in the format: yymmdd 001 So if you could tell me what part of the code to keep for just the date automatically changing at midnight and in the format yymmdd. (no slashes "/" ) Right now it is giving me yymmdd001 just need the yymmdd. And could you tell me a counting code that will start at 001 at midnight and increase by 1 for every record recorded, that will make this alot easier. It should revert to 001 at midnight when the date changes. Unless we can make the yymmdd001 change with each new record, that might be an easier way to go. You've been a big help so far, thanks for your patience. Tom "Steve Schapel" wrote: Tom, At this stage I think it would be good to continue liaising via the newsgroup. Especially since we have reached the point of identifying specific actions you could take to help the situation. Is the Run Number field in your table a number or text data type? If number, any reason it shouldn't be changed to text? If text, any reason for not entering the leading 0? -- Steve Schapel, Microsoft Access MVP Tom wrote: Steve, I thought this might be a better way to handle the problem. Navigating these discussion logs is time consuming. I'd like to e-mail you an attachment of the file in question. That way you can look at everything. Would that be OK with you? |
#26
|
|||
|
|||
Formula ?
Tom,
Regarding "unless we can make the yymmdd001 change with each new record", it is actually very easy to do this, and I can show you how if you're still interested. But now we have started down another track, which is probably a better one anyway. My understanding is that the new records in the ABLE_Table1 table are entered via a form. Is this correct? There is also apparently an assumption that there will never be more than 999 new records in any given day. Is this correct? If it was mine, I would never have had a function like your GetNextNumber() function in the first place. I recommend you should delete it completely. Now that you have decided to make two separate fields, one should be a Date/Time data type and the other a Number data type. Let's say these fields are called RunDate and RunNumber. Ok, here's how I would do it... 1. Set the Default Value property of the RunDate control on the form to... Date() 2. Set the Format property of the RunDate control on the form to... yymmdd 3. Set the Format property of the RunNumber control on the form to... 000 4. Alter the code on your Command85 button, like this... Private Sub Command85_Click() DoCmd.GoToRecord , , acNewRec Me.RunNumber = Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1 Me.Dirty = False End Sub By way of explanation, the actual data stored in the RunDate and RunNumber fields in the table will be a date and a number, which will look like a date and a number if for some obscure reason you were poking around in the table. In my opinion, this is as it should be. For your purposes on form and report, you want the date to be shown without /s and so forth, and you want the number to be shown as 3 digits with leading zeros. This does not mean that the date and the number should be stored in the table as text. They should be stored as a date and a number, and then getting them to appear the way you want is Formatting. Formatting affects the appearance of the data, but does not affect the value of the data. -- Steve Schapel, Microsoft Access MVP Tom wrote: Steve, Here is the code in the module. Function GetNextNumber() As String Dim strCurrDate As String Dim varHighValue As Variant strCurrDate = Format(Val(Format(Date, "yymmdd"))) varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", "Left([RUN NUMBER], 6) ='" & strCurrDate & "'") If IsNull(varHighValue) Then GetNextNumber = strCurrDate & "001" Else GetNextNumber = Format(CLng(varHighValue) + 1, "000000000") End If End Function I've decided to make this easy, and it will problably be more benificial when it comes time for relationships, and create a field next to the date, that will be just for the last 3 digits. The finished product should be in the format: yymmdd 001 So if you could tell me what part of the code to keep for just the date automatically changing at midnight and in the format yymmdd. (no slashes "/" ) Right now it is giving me yymmdd001 just need the yymmdd. And could you tell me a counting code that will start at 001 at midnight and increase by 1 for every record recorded, that will make this alot easier. It should revert to 001 at midnight when the date changes. Unless we can make the yymmdd001 change with each new record, that might be an easier way to go. You've been a big help so far, thanks for your patience. Tom |
#27
|
|||
|
|||
Formula ?
Steve,
Thank you! I'm learning, which I love to do. Now for tweaking. After entering several records, closing the program, and then reopening the program, I notice that it will always start at 000. When I compare the "RunNumber" with the actual count on the Record Navigation bar, it is way off. When I look at the table, there are several duplicate RunNumbers with "0". Several Questions: 1. Do I set the Indexed to "no duplicates" on the table? 2. How do I set the starting number to 001 and not 000 3. How do I set the RunNumber to always reset at midnight to 001 4. If I close the program, then reopen, is there a way to have it always open with a new record for data entry? Again, Thanks! Tom "Steve Schapel" wrote: Tom, Regarding "unless we can make the yymmdd001 change with each new record", it is actually very easy to do this, and I can show you how if you're still interested. But now we have started down another track, which is probably a better one anyway. My understanding is that the new records in the ABLE_Table1 table are entered via a form. Is this correct? There is also apparently an assumption that there will never be more than 999 new records in any given day. Is this correct? If it was mine, I would never have had a function like your GetNextNumber() function in the first place. I recommend you should delete it completely. Now that you have decided to make two separate fields, one should be a Date/Time data type and the other a Number data type. Let's say these fields are called RunDate and RunNumber. Ok, here's how I would do it... 1. Set the Default Value property of the RunDate control on the form to... Date() 2. Set the Format property of the RunDate control on the form to... yymmdd 3. Set the Format property of the RunNumber control on the form to... 000 4. Alter the code on your Command85 button, like this... Private Sub Command85_Click() DoCmd.GoToRecord , , acNewRec Me.RunNumber = Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1 Me.Dirty = False End Sub By way of explanation, the actual data stored in the RunDate and RunNumber fields in the table will be a date and a number, which will look like a date and a number if for some obscure reason you were poking around in the table. In my opinion, this is as it should be. For your purposes on form and report, you want the date to be shown without /s and so forth, and you want the number to be shown as 3 digits with leading zeros. This does not mean that the date and the number should be stored in the table as text. They should be stored as a date and a number, and then getting them to appear the way you want is Formatting. Formatting affects the appearance of the data, but does not affect the value of the data. -- Steve Schapel, Microsoft Access MVP Tom wrote: Steve, Here is the code in the module. Function GetNextNumber() As String Dim strCurrDate As String Dim varHighValue As Variant strCurrDate = Format(Val(Format(Date, "yymmdd"))) varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", "Left([RUN NUMBER], 6) ='" & strCurrDate & "'") If IsNull(varHighValue) Then GetNextNumber = strCurrDate & "001" Else GetNextNumber = Format(CLng(varHighValue) + 1, "000000000") End If End Function I've decided to make this easy, and it will problably be more benificial when it comes time for relationships, and create a field next to the date, that will be just for the last 3 digits. The finished product should be in the format: yymmdd 001 So if you could tell me what part of the code to keep for just the date automatically changing at midnight and in the format yymmdd. (no slashes "/" ) Right now it is giving me yymmdd001 just need the yymmdd. And could you tell me a counting code that will start at 001 at midnight and increase by 1 for every record recorded, that will make this alot easier. It should revert to 001 at midnight when the date changes. Unless we can make the yymmdd001 change with each new record, that might be an easier way to go. You've been a big help so far, thanks for your patience. Tom |
#28
|
|||
|
|||
Formula ?
Tom,
After entering several records, closing the program, and then reopening the program, I notice that it will always start at 000. Hmmm. I think the method I outlined will rely on all new records being started via clicking the Command85 button. I suspect that the result you are seeing is because of opening the form directly, and entering a new record straight away... does that sound right? When I compare the "RunNumber" with the actual count on the Record Navigation bar, it is way off. When I look at the table, there are several duplicate RunNumbers with "0". Wanting the record navigator to correspond with anything related to your data is a recipe for a headache. Several Questions: 1. Do I set the Indexed to "no duplicates" on the table? You will need a Primary Key inthe table. It can't be either the RunDate or RunNumber fields, as they will both contain duplicates. You can set *both* fields together as a composite primary key. Or you can add an AutoNumber field to the table as the primary key. But you can't set the Index just on the RunNumber to 'no duplicates', because there will be a 001 every day. 2. How do I set the starting number to 001 and not 000 Unless I've missed something, this should automatically be the case if you always start a new record via clicking the Command85 button. If you want to be able to start a new record by another route as well, you would need to add some code somewhere (depending on what you want to do), to ensure that the next RunNumber is allocated. 3. How do I set the RunNumber to always reset at midnight to 001 Unless I've missed something, this should automatically be the case with the procedure I suggested. 4. If I close the program, then reopen, is there a way to have it always open with a new record for data entry? Does the application open with a "menu/switchboard/whatever" type of form? Or do you want it to open straight to your data form? You can set the Data Entry property of the form to Yes, but this won't be a good way to go if you want to be able to scroll back to previously entered records. You could possibly use an AutoExec macro to open the form as soon as the application is opened, and this could also include assigning the applicable RunNumber to the first record entered for the session. -- Steve Schapel, Microsoft Access MVP |
#29
|
|||
|
|||
Formula ?
Steve,
Right now, I have a shortcut on my desktop to the form. When I open that up, that's when "000" appears everytime. You are right about the command button, it does change the number in the control. What I need is a way for that control to generate the next available number, in sequence, if I were to start up the program from scratch. Have it search the database upon opening and plop that next number right into the control. The command button is working fine. I also noticed that when I click on the command button, that the flashing cursor does not go to the 1st tab stop, on the next record. When I first open the database it does, but not after I record a record. A switchboard/ menu sounds like a great idea. I'm assuming, as a newbee, that this is like a "program" front end, "Welcome to ABLE Medical Dispatch" "select where you want to go" type of thing. With buttons for "Dispatching" "Reports" etc. It sounds like I need some books, and I have a list. I just wanted to get started as we are going to begin operations in January. Tom "Steve Schapel" wrote: Tom, After entering several records, closing the program, and then reopening the program, I notice that it will always start at 000. Hmmm. I think the method I outlined will rely on all new records being started via clicking the Command85 button. I suspect that the result you are seeing is because of opening the form directly, and entering a new record straight away... does that sound right? When I compare the "RunNumber" with the actual count on the Record Navigation bar, it is way off. When I look at the table, there are several duplicate RunNumbers with "0". Wanting the record navigator to correspond with anything related to your data is a recipe for a headache. Several Questions: 1. Do I set the Indexed to "no duplicates" on the table? You will need a Primary Key inthe table. It can't be either the RunDate or RunNumber fields, as they will both contain duplicates. You can set *both* fields together as a composite primary key. Or you can add an AutoNumber field to the table as the primary key. But you can't set the Index just on the RunNumber to 'no duplicates', because there will be a 001 every day. 2. How do I set the starting number to 001 and not 000 Unless I've missed something, this should automatically be the case if you always start a new record via clicking the Command85 button. If you want to be able to start a new record by another route as well, you would need to add some code somewhere (depending on what you want to do), to ensure that the next RunNumber is allocated. 3. How do I set the RunNumber to always reset at midnight to 001 Unless I've missed something, this should automatically be the case with the procedure I suggested. 4. If I close the program, then reopen, is there a way to have it always open with a new record for data entry? Does the application open with a "menu/switchboard/whatever" type of form? Or do you want it to open straight to your data form? You can set the Data Entry property of the form to Yes, but this won't be a good way to go if you want to be able to scroll back to previously entered records. You could possibly use an AutoExec macro to open the form as soon as the application is opened, and this could also include assigning the applicable RunNumber to the first record entered for the session. -- Steve Schapel, Microsoft Access MVP |
#30
|
|||
|
|||
Formula ?
Tom,
Tom wrote: Right now, I have a shortcut on my desktop to the form. When I open that up, that's when "000" appears everytime. You are right about the command button, it does change the number in the control. You can handle this by putting similar code into the On Load event property of the form itself. Something like this... Private Sub Form_Load() Me.RunNumber = Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1 Me.Dirty = False End Sub I also noticed that when I click on the command button, that the flashing cursor does not go to the 1st tab stop, on the next record. When I first open the database it does, but not after I record a record. When you move from record to record on a form, the focus stays on the same control on the next/new record as it was on the previous. If you want the behaviour to be different, you have to control it. The way to do this would be to add a line to the Command85 code, like this... Private Sub Command85_Click() DoCmd.GoToRecord , , acNewRec Me.NameOfFirstControl.SetFocus Me.RunNumber = Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1 Me.Dirty = False End Sub (of course, you substitute the actual name of your control for the 'NameOfFirstControl' in the code) A switchboard/ menu sounds like a great idea. I'm assuming, as a newbee, that this is like a "program" front end, "Welcome to ABLE Medical Dispatch" "select where you want to go" type of thing. With buttons for "Dispatching" "Reports" etc. Yes, that's exactly what I mean. A desktop shortcut to an Access form is not a suitable final solution, you need to provide a user interface to your application, and navigation facilities. It sounds like I need some books, and I have a list. Great idea. Do you have "Building Microsoft Access Applications" by John Viescas on your list? I just wanted to get started as we are going to begin operations in January. Well, I won't hold you up any longer... you've got a busy couple of months ahead of you. :-) -- Steve Schapel, Microsoft Access MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
IF formula? | meris | Worksheet Functions | 1 | September 6th, 2005 07:14 AM |
adding row to forumla | carrera | General Discussion | 9 | August 23rd, 2005 10:24 PM |
Formula checking multiple worksheets | sonic-the-mouse | Worksheet Functions | 11 | June 6th, 2005 06:37 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |
generating formula with a formula | Lisa Linard | Worksheet Functions | 2 | November 27th, 2003 12:03 PM |