Default value (1 Viewer)

Khalil Islamzada

Registered User.
Local time
Tomorrow, 02:32
Joined
Jul 16, 2012
Messages
49
Dear all;

I am developing a customer management database for a water utility, therefore I need to add billing option to database but I have one problem and hope you can help me.

I have a table and a form for billing with following fields

CustomerID
CurrentMeterNo
LastMeterNo
and other required fields

What I need is, When a new bill is recording the last value of "CurrentMeterNo" field appear in "LastMeterNo" field automatically.

Many thanks for your help.
Khalil:banghead::confused::banghead:
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:02
Joined
Jul 9, 2003
Messages
16,245
"CurrentMeterNo" field appear in "LastMeterNo"

Are "CurrentMeterNo" and "LastMeterNo" readings from the same meter, or are they equipment reference numbers for two different meters?





Sent from my SM-G925F using Tapatalk
 

Khalil Islamzada

Registered User.
Local time
Tomorrow, 02:32
Joined
Jul 16, 2012
Messages
49
Yes they are the same meter.

for your more information, for sure you have water tap in your home and the meter reader is reading your meter so there is a degree which always change and your will be charge according to your water consumption.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:02
Joined
May 7, 2009
Messages
19,169
if you are using form to record, the billing, you can use its beforeInsert event to put the currentmenterNo to LastMeterNo:

Private Sub Form_BeforeInsert(Cancel As Integer)
' use DLast or DMax
Me.LastMeterNo = Nz( DLast("CurrentMeterNo","billing name table here", "CustomerID=" Me.CustomerID), 0)
End Sub
 

Khalil Islamzada

Registered User.
Local time
Tomorrow, 02:32
Joined
Jul 16, 2012
Messages
49
Hi arnelgp:

Thanks for your reply but,

1. I couldn't find "before insert" i can find "before update"
2. When I give the code i received error "Compile error Expected list separator or)


Can you plz help me.
Khalil
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:02
Joined
Jan 20, 2009
Messages
12,849
1. I couldn't find "before insert" i can find "before update"

It is a Form Event rather than a Control Event.

2. When I give the code i received error "Compile error Expected list separator or)

Is your regional decimal separator a comma? If so, then use semicolons to separate the arguments.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:02
Joined
Jan 20, 2009
Messages
12,849
Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
' use DLast or DMax
Me.LastMeterNo = Nz( DLast("CurrentMeterNo","billing name table here", "CustomerID=" Me.CustomerID), 0)
End Sub

DLast (and DFirst) are not reliable ways to get the last (or first) record. because first and last have no real meaning in an Access table. Similarly with the First and Last aggregate functions in SQL.

Determine the most recent record using the date of the reading.
 

Khalil Islamzada

Registered User.
Local time
Tomorrow, 02:32
Joined
Jul 16, 2012
Messages
49
Dear Galaxiom;

I still get the error "Compile error"

So, I uploaded the database, can you plz work inside the database and upload it again or give the correct formulae.

Many thanks

Khalil
 

Attachments

  • Customer management.accdb
    1.7 MB · Views: 152
Last edited:

BeeJayEff

Registered User.
Local time
Today, 15:02
Joined
Sep 10, 2013
Messages
198
Just puzzled then as to why you're posting under Access Web ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:02
Joined
May 7, 2009
Messages
19,169
try adding new record to your billingquery form.
 

Attachments

  • Customer management.accdb
    564 KB · Views: 151

Khalil Islamzada

Registered User.
Local time
Tomorrow, 02:32
Joined
Jul 16, 2012
Messages
49
Dear arnelgp:

Thank you very much for your effort, but can you explain how you did it, can I have your skype, yahoo or another messenger to talk.


I really appreciate your help bro.

Khalil
 

Khalil Islamzada

Registered User.
Local time
Tomorrow, 02:32
Joined
Jul 16, 2012
Messages
49
Dear arnelgp;

I found the rout, you made a code for after update, thanks again you really solve my problem.


Thanks again
Khalil:):):)
 

Khalil Islamzada

Registered User.
Local time
Tomorrow, 02:32
Joined
Jul 16, 2012
Messages
49
Dear arnelgp;

Now I need to do this with form which customerid field type is text, but it doesn't work, do I have to change some code???


Thanks:confused:
 
Last edited:

Users who are viewing this thread

Top Bottom