Fill a field with value for subform 1

[MrMcFestoe]

Hi,

I need a field on my main form that displays a vaule for a subform, the subform holds service visits to all customers the field on my main form needs to display the last service visit for that customer.

Any ideas i have tried everything

Life is made on the decisions you make not the chances we take.

 

[GingerR]

make a text box on your main form that will hold one field's worth of info.
i'm doing this without knowing your table structure so you will have to tweak it.

say your table that the subform is based on is called TableSub.
the ID field (that is the same as the main form) is field ID
there's a date field that shows dates for each service call
and the data you want to display is called in a field called ServiceDetails

use dmax function:
=dlookup("ServiceDetails","TableSub","ID = " & Forms!Main!ID & " and [ServiceDate] = #" & dmax("ServiceDate","TableSub","ID = " & Forms!Main!ID) & "#&quot

this will look up the ServiceDetails in your table called TableSub, where the ID in the table = the ID on the main form, and the ServiceDate is the max service date from that same table for that ID.

 

[MrMcFestoe]

GinerR

Thanks for the replie will try that this weekend and let you know.

Thanks


Life is made on the decisions you make not the chances we take.

 

[MrMcFestoe]

GingerR

I must be overlooking somthing simple but i cant get it to work, i have had it working once before but did not know how.

More info on my db

I need a field on my main form CUSTOMERS called LAST SERVICE VIST to display the last visit to this customer. every customer has a ID and i have a table called SERVICE HISTORY which contains all visits to every customer, i also have a SERVICE HISTORY subform which displays all visits to that customer on my main screen (this works). I have tried modify your post to my field names and iam still lost.

Any more info much appreciated.

thanks.

Life is made on the decisions you make not the chances we take.

 

[GingerR]

what is the code you used? please paste it here.

try picking apart the statement above, and doing it in two phases:

first try getting the max date for the current ID into one text box on the form:

dmax("ServiceDate","Service History","ID = " & Forms!Main!ID)

mess with that until you get the max service date for the current ID. name the text box something intelligable (like txtMaxServiceDate.

then make a second text box.
=dlookup("ServiceDetails","TableSub","ID = " & Forms!Main!ID & " and [ServiceDate] = #" & dmax("ServiceDate","TableSub","ID = " & Forms!Main!txtMaxServiceDate) & "#&quot

without looking at your db, you'll have to just mess around with it. look up DLookup in HELP or text book some place. pick apart the statement and do a bit at a time, until you get each bit working, then put it all together....

 

[MrMcFestoe]

GingerR,

Thanks for the posts, but i cant solve this which i thought would be so simple, (4 days later)

I keep getting the same message in the text box #name? not shure what this means any ideas?

Thanks

 

[GingerR]

usually means you are referencing something by the wrong name, and Access cannot find it.

please paste all of your code here.
also your table name (for the subform) along with the field names in that table.

thanks--g

 

[MrMcFestoe]

GingerR

Code what i have been trying since your post

=dlookup("Date of Visit","Service History","ID = " & Forms!Main!ID & " and [Date of Visit] = #" & dmax("Date of Visit","Service History","ID = " & Forms!Main!ID) & "#&quot

On my main form i am trying to diplay information from a Table called SERVICE HISTORY, the fileds are ID, REASON FOR VISIT, DATE OF VISIT, DOCKET NUMBER, NOTES, ENG

What iam trying to do is only display the last service visit, so i tried REASON FOR VIST = Service with the ID number which is primary field in my main table where all customers are stored and tried a MAX on the DATE OF VISIT, but still did not work.

What do you think.

Thanks

 

[GingerR]

did you do them separately like i suggested above? just to see if each part works correctly?

 

[MrMcFestoe]

GingerR

Tried the following in a text box

dmax("Date of Visit","Service History","ID = " & Forms!Main!ID)

and get the error #name? but the names of the fields in the service history are correct.

Just had a thought iam putting this as the control source should it be in the before event ?

Thanks

 

[GingerR]

in the control source of the text box.
try putting square brackets around the field names.
=dmax("[Date of Visit]","[Service History]","ID = " & Forms!Main!ID)

if this still doesnt work you can send it to me at rowe147@hotmail.com, please compact and zip first.

g

 

[MrMcFestoe]

GingerR

A star is on its way, thanks for this i have learn alot, iam i right in thinking the field name you wanting to get has to be in [ ] brackets, so the field name is first [Field name],[table name] or iam total lost.

The code you posted, i changed to my field names but forgot to change the main feild name, so it now looks like

=DMax("[Date of Visit]","[Service History]","ID = " & [Forms]![Customers]![ID])

But is there any way of this code only pulling out the last service visit form the table ? i have a field called LAST SERVICE VISIT which could equal service, callout, customer error, fault, and others the above code pulls the last date from the subform but it could be for a fault call not a service visit,

Can it be done.

Thanks for the time and the education.

 

[GingerR]

hi there. no problem re: helping out....

not clear on what you are wanting now tho, sorry.
your original post, i thought you wanted the details of the last service visit, so what i was doing was first finding the last SERVICE DATE, then getting the details attached to that date. is what you want now different than that?

sounds like you have successfully gotten the MAX SERVICE DATE for the customer who's ID you are looking at on the main form, right?

is what you mean now is that you have a field called "LAST SERVICE VISIT" and you only want to show the max date for LAST SERVICE VISIT = 'SERVICE'?

if so, just add it to the 'where' portion of the dmax statement. please check out dmax, dlookup, dcount etc in HELP or a text book-it will explain what this is all about.

you'd put in something like

=DMax("[Date of Visit]","[Service History]","ID = " & [Forms]![Customers]![ID] & " and [Last Service Visit] = 'Service'")

i know the different sets of quotes and amersands (&) can be confusing but you'll get the hang of it after a while. what your WHERE clause looks like is (i.e. if it's ID=2):

where ID = 2 and [Last Service Visit] = "Service"

that's how it ends up getting parsed out when you type it in like i wrote it in the dmax statement above.

so try that out and see how it works.

have fun!!
g

 

[MrMcFestoe]

GingerR

With your help, it works, cheers for that.

Now onto something else.

 

[NicoleOB]

Ginger - I was browsing for some help on the DMax function when using 2 fields as criteria & saw your above example. I'm trying to number a field, sequentially, where it checks for previous records w/ the same Index # and Year. I keep getting error messages when trying to do this - could you look at my code & advise? Thanks!

= Dim rsNr As Variant
rsNr = DMax("[Invoice_Number]", "tblInvoice", "[Index]=" & Me.Index & " AND "Year]= " & Me.Year & "&quot

 

[GingerR]

is Index a number or text?
is Year a number or text?

please paste all your code. why is there an equal sign before the DIM statement?
looking briefly at your code, you have problems: get rid of the quotes before YEAR, and the square bracket? there's no left-hand square bracket to mate with the right-hand one? and don't know why you have double-quotes at the end? doesn't make sense? perhaps you have not copied this directly from your code?

if both Index and Year are numbers, here you go:


DMax("[Invoice_Number]", "tblInvoice", "[Index]=" & Me.Index & " AND [Year] = " & Me.Year)

 

[NicoleOB]

Ginger - thank you - I had typed it in wrong - tried to do it quickly & noticed my mistakes after I sent it.

Anyway - it worked perfectly! I had looked all over for this & hadn't been able to find it.

 

[MrMcFestoe]

Ginger R

Back again, after doing some other bits on this db i need to set up a report, but the Last_Service_Visit is worked out from the code you helped me though

=DMax("[Date of Visit]","[Service History]","ID = " & [Forms]![Customers]![ID] & " and [Last Service Visit] = 'Service'&quot

but iam right in thinking that because the text box is unbound it wont update my table, and i cant use this feild to run a report?

I have tried changing the above statement into a bound box but then there are no dates shown and it does not work.

Just puzzled now,