really need help

[podge200871]

I am creating a database for my boss,but i am stuck and need somenes help.I work for an engineering company and want to make a database to store info on our cutter stock.The search item i would like to use would be the dp of the cutter(cutters come in sizes of 2dp,3dp etc).I have dp as a header in my table with the sizes in the column below(2,4,6 etc).I want to be able to create a form with a search facilty so i can call up a cutter size(6) in a text box and to get a list of what 6 dp cutters i have in stock.I dont have any experience with vba.Can anyone help with this?
Thanks in advance.

[ajetrumpet]

you don't need much basic code to create a search form anyway. If you're looking for something that you can copy and paste, then ask for help afterwards, Allen Browne might be a place to start: http://allenbrowne.com/ser-62.html.

Some of his stuff is really geared for more advanced users, but it does in fact work if you know how to work it!

[podge200871]

Hi

I have had a look at the website and having difficulty understanding how to do it in my database.I have attached mine so far so it is easier for you you guys to understand what i am trying to do(not that good at explaining).
I want to create a form with a list box with the different pitches in (dp,module).then next it a text box so i can enter the number(6,12,16 etc) then i need a string of results from the different tables(shaper cutters,hobs etc.

I know i am punching way above my weight with this.If anyone can give me some advice(probably give up)or help me out i would be really appreciative.
Attachment 1887

[DaveT]

You have six tables and six forms. It looks like each table represents a cutter type: DP Shaper, Hobs DP, Hobs Module, Mikron Hobs DP, Mikron Hobs Module, Module Shaper Cutters.

Probably these should all be one table (say CutterInventory or something) with a field called CutterType (DP Shaper. Hobs DP, ...)

Then you seacrh for all cutter types per location, search for one cutter type and locations (your original question), be able to generate reports on all cutters, etc.

CutterType should be added as a table. It would contain six records (DP Shaper, Hobs DP, etc). Then in CutterInventory you would only allow CutterTypes that are in CutterType table.

You might also need a Locations table so that only valid locations get entered into CutterInventory.

Your tables would need indexes to prevent duplicate records creeping in.

Anyway, after that it might be easier for someone to offer suggestions as to how add a simple piece of functionality although maybe you should be looking for more complete help.

[podge200871]

Hi
Thanks for your input and taking a look.The search for the location is of no real importance,main use is just to look a size of cutter and for the search to display what different cutter type we have in that size(each cutter suits a different type of machine).

[RuralGuy]

Do all of the tables contain cutters? If so, can they all be in one table?

[podge200871]

Hi
Yes the tables all contain cutters,the dp cutters are imperial,the module are metric.The shaper cutters hobs and mikron hobs all go on different machines.The reason i put them on different tables is because i thought it would be more of a task to use two search strings in one table and if i entered a cutter size ie 6 used a list box to select dp(or module),i could get list of cross table results of what 6dp cutters i have of each type of cutter ie shaper cutter,hob,mikron hob.So yes they could all be in one table,if would be easier to get the search to work.
Again,thanks for the input.any help/guidance is appreciated

[RuralGuy]

I think DaveT was on the right track here. All of the tables seem to have the same fields which shouts "put them in the same table". Add the extra field to identify the type of cutter. Searching one table is much easier than searching many. Post back when you get stuck again. We'll keep this thread open so we can all get an update.

[podge200871]

Ok

I have created two tables,one cutter inventory,the other cutter type.I have entered a couple of examples in the inventory list.In the cutter type table i have entered the shaper cutter dp etc as the field names(added a couple extra ie sprocket hobs).I have created a form for the inventory. The things i need help with so far are:

1.I want to create a drop down box in the form with the cutter type in it,
so when a type is selected it's entered into the table.
2. On the form i put a check box,from time to time we lend out our cutters to another company,so if we lend one out,we'll check the box.How will get that to enter a yes in the borrowed box in the table.***update-sorted this one****
3.The point davet made about only being able to put in the cutter type thats in the cuttertype table,How do i make that connection.
4.To create the search on a seperate form to list what cutters we have in a give size.DB Attached below
Thanks for everybodys help so far
Attachment 1889

[RuralGuy]

I made some changes to your db. See if they get you a little further along with your project.

[podge200871]

Hi
Thanks for taking the time to update my database,its really appreciated.I have a couple of questions about the update.
1.I have entered a couple of new cutters in the revised form,saved them,but it doesnt save them to the cutter inventory table. is that right?
2.when entering new cutters i fill in the two boxes with the cutter type, what is the purpose for this.

Thanks again for your help. i am trying to pull my company out of the dark ages of storing everything on paper.

[RuralGuy]

1.I have entered a couple of new cutters in the revised form,saved them,but it doesnt save them to the cutter inventory table. is that right?

I'll be honest, I did not see if you could do anythig useful with the form. I'll go back and look at that.

2.when entering new cutters i fill in the two boxes with the cutter type, what is the purpose for this?

The 2 boxes are simply to demonstrate two things. You do not need the description in the inventory table; the Key value is adequate. And you can see that you have already started to spell the names differently in the two tables.

[RuralGuy]

I can see why I did not go further. You should really not try and make forms do too many functions. I would probably have a separate form for adding and defining a new cutter and open it when needed. I could not really figure out what that form was designed for so I just got it to sort of show off some ideas.

[podge200871]

Hi all

Been working on my database after taking advice from the above posts.I am nearly finished it and i have accomplished what i set out to do. I am with left with one last thing to do and thats the search from my original post.I created a query which asks for a pitch size then shows the results in a datasheet.I now want this as a form where the pitch is typed in a box then the results are displayed in a continuous type form.Can anyone help me with this.

ThanksAttachment 1927

[podge200871]

Hi all,one other thing,How do i get the forms to open to the size of which they have been set in the design view,without the wasted spaced around the window.