Solved: get data from Last Record of table for form ?

[bdsii]

Hello all.....this one is simple for you experts, I hope. I want to be able to use the numeric data from the CaseNum field in the last record of the table XYZ in a form. I want to take the info from the last record and increment it by one to be the new data for the casenum field in a new record being entered in the form.

I know you can use a macro to move to the last record but is there a VBA command that call pull that info to be used in a formula similar to "CaseNum = LastRecordCaseNum + 1" ?

Also, in the event that I wanted to use data from the last record of a different table for use in that same form as mentioned above, would that change the "formula" ?

I believe I can write the code for what I want to do, but I just have not found anything that explains how to call the data from the last record of a table.

Any help you can provide would be appreciated ! :-)

[orange]

To move to the last record (physically) you could use
DAO recordset and MoveLast.

[bdsii]

Thanks for the reply.....but would if I am using a form and want to capture one field from the last record and not physically move to that table, would this work for me ?

[OBP]

You can always find the last record in a table using a query, ie.
MaxofCaseNum.
To use the max of function you need to use Totals.
This can be used in conjuction with a VBA Recordset to do what you want.

[bdsii]

Thanks OBP, you taught me a new function! :-)

I have just tried your suggestion and created a query to use the Max function to show the last CaseNum.

Now.....I am having trouble figuring out how to take that query result and then use it inside a formula to get what I am looking for.

The SQL code that I got from the query was:

SELECT Max(tblTestTable.CaseNum) AS MaxOfCaseNum
FROM tblTestTable;


So if I wanted the formula to be something like NewCaseNum = Max of CaseNum +1, how do I get the variable from that function ? The SQL code confuses me somewhat trying to figure out how to use that as the variable to substitute into my formula in place of Max of CaseNum.

Any ideas ?

thanks !!

[bdsii]

Woo-Hoo ! I think I have it ! OBP started the wheels turning (however sluggishly on my end) and after digging for some time ran across some info I used to solve (I think) my problem with this.

Tell me what you think of this solution and if it can be refined.

I had to change to a DMax function (not sure if that was required but that is what was used in the documentation I found) and then was able to use the result of that as a variable that I could manipulate. The code below is what I am using in my form as an Event procedure and it seems to work correctly. Hopefully there are no problems lurking around waiting to pop-up.


Dim OldCaseNum As Integer
Dim NewCaseNum As Integer
OldCaseNum = DMax("CaseNum", "tblTestTable")
NewCaseNum = OldCaseNum + 1
Me.CaseNum.Value = NewCaseNum


Thoughts ?

[OBP]

If it is working for you, then that is great, it is short & sweet.
For more complex autonumbering see this thread.
http://forums.techguy.org/business-applications/969483-autoincrement-field-including-year-restart.html

[bdsii]

Thanks for the help OBP ! I suppose that the MAX function could not be used in place of the DMax ? I am only asking to understand better my options since the DMAX works. MAX may work but I couldn't figure out the syntax for the formula.

[OBP]

I am pretty sure that unlike DMax, max can only be used with Queries and VBA Generated SQL, so it can't be used directly in the formula.
I use the query version because it is easy to check you are getting the right answers before working in the formula.

[hansup]

Dim OldCaseNum As Integer
Dim NewCaseNum As Integer
OldCaseNum = DMax("CaseNum", "tblTestTable")
NewCaseNum = OldCaseNum + 1
Me.CaseNum.Value = NewCaseNum


Thoughts ?
Your code may not need a separate variable for OldCaseNum. Consider assigning your DMax function plus one to NewCaseNum directly.
NewCaseNum = DMax("CaseNum", "tblTestTable") + 1

Actually, you may not need any variables for this.
Me.CaseNum.Value = DMax("CaseNum", "tblTestTable") + 1
Make your decision based on which approach will be easier for you to understand when you revisit your code in the future.

[bdsii]

Thanks hansup ! I was rushing to try the code when I wrote it, I didn't think about making it more efficient. I just tried the last code you provided and for some reason it would not work and I got a 0 as the result each time. When I use the code below you provided, it worked correctly.



NewCaseNum = DMax("CaseNum", "tblTestTable") + 1
Me.CaseNum.Value = NewCaseNum


:-)

[hansup]

I don't understand why it failed when assigning directly to the control's Value, but not when assigning to a variable.

However it occured to me that DMax plus one should be wrapped in the Nz() function so it will return 1 when tblTestTable has no records.

I attached a Zip of a sample database in Access 2003 format. The key is the form's On Current event:

Private Sub Form_Current()
If Me.NewRecord = True Then
Me.txtCaseNum.Value = Nz(DMax("CaseNum", "tblTestTable"), 0) + 1
End If
End Sub

See if that approach is useful to you. If it always tries to make the CaseNum zero, I would be curious to figure out why.