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] = '" &
Me.cboDivision & "'"),0) + 1
--
Dave Hargis, Microsoft Access MVP
:
Me.txtRpaNo = Nz(DMax("[RPA_NO]", "tblHRForms", & "[DIVISION_CODE] = '" &
Me.cboDivision & "'",0)) + 1
:
Post the code exactly as you have it written.
--
Dave Hargis, Microsoft Access MVP
:
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
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.
--
Dave 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
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",
:
--
Dave 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!
What
:
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.
--
Dave 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.
:
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"
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#? "tblRPALog"
BTW, Change the name. # should not be used in a name. I did. It's now "RPA_No"
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)---Thank you!
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.--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.
Anyway, post back with the detail, and I can write the code for you.
--
Dave 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
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.
