combine two fields in form to create one field in table

[Guest]

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
want to look up.

--
Doug Steele, Microsoft Access MVP

(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
division code, right?

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)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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