Command button trigger

[ladybug via AccessMonster.com]

I have a form called frm_create_parent. On this form there is a subform
called sfrm_tbl_detail_parent. The record source for sfrm_tbl_detail_parent
is
SELECT tbl_detail.record_id, tbl_detail.parent_no, tbl_detail.stc_no,
tbl_detail.pz_acct_no, tbl_detail.ctr_nm
FROM tbl_detail;

I have created a button on sfrm_tbl_detail_parent called btn_gen_stc. The
purpose of the button is to create the stc_no in the subform. When clicked,
I want the next highest stc_no from tbl_detail to appear. Currently there
are over 100 records in tbl_detail. The highest # for stc_no in the table is
9448. I want to be able to click the button and right now 9449 appear. Next
time 9450 and so on.

I have the following code in the On Click Event for btn_gen_stc:
Private Sub btn_gen_stc_Click()
If Me.NewRecord Then
Me.stc_no = DMax("stc_no", "tbl_detail") + 1
End If
End Sub

However, nothing happens. Can someone please help? Thank you!

 

[Dirk Goldgar]

I have a form called frm_create_parent. On this form there is a subform
called sfrm_tbl_detail_parent. The record source for
sfrm_tbl_detail_parent
is
SELECT tbl_detail.record_id, tbl_detail.parent_no, tbl_detail.stc_no,
tbl_detail.pz_acct_no, tbl_detail.ctr_nm
FROM tbl_detail;

I have created a button on sfrm_tbl_detail_parent called btn_gen_stc. The
purpose of the button is to create the stc_no in the subform. When
clicked,
I want the next highest stc_no from tbl_detail to appear. Currently there
are over 100 records in tbl_detail. The highest # for stc_no in the table
is
9448. I want to be able to click the button and right now 9449 appear.
Next
time 9450 and so on.

I have the following code in the On Click Event for btn_gen_stc:
Private Sub btn_gen_stc_Click()
If Me.NewRecord Then
Me.stc_no = DMax("stc_no", "tbl_detail") + 1
End If
End Sub

However, nothing happens. Can someone please help? Thank you!

I don't see anything fundamentally wrong with your code, except that, if
tbl_detail is empty or all stc_no values are currently empty, you'll get a
Null back. Use this code to deal with that situation:

Me.stc_no = Nz(DMax("stc_no", "tbl_detail") , 0) + 1

But you said that you currently had a high value of 9448 in the table. If
that's the case, you should have gotten 9449 back, unless you were not on a
new (unsaved) record. The code does say:

If Me.NewRecord Then

Therefore, if you clicked the button on anything but an unsaved new record,
nothing would happen. Could it be that you weren't on a new record when you
tested this? Remember that, once the record has been saved, it isn't a "new
record" any more, as far as Access is concerned.

 

[ladybug via AccessMonster.com]

I think that may be the problem. The subforms only purpose is to generate a
new stc_no and then has two other text boxes for free text. That is why I
was thinking new record. Technically, it is not a new record because the
record is already established from the main form. How can I change my code
to generate the stc_no if in fact that record is not new?

I have a form called frm_create_parent. On this form there is a subform
called sfrm_tbl_detail_parent. The record source for

[quoted text clipped - 22 lines]

However, nothing happens. Can someone please help? Thank you!

I don't see anything fundamentally wrong with your code, except that, if
tbl_detail is empty or all stc_no values are currently empty, you'll get a
Null back. Use this code to deal with that situation:

Me.stc_no = Nz(DMax("stc_no", "tbl_detail") , 0) + 1

But you said that you currently had a high value of 9448 in the table. If
that's the case, you should have gotten 9449 back, unless you were not on a
new (unsaved) record. The code does say:

If Me.NewRecord Then

Therefore, if you clicked the button on anything but an unsaved new record,
nothing would happen. Could it be that you weren't on a new record when you
tested this? Remember that, once the record has been saved, it isn't a "new
record" any more, as far as Access is concerned.

 

[ladybug via AccessMonster.com]

Nevermind...Current Record...got it!

I think that may be the problem. The subforms only purpose is to generate a
new stc_no and then has two other text boxes for free text. That is why I
was thinking new record. Technically, it is not a new record because the
record is already established from the main form. How can I change my code
to generate the stc_no if in fact that record is not new?
[quoted text clipped - 18 lines]

tested this? Remember that, once the record has been saved, it isn't a "new
record" any more, as far as Access is concerned.