473,746 Members | 2,717 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating Add/Edit forms for BOMs in Access

I'm working on a BOM in Access 200 from an example downloaded from from
the web. The sample database contains three tables, Assemblies (the
list of items needed to assemble any assembly), Components (the list of
items recognised by the Assemblies Table)and Output (a table used to
display the BOM from a chosen assembly).

It works fine but there are no forms. For a user to edit or create a
BOM, should there be a form for each assembly of the BOM? Can you have
Access automatically generate forms depending on the number of
subcomponents or assemblies, because they will vary depending on the
product?

Any advice would be greatly appreciated.

Nov 13 '05 #1
6 7639
The structure does not sound correct to me. The Output table appears just to
be a rehash of the Assemblies & components.

The normal way is to have a components table and a form to edit those
components, and an Assembly form with the components on a continuous
subform.
Maybe I've got it wrong and you have components to make up assemblies and
assemblies to make up the final product in which case it is much more fun.
If so I have a database for recipes which might help

HTH

Phil
"F-13" <Wa*********@gm ail.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .
I'm working on a BOM in Access 200 from an example downloaded from from
the web. The sample database contains three tables, Assemblies (the
list of items needed to assemble any assembly), Components (the list of
items recognised by the Assemblies Table)and Output (a table used to
display the BOM from a chosen assembly).

It works fine but there are no forms. For a user to edit or create a
BOM, should there be a form for each assembly of the BOM? Can you have
Access automatically generate forms depending on the number of
subcomponents or assemblies, because they will vary depending on the
product?

Any advice would be greatly appreciated.

Nov 13 '05 #2
On 26 May 2005 03:13:55 -0700, "F-13" <Wa*********@gm ail.com> wrote:
I'm working on a BOM in Access 200 from an example downloaded from from
the web. The sample database contains three tables, Assemblies (the
list of items needed to assemble any assembly), Components (the list of
items recognised by the Assemblies Table)and Output (a table used to
display the BOM from a chosen assembly).

It works fine but there are no forms. For a user to edit or create a
BOM, should there be a form for each assembly of the BOM? Can you have
Access automatically generate forms depending on the number of
subcomponent s or assemblies, because they will vary depending on the
product?

Any advice would be greatly appreciated.

Hi
You might also look at
http://www.mvps.org/access/queries/qry0023.htm
which shows how you could do it using Joe Celko's nested sets
and/or read some of Joe's articles.
David

Nov 13 '05 #3
Hi, thanks for the feedback. David, I did try Joe's nested set and it
works fine, but the left and right labelling of nodes and how this
could be done with code threw me a bit.

I example I am using has been written by Robin Stoddard-Stone and is
available for download from
http://www.mvps.org/access/resources/downloads.htm under "BOM". The
output table is really just included for the sake of the demonstration.
The Components and Assemblies tables are the most important one. The VB
code works recursively. You choose an assembly, it checks the
components to see if any are assemblies themselves, then the components
of the sub assemblies are checked and so on.

I'm including the VB code underneath with the comments to see if it
makes things clearer. I understand how it works. My problem is how to
create forms/subforms which will allow a user amend or add a new
assembly. Phil if your recipe DB was anyway near the same sort of idea,
it would be great to see it.

Thanks
VB code of BOMS
---------------------------

Attribute VB_Name = "Bom1"
Option Compare Database
Option Explicit
'The Bill of materials processor:
' is designed to allow you to decompose an assembly into its component
items.
' operates at multiple levels, where each item at each level can be a
component
' or a sub-assembly. (SubAssemblies are further decomposed by the
breakdown).
' uses 3 tables
' :Table 1, Assemblies contains the list of items needed to assemble
any assembly.
' the items can be subassemblies or components.
' Comprises the following fields.
' : ComponentID, a text field to allow the user to recognise the
item.
' : ParentID, a code field to allow linking.
' : NumberRequired, The number of units required for this level of
this assembly.
' For a given assembly, the items that go into its construction are
entered into the
' ComponentID field, while the parentID contains the code identifying
the given assembly.
'
'Table 2, Components contains the list of items recognised by the
Assemblies Table
' Comprises the following fields
' : ComponentID, a unique key, identifying the component
' : ComponentDescri ption describes the component
' : AssemblyBoolean to identify whether this is an assembly which
has a breakdown
' entered in the Assemblies Table.

'Table 3, the Output Table is a variable purpose table which, in this
example, identifies the
' the components and the number required to construct any assembly
' Comprises the following fields
' :ComponentID the same format as the Component Table.
' :Number Required. A Count of the number of items required.

'To Start the demonstration, select the assembly of choice from the
input form

To add items to the demonstration,

add the elements to the Components Table, identifying whether they
Assemblies or Components. Note that an assembly is an element which
will have sub components. An Assembly
may be a subcomponent itself of another assembly.

Enter the assembly units into the assemblies table. You only need to
assemble each assembly once. That is,
if an assembly has subcomponents that are in themselves assemblies, you
reference that sub assembly name, not
add all the components again. Note that when entering an assembly, you
do not add the Assembly record for itself.
E.G: if entering the assembly for a wheelHub, you do not add the
wheelhub component itself to the Assembly record
otherwise the program will loop. ( Each wheel hub requires a wheel hub
assembly which requires a wheel hub assembly etc.)
Comments in the code should make its operation clear. Start with the
BOMHost procedure.
Const Qu = """"
Type typBits
Component As String
NumberOF As Integer
End Type

Sub BOMHost(strAsse mblyP As String)
Dim db1 As Database
Dim rs1 As Recordset

Dim iArray() As typBits
'Dim strAssembly As String
Dim fDone As Integer ' flag as it is bad practice to
modify loop parameters within loop
Dim iCurrent As Integer ' where you are in the current array
Dim intMultiplier As Integer ' multiplication factor for number
of items

Set db1 = CurrentDb()
ReDim iArray(0)

fDone = False
iCurrent = 0
intMultiplier = 1

DoCmd.RunSQL ("Delete *.* from OutPutTable") ' clear the outputTable

If GetSubAssembly( strAssemblyP, db1, rs1) = 0 Then GoTo BOMHost_End
'Set up the array for the item you wish to decompose.
ParseList iArray(), rs1, UBound(iArray), intMultiplier

Do Until fDone
'Take the next item in the array, (for the first item, the next item is
the
'first unit in the array). Gets the constituent items from the assembly
table referencing
'the parentID. If the item has subcomponents then the item can be
decomposed further.
' if the item is a component (no further decomposition) then it goes to
Output.
'Otherwise it is a subAssembly and you add the parts to the array
using ParseList.

If GetSubAssembly( iArray(iCurrent ).Component, db1, rs1) = 0 Then
AddtoOutput db1, iArray, iCurrent
Else
intMultiplier = iArray(iCurrent ).NumberOF
ParseList iArray(), rs1, UBound(iArray), intMultiplier
End If

'That has finished the processing for the item in the array, so
increment the pointer to
'your current position and test to see if you have finished.( your
current pointer is now
'equal to the array UBound). ' if not go back and do the next Item,
'if so then Output Table contains the BillOfMaterials list and you are
finished.

iCurrent = iCurrent + 1
If iCurrent = UBound(iArray) Then fDone = True
Loop
MsgBox "Completed"
BOMHost_End:
db1.Close

End Sub
Private Function GetSubAssembly( strParentID As String, db1 As Database,
rs1 As Recordset) As Integer
' returns 0 if no records, else 1 (doesn't move to end of recordset)
Set rs1 = db1.OpenRecords et("select
Assemblies.Comp onentID,Assembl ies.NumberRequi red from Assemblies where
" _
& "(((Assemblies. ParentID)=" & Qu & strParentID & Qu & "))",
DB_OPEN_DYNASET )
GetSubAssembly = rs1.RecordCount
End Function
'Gets the individual records from rs1 (argument recordset) and puts
them into the array
Private Sub ParseList(iArra y() As typBits, rs1 As Recordset,
intLastPosition As Integer, intMultiplier As Integer)
Dim intSize As Integer
'iArray() is integer array defined in the host procedure
'rs1 the recordset to get data from
'intLastPositio n the last position in the array
' intMultiplier is the multiplying factor based on the number of parent
units required
intSize = intLastPosition + 1
Do Until rs1.EOF

ReDim Preserve iArray(intSize)
iArray(intLastP osition).Compon ent = rs1!ComponentID
iArray(intLastP osition).Number OF = rs1!NumberRequi red *
intMultiplier
rs1.Move 1
intSize = intSize + 1
intLastPosition = intLastPosition + 1
Loop
End Sub

'Components are added to the output table . Modify this module to
modify Output

Private Sub AddtoOutput(db1 As Database, iArray() As typBits, iICurrent
As Integer)

Dim rs1 As Recordset
Dim strAssemblyID As String
Dim intNumberOf As Integer

strAssemblyID = iArray(iICurren t).Component
intNumberOf = iArray(iICurren t).NumberOF
Set rs1 = db1.OpenRecords et("select OutPutTable.* from OutputTable
where " _
& "(( OutPutTable.Com ponentID=" & Qu & strAssemblyID & Qu & "))",
DB_OPEN_DYNASET )
If rs1.RecordCount = 0 Then 'the component is new
rs1.AddNew
rs1!ComponentID = strAssemblyID
rs1!NumberRequi red = intNumberOf
rs1.Update
Else
rs1.Edit
rs1!NumberRequi red = rs1!NumberRequi red + intNumberOf
rs1.Update
End If
End Sub

Nov 13 '05 #4
On 26 May 2005 17:08:50 -0700, "F-13" <Wa*********@gm ail.com> wrote:
.....
makes things clearer. I understand how it works. My problem is how to
create forms/subforms which will allow a user amend or add a new
assembly. ...

Hi
You need a form/subform, with the main form showing the assemblies and
the subform the components.
See for example
http://www.fgcu.edu/support/office20.../subforms.html
for a tutorial of a similar example using orders.
The order form in northwind.mdb is similar but more complicated as it
has lots of other things in it.

If I were you I would add a one-to-many relation on field componentids
between the tables components (1) and assemblies (many). The database
you refer to has an error in it as LowerArm (I am quoting from memory)
appears in the assemblies table but not in the components table.
David

Nov 13 '05 #5
Thanks David

I actually tried this tutorial before and the the only way I can get a
form/subform situation that i like is if i create a relationship
between ParentID in Assemblies and ComponentID in Components. However,
for an entire Car (using the data as example) I need two subforms, one
for WheelAssembly and another for Hub Unit so that these components can
be modified or added to. And if, for example a Hub Unit component was a
further assembly, then I'd need a subform for that too, etc.

Car (main form)
- WheelAssembly (sub form)
- Upper Arm
- Lower Arm
- Hub Unit (another sub form)
- Wheel Hub
- Studs
- Nuts

Am I going about this the wrong way or is it achievable?

I think the author of the example made a small mistake with "LowerArm"
in the Assemblies table and "Lower Arm" (with a space) in the
Components table.

Nov 13 '05 #6
On 29 May 2005 17:57:02 -0700, "F-13" <Wa*********@gm ail.com> wrote:
Thanks David

I actually tried this tutorial before and the the only way I can get a
form/subform situation that i like is if i create a relationship
between ParentID in Assemblies and ComponentID in Components. However,
for an entire Car (using the data as example) I need two subforms, one
for WheelAssembly and another for Hub Unit so that these components can
be modified or added to. And if, for example a Hub Unit component was a
further assembly, then I'd need a subform for that too, etc.

Car (main form)
- WheelAssembly (sub form)
- Upper Arm
- Lower Arm
- Hub Unit (another sub form)
- Wheel Hub
- Studs
- Nuts

Am I going about this the wrong way or is it achievable?

I think the author of the example made a small mistake with "LowerArm"
in the Assemblies table and "Lower Arm" (with a space) in the
Components table.

Hi
I was thinking of only one subform, to let you add or edit the
children of just the current parent node. Eg select any node on the
master form and (using linked parent/child fields) the subform will
show the children. Actually you can do this in just one continuous
form (ie without using a subform) by selecting the parent record in a
combo box in the form header, and changing the form recordsource or
filter.
However, if you are looking for a way of displaying the overall
structure you can use indentation on a report or use a treeview.
Surprisingly, I haven't found a free sample database for this though
there have been discussions earlier in this group.
David

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2002
by: Ray Lavelle | last post by:
I'm new to VB .NET. In the past when creating an application, if I had an input form I would just call the set and get methods on the object that mapped to my database table in order to store and retreive the data for that form. I would manually set each field if the user was editing a record and I would copy each field from the form into my object when they click save. I want to develop some new forms but this time I want to try to make...
2
5970
by: Iain Miller | last post by:
Now this shouldn't be hard but I've been struggling on the best way as to how to do this one for a day or 3 so I thought I'd ask the assembled company..... I'm writing an application that tracks a group of Sales people, the customers they deal with and the business they transact with them. I've got my head around all the tables & some of the basic Query structures OK and am beginning to delve into creating the forms I need to be able...
25
10406
by: dixie | last post by:
I have some code that adds new records into a table for each ID in a list box when a button on a form is clicked. This works fine. My problem now is that I wish to be able to edit all the records for people whose ID is in the list box. I made minor changes to the code (mainly replacing rs.AddNew with rs.Edit)and it appears to be updating only the first record and then overwriting that record with the next, etc until it runs out of ID's...
4
1586
by: Jimmer | last post by:
How does one create/change a form on the fly? I have a situation where I would like the user to be able to determine the fields they view and how the data is grouped at runtime. For example, the form might list Company Name, Last Name, Address, Phone Number, Accounts Payable $ on time and Last Name, Address, Phone Number, Social Security, Accounts Payable $ the next and Company Name and Accounts Payable $ the time after that. Each one...
1
2914
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access 2000. The access file is in access 2000 format. I have a form that will hold the relevent parameters for the query/report that reports the statistics for all job records that match a certain criteria. These are: - A Customer Name.
2
1760
by: Dave | last post by:
Our company intranet allows various users to submit address information and contact names and numbers using ASP pages, that save to an Access 2000 database. There are no issues with retrieving, editing, or deleting the information. This solution works well for 60 users, and results in a database about 300 kB, that feeds other processes downstream. Because most of the front end users are novice (or temporary hires), we want to make more...
2
1823
by: Vish | last post by:
Hi, I amplanning on having a rea-only and edit states for my form. But it do not want my form and its controls to look different or disabled. I am planning on having a edit button that brings the form from read-only to a edit state. Is there any easy or built-in way of doing this in .NET? My preimary purpose for doing it because the combobox controls on my form take a while to load all their data from the database. Since the user uses...
4
3678
by: sklett | last post by:
I've developed an ERP application that we use internally and works quite well. I receiving more and more requests from users to print various transactions, order forms, search results, etc. I haven't decided what the best way to do this is because I don't have much experience with generating printable forms. Early on I knew one of my modules would need to print a clear report so I used the open source SharpPDF library to generate the...
4
1417
by: Constantine AI | last post by:
Hi I am trying to input some error messages into my system. I have come across a slight problem with one of them. i have a query which filters and groups data together displaying details on a form. I then have a button called "Generate" which looks up the last entry from a particular table to assign a Purchase number to the grouped record. This way its assigns the value to all relevant records. This works by the way! However when i tried to...
0
8974
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
9285
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9218
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8227
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6772
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6060
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4836
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2765
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2199
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.