Need guidance on something that seems simple

[Locke_Valken]

I am not very experienced with Access, but it has been requested of me to create a DB that does a specific task that I cannot figure out how to do.



frmIntake/tblIntake (This form should effectively take "X" many of a product type we have in processing and create a record indicating the selected type and generate a/many Job Number/s)
Quantity = X
Type = Dropdown selection from another table (this works)
btnAddRecordsbyQuantityX = does not work

frmJobs/tblJobs
Job Number = short text, this is a custom setup similar to a sequential serial number system (also working)
Type = Dropdown selection
Condition = dropdown selection
Description = long text
btnUpdateRecord = works
btnAddNewRecord = works

Is there a non-vba method to achieve "Add X number of records based on type"?

[ranman256]

Access can do anything.
tho I dont understand your request or your problem.

give a more detailed explanation.

[Locke_Valken]

I need 12 new records generated in the tblHeads table - this gives the auto-generated unique IDs.

The drop down controls which table the new records need to be created in. In this case "Heads" Type is selected.

I assume a nested if/then is needed after the other tables are created (tblRods, tblBonnets, etc...)

[Locke_Valken]

Looks like i cant insert an image link without approval...

The Form seems simple:

frmIntake (Intake Form)
Type: (Heads, Rods, Bonnets, Pistons, etc) = Dropdown
Quantity: (user entry)
Button: btnSubmitNewRecords

tblHeads
ID: Custom ID/Serial
Condition: --
Description: --
etc...

tblRods
ID: Custom ID/Serial
Condition: --
Description: --
etc...

tblBonnets
ID: Custom ID/Serial
Condition: --
Description: --
etc...

I think i need the form's button to determine which table to add records to (via the Type dropdown), then the button's macro would add the records from the quantity field to that table in order to auto-generate the custom IDs. Although, im not sure how to get that to work. The macro builder's actions i see seem limited or I am not seeing something here.

Any advice is appreciated.

[RuralGuy]

You are really limiting yourself by asking for a Non-VBA solution. Good luck.

[pbaldy]

Looks like i cant insert an image link without approval...

It's an anti-spam setting. After a certain number of posts you won't need approval. I approved the post with the link.

[Locke_Valken]

You are really limiting yourself by asking for a Non-VBA solution.

I dont mind VBA if i can understand the code, but im only familiar with javascript (Adobe LiveCycle).

[orange]

I'd like to see the requirements in simple English. All I'm seeing is some rather disjointed text with nothing pulling it all together.

How about 5-6 lines of plain, simple English description of what you're trying to do?

[Locke_Valken]

If I could explain it without convoluting it, i would have already done so. The only thing i can do is re-iterate what I had already said in a different way and provide a more detailed image.

1. There is one form that contain only two fields (Type & Quantity)
2. There are four tables that contain the record information for each type of part (tblHeads, tblBonnets, tblRods, tblLiners)
3. The Type field in the form identifies which table to add the new records to (If Heads is selected then add "Quantity" field amount of records to tblHeads table).
4. The Quantity field identifies how many records to add to the selected Table via the Type field (dropdown as seen in the image).

For future reference, if i do have a question similar to this and this can now be understood, then it would not hurt to know how I am supposed to explain this properly.

[dluga20]

Here is the best I can come up with....

Add the following code on your button click event:

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

For i = 1 To Me.<Name of your Text Box>.Value
rs.AddNew
rs.Update
Next

[orange]

In your diagram you show Heads and 12? How does that add up to 20???

I would not add 12 records to a table in anticipation of going back and editing/updating the other fields in these existing records.

I would have a form and add/data enter the info you want for a specific record, then click an add button to add the record to the table.
It is not generally a good practice to put records in a table in anticipation of future work. Often called "placeholder records".



For many readers tblHeads, tblBonnets, tblRods, tblLiners is jargon-ese. (specific to you and your environment).
It sounds to me that you are in the engine rebuilding business or something closely associated with that.

Try a little harder to describe your business -just as you would tell your granny or a six year old.

Something along these lines (please update as required..)

We are a small business in the engine repair and rebuild business. We receive cylinder heads, cylinder liners, connecting rods and bonnets from individuals and repair businesses. If a part can be rebuilt..... If not we have suppliers to provide new parts....
We often receive random quantities of these materials to be reviewed, then repaired or rebuilt(or other...). We record the Parts in our database and evaluate each and determine what specifically must be done for each part received.....

Let's see the parts and the processes in context before building a form.

Good luck.

[Locke_Valken]

I took to the code builder already as it seems to be a lot more efficient. It is really difficult not knowing the proper syntax, declarations, variables, etc... I did get a message box to work telling the user that the entered amount of parts were being added to the specific table. Also ordered a book for Access programming.

Here is the best I can come up with....

Add the following code on your button click event:

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

For i = 1 To Me.<Name of your Text Box>.Value
rs.AddNew
rs.Update
Next

Yes!, thanks, this may not be the specific code but definitely helps me understand what direction to go. I was just hung up on this before i read your post: "("tbl" & <Name Of Your ComboBox>.Value)"

In your diagram you show Heads and 12? How does that add up to 20???

Because there were 4 pre-existing records. If I add 12 records and assign unique IDs to those, it should be 16 (crap, i had the spacing set wrong in photoshop and it pushed the numbers too close - it would be 16).

I would not add 12 records to a table in anticipation of going back and editing/updating the other fields in these existing records.

Try a little harder to describe your business

I advised against this as well because to me it seems like generating empty records, but it is for the ID for immediate physical labeling (i do not understand the full scope of this yet).

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.

[orange]

1. We receive parts to go into temporary storage before inspecting them
Who do you receive Parts from? Is this Customer/Client?? Can it be an Individual?

2. We enter the bulk number of specific parts into the form and it adds that quantity to the table for those parts
Is this create your placeholder records? Do you assign a number? Is it a database generated autonumber?
Any more detail of what happens here? Who enters the data?Are there changes you would want to make to this process?

3. Each of these part table IDs will be 6 digits, they cannot duplicate in that table, but will duplicate across tables
Why do you say 6 digits? Why is 6 important? What is the real purpose of the number? If this is identifying the part to your organization,why is it possibly duplicated in other tables? Why doesn't that unique Part Identifier not remain with the part throughout all processing?

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.
What is a Job/JobNumber? What is its purpose?You say JobNumber/Serial Number--what's the difference in these?
I think you're starting to tell us how you would do it rather than what is required. You can make a meaningful label based on a combination of fields, if necessary.

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
If the Part Inspection has been done at this point -where exactly did it happen?Who did it? How is it recorded? Do you have criteria to identify "conditions of parts"? So a label here could be the uniqueId + the PartCondition (just saying it could be not that it should be)

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.
It seems there are some process steps missing from your description. If the label included PartCondition, and you know what Part it is, why couldn't/shouldn't an Inspector update the PartRecord or related information with repair notes, and other factors that are relevant to other steps as the Part moves through the organization's processes.

Note: You have not included (in the big picture) the repair/ machining and shipping.
Do repaired parts always get shipped the the Customer(Client) from whom you Received that Part? Could it go to a different Customer/Client? What happens to Parts that "can not be repaired e.g. scrap"? Is there any communications between your organization and the Customer/Client when repairs/machining are expensive (above a certain amount, or if flaws/faults are discovered on inspection)? How are parts shipped (your company/3rd party)? You have not mentioned any Financials. Where do Orders and Invoices fit in this organization's business?

Please review my questions and add as much description to tell us what your organization does.
You should then identify the things you do now that are problems/have issues/are bottlenecks. Once there is a complete description, we can collectively create some draft data models-that you can test- that will serve as a blueprint for your database. The keys is to get a clear picture of what is to be solved/supported with this database application.

Good luck.

[ssanfu]

I am curious why you are using 4 tables instead of 1 when the table structures appear to be identical.
One table with one more field for "PartType" (Head, Bonnet, Rod, Liner).

tblHeads
ID: Custom ID/Serial
Condition: --
Description: --
etc...

tblRods
ID: Custom ID/Serial
Condition: --
Description: --
etc...

tblBonnets
ID: Custom ID/Serial
Condition: --
Description: --
etc...

[Locke_Valken]

I am curious why you are using 4 tables instead of 1 when the table structures appear to be identical.
One table with one more field for "PartType" (Head, Bonnet, Rod, Liner).

I cannot think of another way to do this, the tables are not to just be 3 fields though. There are several, several variables that are inspected for EACH different type of part/table; the base tables I created initially are just to get a core system working with those 3 fields and the others to be added. Additionally, it felt consistent with previous methods in Excel; recording each type on a different worksheet. I realize Access is a different beast indeed, but i have limitations as i learn the program and only familiarity with maxscript, javascript, and excel.

Each part type will need many different fields, but all contain the condition and description fields.

Who do you receive Parts from? Is this Customer/Client?? Can it be an Individual?

There is a customer/client SharePoint list i had intended to import and create dropdowns for that would be associated to each part in their respective tables (would also need to be handled on this form for Type/Quantity - assuming). This would be handled by whomever is doing the initial processing, but is not part of the immediate scope. I still would like to think about the implementation of this though.

Is this create your placeholder records? Do you assign a number? Is it a database generated autonumber?
Any more detail of what happens here? Who enters the data?Are there changes you would want to make to this process?

Initially it is an autonumber, but not after the concept becomes functional. I created a custom format set to no duplicates and set it as the primary key in a test table to verify this would work; i didn't seem to have much control over the "autonumber" which was the only reason i created a custom primary.

Why do you say 6 digits? Why is 6 important? What is the real purpose of the number? If this is identifying the part to your organization,why is it possibly duplicated in other tables? Why doesn't that unique Part Identifier not remain with the part throughout all processing?

6 digits would account for 999,999 part inspections for EACH type, right? Logically, that appeared to be a reasonable number, yet still limited. I am definitely open for suggestions on things i know i have not done or seen. If there are 4+ tables i can almost guarantee that the IDs will definitely be the same on all tables... UNLESS... dang, for some reason i didn't think about changing the format or sequence for each table because i was cloning them, but that would probably be a more efficient solution. Either way, the numbers are only to keep each record/JobNumber unique and the records can be easily accessed via queries or more importantly right now, the number printed on the label.

The real pain here is that more tables will be needed to account for types we aren't expecting, like gearboxes came up today. Adding a gearbox table with that "Type/Quantity form" means that someone will always need to alter the coding to some degree to account for new tables and their respective fields. 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.

What is a Job/JobNumber? What is its purpose?You say JobNumber/Serial Number--what's the difference in these?
I think you're starting to tell us how you would do it rather than what is required. You can make a meaningful label based on a combination of fields, if necessary.

I thought that the job number could be a combination of 2 things:
1. the Table itself - The types in the quantity/type form dropdown are from a type table so i thought using that type as a prefixed numeric would eliminate the duplicate pattern i was going to get across each of the cloned tables (hard to explain this). If the part is from the Heads table then it would be "01", if it's from the Liners tables then it would be "02".
2. the record ID (part ID) - the 6 digit ID mentioned above. So, the first Head in the system would be "01-000001" whereas the first liner would be "02-000001".

Part Number = 000001
whereas
Job Number = 01-000001

That is how i was trying to make a meaningful number based on a combination of fields/tables. However, as many of you have either seen or done this on countless occasions i am open to suggestions.

If the Part Inspection has been done at this point -where exactly did it happen?Who did it? How is it recorded? Do you have criteria to identify "conditions of parts"? So a label here could be the uniqueId + the PartCondition (just saying it could be not that it should be)

Part inspection would not have been done at this point, hence why the condition would be set to "Pending Inspection". However, the ID would have been generated via the "Type/Quantity form" so a label could be applied with the relative Job Number. Once the part has a spot in the shop and is inspected, a designated individual would take the hand written inspection report from a mechanic and update the records.

I did not consider the alpha/numeric option. we don't typically see parts either labeled or stenciled with alphas. This system is just for our shop though so it is not limited by the physical parts' labeling in this industry.

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.
It seems there are some process steps missing from your description. If the label included PartCondition, and you know what Part it is, why couldn't/shouldn't an Inspector update the PartRecord or related information with repair notes, and other factors that are relevant to other steps as the Part moves through the organization's processes.

The label doesn't include anything more than a number. I say the condition would be set to "Pending Inspection" by default to avoid NULLs and to leave a way to query for missed records/part inspections. The personnel assigned to update the records after the inspection would have a queue to look at from a "Pending Inspection" query as well.

Note: You have not included (in the big picture) the repair/ machining and shipping.
Do repaired parts always get shipped the the Customer(Client) from whom you Received that Part? Could it go to a different Customer/Client? What happens to Parts that "can not be repaired e.g. scrap"? Is there any communications between your organization and the Customer/Client when repairs/machining are expensive (above a certain amount, or if flaws/faults are discovered on inspection)? How are parts shipped (your company/3rd party)? You have not mentioned any Financials. Where do Orders and Invoices fit in this organization's business?

Repair and Machining results would be captured in the same tables and their accompanying forms, there are too many fields to account for for me to even know, which is why i don't mention much on them. When they need to be added I will need to transfer the existing columns from excel doc over to table fields (not the data, just the fields - we will likely just manually add them) with a specialist's overview.

All accounting and finance is handled by different systems that i am not familiar with. The purpose here is to be able to track a part and record results for reporting just in the local shop as well as being able to provide immediate part information. The last year has consisted of manually typing individual reports for each part on an engine. Having all of the inspection and repair data available in a database means that we could automatically generate these to a degree as well as help me tie in tasks for Primavera schedules. Ultimately, having Nintex forms driving the database through SharePoint in the end. There are a few live access web app databases we have created that are functional but highly limited; pushing me to get this functional and create some external connections to SharePoint, drive this, and generate some Power View or even BI metrics (that's a long way off though).