Big help with my first database

[daniel.ru92]

Hey everyone!
I've been building towards the end of my first database and now I'm stuck with some forms and macro programming.
My database is for restaurant and actually managing all deliveries story with a few additions.
I would appreciate anyone who can help me even a small part so I can finish this project!
Here are the problems:

1.Form "popLogin" - I want the program to check if the password entered is correct, and if so the user will be listed by default in all new delivery. In addition, the name of the employee / active user will footer.

2.Form "popNewDelivery" - the form for entering new delivery - the user can not enter data in it, each time jumping another error. Among other things, it requires the employee data that has been entered in the table of employees (is not supposed to require data on this form).
In addition, I would like to do the following calculations -
packing pay - default 5 percent of the sum of all orders in the delivery (with an option to change).
total price - the total of all current delivery orders packing + pay + change price (If there is).
As I understand from the database of Microsoft northwand It should also reside in the fields of the table to make calculations and later report on it.
In addition, I want the list of streets will be derived from the city selected. I can not get do it.
In addition, the search button next to the field in the VIP augments the form "VIPInfo." I want the information on the form refers to the number of VIP entered the parent form.

3. Form "popNewCustomer" - same problem with the streets and it problems with data entry failed each time.

4. Subform "tabCustomers" - filter buttons do not work correctly. Also address is not displayed and instead an error is displayed.

5.Query 'TodayDeliveries " - Did not make the cut and don't shows deliveries at all.



Here is my database -
http://www.sendspace.com/file/xs3l9e
different link - http://speedy.sh/w735m/Database9.rar

Again, thanks to everyone, even for the smallest help. Hope I can learn this Access quickly.

[June7]

You have coded entirely with macros. I don't use macros, only VBA. I would choose [Event Procedure] in event property then click the ellipses (...) to open the VBA editor and type code.

As a for instance, for item 1; include the password field in the listbox RowSource but hide the column then code in the AfterUpdate event of Password textbox or the button Click:
If Me.Password <> Me.EmployeeName.Column(3) Then MsgBox "Invalid password."

Debugging macros is not fun for me. You are on your own unless someone else wants to tackle.

"It should also reside in the fields of the table" - What is "It"?

I think Address has #Name? error because Unit field is not in the form's RecordSource.

Part of issue with query is the join types. Instead of INNER should be RIGHT:
SELECT Deliveries.ReceptionHour, Cities.CityName, Streets.StreetName, Addresses.HouseNumber, Addresses.EnterNumber, Addresses.ApartNumber, Addresses.Floor, Addresses.Unit, Employees.EmployeeName, Deliveries.TotalPrice
FROM ((Cities RIGHT JOIN Streets ON Cities.[CityID] = Streets.[CityID]) RIGHT JOIN Addresses ON Streets.[StreetID] = Addresses.[StreetID]) RIGHT JOIN (Employees RIGHT JOIN Deliveries ON Employees.[EmployeeID] = Deliveries.[EmployeeID]) ON Addresses.[AddressID] = Deliveries.[AddressID]

I can't get your conditional filter criteria to work. Try (as displayed in the SQL View):
WHERE (((Deliveries.ReceptionDate)=Date()-IIf(Time()<#12/30/1899 6:0:0#,1,0)) AND ((Deliveries.ReceptionHour)>#12/30/1899 6:0:0#));

[daniel.ru92]

My dear friend! Just finished the password and the address problem!! About the rest, I'll do it at the morning...
Thanks a lot, and if you have solution for other problems or somone else could help me, I'll appreciate it a lot!

[June7]

Unfortunately, even my suggested date and time criteria don't make sense. Why does the time of day matter? Why subtract from current date if current time is before 6 AM?

[daniel.ru92]

Our working day is from 11am to 2 am... So if user want to see deliveries from the current working day, he can do it at the next day on 1 am for example...

Can anyone help me with the "popNewCustomer" and "popNewDelivery" forms?

[June7]

Workday crosses midnight. That always complicates date criteria. Seems your criteria won't retrieve records of the current day between midnight and 2 AM.

Are you familiar with macro debugging http://support.microsoft.com/kb/89610.

Most advanced developers prefer coding with VBA and find it easier to debug.

Some questions/observations.

Did you build with Navigation form wizard? I don't like the Navigation form and don't use it. I think it was developed for web database functionality. I prefer a Tab control.

Your delivery form doesn't make sense to me and I think is over complicated. The subCustomers form is a subsubform and could be up one level. The current RecordSource for subCustomers is non-editable (the joins are backwards). Data entry/edit can be done to only one table at a time. Cannot enter info in Customers and Addresses tables in the same form.

Options for Deliveries set up:
1. A form bound to Deliveries table with a combobox to select customer.
2. A form bound to Customers and a subform bound to Deliveries
3. Side-by-side linking of subforms (resorted to when want a form and it's subform both in Continuous/Datasheet view)http://www.fmsinc.com/microsoftacces...edsubforms.asp

The RecordSource for popCustomers has all the joins backwards, Try:
SELECT Cities.CityName, Streets.StreetName, Addresses.HouseNumber, Addresses.EnterNumber, Addresses.ApartNumber, Addresses.Floor, Addresses.Unit, Addresses.Directions, Streets.CityID
FROM (Cities RIGHT JOIN Streets ON Cities.[CityID] = Streets.[CityID]) RIGHT JOIN Addresses ON Streets.[StreetID] = Addresses.[StreetID];

See a pattern here? Need a better understanding of how data relates and how to join tables in queries to get the desired output. The established Relationships are all messed up so when queries are created they adopt those defined joins and then queries don't work as you expect. Seems all the joins are backwards.

I would try to do more analysis but my Access can't handle the language the db is built in. Can't save any edits.

[daniel.ru92]

1. I really do not remember with what I built up the main menu but do you see it hits the integrity of the system in its current form?


2. About the form of deliveries, built examples of Microsoft, has a database called "northwind". There I saw that they also incorporate new delivery entry form and it is quite similar to mine information. I want that once the user wants to enter a new delivery is able set all the information I requested current form. How do I change the configuration, but serve the same purpose? Not through integration with customer form I want to keep it in its current form. This is because most of my restaurant customers regularly visit so this form will be much less used.
I will try to see how I could rearrange the form so that only one table will be edited at any time.

3.

The RecordSource for popCustomers has all the joins backwards, Try:
SELECT Cities.CityName, Streets.StreetName, Addresses.HouseNumber, Addresses.EnterNumber, Addresses.ApartNumber, Addresses.Floor, Addresses.Unit, Addresses.Directions, Streets.CityID
FROM (Cities RIGHT JOIN Streets ON Cities.[CityID] = Streets.[CityID]) RIGHT JOIN Addresses ON Streets.[StreetID] = Addresses.[StreetID];

I could not figure out what form you're talking about. Form "popNewCostumer" source should be related to the fields of the table "Costumers" and not table "Addresses".

5. About the pattern. Is my relationship building was not correct? As I understand it I build tables with relationships correctly. But these forms are tangled success.

6. About the problem of language. The previous version database was entirely in a foreign language. The desire for help from the forum I built a new database entirely in English. Would love to to know where were language problems and how they affect your use. Maybe because I built the database in 64 bit causing the problem. If so, I'd love to know how I can customize the system for your use.


Thanks

[June7]

1. not sure what you mean by 'hits the integrity'.

2. I've never taken that close a look at Northwind but can't imagine tables are related as they are in your db.

3. Sorry, the form I meant is the one named subCustomers

5. Relationships you have set up won't work.

6. Every time I try to save design edits of form I get error message:
"Microsoft Access cannot save the form or report, because it displays characters from a language that cannot be saved in your current system locale. Switch your system locale to the language in the form or report, and then try again."

[daniel.ru92]

1. Affects the functioning of the system.
2. OK I will rearrange the form.
5. Can you tell me where are the problems with my relationships?
6. About language problems. I went back and changed name of each control built in into English. Hope that now it will be fine. If not probably the only solution would be to install Hebrew on a computer (perhaps as a thank I'll teach you the language later )

In any case I brought my current database. Still not worked on the form of new delivery.
Other problems resolved except subCustomers I did what you said and now have another error. ADMIN user login password is 1234.
http://speedy.sh/CRRCj/Database9.rar

[June7]

1. Navigation form in and of itself does not impair system integrity. It is your triple-tiered form arrangement, bad relationships, multi-table queries with bad joins.

5. Relationships are backwards.
Instead of "Include all records from Addresses and only those from Customers that match", try "Include all records from Customers and only those from Addresses that match". Instead of "Include all records from Employees and only those from Deliveries that match", try "Include all records from Deliveries and only those from Employees that match.", etc.

6. Now when I try to open form Main, get error message:
"The Expression On Load you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE service or ActiveX Control." I click OK and the form opens. I also still get the language error but it does let me save design edits. Weird.

Why does form subDeliveries have a RecordSource that is the Addresses table instead of Deliveries table?

The popNewCustomers form doesn't make sense. There should be a combobox to select an address for the customer record because the relationship is to save AddressID to Customer table, not VIP to Address table.

As for popNewDeliveries, again, the RecordSource is a query that has the join backwards.