Iain,
It is good to see someone who has got it right the first time around. far
too many people want to create custom, sequential numbering formats that can
be troublesome.
see comments inline
HS
"Iain Miller" <do***@spam.me> wrote in message
news:g9******************@newsfep4-glfd.server.ntli.net...
Struggling a bit here & would be grateful for any help.
I have a table which has a list of people in it. Each person has a unique
ID automatically allocated by Access but also belongs to one of 5 Groups -
call them A to E. I'd like to generate a further automatic reference number
based on something like Group/Unique ID so when I create a new record Access
creates the Unique ID & I then enter in the group and then Access combines
the two into a third field automatically to create another unique
reference which would therefore be something like A/1, C/2, D3, A4 etc etc
depending on what I've entered and the unique ID Access has allocated.
Although you could do this in a form, as you will in the case below, there
is no need to
do so in this case. Typically, you would create a query that combines the
two columns
into one. Example:
Select GroupName & "/" & UniqueId as PersonID from YourTable
Secondly, I have another table with that contains customer details. I want
to create a unique reference for each of these that uses say the first six
letters of the customer name combined with the Unique ID field so again,
on creating a new record Access would allocate a Unique ID, I'd enter in the
customer name and a third field would be automatically created in the form
ABCDEF/6. In doing this how could I take out spaces from the customer name
and add leading zeros to the unique ID? So if I'd entered in A. B. C.
Engineering as a customer name (and it happened to be the 7th record I'd
created) I'd want the new reference to be ABCENG/0007 and not A. B. C./7
Create a bound form pointing to your customers table
Each field on your form will also be bound to each of the columns, so
Let us say
txtUniqueId - bound to UniqueID
txtCustomerName - bound to Customer Name
txtCustomerID bound to CustomerID - you can choose to lock and disable this
field do that it is visible but not alterable
now, in the afterupdate event of txtCustomerName add this code
me.txtCustomerID = left( Replace(me.txtCustomerName, " ", ""), 6) & "/" &
format(me.txtUniqueID, "000000")
Also each person in my first table has a Parent/child relationship with
another person in the table. So when I enter a new person I need to select
data (from the same table) to be entered into the new record to identify
who the parent is
in your form which is bound to first table, add a combobox. Follow the
wizard, point to FirstTable
and select EmployeeId and EmployeeName. Make Column1 - (ID) the bound
column - which is
bound to the ParentID column in your table
In addition, if you have a RankID in your table, you restrict the employees
tht appear in the combobox
by rank
In your combobox, you can hide the ID column , and display Employee(Parent)
name eventhough it is the
parent id that is bound.
What I want is a data entry form that has something like a combo box on it
that I can select the parent from by name (or perhaps by my combined
unique ID above). Having selected who the parent is their UniqueID get's entered
into the new record in a field called Parent ID in the new record so how
can I fish a piece of data out of another record in the same table and place
it in a different field in a new record? I may want to do this with a couple
of other bits of data from the parent record as well.
I was also toying with the idea of creating a data entry form that allowed
you to create a new record but did not actually enter it into the table
until you pressed a "submit" button but I understand this needs an Unbound
form and a bunch of code. Is there an easy way to do this to minimise data
entry errors?
Creating unbound forms is laborious. no easy ways around it. For now
though, stick with bound forms.
thanks in advance
regards
Iain
