 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / August 2007combine two fields in form to create one field in table
I'm creating a database to track a particular form that we receive in our HR office. When we receive the form we give it a unique number that combines the division it came from an a sequential number pertaining to that division. In other words, when a form comes in from Executive, it will be given the number EX-001 and the next time one from Executive comes in it will be EX-002, and so on. When one from Finance & Administration comes in it will be numbered FA-001, then FA-002, etc. What I'd like to be able to do in the form is to have a field to select the division abbreviation and then have the sequential number be generated automatically depending on what division you choose. Then, I'd like those two fields to be combined in the table to be one field called RPA#. Although I've worked a lot with Access, I have yet to really grasp it. I guess it's just not my thing...so any instruction given to me should be very detailed and explanatory. I apprecaite any help I can get. Thanks. I can show you how to do this, but I need some info so I can get it right. Do you have a table that contains the division codes? If not, it would be a good idea to have such a table. What is the name of the table you will be putting the RPA#? BTW, Change the name. # should not be used in a name. Naming rules = Use only letters, numbers, and the underscore character in names. Do not use any special characters (#, $ % ^ ~) or Access reserved words (Name, Date, Type, etc) Why do you want to combine the two into one? I would suggest keeping them as separate fields, but concatenating them when necessary, but if you don't want to do this, okay. Anyway, post back with the detail, and I can write the code for you.  SignatureDave Hargis, Microsoft Access MVP > I'm creating a database to track a particular form that we receive in our HR > office. When we receive the form we give it a unique number that combines the [quoted text clipped - 12 lines] > guess it's just not my thing...so any instruction given to me should be very > detailed and explanatory. I apprecaite any help I can get. Thanks. I answered your questions in your post. Thank you so much and please let me know if you need any more information. > I can show you how to do this, but I need some info so I can get it right. > Do you have a table that contains the division codes? Yes..."tblDivisions" [quoted text clipped - 25 lines] > > guess it's just not my thing...so any instruction given to me should be very > > detailed and explanatory. I apprecaite any help I can get. Thanks. >The reason I want to do this is because that would be the key...there will be no two records with the same RPA number. A Primary Key can consist of one field, two fields, even ten fields. It is not necessary to combine them into one field just to create a primary key. John W. Vinson [MVP] It isn't necessary to combine them to create a key. A key can contain multiple fields. I will use two fields. The coding is easier and faster. You can display the two together. I will show you how this is done. Make your RPA_NO a numeric Long data type. First, lets make a combo box to select the division. Its row source will be a query based on the divisions table that will return a list of the divisions. The number will be created in the After Update event of the combo box for new records only. Private Sub cboDivision_AfterUpdate() Dim strRPA As String If Me.NewRecord Then Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", _ & "[DIVISION_CODE] = '" & Me.cboDivision & "'",0)) + 1 Me.txtWholeThing = Me.cboDivision & Format(Me.txtRpaNo, "-000") End If End Sub Now, in the example above the control txtWholeThing will show the combination of the two values. SignatureDave Hargis, Microsoft Access MVP > I answered your questions in your post. Thank you so much and please let me > know if you need any more information. [quoted text clipped - 28 lines] > > > guess it's just not my thing...so any instruction given to me should be very > > > detailed and explanatory. I apprecaite any help I can get. Thanks. I have a couple questions... Why do I need to make a query based on the divisions table for the combo box...can't I just used the table for the combo box? Am I creating two combo boxes: one for the division and one for the "new records only?" I may have more once you've answered these. I can't thank you enough for your help! What > It isn't necessary to combine them to create a key. A key can contain > multiple fields. [quoted text clipped - 55 lines] > > > > guess it's just not my thing...so any instruction given to me should be very > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. SignatureDave Hargis, Microsoft Access MVP > I have a couple questions... > > Why do I need to make a query based on the divisions table for the combo > box...can't I just used the table for the combo box? If you want only the Division field, you should use a query that returns only that field; otherwise, you will need to make your combo a multi column combo. > Am I creating two combo boxes: one for the division and one for the "new > records only?" No, only one, but in this case, you will only want to enter a division number for new records, right? If the combo is a bound control, then changing the value for existing records will change the division for the record. If you want to use the combo as a search, then it should be unbound and you would remove the NewRecord condition from the code. > I may have more once you've answered these. I can't thank you enough for > your help! [quoted text clipped - 60 lines] > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. I copied your code and pasted it into the After Update for the combo box, but I know I'm doing something wrong. I'm not really savvy with code, so forgive me for seemingly ridiculous questions. The "tblHRForms" should actually be referring to a table that I have, correct? I don't have a table by this name...I have one called "tblRPAData" which is the main table storing the data entered on the form. It has the following fields: RPA_No; EntryDate (autodate); ClassificationTitle; Unit_No; Class_No; Serial_No; PriorIncumbent; UnitName; Supervisor; PositionEffctvDate; ApptEffctvDate;Employee Name; ActionType; Comments. The RPA_No field is the one that I want to have the combined code entered into. Also, I'm getting "Invalid Character" errors on the "_" and "&" following "tblHRForms", > > I have a couple questions... > > [quoted text clipped - 78 lines] > > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. The names I used where you did not supply a real name, are made up. You have to use the real names in your database. So, I am assuming that you would replace "tblHRForms" with "tblRPAData". This code: Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", _ & "[DIVISION_CODE] = '" & Me.cboDivision & "'",0)) + 1 The _ is a line continuation character. It is a way of splitting one line of code onto multilple lines in the editor to make it easier to read. But I do see I made an error. It should have been: Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", & _ "[DIVISION_CODE] = '" & Me.cboDivision & "'",0)) + 1 The & is the concatenation character used to string different values together as one continuous string. If you can get it all on one line, just leave the _ out. When I say "all on one line", I mean on a line so you can see the entire line without scrolling in the editor. SignatureDave Hargis, Microsoft Access MVP > I copied your code and pasted it into the After Update for the combo box, but > I know I'm doing something wrong. I'm not really savvy with code, so forgive [quoted text clipped - 93 lines] > > > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. Now I'm getting a "Compile error: Expected: expression" on that "&". > The names I used where you did not supply a real name, are made up. You have > to use the real names in your database. So, I am assuming that you would [quoted text clipped - 113 lines] > > > > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. Post the code exactly as you have it written. SignatureDave Hargis, Microsoft Access MVP > Now I'm getting a "Compile error: Expected: expression" on that "&". > [quoted text clipped - 115 lines] > > > > > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", & "[DIVISION_CODE] = '" & Me.cboDivision & "'",0)) + 1 > Post the code exactly as you have it written. > [quoted text clipped - 117 lines] > > > > > > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. My fault. I think it should be this way. Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", & "[DIVISION_CODE] = '" & Me.cboDivision & "'"),0) + 1 SignatureDave Hargis, Microsoft Access MVP > Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", & "[DIVISION_CODE] = '" & > Me.cboDivision & "'",0)) + 1 [quoted text clipped - 120 lines] > > > > > > > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > > > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. I'm still getting the same error. > My fault. I think it should be this way. > Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", & "[DIVISION_CODE] = '" & [quoted text clipped - 124 lines] > > > > > > > > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > > > > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. The code appears correct. I wonder if in copying, some character that doesn't show is in the code. Try retyping in on a different line and deleting the original. SignatureDave Hargis, Microsoft Access MVP > I'm still getting the same error. > [quoted text clipped - 126 lines] > > > > > > > > > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > > > > > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. Okay, the error is gone. What is DIVISION_CODE supposed to stand for? > The code appears correct. I wonder if in copying, some character that > doesn't show is in the code. Try retyping in on a different line and [quoted text clipped - 130 lines] > > > > > > > > > > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > > > > > > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. That would be the name of the field that has the value you want to filter on. SignatureDave Hargis, Microsoft Access MVP > Okay, the error is gone. What is DIVISION_CODE supposed to stand for? > [quoted text clipped - 132 lines] > > > > > > > > > > > > > > > guess it's just not my thing...so any instruction given to me should be very > > > > > > > > > > > > > > > detailed and explanatory. I apprecaite any help I can get. Thanks. Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", "[DIVISION_CODE] = '" & Me.cboDivision & "'"),0) + 1 (You still had an ampersand in from of "[DIVISION_CODE]", an artifact of when you had the line continuation character there) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > My fault. I think it should be this way. > Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", & "[DIVISION_CODE] = '" & [quoted text clipped - 182 lines] >> > > > > > > > > > > detailed and explanatory. I apprecaite any help I can >> > > > > > > > > > > get. Thanks. what is the difference between the [DIVISION_CODE] and the cboDivision? my underestanding is the cboDivision is the combo box where i choose the division code, right? > Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", "[DIVISION_CODE] = '" & > Me.cboDivision & "'"),0) + 1 [quoted text clipped - 188 lines] > >> > > > > > > > > > > detailed and explanatory. I apprecaite any help I can > >> > > > > > > > > > > get. Thanks. DIVISION_CODE is the name of a field in table tblHRForms. cboDivision is the name of the combo box that contains the specific value of DIVISION_CODE you want to look up. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > what is the difference between the [DIVISION_CODE] and the cboDivision? my > underestanding is the cboDivision is the combo box where i choose the [quoted text clipped - 229 lines] >> >> > > > > > > > > > > can >> >> > > > > > > > > > > get. Thanks. Okay, here is where my lack of knowledge comes in. Where am I supposed to put this code? I've never done anything like this in Access before. Sorry! (Sorry to come back to something so old, but I gave up on it because I didn't know what to do and now I really need to get it done.) > DIVISION_CODE is the name of a field in table tblHRForms. cboDivision is the > name of the combo box that contains the specific value of DIVISION_CODE you [quoted text clipped - 232 lines] > >> >> > > > > > > > > > > detailed and explanatory. I apprecaite any help I > >> >> > > > > > > > > > > can |
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
|
©2009 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.