473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Recall Value from Field

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
15 2665
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
32,556 Expert Mod 16PB
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
2,653 Expert 2GB
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
3,532 Expert 2GB
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
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
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
I am not sure if this the correct lookup. It just gives error when the form opens

Steve
Dec 12 '08 #9
NeoPa
32,556 Expert Mod 16PB
@NeoPa
I'm still waiting on this if you'd like some direction.
Dec 12 '08 #10
DonRayner
489 Expert 256MB
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
Here is a sample of the form and table

Steve
Attached Files
File Type: zip test.zip (23.7 KB, 110 views)
Dec 16 '08 #12
DonRayner
489 Expert 256MB
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
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
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, 190 views)
Dec 17 '08 #15
DonRayner
489 Expert 256MB
That's great Steve, glad I could help.
Dec 17 '08 #16

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

Similar topics

3
by: Carlos Marangon | last post by:
Hello! I need some help. How can I recall the last 5 rows from a MySQL table and show them? Where is the FAQs of this newsgroup? Sincerely,
2
by: Jochem | last post by:
Hello, Lately I came accross this problem a couple of times, and I know there must be a clever solution. However, I did not find it untill now. When I add a entry to my mysql database from a...
3
by: TekWiz | last post by:
I've got a system that automatically generates a form. I have it set up so that the backend will return to the inital form page with an error object in sessions data (assuming the backend detected...
6
by: WindAndWaves | last post by:
Hi Folks I have inhereted a script that I understand reasonably well, I just do not understand !/^\d+$/.test(el.value) what the hell does that mean? Below is the script (there are really...
13
by: dbuchanan | last post by:
Hello, Here is the error message; ---------------------------- Exception Message: ForeignKeyConstraint Lkp_tbl040Cmpt_lkp302SensorType requires the child key values (5) to exist in the...
9
by: Ecohouse | last post by:
I have a main form with two subforms. The first subform has the child link to the main form identity key. subform1 - Master Field: SK Child Field: TrainingMasterSK The second subform has a...
1
by: roveagh1 | last post by:
Hi I've been using the 2 year old link below to repeat values from previous record field into current corresponding field. It's worked fine for text but the last piece of advice was to use the same...
3
AsSeenonTV
by: AsSeenonTV | last post by:
Hey there! I will admit I am new to these Message Boards and to Visual Basic 6, but I am an experienced programmer when it comes to Visual Basic for Excel. Now with working with Excel, I am able...
1
by: Maciej Gawinecki | last post by:
Two questions related to the topic 1. If I have an empty set of relevant results, then it would be better to have no answers from the system at all. But neither precision nor recall gives...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.