Auto update table with range of records from a form

[Flopper]

Hi,

I'm having difficulty designing a form to update a table with range of records entered by a user.

For example the form simply asks the user the following:

First: F1234
Last: F1254

I need the Main table to check whether each of the 21 'F' numbers already exist within the table. If not then each of these numbers should be populated within the Main table as 21 new records.

How can this be achieved... as I really don't know where to start.

Thanks

 

[TheAceMan1]

How are ya Flopper . . .

Whats the Table & Field name? . . . and is the field a primary key?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Flopper]

Hi AceMan,

The table is called AppForm, and the fields are as follows:

AppFormID (PrimaryKey & Autonumber)
FormNo (VarChar 11, Unique, F**********)

Obviously there are other fields within this table, but at this point I would only need the above populated.

I understand that in normal circumstances it is usual to an existing unique identifier as the primary key, however i've also read that many developers prefer Access to create this number automatically.

Thanks

 

[KenReay]

Dim strSQL as String
Dim lFrom as Long
Dim lTo as Long
Dim i as Long

lFrom = Val(Mid(First,2))
lTo = Val(Mid(Last,2))
For i = lFrom To lTo
strSQL = "INSERT INTO AppForm (FormNo) VALUES('F" & i & "');"
docmd.setwarning false
docmd.runsql strsql
docmd.setwarnings true
next i

the fact you have a unique index on FormNo means that an attempt to insert a duplicate witll simply fail, and the use of setwarnings false will suppress the error message


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[TheAceMan1]

Roger That Flopper . . .

Just one more question. As far as [blue]VarChar 11[/blue] is concerned, do you require [blue]leading zero's[/blue] or not?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886