Errors and logic in database

[bdv]

hello all,
i am trying to develop a database for a friend's dispensary in ms access 2007.

the dbase should allow him to input new drugs and sell drugs. the input is fine. when trying to sell the items, an "#Error" appears in the last row of the form. in the QOH, qty out and extended price fields. and sometimes a message pops up saying " Index or primary key cannot contain a null".

i am at loss. been trying to solve this for 2 weeks now. no luck.
i've attached the whole dbase. can anyone take a look and tell me what's wrong?

 

[RuralGuy]

How about if we start off with a link to read?
The Evils of Lookup Fields in Tables

 

[bdv]

Hello Rural Guy,
Thanks for the URL. The information just tells me lookup tables are not worth the trouble. If not lookups, then what? What are my options. the #Error is generated in the last row that is is generated when the preceeding row is filled.
That's what happens when the default view is set to continuous forms. Is there a way that a row can be created only after the Tab or Enter key is pressed after finishing entries in a row?

If anyone can take a look at the database in my initial and give me some feedback.
Thanks so much in advance.

 

[Funkyaccess]

Thanks for the tip on lookups.

 

[mcclunyboy]

I always knew Lookup tables were criticised but what do we use instead.

For example I have a table containing courses - with locations in a lookup table - a combo box on the form assigns the location to the course - what do i do instead?

Value Lists on combo-boxes???

 

[Scooterbug]

I always knew Lookup tables were criticised but what do we use instead.

For example I have a table containing courses - with locations in a lookup table - a combo box on the form assigns the location to the course - what do i do instead?

Value Lists on combo-boxes???

The values you want to be able to lookup you place in it's own table. You can then use a combo box to list the information, and store the ID number of that row in it's place.

So, for example:

tblTable1 (Stores the data you wish to lookup)
PrimaryKeyID
Value

tblTable2
PrimaryKeyFromTblTable1 (This is called a Foreign Key)


You can now establish a relationship between tblTable1 and tblTable2. You could create a query and include both tables. This way you can display the Value from table1 along with all the data from table2.

 

[mcclunyboy]

Oh thats what I do maybe I am misunderstanding what is classed as a lookup...

At uni I was taught to use value lists - not a long course so its fine...

At work I use lookup tables as you posted, I may not define the field as a primary key but generally I do use it as a foreign key in the "main" tables.

For example I have 4 simple tables in a database to hold PC information - tblOS, tblModel, tblApplication and tblPCInfo. TBLPCInfo is the main table and the rest are lookup...mostly semi-static with forms created to add/delete items from those tables too...anyway I thought I was doing something wrong with that type of relationship..

Anyway back on topic

 

[RuralGuy]

The article dislikes Lookup FIELDS in tables and not Lookup Tables. Lookup tables are just fine when used as the RowSource of a ComboBox on a form.