need help with autonumber and number fields (1 Viewer)

Leshunter

Registered User.
Local time
Today, 21:31
Joined
Nov 21, 2017
Messages
13
Hi All
I am working on a database where I need to have two fields produce the same end result
field 1 CustomerID is autonumber
field 2 RecordID is number

I would like to have field 2 produce the same result as field 1

If this is possible could someone please point me in the right direction
PS I am very new to Access and still finding my way around so please be kind

many thanks
Les Hunter:banghead::banghead:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:31
Joined
May 7, 2009
Messages
19,169
Maybe possible on using data macro before update you assign custno to recordno
 

plog

Banishment Pending
Local time
Today, 16:31
Joined
May 11, 2011
Messages
11,611
Why?

You shouldn't store redundant data in a database. If you want the value of CustomerID, then use the value of CustomerID.
 

Leshunter

Registered User.
Local time
Today, 21:31
Joined
Nov 21, 2017
Messages
13
Hi both
Thanks for the quick responses
I need to use the value of RecordID in a search on a form which requires the RecordID and the same ID to automatically populate the CustomerID field when creating a new record saving having to input it manually

once again thanks for you responses
Les hunter
 

MarkK

bit cruncher
Local time
Today, 14:31
Joined
Mar 17, 2004
Messages
8,178
Hi All
I am working on a database where I need to have two fields produce the same end result
field 1 CustomerID is autonumber
field 2 RecordID is number

I would like to have field 2 produce the same result as field 1
The best way to do this it is to use a query. Only have the first field in the table, then write a query as follows...
Code:
SELECT CustomerID, CustomerID As RecordID
FROM  Table
But regardless, it is still a duplication that makes no sense to me to do.
hth
Mark
 

plog

Banishment Pending
Local time
Today, 16:31
Joined
May 11, 2011
Messages
11,611
Yeah, not really following you:

I need to use the value of RecordID in a search on a form...

That value is equivalent to CustomerID, so you would use its value.

...and the same ID to automatically populate the CustomerID field when creating a new record saving

That makes absolutely no sense. Since its an autonumber, you cannot ensure any specific value will populate in the CustomerID field.

You really need to step back, give us a good big picture explanation adn then what you are trying to accomplish in this particular instance.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:31
Joined
Sep 12, 2006
Messages
15,614
field 1 CustomerID is autonumber
field 2 RecordID is number

You may be missing something.

In your customer table, you need a unique identifier. So by all means have an autonumber ID field. You might then also have a customer account number (alpha numeric - like S001), and a customer name, which could be unique, but may not be. Company names will be unique, but Individual names won't be. You may have more than one Mr J Smith.

Having an account number as well as the autonumber may be a bit of overkill, but it's still useful.

What you then have is , say, an orders table.
In this table, you have an autonumber field for the record id for the order, and a number field for the customer ID. The value here corresponds to the customers autonumber, but it's not an autonumber in the orders table.

This way, your customer table is related to the orders table, so you can find all the orders for a given customer, or equally, the customer for a given order.

I hope that makes sense.
 

Leshunter

Registered User.
Local time
Today, 21:31
Joined
Nov 21, 2017
Messages
13
Thanks all.
I understand now a little bit more about redundancy and how to construct my tables so I'm going to start over again.
Thanks to Dave(aka Gemma the Husky) for your guidance,
thanks to Plog for your patience ,
and thanks to MarKK for your idea of a query ,
I will (I promise) to look at all the advice in my re-run
your as ever grateful
Les Hunter
 

Users who are viewing this thread

Top Bottom