Show Result from Text Box in continous form

[GLEberts]

I am trying to show a result in an additional text box on my form - from the last line in a continous form.

(1) My main form - fmInvoice
(2) My sub form - fmInvoiceSubfor m
(3) Text box in "fmInvoiceSubfo rm" = txtstatus [this is the one I am trying to duplicate with the last result]

Example:

I have 4 lines of data in a continous form. What I want to do is have the last or most recent line with the text box "txtstatus" to display the result in another text box on the main form "fmInvoice"

=[FmInvoiceSubfor m].[Form]![txtstatus]

What I have done is insert the following into the 2nd text box on the main form:

What this does is acutually stop access from continuing.

Any help would be appreciated.

Thanks
Gary

[jpatchak]

You probably would have more success if you weren't getting the value from the subform, but from the underlying table itself. Assuming that your main form has a primary key (i'll call it MasterID and assume that you've got it in a textbox called txtMasterID) that is stored as a foreign key in the table underlying your sub form and your sub form's table ALSO has a primary key on it, the following code in your text box's control source should do it:

=dlookup("Status", "subformtablename", "MasterID=txtMasterID AND SubID =" & DMAX("SubID", "subformtablename", "MasterID=txtMasterID"))

This assumes you want the status from the record with the largest ID. You could substitute SubID for any field - it could be a date field or whatever.

[NeoPa]

Gary, I expect the error message would be helpful here, but without one my best guess is that it doesn't like the '.' in your reference. '.' is specific to VBA references and don't work as far as I can tell from anything SQL based (as the form is). First try :

=[FmInvoiceSubform]![Form]![txtstatus]

If that doesn't work check out Referring to Items on a Sub-Form for the full picture. remember to be sure to understand the difference between a Subform control and the actual form attached to it. Only the form name should be in the reference. Not the actual name of any form in your database.

[GLEberts]

jpatchak,

thanks for the reply and suggestion. so far i am coming up with an error but i will work on it awhile and try to sort it out.

what i have is:

=DLookUp("Statu s","Status details","Order ID=txtOrderId AND txtStatusDate =" & DMax("txtStatus Date","Order Details","Order ID=txtOrderID") )

thanks for you help - i will post when i have it working properly

Gary

[GLEberts]

NeoPA,
thanks for your help i will keep working and try to sort it out with the suggestions. so far i keep getting errors but will get it worked out.
Thanks
Gary

[NeoPa]

That's cool Gary. Good luck with your efforts.