 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / May 2008how do i set a field to be the same value as the previous record
I am trying to create some data entry forms. One such forms needs a numeric field to default to the value of the field in the previous record. This field is a date field. the code I am using works but the date displayed is in the american format not the system format. Code is as follows: Private Sub BatchNumber_AfterUpdate() If Not IsNull(Me.BatchNumber.Value) Then BatchNumber.DefaultValue = Me.BatchNumber.Value + 1 End If End Sub The other problem is adding 1 to the value of a field for subsequent records. The code I am using only works for the 2nd record and not the 3rd etc. Private Sub BatchNumber_AfterUpdate() If Not IsNull(Me.BatchNumber.Value) Then BatchNumber.DefaultValue = Me.BatchNumber.Value + 1 End If End Sub I am a new user so be gentle! Thanks John Hi John, See this article from Access MVP Allen Browne. I think it has what you need: Assign default values from the last record http://allenbrowne.com/ser-24.html As for the format, you should be able to set whatever date format you want for the text box control used to display the value. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > I am trying to create some data entry forms. One such forms needs a numeric > field to default to the value of the field in the previous record. This field [quoted text clipped - 20 lines] > > Thanks John If you use the batch number default value then the after update event won't be triggered - after update event for a control is only triggered if you type (or paste) a value into the control. That is why the BatchNumber is not incrementing. I don't know if Allen's method will work for you. If not, post back for alternative solution. John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Hi John, > [quoted text clipped - 36 lines] >> >> Thanks John Hello Tom, I am a little bit confuse with your "http://allenbrowne.com/ser-24.html". 1. Private Sub Form_BeforeInsert(Cancel As Integer) Dim strMsg As String Call CarryOver(Me, strMsg) If strMsg <> vbNullString Then MsgBox strMsg, vbInformation End If End Sub Do I have to replace "Form" by the actual form name? 2. Public Function CarryOver(frm As Form, strErrMsg As String, ParamArray avarExceptionList()) As Long On Error GoTo Err_Handler And here too, do I have to replace "frm" with the actual form name? Because I tried to do exactly the way you have it but it does not work. Thanks for help Ali Mbilo > Hi John, > [quoted text clipped - 36 lines] > > > > Thanks John Hi Ali, > 1. Do I have to replace "Form" by the actual form name? No. A form has several programmable events, one of which is BeforeUpdate. You just paste the code in to the class module of a form, as it is shown in Allen's article. Note: If your form already includes this procedure, then you'd need to add this code to an existing Private Sub Form_BeforeInsert(Cancel As Integer) procedure. > 2. And here too, do I have to replace "frm" with the actual form name? > Because I tried to do exactly the way you have it but it does not work. Nope. What does "does not work" mean? Did you receive any error messages? What did you see or not see? Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Hello Tom, > [quoted text clipped - 21 lines] > > Ali Mbilo Hello Tom, I Have one more question to ask you, How can I get a data from a field (a specific row) from a different table in one form? Thank you very much, Ali Mbilo > Hi John, > [quoted text clipped - 36 lines] > > > > Thanks John Please post this as a brand new question (ie. create a new post). Also, try to explain a bit more in detail what you are wanting to accomplish. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Hello Tom, > [quoted text clipped - 5 lines] > > Ali Mbilo Hello, I really appreciate your previous help and posted this new one 2 days ago but didn't see your answer. I need your help soloving this issue. I have 2 tables in my database and would like to have a: Field A in Table A to be equal to Field C in Table B. I have tried so far to do: Field A = [Table B]![Field C] but I am getting a "#Name?# error. > Please post this as a brand new question (ie. create a new post). Also, try > to explain a bit more in detail what you are wanting to accomplish. [quoted text clipped - 14 lines] > > > > Ali Mbilo Hi Ali, > I really appreciate your previous help and posted this new one 2 days ago > but didn't see your answer. Sorry, but I wasn't on the lookout for your question. I suggested that you post as a new thread, because your issue was significantly different than this existing thread started by John. Are you saying that you haven't gotten *any* responses? I might go take a look at it, but help me find it so that I don't have to go on a fishing expedition. Reply with a link to your new thread, or reply giving the following details so that I can easily find it: Posting Name Posting Date Exact subject of post And, in this posting that you mentioned, did you explain *why* you are wanting to store the same data in more than one table: > I have 2 tables in my database and would like to have a: > > Field A in Table A to be equal to Field C in Table B. You may have a valid reason, but I'd like you to tell me, in your own words, why you need to store the value from field A in table A into field C in table B. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Hello, > [quoted text clipped - 8 lines] > I have tried so far to do: Field A = [Table B]![Field C] > but I am getting a "#Name?# error. Thanks for your prompt reply. I did not get any clear answers at all from others that the reason why I am asking for your help. The first table that I created is full (255 fields) and I had to create a second table. This is for a cellphone repair report. In Table A, there is a field name "TotalQtyRepaired" In Table B, you have these fields "Team1Repair", "Team2Repair", "Team3Repair" I would like to have "TotalQtyRepaired" = Team1Repair + Team2Repair + Team3Repair I have tried to do TotalQtyRepaired = [Table B]!Team1Repair + [Table B]!Team2Repair + [Table B]!Team3Repair But I am getting this "#Name?" on the form view. Any ideas? > Hi Ali, > [quoted text clipped - 41 lines] > > I have tried so far to do: Field A = [Table B]![Field C] > > but I am getting a "#Name?# error. Hi Ali, > The first table that I created is full (255 fields) and I had to create a > second table. Whoa! You are not using proper database design techniques, if you have the maximum number of fields possible. In fact, most well designed databases very rarely have more than 25 to 30 fields, maximum, in any one table. > In Table A, there is a field name "TotalQtyRepaired" You do not want to store the result of something that can be calculated. The word "Total" in this field name stongly hints that it can be calculated from other fields in this table. If this is true, then perform such a calculation in a query, but don't attempt to store the result of your calculation in the table. Here's why: Suppose A + B + C = TotalQtyRepaired. You calculate this quantity and store the value in the TotalQtyRepaired field. Later on, you (or someone else) realizes that one or more of the independent values were entered incorrectly. So they change the value stored in, say Field A. However, they forget to update the total stored in the TotalQtyRepaired field. You now have inconsistent data. > In Table B, you have these fields "Team1Repair", "Team2Repair", "Team3Repair" This is known as a multi-field design, which is not a good thing. If you later have to add (or remove) team(s), you will need to add (or remove) one or more fields. You'll then need to modify any queies, forms, reports and module code that makes use of these fields. You should not have fields that store similar data in the table. Here is something that you should commit to memory: Fields are expensive; Records are cheap In other words, your database design should be such that it can accomodate any number of teams without the need to add or remove fields from a table. This is done by storing the team information in a separate, related, child table. At this point, the best thing that you could do is to stop working on your database for the present time, and start reading all you can about database normalization. Here is a link to get you started: http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101 Make sure to read the first two articles, by database design expert Michael Hernandez. Good Luck, Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Thanks for your prompt reply. > I did not get any clear answers at all from others that the reason why I am [quoted text clipped - 16 lines] > > Any ideas? Thanks a lot Tom. You really helped me. My last question is how can I have a unbound data to not duplicate in a different record? What I meant is, on day 1 whenever I put a value in my unbound data and when I open a new record (day2), I still have that value in the undound field. When I changed it on day 2, it changes too on day 1. Is there a way to stop that to happen? > Hi Ali, > [quoted text clipped - 72 lines] > > > > Any ideas? Hi Ali, > My last question is how can I have a unbound data to not duplicate in a > different record? The data in your unbound control is not getting saved with any record, unless you specifically write VBA code to cause this to happen. So, it's not "duplicating in a different record", it's simply displaying the .text property of the control. Why are you attempting to use an unbound control? There are two uses of unbound controls that come to mind (there may be more as well). The first use would be to help find an existing record. For example, take a look at this tutorial: Combo box to find a record http://www.access.qbuilt.com/html/find_a_record.html Here, we are using a combo box control to assist in the task of finding a record in a recordset. However, we are not using this control to change data. The second use would be unbound forms with unbound controls. This is an advanced technique that only few people ever venture into using. I suppose a third use of an unbound control might be to assist the user with an on-the-fly conversion of data, for example pounds to kilograms, if they needed to store the metric equivalent. In this case, you would be providing an "on-screen" calculator of sorts, to assist with the conversion. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Thanks a lot Tom. You really helped me. > [quoted text clipped - 5 lines] > When I changed it on day 2, it changes too on day 1. Is there a way to stop > that to happen? Hi Tom, The reason I am asking that is we want every records in the forms to be save according to the date. We want to be able to go back in february and see what we did on that day. Since I am out of fields in the table I am using, I am trying to come up with a backup plan to help me save more data. > Hi Ali, > [quoted text clipped - 36 lines] > > When I changed it on day 2, it changes too on day 1. Is there a way to stop > > that to happen? Hi Ali, > The reason I am asking that is we want every records in the forms to be save > according to the date. Okay, so all you need to do is add a field to track the date added. It can have a default value of =Date(). You don't even need to display this field on a form. You can also add another field, called DateLastModified, to track the last date that this record was edited. Generally, people accomplish this by using the Form's BeforeUpdate event procedure. > We want to be able to go back in february and see what we did on that day. By adding one or both of the fields I just mentioned, it will be very easy to query the database for any given day or range of days (for example, any week or any month). > Since I am out of fields in the table I am using, I am trying to come up > with a backup plan to help me save more data. Don't try to "work around" a flawed database design by "coming up" with work-arounds. You will only be creating more work for yourself, in the future, if you try to build using bailing wire and bubble gum. You *really* do need to start normalizing your database, so that you end up with tables that are no where near the maximum number of fields. As I said before, most well-designed databases do not include more than 25 to 30 fields maximum, on average, in any given table. Take what I'm trying to tell you to heart. If you have a table with 255 fields, you don't have a database; instead, what you have is an Access spreadsheet. Invest the time now to start learning more about database design and normalization, to save yourself untold amounts of time and frustration in the future trying to work with your current design. Trust me on this. I hinted before that there is a valid reason for storing the same data in more than one table. The reason is when you need to track history. For example, storing the full address of a customer in a Customers table, and storing the same full address in an Orders table. This way, you have a historical record of exactly where each and every order was delivered, even if your customer has moved several times in the intervening years. Another example is storing current price versus the price at the time of a sale. You want any reports that calculate total sales amounts to reflect the prices charged at the time of the sale, not today's current prices for the same items. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Hi Tom, > The reason I am asking that is we want every records in the forms to be save > according to the date. > We want to be able to go back in february and see what we did on that day. > Since I am out of fields in the table I am using, I am trying to come up > with a backup plan to help me save more data. |
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.