how can i repeat selected fields on an access 2007 form

[dann]

i am trying to create a BOM datta entry form using access 2007. I have one
table. i would like to repeat the drawing number, description, issue date
type fields as i add new records without re-typing.


thanks for your help

 

[KARL DEWEY]

Why not use a subform to enter non-repeating data?

Have one-to-many something like this --
Project
Drawing
Material
Set relationship for one-to-many and Referential Integerity with Cascade
Update.

Use three forms set as --
Project Form
Drawing Subform
Material Subform

 

[Larry Daugherty]

Place the following declaration in a standard module.

Public Const CQuote = """" 'that's two quotes - used in string and
Where clause formation


Then place a copy of the below line of code in the After_Update event
routine for each textbox whose value you wish to carry forward.

Me!Name.DefaultValue = CQuote & Me!Name.Value & CQuote

HTH

 

[KenSheridan via AccessMonster.com]

By having only one table it sounds like you are using Access more like a
spreadsheet than a database, hence the repetitive data.

The classic way of modelling a bill of materials is to have two tables, one
of Parts. Each assembly or sub-assembly is included in this table as well as
base parts, so it might look like this:

PartNum PartName Weight
1 Assembly 1 0
2 Assembly 2 0
3 Assembly 3 0
4 Assembly 4 0
5 Assembly 5 0
6 Part 1 10
7 Part 2 5

Then a PartStructure table references the PartNum column of the above table
in Majorpart and MinorPart columns like so:

MajorPartNum MinorPartNum Quantity
1 2 2
1 3 3
2 3 4
2 4 2
3 4 1
3 5 2
4 5 3
5 6 2
5 7 1


Your newsreader may well have screwed up the alignment of the above, but I
hope you can see what its doing, e.g. assembly 1 is made up of assemblies 2
and 3, assembly 2 of assemblies 3 and 4 and so on.

Data such as drawing numbers would do in another table related to the Parts
table by PartNum (if each drawing relates to one assembly only), or by a
further table to model the many-to-many relationship if a drawing relates to
multiple assemblies. Whichever is the case the repetitive data is avoided
and the tables are thus normalized.

You'll find a simple demo from which the above data is taken at:

http://community.netscape.com/n/pfx/forum.aspx?tsn=1&nav=messages&webtag=ws-msdevapps&tid=23133


The file is attached to the second post in the thread.

Ken Sheridan
Stafford, England

 

[dann]

larry,

thank you for the help. please bear with me, i am very new to this. how do i
add the declaration to a standard module?

 

[dann]

karl,

thanks for the reply. i am going to try the suggestion from mr. daugherty
first, then yours, and finally mr. sheridan. the people on this forum are the
best.

 

[dann]

ken,

you hit the nail on the head with your observation. we are currently using
an excel based form for recording the bom info, then saving each form
individually in a seperate file.

the templates are outstanding and i will attempt to adapt them if the
solution in post 2 and 1 do not work out.

thank you for the help!

 

[Larry Daugherty]

A standard module is one that is not a class module - such as the
module for a form. If you already have modules showing in the
Database|Modules window then you can add the line in an appropriate
module. If you don't see any modules, click the New button and create
one and put your line of code in it.

HTH