Need guidance on something that seems simple

[dluga20]

We work on large scale engines and parts. there are 4 main stages: receiving, inspection, repair/machining, and shipping.

1. We receive parts to go into temporary storage before inspecting them
2. We enter the bulk number of specific parts into the form and it adds that quantity to the table for those parts
3. Each of these part table IDs will be 6 digits, they cannot duplicate in that table, but will duplicate across tables
4. There needs to be some kind of calculated/lookup/concatenated field in another table for a full job number/serial number to be unique.
5. This would then generate the ID (this is for the label sticker) and would automatically set the condition of the part to "Pending Inspection" by default
6. It seems from that point a query would have to be created to identify all parts that have a condition set to "Pending Inspection" that would have to be updated with condition, repair notes, and other descriptive information.

This process has been being done almost exclusively through Excel, Acrobat, and LiveCycle - I cant stand that anymore, so I am trying to pickup on Access.

If you want to switch your process to use Access then you need to change your paradigm about how it works. The way you are trying to use it is pretty far off from how it is designed to be used and there is probably a much better way to do it. You said you ordered a book on Access programming but I would also spend a lot of time understanding database design and normalization. There are tons of youtube videos on this if you aren't already taking advantage of those.

I am far from an expert with access myself, but I remember going through the same process of trying to treat Access as if it were Excel. The way databases work goes against what people are usually used to for managing data.

My recommendation: Keep learning Access, study normalization, and play around with some different designs until you have an understanding of how access should be used. Once you have that figured out, THEN work to put together a database for your company. Point being..I feel like you are trying to solve your problem before you understand the tool you are using to solve it.

[dluga20]

However, this "("tbl" & <Name Of Your ComboBox>)" really seems to give some ideas for both creating tables via a form and possibly generating the fields via a user's form input. Of course that's beyond the current scope, but something i really want to think about because i don't think that a DB should have to be constantly altered manually like that.

Did you try the code I posted? It should not create tables from the form. It will only add records (rows) to existing tables...

[Locke_Valken]

Did you try the code I posted? It should not create tables from the form. It will only add records (rows) to existing tables...

It gives an error because the drop down is pulling the "ID" as the value and not the "text" that correlates with the table name.

With "Heads" selected in the drop down it will return a "1" instead of "Heads", this code sets the table to "tbl1" instead of "tblHeads". I really cant imagine that setting up an if statement here would be the most efficient solution for this, but that could be done as well, no?

Set rs = db.OpenRecordset("tbl" & <Name Of Your ComboBox>.Value)

Did I miss something setting up that table or will a drop down always return the ID as .value?

Edit: After looking into .value and how it works, I had the wrong idea about how these fields/controls worked. I also just realized there is a different between both controls and fields .

It is a very simple table with 2 fields:

ID Type Name
1 Heads
2 Liners
3 Rods
4 Bonnets
5 Pumps
etc etc

ok, that was easy... ".text" solved that... but have to research "setting focus"

Ok, so it works! Amazing

Private Sub btnSubmittoDB_Click()    
    Set db = CurrentDb
    Me.drdType.SetFocus
    Set rs = db.OpenRecordset("tbl" & drdType.Text)


    For i = 1 To Me.txtQuantity.Value
    rs.AddNew
    rs.Update
Next


    MsgBox ("You are submitting " & txtQuantity & " to Pending Inspections for the " & drdType.Text & " Queue.")


End Sub