range of numbers in txtBox to MS Access Table

[DefaultWorkgroup]

Good Morning Ladies and Gentleman.

My question is in MS Access 2000 (I know is old, is all I have)

I’m basically a Newbie in this posting of new threads and in the MS Access as well.

I spend a lot of hours trying to get this answer out of the Internet and 6 different books of MS Access and VB with no results. I don’t even know if is possible.

Here is the Question:

I have a txtBox with a simple range of numbers, but not all the numbers, only the first and the last number.

Lets say the range of numbers is: 201, 202, 203, 204, 205
All I have in the txtBox is: 201 205

I need to have all the range of numbers: 201, 202, 203, 204, 205
in my UnitTable all under the UnitField like this:

UnitField
201
202
203
204
205

I suspect I have to write a special Function or Module for this, but I’m out of ideas so far.
Any Help in this matter is going to be greatly appreciated.
Any guidance, brainstorms, tips, etc…
Thank you Ladies and Gentleman in advance.

[IT Couple]

Hi

1) Do you enter the range in the text box manually?
2) Could you clarify what you would like to do with it exactly (unit table / unit field)
3) If you want to get all numbers in the range and you have 2 input values from / to then I would build a table with all numbers (0 to max possible) and use criteria from / to to get the range I need and then use it for my purpose.

I will try to help more if I get more info from you.

Hope it somehow helps you
Regards
Emil

[ADezii]

Here is the logic and code that will do the trick for you. Is assumes that you have a Table named UnitTable and it contains a [UnitField] Field of Data Type LONG INTEGER. I did not want to overkill with comments, so they are few but strategically placed. Should you have any questions, either myself or one of the gang will be happy to answer them for you.

Dim varRange As Variant
Dim lngLow As Long
Dim lngHigh As Long
Dim lngCounter As Long

DoCmd.Hourglass True

If Not IsNull(Me![txtBox]) Then
  'Place the Upper and Lower Range into an Array with a
  'Space (" ") as a Delimiter
  varRange = Split(Me![txtBox], " ")
    'There must be exactly 2 Elements in the Array, they must both be Numbers, and the 1st must be  > the 2nd
    If UBound(varRange) = 1 And IsNumeric(varRange(0)) And IsNumeric(varRange(1)) And _
      (varRange(1) > varRange(0)) Then
      lngLow = varRange(0)
      lngHigh = varRange(1)
      For lngCounter = lngLow To lngHigh
        CurrentDb.Execute "INSERT INTO UnitTable ([UnitField]) VALUES (" & lngCounter & ");", dbFailOnError
      Next
    End If
End If

DoCmd.Hourglass False

P.S. - If you had 3500 4129 written in txtBox, then the following values will be written to UnitTable:
3500, 3501, 3502, 3503, ...4129

[DefaultWorkgroup]

Thanks for the fast response; you Gals and Guys are awesome.

These are the answers to the last reply:

Q: 1) Do you enter the range in the text box manually?
A: Yes, I enter the texBox manually.

I daily-rent Beach Chairs and they are numbered.
I rent the Chairs in order and some groups get more than 1
Sometimes I rent more than 20 hairs at the time.
With the rush in the morning I want to type the less I that I can.

That’s why the content of the txtBox is: 201 205
Instead of the whole range of numbers: 201, 202, 203, 204, 205

Q: 2) Could you clarify what you would like to do with it exactly (unit table / unit field)

A: When I DoubleClick in the txtBox I want to have the whole data stored in a table not only what I type in the txtBox.

The (unit table / unit field) should be:

UnitField
201
202
203
204
205


Q: 3) If you want to get all numbers in the range and you have 2 input values from / to then I would build a table with all numbers (0 to max possible) and use criteria from / to to get the range I need and then use it for my purpose.

A: I can not use the criteria from / to because we are always adding new chairs and the idea is that I rent and at the same time add chairs in to the (unit table / unit field)

Thanks again.

[DefaultWorkgroup]

I was typing and I got another response...you Gals and Guys are awesome.
I'm going to look and try this.
Thanks again

[NeoPa]

Adding a table with the possible chair numbers in it is not such a bad idea. I'd be surprised if you didn't find it necessary later on anyway.

With a table ([tblChair] for example) you can execute a fairly simple action query to add the numbers into a table for you. Assuming a table name of [tblInUse] to put the data into you can handle the event (either a double-click, or you could even add a command button to be more user-friendly) by executing the following SQL :

INSERT INTO tblInUse (ChairNo)
SELECT ChairNo
FROM tblChair
WHERE [ChairNo] Between Forms("YourForm").txtChairFrom
                    And Forms("YourForm").txtChairTo

[DefaultWorkgroup]

Thank you ALL so much.
With some of the coding I got the problem solved

[NeoPa]

Very pleased to hear it. You're quite welcome.

Welcome to Bytes!