Auto Number Not Linking

[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.