A really easy one that's driving my up the wall... dlookup

[calum112 via AccessMonster.com]

I've been patient with this one as I managed to solve my last one on my own,
straight after posting it. However nothing I can think of / try works and I'm
sure what I've got it right (although it's obviously not).

It's pretty simple:

Table 1 called ' table1' has two fields 'companyid' and 'manufacturer'

Table 2 called 'table2' also has the field 'companyid' of which relates each
record to one/more manufacturers, through the above table.


I want to be able to filter table2 to show all of the records relating to a
certain manufacturer.

There is a text box (manufacturerlist) in the footer of table where you type
the manufacturer and a go button next to it to activate the filter.
I've put this code in the 'where' condition for the filter:

[Forms]![table2]![companyid]=DLookUp ([companyid],"table1","[table1]!
[manufacturer]=Forms![table2]![manufacturerlist]")

So it shows all records in table2 where the companyids match table1 for that
manufacturer.

Yet it doesn't work; if manufacturerlist and table1 have nothing in common it
doesn't show any reocrds (correct) however if there is one or more in common
then it just shwos record one in table2, regardless if what is chosen.

 

[dcichelli]

I've been patient with this one as I managed to solve my last one on my own,
straight after posting it. However nothing I can think of / try works and I'm
sure what I've got it right (although it's obviously not).

It's pretty simple:

Table 1 called ' table1' has two fields 'companyid' and 'manufacturer'

Table 2 called 'table2' also has the field 'companyid' of which relates each
record to one/more manufacturers, through the above table.

I want to be able to filter table2 to show all of the records relating to a
certain manufacturer.

There is a text box (manufacturerlist) in the footer of table where you type
the manufacturer and a go button next to it to activate the filter.
I've put this code in the 'where' condition for the filter:

[Forms]![table2]![companyid]=DLookUp ([companyid],"table1","[table1]!
[manufacturer]=Forms![table2]![manufacturerlist]")

So it shows all records in table2 where the companyids match table1 for that
manufacturer.

Yet it doesn't work; if manufacturerlist and table1 have nothing in common it
doesn't show any reocrds (correct) however if there is one or more in common
then it just shwos record one in table2, regardless if what is chosen.

Hi there -

I think your table design/ relationships is the problem.

Let me know if this is incorrect: A company can have many
manufacturers and a manufacturer can service many companies.

If the above statement is correct, then you have a classic many-to-
many relationship and therefore you are missing a table.

You need three tables:

Table 1: Company

CompanyID - autonumber
Companyname - text
CompanyInfo - memo
etc...

Table 2: Manufacturers

ManufacturerID - autonumber
ManufacturerName - text
ManufacturerContact - text
etc...

Table 3: CompanyManufacturers

CompManuID - autonumber
Company - lookup field to company.companyid
Manufacturer - lookup field to manufacturers.manufacturerID
Notes - memo field about how the company has worked with the
manufacturer
etc...



If I am mistaken please let me know.

Diane

 

[calum112 via AccessMonster.com]

Spot on, see below for my actual database design:

http://www.websitedragon.com/untitled.GIF

table1=distributorsmanufacturerstbl
table2=companiestbl

I haven't used a 'manufacturerid' autonumber field though, if that makes a
difference?

I'm thinking you maybe right about the problem laying with my design; I tried
doing the above using a query however that doesn't work either. It just seems
to ignore the query and prompts me for all the values it should be obtaining
from it. Something isn't right...

Cheers

 

[dcichelli]

Spot on, see below for my actual database design:

http://www.websitedragon.com/untitled.GIF

table1=distributorsmanufacturerstbl
table2=companiestbl

I haven't used a 'manufacturerid' autonumber field though, if that makes a
difference?

I'm thinking you maybe right about the problem laying with my design; I tried
doing the above using a query however that doesn't work either. It just seems
to ignore the query and prompts me for all the values it should be obtaining
from it. Something isn't right...

Cheers

Yeah - you need to add a unique primary key to
"distributorsmanufacturerstbl"

and add one to "manufacturerstbl."


Your relationships are correct except with your new keys the
distributorsmanufacturerstbl.manufacturerID will join on
manufactuers.manufacturerID

what kind of query are you trying to run?

Diane

 

[calum112 via AccessMonster.com]

Thanks for the help, it's still throwing a wobbly though .
I created the manufacturerID in both tables and used that for the
relationship. This caused the problem that the manufacturer's name is no
longer in the table distributorsmanufacturerstbl so I have to put the
manufacturerID in my search box instead of the actual name (although that can
be worked around by using a lookup I guess).
But anyway, it is still doing the exact same thing: If I put nothing /
something random in to my search field then nothing is shown. If I put a
genuine manufacturerID then it still only shows record one in 'companiestbl'?
This seems strange...?


The Query I made included both fields in 'distributorsmanufacturerstbl' and
then just put a criteria on the manufacturers name so it would only show
those where the name matched what I put in the search field in my main form.
This worked fine. Then for my filter in the main form I set it so that it
would only display the companies whose ID was listed in the query. This
should have worked, yes? However everytime I run the query it prompts me for
the companyID (which should have been obtained from the query)???
Oh and the text for the prompt is the first record's companyname, if that
helps?


Access seems to have a spanner in its works?

Spot on, see below for my actual database design:

[quoted text clipped - 15 lines]
Yeah - you need to add a unique primary key to
"distributorsmanufacturerstbl"

and add one to "manufacturerstbl."

Your relationships are correct except with your new keys the
distributorsmanufacturerstbl.manufacturerID will join on
manufactuers.manufacturerID

what kind of query are you trying to run?

Diane

 

[calum112 via AccessMonster.com]

I've just been informed that dlookup will only return a single value, could
this be what is causing the problems?
I have tried coding it using VBA but am having trouble storing the companyids,
to use fo the filter.