Add sequential to table on click

[moishy]

Our organization has a db used among other things to track all checks (cheques) given to employees and suppliers.
All check information (bank info, and check numbers) is entered by hand in a form in datasheet view, that seems ridicules because the checks are numbered sequentially.
It would make sense to create a button which on click will add the records (bank info (retrieved from text boxes on the form) and the check numbers starting at the first number of the current check book (entered in a text box on the form)) to the table.
The problem is I'm not sure how to code it. All help will be appreciated.

 

[John Big Booty]

In the On Current event of your form you could use some code along the lines of;

    If Me.YourChqNum = 0 Or IsNull(Me.YourChqNum) Then
        Me.YourChqNum = Nz(DMax("YourChqNum", "YourTable"), 9910079) + 1 [COLOR="SeaGreen"]'Replace 9910079 with your chosen seed number if starting with a blank DB[/COLOR]
    End If

The attached sample demonstrates the principal.

 

[moishy]

John Big Booty,

Thanks for your response.

Using your suggested method only adds one record per on current firing.
Ideally the number of records and the seed number will be entered in unbound text boxes on a form, and on a click of a button the records will be added to the table and displayed in a datasheet subform.

 

[John Big Booty]

The seed number is only required if you are starting from scratch with a completely empty counter table.

If all you want to do is add a new record every time a button is clicked, I think the following (in the button's On Click event) should suffice;

DoCmd.GoToRecord acDataForm, "YourFormName", acNew

Me.YourChqNum = DMax("YourChqNum", "YourTable") + 1

 

[John Big Booty]

The above code, assumes that your tables containing the Cheque Numbers has at least one record in. If not you will need to use;

DoCmd.GoToRecord acDataForm, "YourFormName", acNew

Me.YourChqNum = Nz(DMax("YourChqNum", "YourTable"), 9910079) + 1[COLOR="SeaGreen"] 'Replace 9910079 with your chosen seed number [/COLOR]
    End If

The Nz() function is required to seed the number sequence and get things rolling.

 

[moishy]

I understood the concept. The problem is I want to add, lets say, some 30 cheques per click.

 

[John Big Booty]

OK sorry my bad.

in that case something along the lines of;

Dim intChqCnt As Integer
intChqCnt = 1

If IsNull(Me.FieldChqCnt) or Me.FieldChqCnt < 1 Then [COLOR="SeaGreen"]'FieldChqCnt would be a field the user enters the number of cheques to add[/COLOR]
     MsgBox "This field must be a number greater than one"
     Me.FieldChqCnt.Setfocus
     Exit Sub
End If

While intChqCnt < Me.FieldChqCnt
     DoCmd.GoToRecord acDataForm, "YourFormName", acNew
     Me.YourChqNum = DMax("YourChqNum", "YourTable") + 1
     intChqCnt = intChqCnt +1
Wend

 

[moishy]

I added your code to a command button and it throws a runtime error 2105; You can't go to the specified record.

Attached you'll find my sample.

 

[John Big Booty]

Your form is unbound. The code I have presented assumes you are working from a bound form.

 

[moishy]

The same thing happened when I had bound the form to the table (and added a textbox (YourChqNum) to hold the cheque numbers.

 

[John Big Booty]

My bad a couple of small problems in the air code. This will work;

Dim intChqCnt As Integer
intChqCnt = 1

If IsNull(Me.FieldChqCnt) or Me.FieldChqCnt < 1 Then 'FieldChqCnt would be a field the user enters the number of cheques to add
     MsgBox "This field must be a number greater than one"
     Me.FieldChqCnt.Setfocus
     Exit Sub
End If

While intChqCnt < Me.FieldChqCnt [COLOR="Red"]+ 1[/COLOR]
     DoCmd.GoToRecord acDataForm, "YourFormName", acNew[COLOR="Red"]Rec[/COLOR]
     Me.YourChqNum = DMax("YourChqNum", "YourTable") + 1
     intChqCnt = intChqCnt +1
Wend