By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Recall Value from Field

P: 22
Being a newbie, I am buildig a mileage db and need some help. I have a mileage table that has starting and ending odometer fields. As I enter the records for the entry I want the starting value of start odometer to prefill with the value of the last end odometer record.

Here is the code I tried.
Expand|Select|Wrap|Line Numbers
  1. Private Sub EndOdometer_AfterUpdate()
  2. If Not IsNull(Me.EndOdometer.Value) Then
  3.   StartOdometer.DefaultValue = Me.EndOdometer.Value
  4. End If
  5.  
  6. End Sub
It did not work. Any help is appreciated.

Steve
Dec 7 '08 #1
Share this Question
Share on Google+
15 Replies


puppydogbuddy
Expert 100+
P: 1,923

P: 22
Thanks for the reply. I found this a couple of days ago and could not get this to work as well.

I will keep trying

Steve
Dec 8 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
Do you have the value you want in [EndOdometer]? Or is that in a separate record?

PS. Please note the edit comment about the tags.
Dec 8 '08 #4

FishVal
Expert 2.5K+
P: 2,653
Hello, Steve.

Looks like the controls have date values.
If so, then you should add # delimeters to the string assigned to DefaultValue property.

See:
Repeating values for a field in several records
Problem with DateAdd and Default Value
Dec 8 '08 #5

missinglinq
Expert 2.5K+
P: 3,532
Why would a field holding an odometer reading (mileage) hold a date? Presumably it would hold a numeric value, in which case the original code posted by the OP should work.

Note that this code will only work if you're entering multiple records for the same vehicle at the same time,i.e. if you enter one record, then move on to the next record and enter it and so on. If you enter one record for a given vehicle then close the form, then open it later to enter mileage for the same vehicle, the value will be lost. It that case you would have to use the DLookup() method given in the link PuppDogBuddy posted earlier.

Exactly where are you placing this code? In the code window or in the Properties Sheet?

Can you zip your file and post it here?

Linq ;0)>
Dec 8 '08 #6

P: 22
Thanks for the responses. Wow. I am on the road and will return this weekend. I will post a zip file when I return.

Thanks again

Steve
Dec 10 '08 #7

P: 22
I have tried on the form DLookup
=DLookUp([EndOdometer]","tblMileage","[CarId]=Forms![frmMileage]![CarId]-1")

I placed this as the source for starting Odometer. I get an error and the form will not open

Steve
Dec 11 '08 #8

P: 22
I am not sure if this the correct lookup. It just gives error when the form opens

Steve
Dec 12 '08 #9

NeoPa
Expert Mod 15k+
P: 31,186
@NeoPa
I'm still waiting on this if you'd like some direction.
Dec 12 '08 #10

DonRayner
Expert 100+
P: 489
hmmmm. I always have a little trouble with the dlookup function myself. Try this

Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[EndOdometer]","tblMileage","[CarId] = " & "" & Forms![frmMileage]![CarId] & "")
  2.  
Although I don't think that it's the DLookup() function that you need but rather the DMax()

If I assume that you select the [CarID] after starting a new record you could impliment it somthing like the following.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CarID_AfterUpdate()
  2. On Error goto ErrPoint
  3.  
  4.     Dim inLastReading as integer          ' use whatever name you want
  5.     If Me.NewRecord then                     ' only run this code if it's a new record
  6.         inLastReading = NZ(DMax("[EndOdometer]","tblMileage", "[CarId] = " & "" & Forms![frmMileage]![CarId] & ""))
  7.     End If
  8.     If Not IsNull(Me.StartOdometer) then ' Leave it alone if it's already filled in
  9.         Me.StartOdometer = inLastReading
  10.     End If
  11.  
  12. ExitPoint:
  13.     Exit Sub
  14.  
  15. ErrPoint:
  16.     MsgBox Err.Number & " " & Err.Description
  17.     Resume ExitPoint
  18.  
  19. End Sub
  20.  
Dec 12 '08 #11

P: 22
Here is a sample of the form and table

Steve
Attached Files
File Type: zip test.zip (23.7 KB, 74 views)
Dec 16 '08 #12

DonRayner
Expert 100+
P: 489
You can't put your statement in as the control source for your StartMiles text box. By doing this you will only be showing a calculated value that would never be added to the database. The control source needs to be the StartMiles field from tblMileage. Putting your expression in as the Default Value also won't work. Since the CarID field is not filled out when arriving at the new record it will return an #Error.

Best way to do this is to add your expression to the "After Update" event for the CarID textbox on your form.

The Dlookup() function will only return the first record it comes accross that matches your criteria so you need to use the DMax() function instead. It will find the maximum value that matches your criteria.

The VBA for the CarID "After Update" event would look like the following code. This code will update the StartMiles textbox with the largest EndMiles that it can find for the CarID that is entered on the form. If the StartMiles is already filled out it won't change the value.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CarId_AfterUpdate()
  2. On Error GoTo ErrPoint
  3.  
  4.     Dim inLastReading As Integer 
  5.     If Me.NewRecord Then 
  6.         inLastReading = Nz(DMax("[EndMiles]", "tblMileage", "[CarId] = " & """" & Forms![frmMileage]![CarId] & """"))
  7.     End If
  8.     If IsNull(Me.StartMiles) Then 
  9.         Me.StartMiles = inLastReading
  10.     End If
  11.  
  12. ExitPoint:
  13.     Exit Sub
  14.  
  15. ErrPoint:
  16.     MsgBox Err.Number & " " & Err.Description
  17.     Resume ExitPoint
  18.  
  19. End Sub
  20.  
Your table has the primiary key set to the CarID field. This won't work as it will only allow one entry into the database for each CarID. Change the Primary key to the ID field instead.

Also your table has "Date" as the name for one of your fields. Date is a reserved word in access and this is going to cause you problems further down the line as you develop your DB further.
Dec 16 '08 #13

P: 22
Excellent. I have already implemented most of what you stated and I working on the rest. I will give an update tomorrow.

Steve
Dec 17 '08 #14

P: 22
That worked wonderfully. This has been a fun project of learning. Most of your comments on the previous post were right on as I had learned most of this the hard way. The zip file I sent was just quick example of what I was working with. It took me forever to figure out the CarID problem.

Here is a screen shot of where I am now.

thanks again

Steve
Attached Images
File Type: jpg mileage1.jpg (18.6 KB, 149 views)
Dec 17 '08 #15

DonRayner
Expert 100+
P: 489
That's great Steve, glad I could help.
Dec 17 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.