getting records from a table

[113deluxe]

I have a table with 5 colloumns.the first colloumn contains a serialnumber for a product. I`ve made a combo box where all the serial numbers are listed and some text fields where I can view the whole record for the chosen serialnumber.

this works fine

now this is what I wanna do

In the table there are listet some products and all the parts that are used to make the product. all the parts have a different serial number. What I want to do is to be able to type in the serial for f.ex a cd-player and then view ALL the records of the parts that are used to make the cd player... is there some way I link certain records to the a number? So that when I tyne in the one serial I get a list over all the products that are INSIDE the product thet I typed the serial for??

 

[wiltshire]

Hi there,

This should be quite simple:

In one table, have all the serialnumbers for the product with descriptions etc - this is your top level table. Give the table an ID number column set to autonumber. Base a form on this table showing all fields required and your combo box listing all serial numbers.

Now, in a second table (parts), have all the parts listed. In this table, also have an ID column and set the ID number the same as the top level ID for each of the parts.

Write a query based on the parts table, with the criteria for the ID filed set to the combo box on the form, using the build function. Create a form based on this query set to continuous forms.

Now put this form into the top level form as a subform and link child and master fields using the ID numbers for each. (master being the name of the ID on your top level table and child being the name of the ID on your parts table).

In the after update proprty of the combo box, point to a macro which simply requeries the form and this will show the required records.

Hope this helps, sorry there's no VB but don't really use it so everything's simple and 'old school'.

Good Luck !

 

[113deluxe]

Do you have any sample code?? I`m new at this so I`m having a hard time handling all the fuctions...

 

[wiltshire]

sorry I don't use VB so no code available. Have a go using my instructions and if you get stuck, take a look at Northwind sample database which came with your access package. This contains and example of this and you can analyse the settings etc.

 

[113deluxe]

Ok...thanks

 

[113deluxe]

I just can`t figure out how to do this. I`ve attached an example of what my tables look like. The things that are BOLD are the products and the things that are listet under are the parts, I have everything in the same table. How can I type in the art.nr for the things that are BOLD and view everything that are listet below at the same time??

 

[wiltshire]

I think you've forgotten the attachment !!!

 

[113deluxe]

OOPS...sorry `bout that...

 

[wiltshire]

here's a quick database I've knocked up for you - it just needs tarting up a bit.

 

[wiltshire]

sorry - forgot to tell you anything about it !!!

If you open the 'art num pop-up' form, it give sthe choice of article number. Choose the number and click 'OK' and it shows the details for this number and the parts list.

I had to separate your data into 2 tables with a cross ref ID using the article number. If you look at the relationships settings, I've joined the 2 tables and enforced referencial integrity. If you change a top level article number, the parts list changes reference accordingly, which is neat.

Try it and look at how everything's linked together to get some idea of how it works.

 

[113deluxe]

How do I create the macro in your xample?? I can`t figure out how to do it...

 

[wiltshire]

Just click on the Macro tab on the base window, then click on 'NEW'. This opens the macro window. The macro can be built from here.

Click on the 'macro names' button in the toolbar to show the names column. This the button with 'xyz' and some boxes on it. Call the macro 'open form' or whatever. Then in the 'action' column, type 'openform' and hit 'F6' to bring you to the form names section of the page. Choose the form from the pull down list.

The macro name is what you put on the 'on click' setting of the 'ok' button.

The process is similar for all other entries in the macro window.

 

[113deluxe]

I can`t find it... the only option I get is open...import and so on when I right click the macro tab in the menu window.

 

[wiltshire]

don't right click the macro tab, just left click the macro tab then look for the 3 options which, if youre in XP will be on the top of the window saying 'run', 'design' and 'new'.

'New' is the one you want, just left click on this.

 

[113deluxe]

Thanks for the example, it was just what I needed, I just have a question, in the table where we put in the top ID and art no fields there are a limit of 9 digits, the numbers I have look loke this 60181398-00 , what do I have to do to use this type of number and edit the max number of digits that are allowed??

 

[wiltshire]

My pleasure !

It looks like you're going to have to change the data type from number to text as you're not allowed to put dashes in a number field.

Just go into the design of the table and change the type by using the pull down list next to the appropriate field name.

 

[113deluxe]

I`ve tried that but I get an error message."You can`t change the datatype or field size of this field: it is part of one or more relationships"

 

[wiltshire]

Go back to the base Access window and click on the button on the tooolbar that looks like 3 boxes joined with lines. This is the relationships window. Now click on the link between 'art no' and 'top id' - it should go bold - then just press delete.

You can now change the data type.

 

[113deluxe]

Thanks alot... Got it now...

 

[113deluxe]

I just have one last question... I have som problems with the combo box now... When I choose one of the values in the combo box I get the following message."The value you entered isn`t valid for this field". has this something to do with the changif from numeric to text in the table???