Code to reassign invoice number

S

SF

Hi,

I want to reassign the invoice number and I have the following which is not
working as far:

Public Function ReID1()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim I As Long
Dim SQL As String

If IsLoaded("frmTransactionHistory") Then
SQL = "SELECT tblTransaction.InvoiceNo FROM tblTransaction"
SQL = SQL & " WHERE (((tblTransaction.TransactionDate)=#" &
[Forms]![frmTransactionHistory]![TransactionDate]
SQL = SQL & "#) AND ((tblTransaction.TransactionTypeID)=20) AND
((tblTransaction.[Select])=-1));"

I = 1
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQL, dbOpenDynaset)

Do While Not rst.EOF
Debug.Print rst![TransactionID]
rst.Edit
rst![IncoiceNo] = I
I = I + 1
rst.Update
rst.MoveNext
Loop
rst.Close
End If
Debug.Print "Done..."
End Function

Could someone advice?

SF
 
A

Alex Dybenko

Hi,
not clear what is not working exactly. if you construct SQL correctly -
other stuff looks ok
 
S

Steve Schapel

SF,

What's happening, or not happening?

The only thing I can spot wrong is Debug.Print rst![TransactionID] won't
work because rst![TransactionID] doesn't exist.
 
G

Guest

Hi,
Your SQL Select statement doesn't have the TransactionID column as its
resultset.
When the program execution reaches :- Debug.Print rst![TransactionID]
It just exit the whole loops.
Can this be the problem?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top