Apache OpenOffice Community Forum

Objective
I'm implementing a database to track receipts (and shipments, among other things) of unique devices. A device may be received many times (provided that it's shipped out between the receipts), and a receipt can include many devices. Often, the received devices are already in the database. Sometimes, however, a batch of brand-new devices are received from the factory that need to be entered into the database for the first time. I hoping to be able to create new device entries for those such devices by simply logging the receipt, instead of having to create new device entries first and then put them on a receipt entry. My main motivation for this database (the first such project ever for me) is to reduce redundancy, which not only wastes time and often leads to errors.

Work so far
I use the following schema (truncated to stay on point):
With the use of list boxes in the subform to the receipt form, I'm able to view received devices by model_name and serial_number, as desired.

Problems
1. I can't add a brand-new device to a receipt. I can only choose from devices already in the database.
2. Even when I'm choosing an existing device for a receipt, my current solution is clunky. Because the underlying data for each subform item is the device_id, the list box contains lots of repeated model names. For example, typing "a" would pull up "AA, AA, AA, AA, AA, AA, ... " instead of the more sensible "AA, AB, AC, ..."

Questions
1. Is it possible for me to add brand-new devices in the receipt form? (This would require creating not only new entries in the "receipt_device" table, but also the "device" table as well.)
2. If that's not possible, I can imagine creating a new special form for receipt of brand-new devices, but I'm not sure how exactly. Would this work?
3. How can I make choosing from an existing device more user friendly. I think using model_id and serial_number as (composite) primary key would help here, but might bring up unforeseen problems since the use of a surrogate key as I have is usually recommended in this situation. Is there a better solution?

Thank you.

PS I have read Villeroy's informative post: Relations reflected by list boxes in forms, but I don't think it answers my questions.

S I have read Villeroy's informative post: Relations reflected by list boxes in forms, but I don't think it answers my questions.

1) Just add an additional main form (not subform) where you can enter the required information for a new device (set property "new records only"). After that, you can refresh the list box of your data form without leaving the current record. This is what the second refresh button on the navigation toolbar does.
download/file.php?id=21160 (genres to which you can assign movies and add new movies, movies to which you can assign genres and add new genres).

2) "AA, AA, AA, AA, AA, AA, ". This deserves a separate table of models with distinct model names and IDs.

Thanks, Villeroy. I will look into your solution (1) as soon as I get a chance. However, your point (2) puzzles me. I already have a table with distinct model names and ID (see schema in original post), which I join to the device table in order to be able to see model names at all. However, the "AA, AA, AA, AA, AA, AA, ..." seems the inevitable by-product of using "device id" as the identifier in the subform. I don't see how "model id" can be used here instead. If there's a way, could you please elaborate? Thank you.