Auto Number Not Linking

[Scotter]

Ok, I have 3 tables, one with a customer ID(PK) number and info about the customer(custom er table). Another table with an order number(PK), with payment info and such, bieng linked by the customer id(FK),(orderin fo table). And a final table with the order number and the description/items that make up the order(order table). I did it this way so i can have more that 1 item per order number.

Heres kinda what it looks like:

Customer table              Order Info table               Order table  


|Customer ID*|-------------|Customer ID    |    /--|Order Number|
|Name        |             |Order Number*  |---/   |Item        |
|Phone       |             |Payment Method |       |Quantity    |
|etc...      |             |ect...         |       |etc...      |


where both *'s are primary keys, and auto numbers

I have a form to enter the customer info, and order info, and a subform that handles the order stuff. But I cant make a new order. Everything works, as far as displaying current orders, and I can make new orders if I go into datasheet view of the tables.

Its like the order number(auto number) isnt making a new number.

Any ideas about how to get this working would be greatly appreciated.

[Stang02GT]

Technically, a subform isn't considered an open form, but is a control on the main form, and can't be referenced as this.

You'll have to setfocus to the subform, then perform the acnewrec, and set focus back to the main form again. This is one way to do it. Put this code in the On_Click event of a button. Try it and let me knwo if it works

Me![sub form name].SetFocus
DoCmd.GoToRecord ,,acnewrec
Me!parentformName.SetFocus

[Scotter]

Ok, I tried the code, but pretty much nothing happens.

[mlcampeau]

what are the queries that you are basing your forms on?

[Scotter]

Im basing my form on a querry that basically has almost all the fields, accept for a few that im not using in the form.

[mlcampeau]

Im basing my form on a querry that basically has almost all the fields, accept for a few that im not using in the form.

can you post the sql?

[Scotter]

The querys sql is;

SELECT CUST_NAME.[Customer ID Number], CUST_NAME.[First Name], CUST_NAME.[Last Name], CUST_NAME.Phone, CUST_NAME.Address, CUST_NAME.City, CUST_NAME.State, CUST_NAME.Zip, ORDERS.[Order Number], ORDERS.[Payment Method], ORDERS.Deposit, ORDERS.Due, ORDERS.[Delivery Date], ORDERS.[Pick up Date], Items.Item, Items.[Tent Size], Items.Combo, Items.[Table Cloth], Items.[# of Tables], Items.[# of Chairs], Items.[# of Table Cloths], Items.[Order Number]
FROM (CUST_NAME INNER JOIN ORDERS ON CUST_NAME.[Customer ID Number] = ORDERS.[Customer ID Number]) INNER JOIN Items ON ORDERS.[Order Number] = Items.[Order Number];

[mlcampeau]

The querys sql is;

SELECT CUST_NAME.[Customer ID Number], CUST_NAME.[First Name], CUST_NAME.[Last Name], CUST_NAME.Phone, CUST_NAME.Address, CUST_NAME.City, CUST_NAME.State, CUST_NAME.Zip, ORDERS.[Order Number], ORDERS.[Payment Method], ORDERS.Deposit, ORDERS.Due, ORDERS.[Delivery Date], ORDERS.[Pick up Date], Items.Item, Items.[Tent Size], Items.Combo, Items.[Table Cloth], Items.[# of Tables], Items.[# of Chairs], Items.[# of Table Cloths], Items.[Order Number]
FROM (CUST_NAME INNER JOIN ORDERS ON CUST_NAME.[Customer ID Number] = ORDERS.[Customer ID Number]) INNER JOIN Items ON ORDERS.[Order Number] = Items.[Order Number];

Are you using this same query for both your main form and your subform??

[Scotter]

no, the record source for the subform is based on the fields in the subform

[mlcampeau]

no, the record source for the subform is based on the fields in the subform

I'm sorry, you've confused me a bit here. So the above query is what you've based your main form on? What is the sql for the query you based your subform on?

[Scotter]

SELECT DISTINCTROW [Items].[Order Number], [Items].[Item], [Items].[Tent Size], [Items].[Combo], [Items].[Table Cloth], [Items].[# of Tables], [Items].[# of Chairs], [Items].[# of Table Cloths] FROM [Items];

[Scotter]

SELECT DISTINCTROW [Items].[Order Number], [Items].[Item], [Items].[Tent Size], [Items].[Combo], [Items].[Table Cloth], [Items].[# of Tables], [Items].[# of Chairs], [Items].[# of Table Cloths] FROM [Items];

But its not a saved query, its just typed in the record source. If that matters for anything.

[mlcampeau]

SELECT DISTINCTROW [Items].[Order Number], [Items].[Item], [Items].[Tent Size], [Items].[Combo], [Items].[Table Cloth], [Items].[# of Tables], [Items].[# of Chairs], [Items].[# of Table Cloths] FROM [Items];

First off, have you linked your subform and main form?
Secondly, I'm not exactly sure, but I'm wondering if your problem has something to do with the fact that your query for your subform is just a part of your query for your main form.
Is there a particular reason that you are selecting [Items].[Order Number], [Items].[Item], [Items].[Tent Size], [Items].[Combo], [Items].[Table Cloth], [Items].[# of Tables], [Items].[# of Chairs], [Items].[# of Table Cloths]
in your main form query? If not, try removing the Items table fields from your main form query, and then link your subform and main form by OrderNumber.

[mlcampeau]

But its not a saved query, its just typed in the record source. If that matters for anything.

Try saving the query and then changing the record source to the name of the query. I don't know if it will make a difference, but it very well might

[Scotter]

ok I tried removing thoose fields from the query, but doesnt work. After I remove them the sub form and everything gets messed up, and the subform doesnt work. When I try to add a new record the error it gives me is

"Can't add record(s); join key of table 'order info' not in recordset."

when I add an order, im putting in the order number, and what they order, all goes into the order table. but its like the order number in the order info table isnt creating a new record when i try to enter payment information and stuff.

That was kinda confusing i know. Sorry:)