Assigning a number to a field using vb

[VALERIA]

I am trying to assign a value to a field using vb. I have already used an auto number for another field, so i definitely have to write the code for this one. I need to assign a number to a field in increments of 1 and the starting number is on a query. Any ideas? The code i have so far is below.

Dim JRNNEXT As Variant

'Loop through unposted journal entries
Do Until rstJournalEntriesToPost.EOF

' Find the next Journal Entry Number
JRNNEXT = DLookup("JRNNEXT", "qry_JENextNumber")
JRNNEXT = JRNNEXT + 1

rstJournalEntriesToPost.Edit
rstJournalEntriesToPost!JRNENTRY = JRNNEXT
rstJournalEntriesToPost.Update

JRNNEXT = JRNNEXT + 1

rstJournalEntriesToPost.MoveNext
Loop

 

[WayneRyan]

V,

You'll want to use the DMax function. Use the Search Facility here, it's a
pretty common topic.

Wayne

 

[Lister]

Update if null

I hope this is along the lines of what you are looking for.
Create a new module and paste this code in.
You can run this function form the imediate pain with...

? newnum("YOUR QUERY NAME", "THE FEILD YOU WANT TO UPDATE")

Function newnum(QryName As String, fldName As String) As Integer
'Lister, 5th May 2006
'Pass the QueryName that you want to update to QryName variable.
'Pass the fieldName that needs the update to fldName variable.
'If the fild name has a null value it will add the next number in the sequence.
'ie: if the max number is 2 the next null value field will be updated to 3
'If all fields are null it will start a 1 and move on.
'Dont for get to make sure your Tools > References has the Microsoft DAO 3.6 Object Library ticked.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim x As Variant
    Dim y As Integer
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(QryName, dbOpenDynaset)
    
    rs.MoveLast
    rs.MoveFirst
    y = rs.RecordCount
    Do Until y = 0
        x = rs.Fields(fldName).Value
        
            If IsNull(x) Then
                x = Nz(DMax(fldName, QryName), 0) + 1
                rs.Edit
                rs.Fields(fldName).Value = x
                rs.Update
            End If
        Debug.Print x
        rs.MoveNext
        y = y - 1
    Loop
    
    rs.Close
    Set rs = Nothing
End Function

Hope this helps or gets you going
Let us know how you get on.

 

[Lister]

Hmmm, forgot to put an error trap in if rs.recordcount = 0. Just be aware of this and add it if you use the code.

 

[VALERIA]

Thanks

Thanks for the help, the code actually helped, i appreciate it.:

 

[Smart]

In your query add a new column and in the field section type


Sequence: (Select Count(1) FROM Yourtable A
WHERE A.yourfield <=yourtable.yourfield)