Using D Lookup in a Subform in Access - HELP! 1

[csunsun]

In a subform, the user needs to pick one field, Contract #. After the user picks the Contract # then a few fields related to the Contract # need to be populated. I did a D Look Up after the "after update" event. From what I hear, looking up on a subform is different from doing a D Look Up on the main form.

Do you know the code? IS it still a D Look Up?

Thanks!!!!!!!!

 

[shannonp]

Is the value of [Contract #] coming from your Main Form or subform?

 

[csunsun]

It is coming from the subform.

Sorry for the delay, lots of meeting today!

Thanks!!

 

[csunsun]

This is the macro I built to execute after Contract # is selected, event "after update". After selecting a Contract # named Combo95 in the Admin Fees Detail subform, Vendor Name and Commodity Description that pertain to the Contract # need to be looked up in the Lookup Contract table and displayed on the subform.

Item: [Forms]![Admin Fees Detail subform]![Vendor Name]

Expression: DLookUp("[Vendor Name]","[Lookup Contract]","[Contract #] = Forms![Admin Fees Detail subform]![Combo95]&quot

Item: [Forms]![Admin Fees Detail subform]![Commodity Description]

Expression: DLookUp("[Commodity Description]","[Lookup Contract]","[Contract #] = Forms![Admin Fees Detail subform]![Combo95]&quot

Thanks for any help given!!!!

 

[shannonp]

It appears you are only populating values on the subform only...you shouldn't have to worry about referring to the subform explicitly although it won't make much difference. I've taken your code and taken out explicit referals in your subform.

Item: [Vendor Name]
Expression: DLookUp("[Vendor Name]","[Lookup Contract]","[Contract #] = " & [Combo95])

Item: [Commodity Description]
Expression: DLookUp("[Commodity Description]","[Lookup Contract]","[Contract #] = " & [Combo95])

If you still wanted to refer to the subform from the Main form this is how its full identifier would look:

Item: Forms![MyMainForm]![Admin Fees Detail subform].Form![Vendor Name]
Expression: DLookUp("[Vendor Name]","[Lookup Contract]","[Contract #] = " & Forms![MyMainForm]![Admin Fees Detail subform].Form![Combo95])

Item: Forms![MyMainForm]![Admin Fees Detail subform].Form![Commodity Description]
Expression: DLookUp("[Commodity Description]","[Lookup Contract]","[Contract #] = " & Forms![MyMainForm]![Admin Fees Detail subform].Form![Combo95])

Either of these methods will have identical results, although I would definitely go with the latter.

 

[csunsun]

I used the latter and it produced an error. It says the object I am referencing is not an OLE object.

I then used the first and the macro just halted.

Any other suggestions??

Thanks much!!

 

[shannonp]

hmmmmmm - try it again but this time instead of using a macro, use the 'Code Builder' and insert the code I've written below which is essentially the same code as above.

This example uses the After Update Event:
=========================
Private Sub Form_AfterUpdate()

[Vendor Name] = DLookUp("[Vendor Name]","[Lookup Contract]","[Contract #] = " & [Combo95])

[Commodity Description] = DLookUp("[Commodity Description]","[Lookup Contract]","[Contract #] = " & [Combo95])

End Sub
=========================

 

[csunsun]

Ok, I tried to stick this code in and got the following error:

Run-time error '64479':

The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'DN".

DN is the beginning of a series of Contract #'s.

I don't understand this error, nothing is populating!

Sorry for having such a high maintenance issue.

Thanks!

 

[shannonp]

hmmmmmmmmmm, how big is your DB? Can you send?...you can send to bopsuperman@hotmail.com if you like.

 

[csunsun]

Ok, I will email it.

"Admin Fees Tracking" is the main form.

"Admin Fees Detail subform" is the subform within that main form.

Upon opening the main form, the user will pick the Contract # in the subform and the next 8 fields in teh subform need to populate with information related to the contract #.

The event for "After Update" of Contract # is the last code you gave me. You can also try the macros I created (Contract Lookup).

Thanks!!!!

 

[shannonp]

I think I have it. My fault...I presumed that [Contract #] was a numeric value and now I see it is not...this means that we need to enclose the [Combo95] value between quotes (" &quot.

=========================
Private Sub Form_AfterUpdate()

[Vendor Name] = DLookUp("[Vendor Name]","[Lookup Contract]","[Contract #] = '" & [Combo95] & "'")

[Commodity Description] = DLookUp("[Commodity Description]","[Lookup Contract]","[Contract #] = '" & [Combo95] & "'")

End Sub
=========================

 

[shannonp]

Looking more closely at your code, the following code might be a better solution as it essentially processes (loads) the [Lookup Contracts] table once where as with Dlookup it needs to open and close each time Dlookup is executed:

Try it and see how it goes (it uses DAO references btw):

===============================
Private Sub Combo95_AfterUpdate()
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Lookup Contract] WHERE [Contract #] = '" & [Combo95] & "'&quot
With rst
[Vendor Name] = ![Vendor Name]
[Commodity Description] = ![Commodity Description]
[Effective Date] = ![Effective Date]
[Expiration Date] = ![Expiration Date]
[Rebate Cycle] = ![Rebate Cycle]
[Grace Period] = ![Grace Period]
[Admin Fee %] = ![Admin Fee %]
[Annual Spend] = ![Annual Spend]
End With

rst.Close
End Sub
===============================

 

[shannonp]

You may have noticed I'm not a great fan of Macro's where I'd much prefer to use standard VB coding conventions as macro's tend to be too restrictive for me, this is just preference more than anything else though...cheers and happy coding.

 

[csunsun]

thank you, thank you, thank you!!!!!!!!!!!!!!!!

do u ever have that feeling that you cannot figure something out (probably not)???

anyway, this was a great learning experience!!!

is there anything i can do to show my gratitude????

 

[shannonp]

hehe - the star you posted will be fine thanks . If you need further assitance I would be more than willing to help, just use the e-mail addy I gave you.

 

[csunsun]

thanks again!!!...i will try not to bug you too much in the future, knowing you are a guru!!!!

 

[csunsun]

another question!

after updating "Service Provider" in my main form, i need to update "Service Provider" in subform 1 and alsop update "Service Provider" in subform 2.

i thought this code would work:

Private Sub Service_Provider_AfterUpdate()

Forms![Data Entry Form]![Service Provider] = Forms![Work Orders subform]![Service Provider]

End Sub

thanks for any help!!

 

[DataFanatic]

Question regarding return value from DLookup.. The help states that DLookup will return NULL if no data is returned.

I have used the example: dim varx as Variant and then checked varx for NULL value. I can't seem to get the check to work. I want to direct the user to renter a correct value in the event of NULL.

Can anyone shed some light on the proper evaluation of varx being a null value.

Thanks