Noob form/query problem

[Tinbendr]

Trying to get my head around Access. I'm fairly competent in Word/Excel, but slogging through Access.

In the attached db, I'm trying to get Description of the Part Number to display dynamically in the Form, instead of storing each desc.

In Frm_SN_Assigned, I can correctly get the descriptions to show as desired, but I can't enter new records and the sort order is wrong. It has to be sorted by SerialNumber.

In Frm_SN_Assigned2, I can't get the desc to appear (?NAME error), but the sort is correct.

If I run the query linked to the Form2, manually enter the part number, I get the correct result, but can't seem to get that to show in the form.

Thanks in advance.

[OBP]

Tinbendr, there are is a slight design problem with your tabels and Relationships. First of all in the "tbl_SN_Assigned" you do not want to store the Part Number, only it's ID, that is the point of "Relational" databases, the data only goes in once and then is Referred to by the relationship that you create which is usually with the Autonumber ID field. To make this happen your "PartNum" field in the "tbl_SN_Assigned" table has to be a Type = Number to match up with the Autonumber ID.
In the Relationships you need what is known as a One to Many relationship, One Part Number in the Part Number table can have Many records in the "tbl_SN_Assigned" table using the Part ID.
Once you have established this relationship you then use a Query (tbl_PNDesc Query) to "join" the 2 tables of data together and use that as the basis of your Form.
I have also added a Combo Box to your form so that you only need to select the Part for each record, this Combo's "Control Source" is the "tbl_SN_Assigned" table's PartNum field. It's "Data Source" for it's List is your qry_PNDesc Query.
So in a new record when you select a Part it puts it's ID in the PartNum field.

To get whatever Sort order you want for your Form you use the Sort facility of the tbl_PNDesc Query, remember it sorts by as many fields (columns) as you want with the left most taking precedence.

So here is the revised version.

[OBP]

I have also attached a short summary about Tables, Queries and Relationships which I wrote for soemone else.

[Tinbendr]

Ok thanks!

Still very strange to me, but I think I get it.


First of all in the "tbl_SN_Assigned" you do not want to store the Part Number, only it's ID
Is the significance of only storing the id a matter of space/efficiency?

Can the comboboxes be disabled until the new record is added? Because once the serial number is assigned, nothing in the record can be changed.

Ideally, I would like the top line to be the add line, and another window below for viewing.

For lack of a better desciption, a text box line (with a listbox for picking part numbers) to enter data, and a listbox for viewing recent records.

[OBP]

What you are describing is a Main and Subform. On the mainform you just have your Part Number details, (Number & Description) with a Combo to select them.
Then you have the "tbl_SN_Assigned" data on a separate form which is placed on the mainform as a Subform. The 2 forms are linked via the Part IDs.
You are correct that space and memory considerations are involved with the use of the ID number, but it is also the main reason for having the ID number in the first place.
Do you need me to create and add the Subform or do you think that you can do that?
If you do have a problem just let me know.

[Tinbendr]

Do you need me to create and add the Subform... Let me try. I don't just want a solution for this, I want to learn it. I have many more Excel sheets I'd like to port over.

I'll post back if, I mean, when I have more questions.

Thanks!