Re: Autonumber using alphanumerics

Tech-Archive recommends: Fix windows errors by optimizing your registry

I wonder if you can help with another problem thats reared it head. in order
to test how the database is working I only have 2 records entered. The on the
form and query it has created a duplicate of each record with the wrong
ClientRef for each record, so for example 301PET is peterInc, 302JON is
jonInc, which is there, but it shows another record as 301PIN as jonInc, and
302JON as peterInc. Any idea what has happened here? In the intial table that
I based my form on originally this didn't happen.

Thanks in advance
Katharine

"Katharine Jansen" wrote:

> Hi Bruce
>
> Thank you so much for your response. I have done as suggested and it works
> perfectly.
>
> Ever grateful
> Katharine
>
> "BruceM" wrote:
>
> > 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