Find unused values?????

[Tim]

I have 2 fields that contain numeric values. I want to use VB code to find
missing values in field Number based on the data in field Location.

For example

Location Number
20 1
20 3
20 4
20 5
20 7
30 1
30 2
30 5

I want to populate a text box with an available value from Number based on
the value in Location. In the example above there is no number 2 for location
20 or no number 3 or 4 for Location 30. I want to have a command button
labeled "Next Available Number." If location 20 is choosen from a combo box
the code will look for an unassigned number for this location. In this case 2
and 6 are available and assign the next available number. If location 30 is
choosen then 3 or 4 are available.
Can you please help??

ThanX

Tim

 

[Tom Ellison]

Dear Tim:

There are some options here. It is a bit easier to define each "range" of
missing numbers, but that's not what you said. You don't want to see that
2-7 are available.

In order to produce the list for your list box, you'd need a table of all
the numbers that could be used. From these, we'll eliminate the values that
are used. Do you want to see all the values above the largest one already
in use? For you example Location 20, do you want 8, 9, 10, . . .?

Basically, you could have this table:

tblNumbers
Number
1
2
3
4
5
6
7
8
9
10

Add more rows as desired.

For your combo box row source:

SELECT Number
FROM tblNumbers
WHERE Number NOT IN
(SELECT Number
FROM YourTable
WHERE Location = 20)

Now, this isn't VBA code, its a query. It would be fairly easy to build,
and would perform well. You may need some VBA to pick up the Location value
(20 above) and put it into the SQL, or you could do it with a reference from
the query to the Forms!FormName!ControlName where you have that value
already.

Tom Ellison

 

[Tim]

Tom, thanks for your response. I followed your instructions and added a table
called 'Available Numbers' They are 1-254. I tried using the query you
suggested but kept getting an error 'Compile Error, Expected Case.' The
process goes something like this. A user creates a new record. Selects a
Location from a combo box and clicks on a command button that says "Next
Available Number" Based on the value in the Location combo box the query will
parse the Availble Number table looking for an unused value. That value will
appear in a text box on the form. Maybe this helps....??

Thanks again!!!

Tim

 

[John Vinson]

I tried using the query you
suggested but kept getting an error 'Compile Error, Expected Case.'

Sounds like you put Tom's code in the VBA editor. It's not VBA; it's a
Query, and needs to be entered in the SQL window of a new Query.

John W. Vinson[MVP]

 

[Tom Ellison]

Dear Tim:

Did you put this in "your combo box row source" as I specified?

Tom Ellison

 

[Tim]

I feel pretty stupid - I had the query in the VBA code. The combo box row
source is already a list of locations. Here's what should happen; A user
creates a new record. Selects a Location from a combo box and clicks on a
command button that says "Next Available Number" Based on the value in the
Location combo box the query will parse the Availble Number table looking for
an unused value. That value will appear in a text box on the form. Can this
be done without sacrificing a goat?


Thanks!!!!


Tim

 

[Tom Ellison]

Dear Tim:

Write a function that runs the query and returns the count. Set this
function to be the calculated value of the text box. Refresh it as needed.

Tom Ellison

 

[Tim]

Thanks Tom, I'm getting close (I think.) I don't get any errors from the
query but it returns no values. I am running it from the command button.
Also, how do I reference the value of the combo box in the query amd how do I
display the returned value in my text box. Sorry, to be a pain but this is
pushing the limits of my knowlege.


Tim

 

[Tom Ellison]

Dear Tim:

Don't leap to the final product in one giant bound.

Use the query tools to build and test the query and see what it returns.

You can reference the value of a combo box with the syntax [Forms]![Your
Form Name]![Combo Box Name], replacing the name of the form and combo box.
The form must be open at the moment it is referenced.

If you write this as a function and use that function as the calculated
value of the text box, then the value will be displayed.

Tom Ellison