DLookup

[smidgey]

Hi all,

I have a form named frm_dataentry

On the form I have a field called Product_Number



A field called Product_Name

When the user enters the product number the other field populate using a d look up.

=DLookUp("Product_Name","tbl_Products","Product_Number'")

This worked okay but now when adding a new record the first product in the products table populates automatically even though the product number field is blank?

What is the correct code so that the field is blank when no product number is entered?

many thanks in adavnce

Chris

[RuralGuy]

It can all be done with a query as the RecordSource of your form if there is a relationship. You should not need to use DLookup's to complete your form if it is a bound form. Is it?

[smidgey]

Thanks for the reply RuralGuy.

I have never used access before so I am a complete newbie to it. How do I tell if it's bound? The forms source is the main data table.

[RuralGuy]

What relationship does your MainData table have with the tbl_Products table? Do they both have PrimaryKey fields and are they AutoNumbers?

[smidgey]

They currently have no relationship. The only field that could link the two is Product_Number.

The primary key field in the products table is the Product_Number field (contains 12,000+ products)

The primary key on the MainData table is set to a field called form_number - this is taken from the paper forms we use as they have carbon copies.

[RuralGuy]

Try using the Relationship window and joining those two tables on the Product_Number field. I assume the MainData table has this value in one of the fields. It is a Text field, right?

[smidgey]

Okay they are now joined. Yes they are text in both tables as alot of the product numbers begin with 0.

So now I need to create a query to obtain this information from the product_table?

[smidgey]

Okay the forms source is now the query and it works when putting the product number in, it pulls up the product name.

However when adding the record it doesn't write the product name MainData table?

[RuralGuy]

Pretty much. Create a query with the query builder and add the two tables to the grid. Because you have defined the relationship, the grid will put in the Join line. You did turn on Referential Integrity right? I do not use cascade updates or deletes as they are simply too powerful and can get you into trouble too quickly. Now bring down the fields you need from *both* tables. You should not be using any Lookup Fields. Are you? http://www.mvps.org/access/lookupfields.htm

[RuralGuy]

Okay the forms source is now the query and it works when putting the product number in, it pulls up the product name.

However when adding the record it doesn't write the product name MainData table?

You do *not* want that data in more that one table. All that is needed is the [product number]. you can always get the name with a query.

[smidgey]

Ahhhh right! I get you!

I am using combo box lookups. This is bad??

[RuralGuy]

On a form, NO...in a table, YES.

[smidgey]

Okay so I need to correct this in the table. What is the best way to action these? I have one which is a list of members of staff.

[RuralGuy]

http://www.btabdevelopment.com/ts/removelookups