Entering a range of data

[marcfl]

Hi,

This should be a pretty simple question for you guys, but i dont know where to start.

Ive created a form which is supposed to enable me to enter storage information. What id like is to be able to enter a Box Number in one field and then under the Tape Number field (the contents of that box) enter a range of tapes that belong in it.

Could i do something like enter the first number and have a msgbox to pop up asking me how many tapes are in the box and get it to automatically enter the information as new records along with all the other details filled in on the form? Or is there an easier way?
for example "From" "To" text boxes?

Cheers,

Marc.

 

[KenReay]

Alarm bells ring here...

Is your table structure something like

BoxNumber Number
TapeNumber Text(50)

with one record per BoxNumber?

if yes, beware, you probably need

BoxNumber Number
TapeNumber Text

with one record per BoxNumber/TapeNumber combination

otherwise

Searches for a given tape number will be difficult

plus

how to you handle incomplete series of tape numbers (eg 1-3, 7-9)



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[marcfl]

well ye thats my table structure (apart from the box number is a text field too as it will have letters in it as well), Ive got the database to do everything it needs to including searches, reports etc.. i just need to reduce the time it takes to enter data in the form. There can be up to 50 tapes in a box so entering the same data 50 times lets face it would be pretty tedious not to say a complete waste of time.
I only have two tables in the db its a really simple creation and non-consecutive numbers arent really a problem even if theyre in the same box with the structure i have.

 

[KenReay]

Hi

do you have "with one record per BoxNumber/TapeNumber combination" ?

I was commenting of what I saw as a potential problem in your table structure, but maybe you do not have that problem

As you your original question

why not have (as you suggested) two text boxes to accept tape numbers, then have code something like
(you need to substitute your own variable and table names)

i = Val(txtFrom)
J = Val(Nz(txtTo,txtFrom))

for l = i to j
strSQL = "INSERT INTO YourTablename (box,Tape) VALUES(" & Box & "," & str(i) & ");"
docmd.runsql strSQL

next i



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[marcfl]

yeah im using that combination. I was asked to do this as a rush job, were having a proper database made by a separate company but we need something desperately in the mean time so something simple like this will suffice.

Sorry im really bad with vb heh... do you mind explaining the process a little bit more? :s

Thanks for the help i really appreciate it! I searched the forums, googled it and read through my books and couldnt find anything.. i really need to go on a vb course :/

 

[KenReay]

First determine the form anf two values, I am using VAL to convert the strings in the text boxes to numeric values, I am here assuming the are number of course and not values such as T1, T2 etc...

i = Val(txtFrom)
J = Val(Nz(txtTo,txtFrom))

the Nz() function returns txtTo, or if txtTo is Null it returns txtFrom

Now we loop from the value of txtFrom to the value of txtTo, increasing by 1 each time we go round the loop

for l = i to j
here we build an SQL string to intert a recow into our table
strSQL = "INSERT INTO YourTablename (box,Tape) VALUES(" & Box & "," & str(i) & ");"
and here we execute the sql
docmd.runsql strSQL

next i


Is that OK, or do you need more?

If you need more, I am currently hiding in my computer room at home to avoid baby sitting duties, so you could zip the db and EMail it to me ken dot reay at talktalk dot net


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[marcfl]

The tape codes unfortunately will have a letter or more behind them.. eg. B0000406

Im giving it a go now so well see if i can get it to work hehe if things go awry ill send it to you.

Cheers