By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 775 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

range of numbers in txtBox to MS Access Table

P: 4
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.
Sep 14 '09 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. Dim varRange As Variant
  2. Dim lngLow As Long
  3. Dim lngHigh As Long
  4. Dim lngCounter As Long
  5.  
  6. DoCmd.Hourglass True
  7.  
  8. If Not IsNull(Me![txtBox]) Then
  9.   'Place the Upper and Lower Range into an Array with a
  10.   'Space (" ") as a Delimiter
  11.   varRange = Split(Me![txtBox], " ")
  12.     'There must be exactly 2 Elements in the Array, they must both be Numbers, and the 1st must be  > the 2nd
  13.     If UBound(varRange) = 1 And IsNumeric(varRange(0)) And IsNumeric(varRange(1)) And _
  14.       (varRange(1) > varRange(0)) Then
  15.       lngLow = varRange(0)
  16.       lngHigh = varRange(1)
  17.       For lngCounter = lngLow To lngHigh
  18.         CurrentDb.Execute "INSERT INTO UnitTable ([UnitField]) VALUES (" & lngCounter & ");", dbFailOnError
  19.       Next
  20.     End If
  21. End If
  22.  
  23. 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

Share this Question
Share on Google+
7 Replies


P: 36
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
Sep 14 '09 #2

ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. Dim varRange As Variant
  2. Dim lngLow As Long
  3. Dim lngHigh As Long
  4. Dim lngCounter As Long
  5.  
  6. DoCmd.Hourglass True
  7.  
  8. If Not IsNull(Me![txtBox]) Then
  9.   'Place the Upper and Lower Range into an Array with a
  10.   'Space (" ") as a Delimiter
  11.   varRange = Split(Me![txtBox], " ")
  12.     'There must be exactly 2 Elements in the Array, they must both be Numbers, and the 1st must be  > the 2nd
  13.     If UBound(varRange) = 1 And IsNumeric(varRange(0)) And IsNumeric(varRange(1)) And _
  14.       (varRange(1) > varRange(0)) Then
  15.       lngLow = varRange(0)
  16.       lngHigh = varRange(1)
  17.       For lngCounter = lngLow To lngHigh
  18.         CurrentDb.Execute "INSERT INTO UnitTable ([UnitField]) VALUES (" & lngCounter & ");", dbFailOnError
  19.       Next
  20.     End If
  21. End If
  22.  
  23. 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
Sep 14 '09 #3

P: 4
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.
Sep 14 '09 #4

P: 4
I was typing and I got another response...you Gals and Guys are awesome.
I'm going to look and try this.
Thanks again
Sep 14 '09 #5

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblInUse (ChairNo)
  2. SELECT ChairNo
  3. FROM tblChair
  4. WHERE [ChairNo] Between Forms("YourForm").txtChairFrom
  5.                     And Forms("YourForm").txtChairTo
Sep 14 '09 #6

P: 4
Thank you ALL so much.
With some of the coding I got the problem solved
Sep 14 '09 #7

NeoPa
Expert Mod 15k+
P: 31,494
Very pleased to hear it. You're quite welcome.

Welcome to Bytes!
Sep 14 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.