Re: Autonumber using alphanumerics

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

If the fields are being combined successfully in the query, base the form on
the query and add to the form a text box bound to the query field. If
ClientID Number is unique to the client then it is all you need for a
primary key field; however, you can use a combined-field PK if necessary.
If you scroll through the client records (on the form, not the table) you
will see the combined fields from the query if you followed the suggestion
in the first sentence.
If you are working directly with records in a table, stop doing that. Forms
are how you interact with the data. If you have successfully combined
fields in a query it is possible to view the query in data*** view, where
you can see the combined number directly next to the Client name, but again,
this is not the best way to interact with data. You may be trying to treat
a table or query as a spread***. They may look similar, but they work
very, very differently.
Combining fields may be thought of as a sort of calculation. Calculations
are not stored except in rare and specific instances (which do not apply in
your case), but are instead figured "on the fly."

"Katharine Jansen" <KatharineJansen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:C241B7F7-D104-4217-890E-11A2FA88F5F5@xxxxxxxxxxxxxxxx
> Hi Jeff,
>
> Thanks for the prompt response. I've tried what you have recommended, and
> it's not quite how I want the form to function. What I want the form to do
> is
> to recall the data from the "ClientID Number" and "ClientID Name" fields
> and
> display them as one field on the form relative to the client data being
> recalled, as opposed to having a combo box which lists the various options
> available to me. So as I scroll though the client listing it will display
> the
> "ClientRef" relative to the client I am looking at at any particular time.
>
> I hope this is a little clearer for you this time. If you have any other
> suggestions I would be most grateful.
>
> Thanks in advance
> Katharine
>
> "Jeff Boyce" wrote:
>
>> I'm not sure I understand, but I suspect you are working directly in the
>> table, rather than via queries and forms. Access (JET) tables store
>> data,
>> forms (and reports) display data.
>>
>> A search through this newsgroup (tablesdbdesign) will reveal a strong
>> consensus against using lookup data type fields in your table
>> definitions.
>> The more commonly recommended approach is to create independent tables
>> holding your lookup values, and to use queries to return those values to
>> combo boxes in forms.
>>
>> Good luck
>>
>> Jeff Boyce
>> <Access MVP>
>>
>> "Katharine Jansen" <KatharineJansen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> message news:8DC01083-A840-44E0-AF73-EDE0D7509264@xxxxxxxxxxxxxxxx
>> > Hi there,
>> > I received some good advice relating to having an alphanumeric
>> > reference
>> > number, which I have been able to create and it works well in a query.
>> Thanks
>> > to those who took the time to give me the advice. However, the data
>> > that I
>> am
>> > able to obtain from the query is not displayed in the underlying table
>> > or
>> > form.
>> >
>> > The Table has 3 fields relative to the query; "ClientID Number", a 3
>> > digit
>> > autonumber, "ClientID Name", a 3 digit manually created alpha figure,
>> > and
>> > "ClientRef", which displays a combination of both these data to display
>> the
>> > alphanumeric figure that I need. The query draws on the information
>> > from
>> the
>> > "Corporate Client Details" Table, where all related fields are found.
>> >
>> > In the underlying table I tried to use the lookup wizard to recall the
>> data
>> > from the query for display in the table/form, but when doing so got the
>> > message "no valid fields can be found in 'Corporate Client Details
>> > Query'.
>> > You may have selected a query that uses the table your adding the
>> > lookup
>> > column to. Please select a new source".
>> >
>> > I guess the error message is self-explanatory, but I'm wondering how I
>> > can
>> > get around this problem. Off the top of my head it seemed appropriate
>> > to
>> then
>> > create another table which holds the alpha and the numeric fields,
>> > along
>> with
>> > the client name, then only keep the ClientRef, which is a combination
>> > of
>> the
>> > two fields in the "Corporate Client Details" Table. Upon doing so, I
>> > was
>> > still unable to recall the data from the query using the lookup wizard.
>> > Is
>> > there a way around this?
>> >
>> > The other problem that I have is that I want the alpha figures in the
>> > ClientRef to appear in uppercase. I have enabled this to happen in the
>> table,
>> > but it does not in the query when I combine the two fields together.
>> > Does
>> > this matter that it doesn't appear in the query, or is it that once I
>> manage
>> > to combine the "ClientID Number" and "ClientID Name" in a single field
>> > I
>> can
>> > then set it to view in uppercase?
>> >
>> > Thanks in advance
>> > Katharine
>>
>>


.

Quantcast