form with subform

[Kelly@AllenEnt]

Thank you for your help. Once you said SQL I found the view you were talking about. I will learn this program yet especially with nice people like you helping me out!

 

[bob fitz]

Thank you for your help. Once you said SQL I found the view you were talking about. I will learn this program yet especially with nice people like you helping me out!

Thank you for your kind words. I wish you well with your learning.

 

[Kelly@AllenEnt]

Is there a way for the checkbox under "PAID?" to automatically check when the "OUTSTANDING" becomes $0?

 

[bob fitz]

Is there a way for the checkbox under "PAID?" to automatically check when the "OUTSTANDING" becomes $0?

Yes, I think that could be done but I would urge you to look at some basics first before developing further. I am particularly concerned about your table structure and relationships.
Can you explain in a few words what the db is about and how you expect to use it.

 

[Kelly@AllenEnt]

We currently use dbase III and this is to replace that. We have jobs that we receive commission on. The purpose of the data base is to track the payments against the jobs to ensure we collect all of our commission.That is as concise as I can make it.

Customer: contains customer # and name
Orders is the most involved and you have been helping me with that.
Airport Codes is to track the airports location and contact person.
Payments: invoice #, amt, date, and commission rate.

For some reason, Access automatically created some of the relationships and I don't understand why. I have tried to delete the duplicate tables from the relationships but they automatically reappear.

I zipped the newest version of the Dbase. I have deleted some of the information because it was just place holders to ensure some items were working properly.

 

[bob fitz]

I have made some changes to your table relationships.
I have noted the Primary and Foriegn Keys in the tables.
I have removed "Customer Name" field from the "Orders" table.
I have changed the query "ALL ORDERS QUERY".
I have changed the form "ORDER ENTRY FORM WITH PAYMENTS".

It would be best to remove all non AlphaNumeric characters and spaces from your field, table, query, and form names.

Is there a way for the checkbox under "PAID?" to automatically check when the "OUTSTANDING" becomes $0?

Do you still want to do this?

The attached DB has the changes that I have made.

 

[Kelly@AllenEnt]

Yes....I would still like the the checkbox under "PAID?" to automatically check when the "OUTSTANDING" becomes $0.

I am looking at the changes now.

 

[bob fitz]

Yes....I would still like the the checkbox under "PAID?" to automatically check when the "OUTSTANDING" becomes $0.

I am looking at the changes now.

Do you intend to make any of the other changes that I suggested?

 

[Kelly@AllenEnt]

I am looking at them now and will come back to you shortly.

 

[bob fitz]

Yes....I would still like the the checkbox under "PAID?" to automatically check when the "OUTSTANDING" becomes $0.

Set the Control Source property of the check box to:
=IIf([OUTSTANDING]=0 And [COMMISSION]>0,-1,0)

Remove "PAID?" from the table and any queries in which it appears at the moment. Because it is a calculation, its value should NOT be saved in a table. It should be calculated whenever and wherever it is need with the calculation used above.

Also, change the Control Source property of the "OUTSTANDING" textbox to: =Nz([COMMISSION],0)-Nz([txtTotalPayments],0)

 

[Kelly@AllenEnt]

I cannot keep all of the changes you made. I need the Customer name to appear in the Order Table. It is imperative. It is not stored there but my Query pulls the name for it. My boss needs to see them all together. Just the number is not helpful! He is going to look at the datasheet view of the query and need to see the information there. He does not want to switch back and forth to see the number and the customer name.

In a previous thread that I posted you will see all the work it took to get the query to pull that information.

Are the spaces in the name really that bad? Once I change them in the tables, I have to make the same changes in all the forms, queries, and reports. I just finally got them working properly and I am afraid to mess them up. If I need to make the changes, I will make them in a previous version that did not remove the Customer Name. Please let me know how to proceed.

 

[bob fitz]

I cannot keep all of the changes you made. I need the Customer name to appear in the Order Table. It is imperative. It is not stored there but my Query pulls the name for it. My boss needs to see them all together. Just the number is not helpful! He is going to look at the datasheet view of the query and need to see the information there. He does not want to switch back and forth to see the number and the customer name.

It should not be in there and you do not need it in there. With respect, you just need to learn how to use the DB correctly. Copy and paste the SQL statement below into a new query. Then change to design view to see how it is done.

SELECT ORDERS.[CUSTOMER #], CUSTOMERS.[CUSTOMER NAME], ORDERS.[ADB ORDER #], ORDERS.[ORDER DATE], ORDERS.[ORDER AMOUNT], ORDERS.COMMISSION, [COMMISSION]/[ORDER AMOUNT] AS RATE, [Commission]-Nz(DSum("[PAYMENTS]![PAYMENT AMT]","[Payments]"," [PAYMENTS]![ADB ORDER #] = '" & [ADB ORDER #] & "'"),0) AS OUTSTANDING, ORDERS.[EXPECTED SHIP], ORDERS.[LAST PAID], ORDERS.[COMPLETED?], ORDERS.[PAID?], ORDERS.CODE
FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.[CUSTOMER #] = ORDERS.[CUSTOMER #];

Are the spaces in the name really that bad? Once I change them in the tables, I have to make the same changes in all the forms, queries, and reports. I just finally got them working properly and I am afraid to mess them up. If I need to make the changes, I will make them in a previous version that did not remove the Customer Name. Please let me know how to proceed.

No, the spaces are not really that bad. As you progress with the development of your DB, you will have to enclose field names in [] square brackets. However, IMHO it would be best to at least remove the non AlphsNumeric characters. Things like #?
There's not many of them. If you are concerned about removing those without making a mistake, I am happy to give it go for you.

Did you see my last post #30 regarding "Paid?"

 

[bob fitz]

I have made the changes that I suggested in my last post. I have also included the example of a query using joined tables. The DB is attached.

 

[Kelly@AllenEnt]

I do not take offense to saying that I need to understand the DB more fully. But I also know that when the boss is specifically asking for something, I have to deliver or I have failed. I admit when I need help and that is why I am here.

The Query you are asking me to examine is not working. When I click on it it is asking for different parameters. It also has an error message when I try to open it in design view.

I need to be able to view the name on my Order Form. That is the requirement. I need the form "ORDERS ENTRY FORM WITH PAYMENTS" to work AND display the customer name.

My report is no longer working. I spent a long time properly formatting to my boss's specifications. Transitioning from dbase III to Access has be be flawless and look as much like the dbase reports as possible before he will make the switch. My hurry is that dbase is becoming obsolete and this replacement is needed.

I am familiar with the [] so that is not a problem. I completely understand and I use the [] whenever I mention a field name.

 

[bob fitz]

My apologies. I made a mistake on the form. That's now fixed.
I had not realised that there was a report. I will look at that now.

 

[Kelly@AllenEnt]

Thank you! The report is the main thing the boss looks at.

Just so you know, this is as advanced as the database is going to get. It may have a few more queries or reports created to look at the information in different ways but this is as complex as it will get.

 

[bob fitz]

OK I think the report is as it should be, but if not, post back.
DB is attached.

 

[Kelly@AllenEnt]

The Query you are asking me to examine is still not working. If I click on design view it says "Access can't represent the join expression CUSTOMERS.[CUSTOMER#] = ORDERS.[CUSTOMER #] in Design View.

The report is working well. Thanks for your help with that.

 

[bob fitz]

My! I seem to having a bad day. I'll be back soon.

 

[bob fitz]

Right. Sorry about the error. Please take a look at the attached DB.