DLookup Returns #Error on Subform/not on normal form (1 Viewer)

[DKO]

I'm using the DLookup function as the control source for a text box on a subform and it is returning "#Error".

But, if I open the subform on it's own - it returns the correct data.

Here's the code:

=DLookUp("[STATUS]","[tblSTATUS]","[tblSTATUS].[ID] = (SELECT [tblACTIONS_StatTRACKING].[1PREV] FROM [tblACTIONS_StatTRACKING] WHERE [tblACTIONS_StatTRACKING].[ID] = [Forms].[sbfrmSTATUS].[ID])")

I don't think it's a problem with the code itself, because it works when I use it on the main form.

Any ideas on what could cause it to not work when placed in the subform?

 

[allan57]

Your not using the DLookup in the right context, examples of usage:-

Normal usage

For numerical values:
DLookup("FieldName" , "TableName" , "Criteria = n")

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #date#")


Refering to a form control

For numerical values:
DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)

For strings: (note the apostrophe before and after the value)
DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")

dates:
DLookup("FieldName", "TableName", "Criteria = #" & forms!FormName!ControlName & "#")


Mix-n-Match

DLookup("FieldName", "TableName", "Criteria1 = " & Forms!FormName!Control1 _
& " AND Criteria2 = '" & Forms!FormName!Control2 & "'" _
& " AND Criteria3 =#" & Forms!FormName!Control3 & "#")

 

[DKO]

Disregard - I spotted the problem.

Changed the ending to:

...Forms!frmACTIONS.sbfrmSTATUS!ID

Hopefully this thread might at least help someone else. lol

 

[DKO]

Your not using the DLookup in the right context, examples of usage:-

Normal usage

For numerical values:
DLookup("FieldName" , "TableName" , "Criteria = n")

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #date#")


Refering to a form control

For numerical values:
DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)

For strings: (note the apostrophe before and after the value)
DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")

dates:
DLookup("FieldName", "TableName", "Criteria = #" & forms!FormName!ControlName & "#")


Mix-n-Match

DLookup("FieldName", "TableName", "Criteria1 = " & Forms!FormName!Control1 _
& " AND Criteria2 = '" & Forms!FormName!Control2 & "'" _
& " AND Criteria3 =#" & Forms!FormName!Control3 & "#")

Thanks for the reply...

In my "ACTIONS" db - I have one table named "tblSTATUS" that contains all the possible status options for each action, Then a separate table named "tblACTIONS_StatTRACKING" in which the status history for each action is stored as a number value that corresponds to the "ID" field in the "tblSTATUS" table.

My form is based on another table - "tblACTIONS", in which all the other info for each action is stored.

The subform is based on the "tblACTIONS_StatTRACKING" table.

Every time the user updates the "CURRENT" status field on the subform - the previous status is sent to 1 of 10 previous status fields.

I'm trying to display those previous status fields as text boxes, displaying the actual status from the "tblSTATUS" table instead of the corresponding number that is stored in the "tblACTIONS_StatTRACKING".

Is there a better way to accomplish this?

It's working, now that I fixed that little bit.

But, would I be better off just basing the subform on a query and forgetting about the DLookup?

I'm trying to keep the database as compact as possible.

Thanks again.

 

[allan57]

It would be faster to base the subform on a query, as using a lot of dlookups can slow your database down.

 

[jdcallejas]

Ok, what the heck am I doing wrong!!!!!!!

I have a FORM Payments and a subFORM PaymentDetail...

I have an unbound field on that SUBform that I want it to look up a number on a query called invtotalpayments...

I put this:
DLookup("SumOfAmount", "qryInvoiceTotalPayments", "InvID = " & forms!PaymentDetail subform!InvID)

AND IT DOES NOT WORK!!! AM ABOUT TO SMASHMY COMP!!!!!!

hELP ME!!!

 

[boblarson]

Okay, if the control is on the subform where you also have InvID then you don't need some of what you've got and you possibly need something else.

1. The control source of the text box should be
=DLookup("SumOfAmount", "qryInvoiceTotalPayments", "InvID = " & [InvID])
because we are talking the same form as the field is on.

2. If InvID is TEXT instead of numeric then it would be
=DLookup("SumOfAmount", "qryInvoiceTotalPayments", "InvID = " & Chr(34) & [InvID] & Chr(34))

 

[jdcallejas]

Thanks for the response, still not working...

I am sending you the DB so you can see what its doing..

If you look at the query qryInvTotalPayments and run it you will see that I have an amount of $400.00. Now if you goto the form Payments and make a payment on Apartment #2 You will be able to select the Invoice # on the subform Inv# dropdown. Once you select this I should be able th get the $400 to show on the previous payments for that invoice. This way I can get a balance due....

Thanks for the help,

Daniel

 

[boblarson]

 

[boblarson]

Gotta go to bed. Unfortunately I don't have Access 2007 at work, so it will be a fair number of hours before I will get to revisit this. Hopefully someone else will in the meantime.

 

[jdcallejas]

Thanks Bob,

I think your right, am going to give it a rest!!! I fixed wuth didnt make sence to you. Basicaly the Aptnum is related to a leaseid, the dateissued is just when they made the lease and the rent is how much is the monthly rent. I did this so I could only see Apts or better say leases that have an invoice created to them.. Also it shows me only the leaes that are active..

I pretty much have it working ok, the only thing is the previous payment that is killing me right now... The reason I want to see the previous is so I can calculate the balance due on that invoice and be able to put what is due on the payment.

Once the amount of payments is equal to the amount of the invoice the inv is marked Paid..

Thanks again for your fast response,

Daniel

 

[Moses76]

I think I have the opposite of this problem . When I open the main form the dlookup on the subform does not work. Any ideas how I can fix that ?

Moses

 

[Moses76]

Disregard - I spotted the problem.

Changed the ending to:



Hopefully this thread might at least help someone else. lol

My issue is this - works on subform in isolation ..meaning on its own. Does not work when I use the subform in the main form. Any ideas ?