473,320 Members | 1,832 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

range of numbers in txtBox to MS Access Table

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

7 5194
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
8,834 Expert 8TB
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
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
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
32,556 Expert Mod 16PB
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
Thank you ALL so much.
With some of the coding I got the problem solved
Sep 14 '09 #7
NeoPa
32,556 Expert Mod 16PB
Very pleased to hear it. You're quite welcome.

Welcome to Bytes!
Sep 14 '09 #8

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

Similar topics

2
by: Jason | last post by:
What I am trying to do is get all of the records in a table that are out of sequence so I know which account numbers I can reuse. I have a range of account numbers from 50100 to 70100. I need to...
5
by: ratu | last post by:
I'd like to use a stored procedure to insert large amounts of records into a table. My field A should be filled with a given range of numbers. I do the following ... but I'm sure there is a better...
24
by: PromisedOyster | last post by:
Is there a way that I can get a resultset that contains unique dates in a given date range without the need to have a temporary table and a cursor? perhaps something like: declare @start_date...
3
by: Magno101 | last post by:
Hi all, First let me start out with what I want to happen with this and then maybe y'all can give me some better insight. I have a database in Microsoft Access which we need to upsize to SQL. We...
3
by: tai.cabrera | last post by:
What I would like to do is input a range of serial numbers in a form and have that range populate in the table without me having to put them in one at a time manually. The numbers do not exist yet,...
4
by: anagai | last post by:
hi I have a javascript function that will add a column of numbers in html table. Is there a way to assign a ID to a group of table rows that so i can easily access the rows through...
3
by: rugger81 | last post by:
I am currently working in the sql server 2000 environment and I want to write a function to pull all dates within a given date range. I have created several diferent ways to do this but I am...
7
by: dunkleypilot | last post by:
Hello, I have created a database to track the flights on three aircraft owned by the flight school I manage. The relevant fields in the FlightRecords table are Date, AircraftID, HourMeterOut, and...
4
by: Michael R | last post by:
Hi. I'm currently dealing with the following: I need a drop box that will have in it a certain range of accending consuquential numbers for ex: 1, 2, 3, 4, 5, 6, 7. The min and the max number...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.