Re: Autonumber using alphanumerics

Tech-Archive recommends: Speed Up your PC by fixing your registry

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
>
>
.

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: how to surpress a server error msg?
    ... As this is a client issue, I suggest you post the question to an Access group. ... > i'd prefer a solution to control the display. ... > i was calling the sp thru SQL pass thru in Access and return the query ... >>> following msg in query analyzer messages tab. ...
    (microsoft.public.sqlserver.server)
  • Re: in a table using lookup how do i get results based on a value
    ... Thanks John but I think I need to explain it more. ... Most of us HATE the Lookup ... >>I have table A where I have a field called Client. ... > table in your query. ...
    (microsoft.public.access.queries)
  • Re: sorting data in a report
    ... the query is based, this is for ease of entering the data. ... that I can't change the field in the query without losing the client name ... I also have tried removing the client ID part of the lookup in the ... row source on the report but then I again lose the data on the report. ...
    (microsoft.public.access.reports)
  • Re: Autonumber using alphanumerics
    ... "Katharine Jansen" wrote: ... >> If the fields are being combined successfully in the query, ... >> If you scroll through the client records ... So as I scroll though the client listing it will display ...
    (microsoft.public.access.tablesdbdesign)