Form designing.

[Missinglinq]

...BTW, I would use queries for the record sources of the form...If you use tables, the data won't/can't be sorted.)

While I always use Queries as the basis for Forms, for a variety of reasons, Forms based directly on a Table can certainly be sorted, using the Form's OrderBy Property.



Linq ;0)>

[ssanfu]

Add "DeptID_FK" to the "Location" table.

The SQL for "cboLocation" would be:

SELECT LocID, Location FROM Locations WHERE [DeptID_FK] = Forms!YourForm.cboDepartment

("cboLocation")
Bound Column =1
Column Count = 2
Column Widths = 0 'hides the first column

The After Update event of cboDepartments should have

 Me.cboLocation.Requery
 Me.cboLocation = null

[glen]

Thank you very much for your help.
I am just curious about table Location.
there are different locIDs for same Location.
like 1 for Factory A
and then 4 is also for Factory A.
So is it Ok for quaries filtering or some other programming?

[ssanfu]

I made up some data to demonstrate the different locations based on selecting different departments.

You said: "I want that first I will select department from combo1 then Location so it should show me records of that department and location."
So one department can have (be in) many locations.

If you want a list of unique locations, you would use: "SELECT DISTINCT LocDesc FROM Locations ORDER BY LocDesc"

In a query, going from Orders to location and department, there will be one Location.LocDesc for each Order.LocID and one Department.DepartDesc for each Order.DeptID.

[glen]

I think my main problem is in table relationships.The design which I want I can draw on paper but
I can't make it in access.So here I am attaching my design and a sample database.
Please you make table relationships for me in that style which I draw.
Thanks

[ssanfu]

Not sure how this will act/look in Access 2012. Tried to keep it like looking like shown in your attachments.
The combo boxes for Items and UOM (??) do not have a row source - probably need additional tables.
I used different BG colors to show which sub form was which. Dates could have display formats added.....

Personal comment:
I don't like the sub-sub form arraignment. I would use list boxes with additional forms to add data. But this is your project.

[glen]

Yes everything is working fine.
but the two combo boxes "Department" and "Location" is not filtering records.
I am working on this. If not solve from me then I will ask you.

[glen]

I tried my best but could not solve this problem.
the two combo boxes Department and Location not showing their related records.
and in your sample database these two combo boxes are not showing in form mode So that I check that in your
sample database it working or no.
but I checked in design mode and applied the same procedure in my database but not working.

[ssanfu]

but the two combo boxes "Department" and "Location" is not filtering records.

Don't understand. They are not for filtering, they are for entering data.

Do you want to post your Access 2010 dB?

[glen]

I am so sorry.
due to my poor English I can't explain properly that what I want.
Actually these two combo boxes are not for entering data.These are only for filtering to show data of form "deliveries" and "orderdetail".
If I want to make new order for some different department and location then I will make new order from some other form.
I will use only deliveries and orderdetail form here for entering new data in existing Departments and Location.
Suppose I make an order for department "Production" in Location "Factory" then next time I will never make new order for this department and location.
In deliveries serial 1 I will send items in orderdetail.
Next time if I want to send items in this department and location.I will just select "Production" from combo "department"
and "factory" from combo Location. So it will show me all previous records.Now if I want to send other items in this order I will just click on New blank record
of form "deliveries" so it will jump to serial 2 in same order but only serial will change.before I sent items in serial 1 and now I will send in serial 2.
and department, Location and Order no. will always remain same.
Note: If you check my drawing in post no. 20 which I made for table relationship there I show what I want.

[ssanfu]

Been very busy...

Took a while to understand what you were trying to do. Had to change some record sources (from tables to queries).

I added a couple of records to Department: "Finishing" , so there are test records for Departments Delivery & Finishing.

See zip file...

[glen]

Thanks for your reply.
Ok I will check then I will reply.

[glen]

I checked all. It is exactly the same which was in my mind.
Thank you very much.
My problem solved.

[glen]

Mr. Ssanfu you put this expression for Serial number :=IIf(IsNull([Forms]![ORDERS].[ORDERID]),0,Nz(DMax("serialNO","DELIVERIES","ORDERID = " & [Forms]![ORDERS].[ORDERID]),0)+1)

which related to order id. when we put order date it will show serial 1 in serial number.
I want to relate it with delivery date so for this i create this expression but it is not working plz you check that where is problem.


=IIf(IsNull([Forms]![SUBFORMDELIVERIES].[DELIVERYID]),0,Nz(DMax("serialNO","DELIVERIES","DELIVERYID = " & [Forms]![SUBFORMDELIVERY].[DELIVERYID]),0)+1)

[glen]

Ahh Ok My problem solved.
I just put this code in subformdelivery before insert event and it working fine for me.
serialno = Nz(DMax("serialno", "deliveries","OrderID=" & Forms!Order!OrderID)) + 1

Now I want that when ever I click on New (blank) Record of sub form deliveries then it should give me message that
" Enter Delivery Date First"
I have little bit idea that len expression will use for this.
But I don't know in which event I have to put this.
I tried in sub form deliver before event but it is not working.