Query issue

[Robison]

I have been trying for days to figure out what is wrong with my frmOrders. It's record source is qryOrder. It always gives me a error message "The Microsoft Access Database engine cannot find a record in the table 'tblCustomer' with key matching fields 'CustomerID'.

I have looked at my query and my tables included and I just don't understand. Could ANYONE please look at my database and help me out. Sure would appreciate any and all assistance.
S:banghead:

 

[JHB]

The problem is the control source for the control "Text62" with the label "CustomerID" it is set to "=[Forms]![frmNewCustomer]![ID]".
You need to have a control in which you can put in the key value "CustomerID" which has to be a number found it the table "tblCustomer" "ID" field.
For me it looks like you have to rethinking the whole set up for the form/database.
A good advice, give controls a meaningful name, not like "Text62", here it could be "CustomerID" and for the label "CustomerID_Label".

 

[CazB]

If you open your frmNewCustomer, and then click the 'new order' button, your frmOrders opens without a problem.

As JHB said - your problem is the control source for Text62 and Organization - they're set to look at frmNewCustomer, so will only work if that form is already open/loaded.

 

[Robison]

I just tried this but it still gives me the same message...."The Microsoft Access Database engine cannot find a record in the table 'tblCustomer' with key matching fields 'CustomerID'. I have looked at my relationships and lookup and removed all of the lookups in the table but nothing seems to help. One thing I was trying to do was to have an individual start with a frmCustomer then move to frmOrder then frmApparelOrderDetails and have the customerID and Ordganization fields as reference. But somewhere along the way something is in error with the order frm and qryOrders. Sure hope you can take another look at this and help me. I can't really go forward until I fix this situation. S

 

[CazB]

in qryorders REMOVE tblCustomer.... and on the orders form, change the data source of Text62 to be CustomerID, with a default value of =[Forms]![frmNewCustomer]![ID]

then all should be ok

 

[Robison]

wOW...sort of got things going based off of your suggestion (THANKS) but now the form won't enter the data from the fields DueDate, TypeOfOrder and Notes. Just the other infomation. See tblOrders Order numbers 31 through 35. Thanks and hope you can stay with me YOU have know idea how much I appreciate it. S

 

[CazB]

how do you mean, won't enter it?
I added a new order on your form, and the detail was entered?

oh, and you are missing the default value on text62... it should be
=[Forms]![frmNewCustomer]![ID]

 

[Robison]

Another clarification if you don't mind...when you create a query that involves two joined tables (established before hand) do you have to load both of them in the query itself? Also do you use the child field or the parent field in the query? Just wondering if it makes a difference. S

 

[CazB]

I'm not sure I understand your question? What do you mean by 'load' them in the query?

In the query design, you only need to include a table IF

EITHER you want to show info from that table - for example, you want to build a query that shows a customer address, but the order table (which contains everything else you need) only has the customer ID... then you would need to include the customers table to be able to pull through the customer info.
OR you want to limit the query to only show data that has 'matches' in both tables

If, on the other hand, it was enough to show the CustomerID, then you wouldn't need the customers table in there at all.

Is that what you meant?

 

[Robison]

Can you help me understand or explain to me exactly how the information from one form to another using the query join? For some reason the concept is muddled for me. I have read the section on relationship and querys many times but when I try to apply this method my forms don't respond the way I want them to work. Example. I entered a new Customer using frmCustomer...just right as it has been for awhile. Next I used my command button which only says to OpenForm frmOrders. I go to the next available new form and the information is there but when I try to enter DueDate Access tells me I didn't enter FirstName field (which is required in Customer form)...ie I DID enter data there. See why I am getting frustrated. Fix and understand one issue and another one pops up.

If I can just get this one relationship to work right then I have many more I need to develop.

THANKS THANKS THANKS AGAIN FOR YOUR PERSEVERANCE WITH me. S

 

[Robison]

I'm not sure I understand your question? What do you mean by 'load' them in the query?

In the query design, you only need to include a table IF

EITHER you want to show info from that table - for example, you want to build a query that shows a customer address, but the order table (which contains everything else you need) only has the customer ID... then you would need to include the customers table to be able to pull through the customer info.
OR you want to limit the query to only show data that has 'matches' in both tables

If, on the other hand, it was enough to show the CustomerID, then you wouldn't need the customers table in there at all.

Is that what you meant?

Yes....I thought that was the issue however what about the child vs parent field situation. I want to use the ID and Organization from the parent table but the CustomerID is the joined field. Would I use ID or CustomerID in the query.

 

[Robison]

One other thing I was wondering about is how to open a form and go straight to the new entry. I have seen conditions that were set =1=0 but not sure what that tells the system. Also have seen a strange condition starting with Do....

Can you help with with this command setup in the condition field? Super being able ask someone questions that know what they are doing. SSSSS

Do I always have to use the default value in my forms when using tables? Control source tblColors...RowSource =tblColor![Color]...would I still need to populate the Default Value =[Color].

I know this is a bother but don't know were else to get my questions answer....thanks so much

 

[CazB]

Example. I entered a new Customer using frmCustomer...just right as it has been for awhile. Next I used my command button which only says to OpenForm frmOrders. I go to the next available new form and the information is there but when I try to enter DueDate Access tells me I didn't enter FirstName field (which is required in Customer form)...ie I DID enter data there.

In your Orders form, you were including the customers table. So, when you add a new order... it thinks you are adding a new customer as well. You don't need the customers table in there - all you need is the customer id field in the orders table.

What you would then need to do is to tell it to use the customer id field from the customers form as the default customer id on the orders form.

I'm not in the office today, will answer your other questions when I am!

 

[Robison]

CazB....along with the other host of questions I have now created a subform to my frmApparelOrderDetail. I have done because there is a lot of details and I couldn't put it on one form. I do use the same query qryApparelOrderDetail and same table tblApparelOrderDetail. Here is the question....how do I tell the database where to put the input? The Source Object is: frmApparelDecoration....Link Master Field OrderID...ike child Fields OrderID. The form data source is the qryApparelOrderDetail. I have great strides thanks to you and I feel I am getting close to all the pieces needed for my Son's business. Again I can't thank you enough for your kindness. (I'm just a old women trying to help her son automate his small business)..S

 

[CazB]

Had a quick look at the changes you've made, and I'm not sure you're going the right way with it at the moment... leave it with me to have a better look!

 

[CazB]

Ok, don't tell my boss, but I got bored today (a meeting was cancelled!) so I've had a play round with your db, did a bit more with it than I intended lol but have a look and see what you think of this?

 

[Robison]

Ok, don't tell my boss, but I got bored today (a meeting was cancelled!) so I've had a play round with your db, did a bit more with it than I intended lol but have a look and see what you think of this?

Holy Crap batman...awesome forms. Will take a few days to digest what you have done but basically I understand it. One thing that is a question that has been addressed but I been trying to understand the basics is that one customer will have many items ordered at once. CustomerA could have three different Apparel orders and two or three trophies all at once. That is one of the things I haven't yet tried to accomplish was multiple item in one order. That is why I was trying to go horizontal and not vertical so that I would stack the items ordered. One thing I was going to ask you about. My understanding was that Acccess would produce a new line on the form once the end of the fields had been reached. THERE isn't enough adjectives to thank you for what you have done so far. I am sure your boss wouldn't be too mad if he knew you were helping someone with your proficient Access Skills. THANKS AGAIN. S

 

[CazB]

ok the multiple lines thing should be easy enough to do - just means changing the subform to be a continuous form, a bit like the Customers form with the list of orders at the bottom, showing a 'summary' of what's been ordered, with an 'Add Order Detail' option like the 'New Order' button'...

 

[CazB]

From now on in, you're on your own, lol....





... at least until Tuesday

(Just noticed you've said you can have both apparel and trophies on the same order - this version allows for multiple lines but of the same type.... see if you can work the next bit out for yourself? )