By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Auto Number Not Linking

P: 80
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:

Expand|Select|Wrap|Line Numbers
  1. Customer table              Order Info table               Order table  
  2.  
  3.  
  4. |Customer ID*|-------------|Customer ID    |    /--|Order Number|
  5. |Name        |             |Order Number*  |---/   |Item        |
  6. |Phone       |             |Payment Method |       |Quantity    |
  7. |etc...      |             |ect...         |       |etc...      |
  8.  
  9.  
  10. where both *'s are primary keys, and auto numbers
  11.  
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.
Aug 8 '07 #1
Share this Question
Share on Google+
26 Replies


Stang02GT
Expert 100+
P: 1,208
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


Expand|Select|Wrap|Line Numbers
  1. Me![sub form name].SetFocus
  2. DoCmd.GoToRecord ,,acnewrec
  3. Me!parentformName.SetFocus
Aug 8 '07 #2

P: 80
Ok, I tried the code, but pretty much nothing happens.
Aug 8 '07 #3

Expert 100+
P: 296
what are the queries that you are basing your forms on?
Aug 8 '07 #4

P: 80
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.
Aug 8 '07 #5

Expert 100+
P: 296
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?
Aug 8 '07 #6

P: 80
The querys sql is;

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. 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];
  3.  
Aug 8 '07 #7

Expert 100+
P: 296
The querys sql is;

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. 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];
  3.  
Are you using this same query for both your main form and your subform??
Aug 8 '07 #8

P: 80
no, the record source for the subform is based on the fields in the subform
Aug 8 '07 #9

Expert 100+
P: 296
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?
Aug 8 '07 #10

P: 80
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];
Aug 8 '07 #11

P: 80
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.
Aug 8 '07 #12

Expert 100+
P: 296
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.
Aug 8 '07 #13

Expert 100+
P: 296
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
Aug 8 '07 #14

P: 80
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:)
Aug 8 '07 #15

P: 80
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.
Aug 8 '07 #16

Expert 100+
P: 296
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?
Aug 8 '07 #17

Expert 100+
P: 296
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.
Aug 8 '07 #18

P: 80
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...
Aug 8 '07 #19

Expert 100+
P: 296
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?
Aug 8 '07 #20

P: 80
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.
Aug 8 '07 #21

Expert 100+
P: 296
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!
Aug 8 '07 #22

FishVal
Expert 2.5K+
P: 2,653
The querys sql is;

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. 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];
  3.  
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?
Aug 8 '07 #23

Expert 100+
P: 296
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.
Aug 8 '07 #24

P: 80
yea, once i removed thoose fields from the query, and redid some of the form everything is working now.
Aug 8 '07 #25

Stang02GT
Expert 100+
P: 1,208
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.
Aug 8 '07 #26

P: 80
no problem, mlcampeau had my back, but I'm sure ill need more help soon.
Aug 8 '07 #27

Post your reply

Sign in to post your reply or Sign up for a free account.