Microsoft Access 2000 Form Help

[dsheets05]

Hello all,

I am a mechanical engineering trying to create a drawing log for my company in access. We are currently in the dark ages still penciling in our drawing log. With over 100,000 entrees to date. We have expanded quite a bit lately and it is very annoying when you have to walk across a 400,000 sqft manufacturing facility just to pull the next drawing number. With no IT support I have forced to do it myself. So please bare with me as I am unfamiliar with access and the coding language.



Now that you know the background let me explain what I am trying to do, and my current problem.

The tables give you an idea of what I am looking for. The assembly number relates multiple drawing files together. For example assembly drawing number 98472 might have five corresponding drawing numbers to it 98472-1, 98472-2, 98472-3, 98472-4, 98472-5. My first step in creating the database was to have a searchable “form” for the master assembly numbers. I want this form to be able to pull all the assembly numbers and organize them by columns with a click on the column headers. (This is where I am stuck). I borrowed the code to do this from a database template I found online. If someone could review this form and see what section of code is messed up I would appreciate it. I think it has to do with the buttons. More specifically the line of code that pulls the queries.


Thank you very much for taking the time to look at this post. I appreciate any input or help!

On another note if anyone needs some matlab, equations worked, engineering work in exchange for a little bit of access help I would be more than happy to lend a hand!

[jzwp11]

The code you have refers to form controls that don't exist on the form, so that is what is causing the error.

I could not follow what all the code was trying to do, so I started fresh. I created a form (frmSearchAssemblyLog) that I hope does what you want.

I also made some changes to your table. Instead of having a text field that holds the subassembly number (i.e. 98472-5), I just had a long number field where you would record the sequential number. You can always use an expression to combine the main assembly drawing # with the sequential number as I illustrate in the subform on frmSearchAssemblyLog. With respect to the project number, you have it as a text field, so when you sort it, it sorts as text, not a number. If all of your project numbers will be strictly numbers then I would recommend changing the datatype of the field to a number.

I've attached the amended database.

[ssanfu]

The major error I found is that there is a duplicate sub-routine name in the form "SRCHASSEMBLY". The first 3 lines of each sub are:

Private Sub cmdOrderbyAssembly_Click()
Dim Response As Integer
Response = basOrderby("Assemblylog.AssemblyID", "asc")
.
.
 
Private Sub cmdOrderbyAssembly_Click()
Dim Response As Integer
Response = basOrderby("Assemblylog.AssemblyID", "DESC")
.
.

The second sub should (might) be named

Private Sub cmdOrderbyAssemblyDesc_Click() ????

(BTW, there are better ways to change the sort order.)

====================
Other things I saw:

Under TOOLS/OPTIONS, General Tab, the two checks under "Name AutoCorrect should be unchecked. This is a known cause of mdb corruption.

On the FORMS/REPORTS tab, "Always use Event Procedures" should be checked.

----------
There is not a reference to Microsoft DAO 3.6 Object Library
(in the IDE - TOOLS/REFERENCES..)

Every code page should begin with these two lines:

Option Compare Database
Option Explicit



----------

You should think about splitting the mdb if more than one person will be accessing the database at the same time.

[dsheets05]

THANK YOU SO MUCH for the responses everyone. I decided to consolidate everything into one table which solved most of my problems. However, I now have One problem I cannot solve. I am stuck on this and it is the last problem which is stopping me from completing this data base.



Go to Formsà”frmAssembly” (This is my main form)


My problem is creating a linked value. I need to be able to link “parts” and only parts(Not assemblies) to assemblies. I have made a button on my assembly page to pull up ALL Parts. I have also made a colum in my table called “linkedparts”.
Could someone please help me with the code on my button which will add the part number from “frmPARTSONLY” to the “linkedparts” field on the assembly I currently have chosen on form “frmsubassemblylog”. In addition, I would need to have a way to display all the corresponding “linkedparts” to assembly on the “frmsubassemblylog”.

[jzwp11]

If you have many parts associated with an assembly (and you do not consider an assembly a part) and a part can only be associated with 1 and only 1 assembly then you are describing a one-to-many relationship which requires 2 tables.

tblAssembly
-pkAssemblyID primary key, autonumber
-txtAssemblyNo
other assembly related fields

tblAssemblyParts
-pkAssemblyPartID primary key, autonumber
-fkAssemblyID foreign key to tblAssembly
-txtPartNo

Now if a part can be associated with more than 1 assembly then you need 3 tables


tblAssembly
-pkAssemblyID primary key, autonumber
-txtAssemblyNo
other assembly related fields

tblParts
-pkPartID primary key, autonumber
-txtPartNo

tblAssemblyParts
-pkAssemblyPartID primary key, autonumber
-fkAssemblyID foreign key to tblAssembly
-fkPartID foreign key to tblParts
-qty (quantity of the part needed in the assembly)