Stop a Record Count

Hi Guys,

Question for ya.

I want to stop the database to add new records and start with a new batch. My recordset is comprised of 831 entries.

I want it to start from the begining with record number to read 04001 as in year 2004, record 001, instead of just 831.

this is the code i have:

Private Sub cmdNew_Click()

Data1.Recordset.AddNew

' This generates the next Order Number * RAP
Dim OrderNumber As Integer
OrderNumber = (Data1.Recordset.RecordCount + 1)
TxtOrderNumber2004 = OrderNumber

txtOrderDate = DateTime.Date

End Sub

Anything will be appreciated

Thanks

Hi Nicholas,
Not quite clear what you'd like to achieve.
What is a "new batch"?
Would yo like to do a loop through the recordset or so?

Regards,
Shurik.

Hi Nicholas,
Not quite clear what you'd like to achieve.
What is a "new batch"?
Would yo like to do a loop through the recordset or so?

Regards,
Shurik.

Hi Shurik,

What I want is for that particular field to stop adding records sequentially 831, 832, 833 and so on. I want it to read 150 as 150 being a new record.

Its sort of a new financial year for me and I want the squence to restart in the same field but instead:

example

I dont want field to read on date 1.01.2004 ordernumber 832 ( Dec 2003 was 831 )

I just to the sequence to restart as 1.01.2004 ordernumber 150
so that everything starts all over again. Then to re-synchronise at the begining of 2005 to read ordernumber 150

I hope this helps

hmmm...
> What I want is for that particular field to stop adding records sequentially 831, 832, 833 and so on.
Do you have Autonumbering in the field in the underlying table?

>I want it to read 150 as 150 being a new record.
???

In general to refer to the last record in a recordset you'd basically need to do Data1.Recordset.MoveLast

I'm sorry but for the rest I still have a vague picture on what you'd like to achieve...

OK lets see if this explanation works.

My Database is just to generate Order Numbers and fill fields with my Suppliers particulars, Invoice Totals etc etc.

My financial year ended 31.12.03 with Order Number 831

As from 1.01.04 I need to start with Order Number 001

I dont want to modify my Access Database unless its nessecary

This is the code I have for my add new button

Data1.Recordset.AddNew

' This generates the next Order Number * RAP
Dim OrderNumber As Integer
OrderNumber = (Data1.Recordset.MoveLast + 1)
TxtOrderNumber2004 = OrderNumber

txtOrderDate = DateTime.Date

End Sub

I need to modify something there, but just cant figure it out.

Thanks for your help mate

Nicky

first you need to select your table you can still use your data control
provided your recordsource should be sql statement with order by date and ordernumber then continue with your incrementation method but you need to check the date of the last number if the year of this last number < current year then start the number with 001 else increment ordernumber.

Duane

Thx very much for your feedback

Can you please post an example on how code this?.

I am a complete newbie as you might have realised and my knowledge of SQL coding is rather inexistent at the moment.

Thx for your time.

what data control are you using DAO or ADO?

ADO

Thx once again

can you post the format of your or date and gthe code how you increment your OrderNumber?

Here is the full coding of my program

Private Sub CmbGoods_Click()
Me.Text1 = Me.CmbGoods
End Sub
Private Sub CmdDeleteRecord_Click()

Data1.Recordset.Delete
Data1.Recordset.MoveLast

End Sub
Private Sub CmdSearchOrder_Click()
Form1.Show

End Sub
Private Sub mnuAbout_Click()
frmAbout.Show
End Sub
Private Sub cmdNew_Click()

Data1.Recordset.AddNew

' This generates the next Order Number * RAP
Dim OrderNumber As Integer
OrderNumber = (Data1.Recordset.RecordCount + 1)
TxtOrderNumber2004 = OrderNumber

txtOrderDate = DateTime.Date

End Sub
Private Sub cmdDelete_Click()

Data1.Recordset.Delete
Data1.Recordset.MoveNext
Data1.Refresh

End Sub
Private Sub cmdRefresh_Click()
'this is really only needed for multi user apps
Data1.Refresh
End Sub
Private Sub cmdUpdate_Click()

Data1.Recordset.Update


End Sub
Private Sub cmdExit_Click()
' Data1.UpdateRecord
Data1.Recordset.Bookmark = Data1.Recordset.LastModified
Unload Me
End Sub
Private Sub Data1_Reposition()

Data1.Caption = Data1.Recordset.AbsolutePosition + 1 & " of " & _
Data1.Recordset.RecordCount

Me.CmbGoods.Text = ""

End Sub
Private Sub Data1_Validate(Action As Integer, Save As Integer)
'This is where you put validation code
'This event gets called when the following actions occur
Select Case Action
Case vbDataActionMoveFirst
Case vbDataActionMovePrevious
Case vbDataActionMoveNext
Case vbDataActionMoveLast
Case vbDataActionAddNew
Case vbDataActionUpdate
Case vbDataActionDelete
Case vbDataActionFind
Case vbDataActionBookmark
Case vbDataActionClose
End Select

End Sub
Private Sub Data2_Reposition()

Do While Not Data2.Recordset.EOF
CmbSupplier.AddItem Data2.Recordset.Fields("supplier")
Data2.Recordset.MoveNext
Loop
End Sub
Private Sub Data3_Reposition()

Do While Not Data3.Recordset.EOF
CmbGoods.AddItem Data3.Recordset.Fields("Product_Description")
Data3.Recordset.MoveNext
Loop
End Sub
Private Sub Form_Load()
Me.TxtOrderyear = DatePart("yyyy", Date)
End Sub


Thx

Private Sub cmdNew_Click()

Data1.Recordset.AddNew

' This generates the next Order Number * RAP
Dim OrderNumber As Integer
OrderNumber = (Data1.Recordset.RecordCount + 1)
TxtOrderNumber2004 = OrderNumber

txtOrderDate = DateTime.Date

End Sub

using the code above won't give you this 001 number but rather this 1
do you really want 001 this or just this 1? and I need the format of your date i.e 01/01/2004