autonumber keeps increasing when i add a record to other table

[selavarti]

Hi,

I am having two tables for tools
tbl_tool_type(Tool_type_auto_number( auto number & PK), tool_type) other tbl_tools(tool_type_auto_number, tool_barcode. tool_acticeinactive).

There can be more than one tool of same type and all that are listed in tbl_tools, but the corresponding tool_type_auto_number is picked from tbl_tools. But tbl_tool_type has all unique tool_types. Bascically its one-to-many relationship from tbl_tool_typr to tbl_tools.

But the problem is I am adding a new tool(from existing tooltypes) in the form with a new barcode, its saving as a new tool type in tbl_tool_type with new auto number. But what it should do is save a new barcode and existing auto number of that tool type on tbl_tools.

Can any one figure out what is the problem with the autonumber

 

[BNPMike]

It's not a problem with autonumber. Your problem seems to be your form is creating a new tbl_tool_type record instead of just a tbl_tools record.

You need to give more detail about your form.

 

[selavarti]

Hi,
Thank you for the reply. Actually what you said is right its creating a new record in tbl_tool_type. I had a query with fields from tools and tool_type table. qry_newtool has tbl_tools.tool_barcode,
tbl_tools.tool_type_auto_number, tbl_tool_type.tool_type.
The form has qry_newtool as record source. It has the fields tool type and tool barcode which are bound to the corresponding fields in qry_newtool. When I add a new tool type and a new barcode and click add record it adds a new auto number in tool_type_auto_number and puts the original auto number in the tool type, instead of adding tool name.

i hope i made some sense
Thanks

 

[BSman]

tbl_tool_type should be an autonumber column in only one of the tables (tbl_tool-type which is the lookup table).

The second table, tbl_tools should have its own autonumber column (tbl_tools_autonumber, PK). A second column will be tbl_tool_type with a type of long integer. Then it will store the ID (tbl_tool_type) value of the row selected from the tool type table...and retain it.

 

[BNPMike]

I recommend you use the form/subform wizard. This will create a form that does what you want.

Look at eg : Link a main form and subform in Access Help.