find the ID for previous record, and calculate to make new record

[rxsims]

Hi,

I am trying to find out how I can update a control based on the value from the previous record, i.e.


Inventory(newrecordvalue) = Inventory(currentrecordvalue) - InventoryChange

or

Inventory(currentrecordvalue) = Inventory(currentrecordvalue-1) - InventoryChange

I can't find any reference to find the actual number of the recordset that is seen in the navigation bar on the form.

Any help would be greatly appreciated. This should be quite simple, but I think I don't know the correct syntax to look up.

 

[fneily]

How do you tell the previous record? By date? By an ID? If you know the primary key of the previous record, or some unique value, you can use the Dlookup function. You can use the Access Help to see the dlookup syntax and example.

 

[Pampers]

I can't find any reference to find the actual number of the recordset that is seen in the navigation bar on the form.

I dont think there is any reference. It is more like a counter, depending on how to recordset is sorted and so on.

Like Fneily says: how would you define the previous record? In anyway you can not use the navigation bar numbers. Most of the time one could use the ID or any other unique value that identifies a record.

Pampers
Keeping it simple can be complicated

 

[TheAceMan1]

How are ya rxsims . . .

As a starter, here's a no frills idea on getting the previous value:

[blue]Public Function PrevVal()
   Dim rst As DAO.Recordset, Cri As String
   
   Set rst = Me.RecordsetClone
   Cri = "[PrimaryKeyName] = " & Me!PrimaryKeyName
   rst.FindFirst Cri
   
   [purple][b]If rst.AbsolutePosition >= 1 Then
      rst.MovePrevious
      [b]PrevVal = rst![TextboxName][/b][/purple]
   Else
      MsgBox "Record Not Found!"
   End If
   
End Function[/blue]

Be aware: the current record is the record that has the focus, [blue]at the time you run the code[/blue]. New record or not!

Also, for a new record how is [blue]Inventory(currentrecordvalue)[/blue] assigned? ... or better yet, how do you intend to trigger what you want?

Your also showing values representing control names. It would be better if you gave the names of the textboxes instead.

You need to define [blue]InventoryChange![/blue]

[blue]Your Thoughts?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JoeAtWork]

I can't find any reference to find the actual number of the recordset that is seen in the navigation bar

As the others have pointed out, when opened without a sort order, there is no guarantee of consistency here. The record that shows in the 5th place today may be in 8th spot tomorrow. This is regardless of whether any updates, inserts, or deletes took place in those records.

Joe Schwarz
Custom Software Developer

http://www.plandev.net