Conditional Data Entry Into Tables (1 Viewer)

[hassanlabban]

Dear All
I am new to Access in a way, and developed a small tool as a warehouse management system. the system is working great but I need to add a rule or condition to this tool.
here is the situation:
I have Tables having warehouses locations (LOCATION1,LOCATION2, LOCATION3, etc...)
some items (products) will be entered into a transaction table where with queries and reports, can see each item in which location it is or in a particular location what are the products stored in.

however, if there is no limitation or condition in a way or another into the access application to limit users from entering an item OUT in the transaction from a location which doesn't have this product, I need access to tell me a message or stop me from entering that transaction.

to explain more, if I have ProductA in LOCATION1, and one of the data entry guys entered a transaction in LOCATION2 for the same ProductA, access will not allow him since there is no quantity of that particular product in that LOCATION2.

Any ideas how to do it? I checked in Data Validation and Validation rules, but its either hard for me to understand or am not getting how to do it?

I truly appreciate any help from you guys and thanks again for your help in advance.

 

[Uncle Gizmo]

>>>I have Tables having warehouses locations (LOCATION1,LOCATION2, LOCATION3, etc...)<<<

That's not the way to do it....

See Here:-
http://www.access-programmers.co.uk/forums/showthread.php?p=671226

 

[plog]

Gizmo thinks you have an improper structure, I'm on the fence. In your table do you have multiple fields for locations (Location1, Location2, etc.) or do you have one field which allows multiple location values (Location)? Are you listing locations vertically (with more rows) or horizontally (with more columns)?

The first one is wrong, the second is proper. With the second method you simply need to create a query to sum up the inventory at each location, look at it everytime someone is going to enter data and prevent them from doing so if there isn't quantity at a location. In fact, it would be best if that query fed a drop down on the input form. That way youre query could feed the drop down and only show those location values with quantity.

Again, though tell us about the table set up. Post a screenshot of your table structure or list it out here like so:

Table1NameHere
Field1NameHere - Autonumber PK
Field2NameHere - Date field
Field3NAmeHere - Text

Table2NameHEre
Field1NameHere - Autonumber PK
Field2NameHere - Number (Foreign key to Table1NameHEre)
Field3NameHere - Text

etc.

 

[hassanlabban]

HI Plog, thanks for your reply.
in fact, I have a table called locations, and have the locations listed vertically down showing all the locations under a column called Location. and in total I have 4500 locations in my warehouses.

I didn't get what you meant by a form from query, and am worried that incase I did this, the access will not calculate new transactions to deduct and add items.

attached is a pic of the locations I have and the query design I did to check in each location what are the items in it.

 

[plog]

How are users entering data into your database? If the answer is directly into a table or query, then what you want to accomplish isn't possible. Neither a table nor a query can apply logic to control what gets entered via them. To do that, you need to create a form.

That way you can test teh user's input to make sure it conforms to your logic. On that form, instead of letting them type in location names, you provide them with a drop down to select a location. That drop down would apply your logic and only allow show locations that meet your criteria.

So, your table seems to be structured properly (or doesn't have the issue Uncle assumed it did), and this issue you posted about is a Form issue and not a table/query issue.

 

[hassanlabban]

Dear Plog and Uncle.
your inputs are really amazing and very valuable for a newbie like me.

Dear Plog, I use forms to enter data and never into a query directly or a table.

I will try to accomplish what Plog said about creating a query to show the locations of a specific Item and use this query in the form of entering the data into my tables. Please bare with me friends till I get to know how to do this and forgive me if I come again for new help in this.

Thanks

 

[hassanlabban]

OK, I kind of getting hit with a big brick here,
am not sure if am looking correctly, but I seem not to be able to create the data entry form having the list box based on the query to limit users in selecting the right warehouse location. would anyone be able to help in that either by referring me to a tutorial for dummies on how to create it or help in doing that form? am getting really frustrated and at the same time I need to get it done

:banghead::banghead::banghead::banghead::banghead::banghead:

 

[plog]

Can you post your database?

 

[hassanlabban]

Hi Plog
Please find attached the database I did. Its a .rar file inside a zip file. this is the only way possible to upload my db to you.

Thanks Sir

 

[plog]

No, I think you can put an .mdb inside a zip and upload it. I can't open a .rar

 

[hassanlabban]

Dear Plog
The size of my mdb is hige and will not be smaller than 2mb to attach here

 

[plog]

Make me a copy with just the entities I need to see. Remove some of the records from your tables as well.

 

[hassanlabban]

Dear, can I send it to you by mail? its still 2.4mb when zipped with all the not required tables and reports in the database,. still cant send it here.

 

[plog]

That database has more than 10 tables, more than 10 queries, more than 10 forms and more than 10 reports. I'm not digging through that trying to find out what you need.

Either tell me what I need to look at, or provide me with just the objects I need.

 

[hassanlabban]

Dear Plog
here is the summary. The transactions table is the table where all the movements of in and out of the locations be recorded. The locations table is called Locations. The form for entering the data into the transactions table is called ADD TRANSACTIONS for adding items and REMOVE TRANSACTIONS for anything going out of the warehouses. these are the only ones you need to concentrate at in addition to the query called TransactionsQuery which gives the sum of pieces in each location which each item is filling in the warehouses. The logic is to have the form REMOVE TRANSACTIONS Have as you told me earlier a limited selection in the LOCATION field in that form to show only the locations where these items have a positive sum or number.

 

[plog]

Where are you stuck? What have you tried to accomplish?

 

[hassanlabban]

Hi
I tried to re-create the transactions forms using a query to give me available locations to select from which in turn this will be posted into a transactions record in the transactions table, but didn't succeed in that.
I basically want to accomplish a limitation on the choices I can use for the locations field whenever I am entering an item number (which has a positive number in a certain location.)

 

[plog]

The forms don't need to be completely redone, you just need to change the location input to a drop down. That drop down needs to be fed from a query that lists only the locations that meet the criteria.