Get last number to new (1 Viewer)

Khalil Islamzada

Registered User.
Local time
Today, 18:56
Joined
Jul 16, 2012
Messages
49
Dear all:

I have below code;

Private Sub CusCard_No_AfterUpdate()
Dim lngBillNo As Long
Dim lngOldMeter As Long

lngBillNo = Nz(DLast("[Trn_No]", "Water_Journal", "Cus_Card_No=" & Me.Cus_Card_No), 0)
lngOldMeter = Nz(DLookup("[Current_Meter_No]", "Water_Journal", "[Trn_No]=" & lngBillNo), 0)

Me.Previous_Meter_No = lngOldMeter & ""


End Sub

It gets last meter number and working well, but if any record is done without any meter number the last meter number will be zero, while I want it to get the last number not zero.


Can anyone help me please????:banghead:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:26
Joined
May 7, 2009
Messages
19,169
after getting the meterNo and it is zero, get the latest meterNo:
if you have "TransactionDate" on Water_Journal table:




Dim rs As Dao RecordSet

lngOldMeter = Nz(DLookup("[Current_Meter_No]", "Water_Journal", "[Trn_No]=" & lngBillNo), 0)


if lngOldMeter = 0 Then
Set rs = CurrentDb.OpenRecordSet("Select Top 1 Current_Meter_No From Water_Journal Where Trim('0' & Current_Meter_No & '')<>'0' Order by TransactionDate Desc;")
lngOldMeter = rs(0)
rs.Close
set rs=Nothing
End If
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:26
Joined
May 7, 2009
Messages
19,169
acually thete is an addition to the criteria of the query:

Where Trim('0' & Current_Meter_No & '')<>'0' And Cus_Card_No=" & me.Cus_Card_No & " Order by TransactionDate Desc;")
 

Users who are viewing this thread

Top Bottom