how to take a value from one form to anothor

[sspreyer]

vbainet worked you Legend!!!!!
thanks again
shane

 

[vbaInet]

You're welcome Shane.

 

[sspreyer]

hi i have now found problem
on close it takes the value from the first entered record and adds it to the serviceduedate but i want it to take the last record value and add it to service date if this make sence

cheers

shane

 

[vbaInet]

It will take the value of record you were last on, not the first record. If you were on the first record, then that's the value it will take.

 

[sspreyer]

It will take the value of record you were last on, not the first record. If you were on the first record, then that's the value it will take.

it defo taking the first record it doesnt matter what one i click on and close
always take first record

 

[vbaInet]

When I say on, I mean the record you click, not just the record you scroll down to view. So let's see if you can work this one out yourself, you will need two functions but start of with the DMax function (link below). Get the highest (or max) value of the unique ID in the query or table that's bound to the form:

http://www.techonthenet.com/access/functions/domain/dmax.php

 

[sspreyer]

hi

here's a sample database I can't understand why it always takes the first record and transfer it but the focus is set to the last record which I want it to transfer very weird I thought it would transfer they last record date

cheers

shane

can any one see a problem cheers :banghead::banghead::banghead::banghead:
View attachment shanetest2.zip

 

[sspreyer]

hi right this is what I have tried but doesn't seem to be working

DMax("[ServiceDate]","[PlantServiceTbl]","([serviceid]= " & [PlantServiceTbl] & ")")

see what you think

cheers

shane

 

[vbaInet]

Test it with a MsgBox. It's a good way for beginners to test things. There are other ways, but let's stick with this one first.

Msgbox DMax("[ServiceDate]","[PlantServiceTbl]", [COLOR="Blue"]"[/COLOR][serviceid]= " & [PlantServiceTbl][COLOR="blue"])[/COLOR]

I hope you notice the changes

 

[sspreyer]

ok..

its now saying the expression you have entered is a invalid syntax
it highlights Dmax

 Msgbox [COLOR=red][B]DMax[/B][/COLOR]("[ServiceDate]","[PlantServiceTbl]", "[serviceid]= " & [PlantServiceTbl])

cheers

shane

 

[vbaInet]

My bad:

Msgbox DMax("[ServiceDate]","[PlantServiceTbl]")

 

[vbaInet]

But ServiceDate can't be the field you want to return the max of anyway.

 

[sspreyer]

no thats my next question

its service next due which is a calculated field called DateDue

but this expression will not work

Msgbox DMax("[datedue]","[PlantServiceTbl]")

as it not in my table its a calculated field

cheers

shane

 

[vbaInet]

What is the sequential Number ID field that is unique in table?

 

[sspreyer]

serviceId is the primary key
impPlantitemId is the Plant item id the link between plant item and plant service

if this helps

thanks

shane

 

[vbaInet]

And ServiceID increments automatically right? So what do the three parameters in the DMax function represent?

DMax(first part, second part, third part)

first part - the field that you want to get the highest number of
second part - the table or query where this field is
third part - criteria part if you want to filter based on certain conditions. In your case you don't need this part here.

So from what I just explained what should be your first and second parts?

 

[sspreyer]

right...
ServiceID increments automatically right? answer yes

first part: datedue
second part: plantservicetbl subform : query builder

thanks

shane

 

[vbaInet]

Please re-read my last post.

first part - the field that you want to get the highest number of

Is DateDue the field you want to get the highest number of?

 

[sspreyer]

Right ....

Yes I want to get highest date from datedue this is calculated field in my query

So it would be

First part the

field = datedue

Second part is

Query name = qtyplantservice

Thanks

Shane

 

[vbaInet]

Upload a copy of your db and tell me where to look.