Table-query-form-autonumber question

[nadir66]

Table, Query, Form question
I have a table with ID numbers that are written out already. Numeric sequence.
I need a procedure that will take the next available number and display it on my form, as well as write it in a different table. The number has to be deleted from the first table. Also if possible I need to take numbers as per need from the table (so, not the next in line) but not at all time. (Just to allocate “easy” numbers like 300, 400, 450 etc).

Table 1 Field DocketID
Form Docket, field DocketID
Table 2 field DocketID

 

[LittleSmudge]

If you are using A2k or beyond

On the Form have combo box to allow user to 'pick' the next number to be used
Also have a button to allow user to 'take the next available number'

Button's OnClick event calls MakeNewRecord(0) proc
Combo's AfterUpdate called MakeNewRecord(ComboBoxValue) proc

MakeNewRecord(NextId As Long)

Open Connection
Connection.BeginTrans

If NextId = 0 Then 
    Open Recordset rst1 Top 1 On Table1 ORDER BY Id Asc  
Else
    Open Recordset rst1 On Table1 WHERE Id = NextId
EndIf

Open Recordset rst2 On Table2 ( Target Table )
rst2!Id = rst1!Id
rst2.Update
rst1.Delete

Connection.CommitTrans

rst1.Close
rst2.Close
etc. 

Add Error Handling with Connection.RollBackTrans etc.

'ope-that-'elps



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[nadir66]

unfortunately, i am using A97

 

[LittleSmudge]

Okay - you can do all of the above without the Connection.Transaction brackets.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[nadir66]

hi
i am sorry, but it is a bit too advanced for my knowledge to just like tahat write code. i could use some more help. Very thankfull to all help.

 

[LittleSmudge]

Okay - here goes an on the fly write of A97 code for the first time in .. .. .. ?

Private Sub MakeNewRecord(NextId As Long)

Dim db As Database
Dim rst1 As RecordSet
Dim rst2 As RecordSet
Set db = CurrentDb

If NextId = 0 Then
    Set rst = db.OpenRecordSet("SELECT Top 1 * FROM Table1 ORDER BY Id Asc ")
Else
    Set rst = db.OpenRecordSet("SELECT * FROM Table1 WHERE Id = " & NextId )
EndIf

Set rst2 = db.OpenRecordset("SELECT * FROM Table2 WHERE False ")
rst2.AddNew
rst2!Id = rst1!Id
rst2.Update
rst1.Delete

rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing 

End Sub

'ope-that-'elps




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.