Auto generated values

[Guest]

Hello

I’d like to work out on this.
I want Access to automatically fills a textbox with a value from a list that
matches the characters as I type them in the textbox.
This is similar to what I can get by the Auto Expand property of a combo
box, but what I want different is:
1.The list of values is not coming from the field values of a whole table
but from what is included in an open recordset.
2.I don’t want to take the first value but the last one i.e. if I have the
values MN101, MN102, MN103, when I type MN to get MN103.
3.If I click the ‘+’ button after the value is displayed I want to increment
by 1 i.e if I have MN103 clicking ‘+’ I take MN104.

I should appreciate if somebody could help on that.

Thank you

GL

 

[Arvin Meyer [MVP]]

You do not need a table, but you do need to use a query to get the records
you want. A plain select statement ought to suffice. Have a look at my code
in the following demo database:

http://www.datastrat.com/Download/ExpandingSearch2K.zip
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Steve Schapel]

GL,

I would certainly be tempted to use the built-in Auto Expand feature of
a combobox, as you mentioned. It is not clear what you mean by "the
list of values is coming from what is included in an open recordset",
but whatever it is, I would expect it to be easy to set the Row Source
property of the combobox to operate as required, as well as having the
values in the list sorted in descending order so that the highest value
is the one selected. It may even be possible to disguise the fact that
it is a combobox by covering the drop-down arrow button, though I am not
sure about this.

As for the increment of the number, the specifics of how to do this
would depend a bit on the specifications of the values in the list. Are
they always of the structure 2 letters followed by the number?

 

[Guest]

The idea is to generate values for parts stored on certain location. So a
code is composed from a two letters describing the kind of part i.e. IC for
Integrated circuit, followed by a 5 digit number. Two first digit are the
location code and the last three the part number. When I say for an open
recordset I mean that I work editing by opening a recorset that contain only
the parts of a certain location.

GL

 

[Steve Schapel]

GL,

Ok. So is it always the last 3 digits that you want to increment? If
so, what happens when you get to 999 parts in a particular location for
a particular part type?

The SQL view of the query that you could use for the Row Source of the
combobox would look something like this...
SELECT [YourCode]
FROM [YourTable]
WHERE [Location]=[Forms]![YourForm]![Location]
ORDER BY [YourCode] DESC

 

[Arvin Meyer [MVP]]

Is the 3 digit part number incremented universally? By that I mean is the 3
digit code repeatable between the other 2:

IC50103
IC60103
MN50103

or is it like this:

IC50103
IC60104
MN50105

In either case the number is violating relational rules because it can be
decomposed. As such, it would be better to break the value up into 3
different fields:

IC 50 103

They can still be displayed together, or they can be saved together if your
business rules require breaking the relational ones. Using 3 tables (or 4
depending upon the method of incrementing the 3 digits) allows you to use
separate combo boxes for the first 2, and a bit of code to get the
increment.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

The part number is like the first case, I mean The IC50103 is the 103rd part
at location 50 and is a different entity to the IC60103, the 103rd IC stored
at location 60. I don´t understand what is wrong to this coding.

GL

 

[Guest]

OK but how I can increase +1 when pressing the '+' button?

GL

GL,

Ok. So is it always the last 3 digits that you want to increment? If
so, what happens when you get to 999 parts in a particular location for
a particular part type?

The SQL view of the query that you could use for the Row Source of the
combobox would look something like this...
SELECT [YourCode]
FROM [YourTable]
WHERE [Location]=[Forms]![YourForm]![Location]
ORDER BY [YourCode] DESC

--
Steve Schapel, Microsoft Access MVP

The idea is to generate values for parts stored on certain location. So a
code is composed from a two letters describing the kind of part i.e. IC for
Integrated circuit, followed by a 5 digit number. Two first digit are the
location code and the last three the part number. When I say for an open
recordset I mean that I work editing by opening a recorset that contain only
the parts of a certain location.

 

[Steve Schapel]

GL,

So, the code on the Click event of your '+' button could be like this...
Me.YourCodeField = Left([YourCodeField], Len([YourCodeField]) - 3) &
Format(Val(Right([YourCodeField], 3)),"000")

You could use similar code on the form's Before Insert event if you
wanted this process to happen more automatically.

To repeat my earlier question, what happens when you get to 999 parts in
a particular location for a particular part type?

I agree with Arvin that it is not a good idea to have these 3 pieces of
information all in the one field.

 

[Steve Schapel]

GL,

Forgot to increment!
Left([YourCodeField], Len([YourCodeField]) - 3) &
Format(Val(Right([YourCodeField], 3)) + 1, "000")