Replace AutoNumber

[Bob Hughes]

I currently update a Receipt table with the following Query, where
TblReceipts has an AutoNumber field ReceiptNumber

INSERT INTO TblReceipts ( mem_ID, EntDate, FullName, Address, Amount, sent,
IssueDate )
SELECT QryMemberInfo.ID, QryMemberInfo.Ent_Date, QryMemberInfo.FullName,
QryMemberInfo.Address, QryMemberInfo.Donation, False AS sent, Format(Now
(),"dd mmm yyyy") AS Issuedate
FROM QryMemberInfo
WHERE (((QryMemberInfo.Donation)>=10) AND ((QryMemberInfo.R_status)
=False));

I would like to replace the AutoNumber field with a number and loop through
the above query adding ReceiptNumber = DMax("ReceiptNumber ","TblReceipts")
+1 for each new record.

Would some one please give me an example using DAO.Recordset's

Bob

 

[TC]

You cannot edit autonumber values.

In my opinion, your table structure probably has some problems. Forget
the SQL for the moment, and show us the main fields in each table.
(Just the names will do, yo need not give the types & lengths.) Be sure
to show the primary key field(s) for each table.

HTH,
TC

 

[Alex Dybenko]

Hi,
you can only insert a number in autonumber field using INSERT INTO SQL, if
number is not in a table yet

 

[Bob Hughes]

I currently update a Receipt table with the following Query, where
TblReceipts has an AutoNumber field ReceiptNumber

INSERT INTO TblReceipts ( mem_ID, EntDate, FullName, Address, Amount,
sent, IssueDate )
SELECT QryMemberInfo.ID, QryMemberInfo.Ent_Date,
QryMemberInfo.FullName, QryMemberInfo.Address, QryMemberInfo.Donation,
False AS sent, Format(Now (),"dd mmm yyyy") AS Issuedate
FROM QryMemberInfo
WHERE (((QryMemberInfo.Donation)>=10) AND ((QryMemberInfo.R_status)
=False));

I would like to replace the AutoNumber field with a number and loop
through the above query adding ReceiptNumber = DMax("ReceiptNumber
","TblReceipts") +1 for each new record.

Would some one please give me an example using DAO.Recordset's

Bob

I obviously have not explaned myself properly. My new table will not have
an AutoNumber in it. I will copy the old table to a new one which will
just have a number for the ReceiptNumber. I am looking for a procedure to
add records one at a time from my query. so I can incriment the number
manually.
I will use my limited knowledge to write one myself and ask help with the
procedure later.
Bob

 

[Alex Dybenko]

Hi,
in this case you have to use recordset (two actually) - you can find example
opening and working with recordsets in Access help, or any book on access
programming

 

[Bob Hughes]

Hi,
in this case you have to use recordset (two actually) - you can find
example opening and working with recordsets in Access help, or any
book on access programming

Thank you. I have struggled with the following & it appears to work. Have
I overlooked anything?

Public Function fAppDonations() As Boolean
Dim rsA As DAO.Recordset, rsB As DAO.Recordset, NewNumber As Long
Set rsA = CurrentDb.OpenRecordset("QryDonations")
Set rsB = CurrentDb.OpenRecordset("TblReceiptsNew")
NewNumber = DMax("ReceiptNumber ", "TblReceiptsNew")
fAppDonations = True
On Error GoTo FuncErr
rsA.MoveFirst
Do While Not rsA.EOF 'loop through query
rsB.AddNew 'add to table
rsB!mem_ID = rsA!mem_ID
rsB!EntDate = rsA!EntDate
<snip>
rsB!IssueDate = Format(Now(), "dd mmm yyyy")
NewNumber = NewNumber + 1
rsB!ReceiptNumber = NewNumber
rsB!sent = False 'true when receipts mailed
rsB.Update 'update table
rsA.MoveNext 'move to next record
Loop
FunctExit:
rsA.close
rsB.close
Set rsA = Nothing 'release resources
Set rsB = Nothing
DBEngine.Idle dbFreeLocks
Exit Function
FuncErr:
MsgBox "error " & Err.Number & ", " & Err.Description
fAppDonations = False
GoTo FunctExit
End Function

I volunteer for a small organization & it is extremly important I get
this correct.

Bob

 

[Alex Dybenko]

Hi Bob,
at first glance your code looks ok