Re: Autonumber using alphanumerics

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

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

.

Relevant Pages

  • Re: USING DLOOKUP TABLES
    ... > have the klms travelled returned from the lookup table. ... Perhaps the suggestion from James will be of some help, ... What I now guess you are doing is a query which should involve two tables. ... Then select the fields you want to display and run the query. ...
    (comp.databases.ms-access)
  • Re: Autonumber using alphanumerics
    ... So as I scroll though the client listing it will display the ... > consensus against using lookup data type fields in your table definitions. ... which I have been able to create and it works well in a query. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Returning a value based on another value
    ... corresponding name displayed in the datasheet view of the table. ... You need to include the lookup table in the form's query. ... and using the lookup to display the project name on the form. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Count a subset of records in a query
    ... Sorry my fault - I assumed you have created the lookup. ... have to but it's a great cheat for variables) ... The ID field is the primary field of the records in your query so change ... display its data which is extracted from company mainframe and updated ...
    (microsoft.public.access.forms)
  • Re: Help with syntax for passing a field value to a lookup query?
    ... but Access ads square braces around "Table" in the query. ... >> I am trying define a lookup field in a table where the values displayed ... >> box to display only Phoenix and Tempe... ...
    (microsoft.public.access.tablesdbdesign)