Re: Autonumber using alphanumerics

Hi Bruce

I only have 2 records in my table, no others. My calculation combining the
two fields looks to be correct; it appears like this:

Client Ref: [ClientID Number] & [ClientID Name]

and yes, the query is based on the table, and I clicked on a blank field and
entered the calculation you suggested and the problem remains when I switch
to data*** view. I still have a duplicate copy of each record with the
wrong Client Ref.

The ClientID Number is an autonumber and listed as such in design view, its
field size is long integer, new values set to increment, and indexed as Yes
(No Duplicates).

The 2 tables that I have used to create the query are Corporate Client
Details and Corporate Reference, the latter holding the ClientID Number and
ClientID Name fields that the query is based on. Presently I have these 2
tables, although the database will draw on information from 2 other tables,
Active Payments and Closed Payments. I have not included these tables as they
are yet to be completed and no relationships have been made to the first 2
tables at this stage.

tblCorporate Reference
ClientID Number (Primary key, and Automunber)
ClientID Name
Corporate Name
Division

tblCorporate Client Details
Client Ref (the combined ClientID Number and ClientID Name does not
appear
here in data*** view, hence the reason the form is
based on the
query and no relationship established)
Organisation Name
Branch
Title1
Surname1
First Name1
Telephone1
Fax1
Mobile1
Email1
Title2
Surname2
First Name2
Telephone2
Fax2
Mobile2
Email2
Address
Town
County
Post Code
Web Address
Notes

Below is the SQL for the query.

SELECT [ClientID Number] & [ClientID Name] AS [Client Ref], [Corporate
Client Details].[Organisation Name], [Corporate Client Details].Branch,
[Corporate Client Details].Title1, [Corporate Client Details].Surname1,
[Corporate Client Details].[First Name1], [Corporate Client
Details].Telephone1, [Corporate Client Details].Fax1, [Corporate Client
Details].Mobile1, [Corporate Client Details].Email1, [Corporate Client
Details].Title2, [Corporate Client Details].Surname2, [Corporate Client
Details].[First Name2], [Corporate Client Details].Telephone2, [Corporate
Client Details].Fax2, [Corporate Client Details].Mobile2, [Corporate Client
Details].Email2, [Corporate Client Details].Address, [Corporate Client
Details].Town, [Corporate Client Details].County, [Corporate Client
Details].[Post Code], [Corporate Client Details].[Web Address], [Corporate
Client Details].Notes
FROM [Corporate Client Details], [Corporate Reference];

Maybe you can see what I have failed to and advise me as to what the
underlying problem is.

Thanks in advance
Katharine

"BruceM" wrote:

>
>
> Are there additional records in your table, or just the original two? I'm
> not exactly sure what is going on, but I suspect there is a problem with
> your calculation that combines the two fields. Is your query based on the
> table? If so, in query design view click in the top of an empty column and
> type:
> CombinedID: [ClientID Number] & [ClientID Name]
>
> Be sure to use the correct field names, including spaces, underscores, etc.
> Switch to data*** view. If it looks OK, use the query as the form's
> record source. Click View > Field List, and drag CombinedID onto the form.
> This should be all that's needed.
>
> By the way, you referred to ClientID Number as a three-digit autonumber.
> Are you sure about that? Is it listed as autonumber in table design view?
> If it is a number you have created, is it listed at Indexed (bottom left in
> table design view) as Yes (no duplicates)? If not, it needs to be.
>
> Without knowing more about your database it is impossible to say. You will
> need to post your table structure and relationships, and the SQL from your
> query.
>
> To post the table structure, so something like this:
>
> tblClient
> ClientID_Number (primary key)
> ClientID_Name
> Phone, etc.
>
> Do the same for other tables that are involved in the problem. To get the
> SQL for the query (the script that is going on behind the scenes), click
> View > SQL View. Copy what you see and paste it into your reply.
>
>

.