Filtering for a record using VB

[RIP]

I previously asked how to autonumber using vb...

I thought more about my question - autonumbering is not really what I am
having difficulties with, but instead filtering for the specific record to
use for the autonumbering...

This is the code I am currently running to get the wallNo field to increment
based on the last number found in the table.

Private Sub Form_Current()
Dim db As DAO.Database
Dim rstWallAdd As DAO.Recordset

Set db = CurrentDb()
Set rstWallAdd = db.OpenRecordset("tblWallDetail", dbOpenTable)

rstWallAdd.Index = "primarykey"
rstWallAdd.MoveLast
wallNo = rstWallAdd!wallNo + 1
rstWallAdd.Close
db.Close
End Sub
--------------------------------------------------
This works fine for straight autonumbering using a single field.
====================================

Heres what I want to do...

The "tblWallDetail" has the following fields/indexes of relevance.

estnum - (indexed=yes duplicates ok)
wallNo - (indexed=yes duplicates ok)

How can I filter for all the records in the "tblWallDetail" table that have
a particular "estnum" value, and then filter for the last "wallNo" record in
that particular recordset. Does that make sense??

I want to get the last number from the "wallNo" field in the "tblWallDetail"
table that have the same "estnum" value.

In other words, the estimate number ('"estnum") will continue to repeat as
long as we are adding records for that estimate, But the wall number
("wallNo") will increment by one as needed.

Thanks in advance for all your help.

 

[John W. Vinson]

estnum - (indexed=yes duplicates ok)
wallNo - (indexed=yes duplicates ok)

How can I filter for all the records in the "tblWallDetail" table that have
a particular "estnum" value, and then filter for the last "wallNo" record in
that particular recordset. Does that make sense??

I'd just use DMax:

NextWallNo = DMax("[WallNo]", "tblWallDetail", "[estnum] = " & Me!estnum)

assuming that you're using a form with estnum as a bound control.

 

[RIP]

The form is an unbound form. How would you go about filtering for the
correct "wallNo" record, and then using this to increment the "wallNo" field
in the unbound form to a plus 1 (+1)??

estnum - (indexed=yes duplicates ok)
wallNo - (indexed=yes duplicates ok)

How can I filter for all the records in the "tblWallDetail" table that have
a particular "estnum" value, and then filter for the last "wallNo" record in
that particular recordset. Does that make sense??

I'd just use DMax:

NextWallNo = DMax("[WallNo]", "tblWallDetail", "[estnum] = " & Me!estnum)

assuming that you're using a form with estnum as a bound control.

 

[John W. Vinson]

The form is an unbound form. How would you go about filtering for the
correct "wallNo" record, and then using this to increment the "wallNo" field
in the unbound form to a plus 1 (+1)??

Something like setting the control source of the wallNo field to

=NZ(DMax("[wallNo]", "tblWallDetail", "[estnum] = " & [estnum])) + 1

You may need to Recalc or requery this control in the afterupdate event of the
estnum control.

If the field is unbound, I presume you have some other mechanism to store it
in a table? How does tblWallDetail get populated?

 

[RIP]

After testing the code you gave me into the text box's control source, I get
a #error.

I would like to run the code for the autonumbering of the "wallNo" field in
the forms "OnCurrent" event. This way, everytime the form is opened to add
another "wallNo" record, the last "wallNo" record is located (for a specific
"estnum" value also) adds a +1

And yes I do have a "save" button that saves the data after running some
simple code for validating each field.

The code is as follows:

Private Sub add_wall_Click()
Dim db As DAO.Database
Dim rstWallAdd As DAO.Recordset

'*** Create a pointer to the database and a pointer to the table ***
Set db = CurrentDb()
Set rstWallAdd = db.OpenRecordset("tblWallDetail", dbOpenTable)


'*** After Validations, add the new recordset to the table,
'*** always use addnew at the begining and update at the end.
rstWallAdd.AddNew
rstWallAdd!estnum = estnum
rstWallAdd!wallNo = wallNo
rstWallAdd.Update
DoCmd.Close
rstWallAdd.Close
db.Close
End Sub

The form is an unbound form. How would you go about filtering for the
correct "wallNo" record, and then using this to increment the "wallNo" field
in the unbound form to a plus 1 (+1)??

Something like setting the control source of the wallNo field to

=NZ(DMax("[wallNo]", "tblWallDetail", "[estnum] = " & [estnum])) + 1

You may need to Recalc or requery this control in the afterupdate event of the
estnum control.

If the field is unbound, I presume you have some other mechanism to store it
in a table? How does tblWallDetail get populated?

 

[John W. Vinson]

After testing the code you gave me into the text box's control source, I get
a #error.

I would like to run the code for the autonumbering of the "wallNo" field in
the forms "OnCurrent" event. This way, everytime the form is opened to add
another "wallNo" record, the last "wallNo" record is located (for a specific
"estnum" value also) adds a +1

Incorrect. The Current event fires whenever you *move between records* - if
you're navigating back and forth, it fires every time. The BeforeInsert event
fires when a new record is created. And I'm pretty sure that *neither* event
fires on an unbound form - certainly the before insert event doesn't!

And yes I do have a "save" button that saves the data after running some
simple code for validating each field.

Could you do the incrementing in that click event instead, then, using the
same code? At what point is the estnum assigned? You can't assign a new wallNo
until the appropriate estnum is specified.

The code is as follows:

Private Sub add_wall_Click()
Dim db As DAO.Database
Dim rstWallAdd As DAO.Recordset

'*** Create a pointer to the database and a pointer to the table ***
Set db = CurrentDb()
Set rstWallAdd = db.OpenRecordset("tblWallDetail", dbOpenTable)


'*** After Validations, add the new recordset to the table,
'*** always use addnew at the begining and update at the end.
rstWallAdd.AddNew
rstWallAdd!estnum = estnum

wallNo = NZ(DMax("wallNo", "tblWallDetail", "[estnum] = " & estnum)) + 1

 

[RIP]

I have this same code in the "On Current" event in another (similar) form and
it works fine. After I click on the save button, and you are returned to the
same form, the "wallNo" field is updated to a +1 value. The only difference
is that the form doesn't have duplicate "wallNo" values, so it is easy to
find the last "wallNo" value and use it as a starting point for the
autonumbering.

The code looks like this:

Private Sub Form_Current()
Dim db As DAO.Database
Dim rstWallAdd As DAO.Recordset

'*** this module gets the last number used in the current table
'*** to assign the new wall number.

Set db = CurrentDb()
Set rstWallAdd = db.OpenRecordset("tblWallDetail", dbOpenTable)

rstWallAdd.Index = "primarykey"
rstWallAdd.MoveLast
wallNo = rstWallAdd!wallNo + 1
rstWallAdd.Close
db.Close
End Sub

After testing the code you gave me into the text box's control source, I get
a #error.

I would like to run the code for the autonumbering of the "wallNo" field in
the forms "OnCurrent" event. This way, everytime the form is opened to add
another "wallNo" record, the last "wallNo" record is located (for a specific
"estnum" value also) adds a +1

Incorrect. The Current event fires whenever you *move between records* - if
you're navigating back and forth, it fires every time. The BeforeInsert event
fires when a new record is created. And I'm pretty sure that *neither* event
fires on an unbound form - certainly the before insert event doesn't!

And yes I do have a "save" button that saves the data after running some
simple code for validating each field.

Could you do the incrementing in that click event instead, then, using the
same code? At what point is the estnum assigned? You can't assign a new wallNo
until the appropriate estnum is specified.

The code is as follows:

Private Sub add_wall_Click()
Dim db As DAO.Database
Dim rstWallAdd As DAO.Recordset

'*** Create a pointer to the database and a pointer to the table ***
Set db = CurrentDb()
Set rstWallAdd = db.OpenRecordset("tblWallDetail", dbOpenTable)


'*** After Validations, add the new recordset to the table,
'*** always use addnew at the begining and update at the end.
rstWallAdd.AddNew
rstWallAdd!estnum = estnum

wallNo = NZ(DMax("wallNo", "tblWallDetail", "[estnum] = " & estnum)) + 1

rstWallAdd!wallNo = wallNo
rstWallAdd.Update
DoCmd.Close
rstWallAdd.Close
db.Close
End Sub