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.
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
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
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?
thanks in advance
regards
Iain 2 4112
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
"HSalim" <HS****@msn.com> wrote in message
news:3_*****************@nwrddc02.gnilink.net... 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
Thanks very much, I'll give all that a try!
rgds
Iain This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: intl04 |
last post by:
How do I create a data input form in Access that is external to the
Access database to which it's connected (if that's possible, which I
believe it is)? For example, if someone clicks on an Access...
|
by: intl04 |
last post by:
Is it possible to create a Word form as the data entry form for an
Access database? I didn't see any reference to this possibility in my
Access books, so I'm sorry if this is a question that is...
|
by: KC |
last post by:
Hello,
I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB.
I have tweaked this DB to work for our small co. It has worked pretty
well up until I made the mistake of deleting...
|
by: filbennett |
last post by:
Hi Everyone,
I'm generally unfamiliar with Access form design, but have programmed
Cold Fusion applications for a couple of years.
I'd like to build a data entry form in Access that allows the...
|
by: bosmatthews |
last post by:
I have a main form with a subform and a second subform nested to the first subform. The data entry property for all three forms (main, subform and sub-subform) is set to "yes" because I am intending...
|
by: seltzer |
last post by:
I am using Access 2000 but I also have the 2003 version.
I am working on creating a data entry form in Access for a research study. Since there is a maximum of 255 fields per table in Access, I...
|
by: maxpirate |
last post by:
I have data entry form in access and a save button which checks for validations and throws errors. I want to map this function to the save menu item available in the menubar. How do i do it?
|
by: Musky09 |
last post by:
On a data entry form I am creating the answers to questions 2a1-6 are related to the answer to question 2a...If 2a is "N", questions 2a1-6 are "N/A"...If 2a is "Y", questions 2a1-6 can be "Y", "N",...
|
by: rousseaud |
last post by:
Hello,
I could definitely use some help. I'm not even sure if this is possible, but if someone could point out a few options, I would appreciate it.
I would like to be able to email an Access...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |