Auto Number Not Linking

[Scotter]

Ok, I have 3 tables, one with a customer ID(PK) number and info about the customer(customer table). Another table with an order number(PK), with payment info and such, bieng linked by the customer id(FK),(orderinfo 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:)

[Scotter]

I forgot, but yes my forms are linked by order number, and i added thoose fields to the query because at first I wasnt going to use a subform because I dont enjoy them, but they decided it was necessary because I can see all the items under a certian order that way... If you have any other ideas about how to do this, that would be sweeeeet.

[mlcampeau]

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:)

Okay, yes that was confusing but I'll try to work with it. I'm pretty sure I understand what your problem is. So, you enter the order number and order info in the subform, but you try to enter the payment info and such in the main form, but the main form doesn't get updated with the order number from the subform. Is that right?
I am just noticing that in your queries you are referring to a CUST_NAME table, Items table, and Orders table. In your first post you mentioned a customer table, Order Info table, Order table. Are these actually the same tables? I'm going to assume they are.
You didn't answer if you have the main form and subform linked. If so, are they just linked by the Order Number?

[mlcampeau]

I forgot, but yes my forms are linked by order number, and i added thoose fields to the query because at first I wasnt going to use a subform because I dont enjoy them, but they decided it was necessary because I can see all the items under a certian order that way... If you have any other ideas about how to do this, that would be sweeeeet.

On your subform do you allow more than one Order Number to be entered? I'm just wondering if it would be easier to enter the order number on the main form, and then link the two forms by order number....Bare with me. I'm still kind of new at Access and am self-teaching.

[Scotter]

Yeah thats how I'm doing it, im puttin in a order number and filtering the results by that order number.

Yeah the non-updating was the problem. But I have to say I pretty much redid the form, and the querries, like you said to, and It looks like everything is going to work, almost. Now I can update everything (score!) but my only issue now is getting the order number to automatically fill in, within the subform(because if your entering a new order, you wont know what number should be there). But I'm seeing the light at the end of the tunnel. Im right with ya on that whole self teaching thing...

[mlcampeau]

Yeah thats how I'm doing it, im puttin in a order number and filtering the results by that order number.

Yeah the non-updating was the problem. But I have to say I pretty much redid the form, and the querries, like you said to, and It looks like everything is going to work, almost. Now I can update everything (score!) but my only issue now is getting the order number to automatically fill in, within the subform(because if your entering a new order, you wont know what number should be there). But I'm seeing the light at the end of the tunnel. Im right with ya on that whole self teaching thing...

Well, I'm glad it's looking promising. On which form are you trying to get the order number to automatically fill in?

[Scotter]

Everything is finally working correctly. I'm going to attempt to explain what I had to fix...

I had to redo the query and take out all the fields that were on the subform and only leave the fields that were on the main form(plus the order number). I had to make another query with the fields that i wanted in the subform. Then I had to change the record source for the subform to the new query, and re-do the link betweek the tables. and thats about it...

So now I just need to make a button that makes a new record, instead of using the nav bar on the bottom.

[mlcampeau]

Everything is finally working correctly. I'm going to attempt to explain what I had to fix...

I had to redo the query and take out all the fields that were on the subform and only leave the fields that were on the main form(plus the order number). I had to make another query with the fields that i wanted in the subform. Then I had to change the record source for the subform to the new query, and re-do the link betweek the tables. and thats about it...

So now I just need to make a button that makes a new record, instead of using the nav bar on the bottom.

I'm glad you got it to work! As for the button, that will require some VBA coding and that is definitely NOT a specialty of mine (not like anything in Access is...)! Good luck with it though!

[FishVal]

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];
 

Hi, Scotter.

What is the reason to join all three tables and set it as a RecordSet of the main form where only first two are supposed to be modified?

[mlcampeau]

Hi, Scotter.

What is the reason to join all three tables and set it as a RecordSet of the main form where only first two are supposed to be modified?

Hi FishVal. If you refer to post 21, Scotter mentioned that he took the unnecessary fields out of the query that you are questioning. Post 16 explains that he had them in there from when he tried to design the form without using a subform.

[Scotter]

yea, once i removed thoose fields from the query, and redid some of the form everything is working now.

[Stang02GT]

Sorry Scotter I got tied up in meetings for the rest of the day and was unable to get back to you. But its good to see that you were able to get your problem solved.

[Scotter]

no problem, mlcampeau had my back, but I'm sure ill need more help soon.