Updating tables programically

[JES0075]

Hello,

Im not sure if this can be done or not but i'm sure it can. What i'm trying to do is assign control numbers to my forms programically. What I would like to do is have a table that contains control numbers in my database and create a module and on button click assign control numbers to all forms and update my tables, but the the control numbers can only be used once..so basically I would like the module to check the control numbers table and see which control numbers have been assign and increment the control numbers as more data is appended. For example:

Say I appended 10 new records into a blank database and my control number table as of right now is empty. I would like to on button click start assigning control numbers to those 10 new records, but also at the same time update the control numbers table with those control numbers, and then when i append again say another 10 records and I hit that button again it will check that control numbers table and see that there have already been 10 control numbers assigned already and just increment that number based on the number of records that just had been appended. Can this be done? Thanks for your help

Does this make any sense?

James

[DKenny]

Why not use include an AutoNumber field, probably as the PK, in your table?

[JES0075]

Why not use include an AutoNumber field, probably as the PK, in your table?

Well the reason why I can't do that is because i'm going to be recieving data from about 15 different sources at numberous times which will be appended all into a database then well later be appended into another database for web based functionalty which will then use the control number for that form as the PK. I guess I should explain a little bit more.

The Job I do or lets say the people I work for track their data with certain control numbers which will basically have a field in the table called Control_Number. For example, there could be 10 different forms and all 10 of them will have different control numbers. i.e. (DQ1000, EQ1000, FM1000...etc) but being that the data comes in at different times and diffferent amounts the data is not brought in with control numbers, the control numbers are left blank to be filled in by a Data Management personnel..but I wanted to see if there was a way I can assign those control number i.e.(DQ1000, EQ1000, FM1000) programically so when its appened to the database for the Web application it will have that control number as the PK and use that to pull the records.

Hopefully that explains it a little bit more. Thanks

[DKenny]

OK, that makes more sense. What Database are you using? Access, SQLSever, Etc.

[JES0075]

OK, that makes more sense. What Database are you using? Access, SQLSever, Etc.

I am using Access

[DKenny]

You could do something like.

1/ Add a hidden TextBox to each Form and store the value of the Prefix in that TextBox (DQ, EQ, FM, etc.)

2/ Before inserts to the D/b find the Max used number for that prefix

SELECT MAX(Control_Num) FROM YourTable WHERE Control_Num LIKE txtPREFIX.txt & '%'

4/ Convert the numeric part to a number (lets call it COUNTER).

3/ Iterate through your recordset adding txtPREFIX.txt & COUNTER to the record. Then increment COUNTER before RS.MoveNext .

[JES0075]

Thanks DKenny, i will give this a try. Thanks for your assistance.

James