Access Lookup / Query problem

[Guest]

I have encountered the folloowing problem in Access. I have a table of stock
items, PartID, PartName etc. I have a second table, INVENTORY which reflects
these stock items booked into the system TransactionID, PartName, DateCreated
Allocated etc., when booking in an item the user selects the stock item from
a lookup field pointing back to the stock items table. This part works fine.
I have set up a third table / Form for entering orders. The user adds the
customer details etc. and selects the items for the order from another lookup
which is linked to a query. I am encountering the problem of getting the ID
rather than the PartName value returned by the lookup, I read through
previous post and understand why this is happening but I am a real novice at
access and dont understand how to avoid this problem.
I was alsowondering how to change the boolean value of the Allocated field
when a Part is added to an order?

Any help would be greatly appreciated.

 

[Guest]

Hi there

To return the correct field you need to look at the Bound Column property -
this should reflect which column in your Row Source has the ID information
counting from left to right. Can't help with Boolean I'm afraid. HTH

Sheila

 

[Guest]

Thanks Shiela, Ill give that a go.

Hi there

To return the correct field you need to look at the Bound Column property -
this should reflect which column in your Row Source has the ID information
counting from left to right. Can't help with Boolean I'm afraid. HTH

Sheila

 

[aaron.kempf]

mdb queries are crap

use sql server or msde with access data projects

much better implementation

 

[darkroomdevil via AccessMonster.com]

Hi, I am confused but maybe can help ....

I have encountered the folloowing problem in Access. I have a table of stock
items, PartID, PartName etc. I have a second table, INVENTORY which reflects
these stock items booked into the system TransactionID, PartName, DateCreated
Allocated etc., when booking in an item the user selects the stock item from
a lookup field pointing back to the stock items table. This part works fine.
I have set up a third table / Form for entering orders. The user adds the
customer details etc. and selects the items for the order from another lookup
which is linked to a query. I am encountering the problem of getting the ID
rather than the PartName value returned by the lookup, I read through
previous post and understand why this is happening but I am a real novice at
access and dont understand how to avoid this problem.

I assume you mean PartID when you say ID. The PartID is the Field that you
want the control to be bound to that the user is setting - but you want to
display PartName? Here is one easy way - use a combo box. You can use SQL
or a table or a query as the record source of the combo box. If the first
field in the record source is PartID and the second field is PartName then
you would set these combo box properties:

Control Source '= PartID
Row SOurce Type = Table/Query
Bound Column = 1
Column Count = 2
Column Widths = 2";0"

I was alsowondering how to change the boolean value of the Allocated field
when a Part is added to an order?

A boolean value is Yes/No - Yes/True = -1, No/False = 0

Does this mean each item in stock is a separate record? Rather than an entry
for the stock item with a quantity field?

Any help would be greatly appreciated.

Hope this is of help,
Roger