Solved I need help in DLookup

[Khurramshaikh47]

Hi,
I am using DLookup in Microsoft Visual Basic for Applications,
I actually want to get the values of 3 fields:
1) COACODE
2) AccountGroup
3) AccountType
in a form, from a query "COACODE".

Visual Basic Code is:

Private Sub AccountGroupID_AfterUpdate()
Me.COACODE.Value = DLookup("[COACODE]", "COACODE", "[ID]=" & Me.ID)
Me.AccountGroup.Value = DLookup("[GroupName]", "COACODE", "[ID]=" & Me.ID)
Me.AccountType.Value = DLookup("[AccountType]", "COACODE", "[ID]=" & Me.ID)
End Sub

The field COACODE and AccountGroup is appearing in my form. But instead of AccountType its ID is showing.
Please see the screenshot:

Let me tell you about the tables:
AccountType is a Table including 2 columns:

AccountGroup is another Table including 3 columns:

in AccountGroup table the AccountType column is a lookup field from AccountType table.

Thank you in advance to the experts for their precious knowledge.

 

[June7]

Why are you using DLookup() to pull data to form?

I never build lookups in table.

Make the AccountGroupID combobox RowSource a query that joins AccountType and AccountGroup tables and include the AccountType field. Expression in AccountType textbox can reference combobox column by its index to display the descriptive AccountType value. If data is in column 3, its index is 2. Something like:

=[cbxAcctGrp].Column(2)

 

[Khurramshaikh47]

Why are you using DLookup() to pull data to form?

I never build lookups in table.

Make the AccountGroupID combobox RowSource a query that joins AccountType and AccountGroup tables and include the AccountType field. Expression in AccountType textbox can reference combobox column by its index to display the descriptive AccountType value. If data is in column 3, its index is 2. Something like:

=[cbxAcctGrp].Column(2)

Hi, june7,
Thank you so much dear,
Can you please explain it through screenshots?

 

[June7]

I rather you follow my instructions and make attempt. Exactly what do you not understand?

 

[arnelgp]

you try this:

Me.AccountType.Value = DLookup("AccountType", "AccountType", "AccountID = " & DLookup("[AccountType]", "COACODE", "[ID]=" & Me.ID))

 

[June7]

Arnel, are you advising OP to replicate AccountType into ChartOfAccount table?

 

[arnelgp]

Arnel, are you advising OP to replicate AccountType into ChartOfAccount table?

the "last" db he uploaded has that field on AccountType table.

 

[Khurramshaikh47]

you try this:

Me.AccountType.Value = DLookup("AccountType", "AccountType", "AccountID = " & DLookup("[AccountType]", "COACODE", "[ID]=" & Me.ID))

Arnel this error appeared

 

[arnelgp]

check the Spelling of DLookup (the last dlookup has double "u")

 

[Khurramshaikh47]

check the Spelling of DLookup (the last dlookup has double "u")

Can I use this code for also other 2 fields? Like COACODE and AccountGroup etc?

 

[Khurramshaikh47]

check the Spelling of DLookup (the last dlookup has double "u")

Thank you so much it's working perfectly.

 

[Khurramshaikh47]

you try this:

Me.AccountType.Value = DLookup("AccountType", "AccountType", "AccountID = " & DLookup("[AccountType]", "COACODE", "[ID]=" & Me.ID))

I am still facing a problem, when I am selecting AccountGroup from AccountGroupID field an error occurs:

Then I am clicking end, Nothing is appearing in COACODE and AccountType:

When I am clicking on Debug then

 

[arnelgp]

use Nz() function:

Me.AccountType.Value = DLookup("AccountType", "AccountType", "AccountID = " & Nz(DLookup("[AccountType]", "COACODE", "[ID]=" & Nz(Me.ID, 0)), 0))

 

[Khurramshaikh47]

use Nz() function:

Me.AccountType.Value = DLookup("AccountType", "AccountType", "AccountID = " & Nz(DLookup("[AccountType]", "COACODE", "[ID]=" & Nz(Me.ID, 0)), 0))

But I have to select AccountGroupID twice then AccountType appears:

After Second Selection it appears:

 

[June7]

This is still replicating AccountType into ChartOfAccount table which is not good RDBMS design. If you designed combobox and used textbox expression as I suggested, no VBA would be needed and data structure would be normalized. It would eliminate AccountType field from ChartOfAccount.

 

[Pat Hartman]

Here's a sample that might solve the problem

Cascading Combos in continuous form

This is an example that shows how to work with cascading combos in a continuous form. This technique will NOT work for a form in datasheet view. Here's an update.

www.access-programmers.co.uk

 

[Khurramshaikh47]

Here's a sample that might solve the problem

Cascading Combos in continuous form

This is an example that shows how to work with cascading combos in a continuous form. This technique will NOT work for a form in datasheet view. Here's an update.

www.access-programmers.co.uk

Thanks for sharing Pat, I tried but could not understand this.
Can you guide me through simple steps? According to my database?

 

[arnelgp]

can you upload a sample db?

 

[Khurramshaikh47]

can you upload a sample db?

Sure

 

[ebs17]

I would say think twice about everything you do. Start your application from scratch, because it already has a lot of bugs and problems, although it is not yet a real application.

I need help in DLookup

DLookup("COACODE", "COACODE", "ID = 87")
SELECT COACODE FROM COACODE WHERE ID = 87

As you can see very easily, a DLookup is a complete query encapsulated in an Access function. It returns exactly one return value.
Answer the question why single values should be fetched to DLookups by continuous fire when you can do that with a query.
I also find it problematic that a field is named exactly like a table/query. This may cause errors, but in any case it interferes with the readability and comprehensibility of codes.

SELECT COACODE, GroupName, AccountType FROM COACODE WHERE ID = 87
' ----------------------------------------------------------------------------------------------
SELECT COACODE, GroupName, AccountType FROM COACODE

You can easily see that you can get more than one value with one query, without filtering all values at once.
So why should there be a constant barrage of individual queries, where COACODE is a query and could/would actually be re-executed each time the query was executed (DLookup call). Technically, this is a disaster, and if the amount of data is large enough, this effort will lead to lousy performance.

If you can lookup individual values using DLookup's, you can do a full query on anything.

Furthermore, values are fetched there in order to rewrite them in a table. This is a gross violation of database rules (normalization, data modeling). To avoid redundancies and disturbances in data integrity, a value of an attribute is stored exactly once in one place. To use this value elsewhere, use references (ID's) to this value.

In summary, if you want to create a real database application and don't just want to pass the time with some games: Learn the basics of a database and start over.