Dlookup From Text Field in Table

[sufianatan]

I'm desperately asking for help now.

So, this is my table:
TBL_COA_Detail
Field name: Account_Detail_No
Field type: Text
Field name: Account_Detail_Name
Field type: Text

And I have a form named SFRM_SalesDetail_Batam with a combo box field named AccNo_Debit
So basically this combo box is to choose the account no from TBL_Account_Detail

Now, I want to have a dlookup field in this form, which can automatically show the account name based on whatever I choose in the combo box.

For example, data in my table is:
Account_Detail_No : 1001
Account_Detail_Name : Cash in Bank

So in my form SFRM_SalesDetail_Batam , in the combo box AccNo_Debit , I choose 1001.
There's a field that will automatically shows "Cash in Bank"

I've tried Dlookup with this formula :
DlookUp("[Account_Detail_Name];[TBL_COA_Detail];"[Account_Detail_No] = ' " & [AccNo_Debit] & "'")

But the result is blank.

I've also tried this formula :
DlookUp("[Account_Detail_Name];[TBL_COA_Detail];"[AccNoDebit] = ' " & [TBL_COA_Detail].[Account_Detail_No] & "'")

But the result is #NAME?

Can anyone please help me? I've searched for the Dlookup formula for string value and tried, but failed.

Please help me, I'm making an accounting system for a company as my final assignment in my campus. So, please help me. Deadline is coming.
:banghead: :banghead: :banghead:

 

[pbaldy]

This would be more efficient:

http://www.baldyweb.com/Autofill.htm

Your error is because each argument should be enclosed in quotes.

 

[Pat Hartman]

There isn't any need to do a dLookup(), you can easily change your combo to show both strings or to show the description in a second box if you want to. Change the RowSource of the combo to:

Select Account_Detail_No, Account_Detail_No & "--" & Account_Detail_Name
From TBL_COA_Detail
Order By Account_Detail_No

Make sure you change the column count to 2 and leave the bound column as 1. Change the column widths to 0", 2". This final change will show the concatenated value in the combo.

If you really want the two fields to be in separate controls, then change the RowSource of the combo to:

Select Account_Detail_No, Account_Detail_Name
From TBL_COA_Detail
Order By Account_Detail_No

The column count will be 2, the bound column will be 1 and the column widths will be 2", 0" to show the first field but hide the second. Then in the AfterUpdate event of the combo, copy the name field to the other control

Me.AccountName = Me.Combo.Column(1)

The columns of the combo's rowsource are a zero based array so .Column(1) actually refers to the second column of the RowSource.

 

[sufianatan]

Thank you!! It works!

 

[Pat Hartman]

Not clear what worked for you but you're welcome