473,326 Members | 2,655 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Access 97 Data entry form questions

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
Nov 12 '05 #1
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

Nov 12 '05 #2

"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
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
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...
3
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...
1
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...
2
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...
3
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...
2
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...
20
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?
0
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",...
2
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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...
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.