If field is empty, Then...

[nickdawes28]

Hi guys,

I am trying to achieve the following-

I want to query a table to see if a record exists with a particular field blank. If so, I would like to prompt the user for data.

In real world terms, when assigning an item to a user I would like to first make sure that the item is not already assigned to somebody else. I have 4 fields, UserName, Item, IssueFrom, IssueTo. So when an item is assigned to a user, the first 3 fields are populated and the IssueTo remains blank, until that item is assigned to somebody else.

At the minute I have nothing in place to prevent a user from assigning the same item to multiple users and having multiple records for the same item in the table.

Thanks!

 

[plog]

Explain the real world process a little better. Give me an example or two. How does a new item get initiated into the system--It has no Issue from, right? I don't know if your table is correctly set up for this.

 

[nickdawes28]

Thanks for the prompt reply! I kept my post brief to encourage interest but see now I may have out some important details... let me explain.

3 tables involved, tblEquipment, tblStaff and tblIssue. The primary keys are EquipmentID, StaffID and IssueID.

tblIssue contains EquipmentID and StaffID as foreign fields, and also IssueFrom and IssueTo.

When assigning equipment to staff, an entry is made into tblIssue. Example...

IssueID StaffID EquipmentID IssueFrom IssueTo
1 Bill Spoon 01/01/2004

This works, however nothing is in place to prevent the same equipment being assigned to multiple members of staff or the same member of staff multiple times as IssueID is the primary key. For example...

IssueID StaffID EquipmentID IssueFrom IssueTo
1 Bill Spoon 01/01/2004
2 Ted Spoon 01/01/2004
3 Bill Spoon 01/02/2006

What I would like to do when assigning equipment to staff is to first check tblIssue for that equipment, and then check to make sure the IssueTo field has data. If not, prompt for this data.

Hope this clears things up.

 

[plog]

This is controlled via forms. You need a form based on tblIssue, with inputs for the 4 fields that are input by the end user. The inputs for StaffID and EquipmentID will be drop downs. The Row Source for the StaffID dropdown will be the StaffID from tblStaff, the Row Source for the EquipmentID will be a query that only displays Equipment that is available.

Based on your table/field information you posted this would be that SQL:

SELECT EquipmentID, Iif(IsNull([IssueFrom]) = False AND IsNull([IssueTo])=True, "Unavailable", "Available") AS Availability
FROM tblEquipment
LEFT JOIN tblIssue ON tblIssue.EquipmentID = tblEquipment.EquipmentID

Get some sample data in your tables, paste the above into a query and run it to see how it works.

 

[nickdawes28]

That works, thanks very much! However would it be possible to achieve the same thing while making the equipment available to select? The reason being I would like the user to know the reason why that particular piece of equipment is not available to select- ie. the user selects it and receives a prompt letting them know that this item is already assigned to another user.

I guess this is why I posted the question in the VBA section, I assume some code may be required.

Any thoughts?

 

[plog]

You can make your user interface however you want--from an end point user, this sounds kind of frustrating: there would be a drop down of everything, but if they choose an unavailable item they get a message box they have to click to make dissappear--and theres no way to know if its unavailable until they choose it. My choice would be to make it not even available in the drop down and avoid the message altogether.

However, this is how you would accomplish that: make the Equipment drop down based on tblEquipment and then on the OnSelect event (or another event) of the drop down and in there take that equipment id and see if its Available/Unavailable in the query I posted last. If Unavailable show your prompt and reset the drop down.

Again though, if I was an end user this would be frustrating.

 

[nickdawes28]

There is another side to this that I have not yet explained... Equipment can also be assigned to a "Job", using same process when assigning it to staff. tblJobs has JobID as the primary key and foreign key in tblIssue.

The situation I would like to "user proof" is the following-

User assigns an item to a job, returns the item after the job but forgets to enter an end date into tblIssue. A new user grabs the same item and attempts to assign it to another job, but finds it is unavailable from the dropdown menu. With training, the user could understand this means it is currently booked out on an existing job and will become available once an end date is entered into tblIssue. However they are unlikely to know which job the equipment is booked out to, and is likely to cause more frustration than a popup explaining the problem.

This is "user proofed" to a degree already, in that the process when a job is over is to enter an "end date" for the job which will automatically update the IssueTo field for all equipment assigned to that job. But users may forget to enter this information as well. Basically I have no faith in users!

What I had hoped to achieve was a message to let the user know that the item they are trying to assign to a job or member of staff, cannot be assigned, explain the reason why, and let them know how to fix the problem. Maybe there is a better way of going about this?

Also, if you could elaborate on the VBA required under the OnSelect event I would appreciate it, I am not sure what this would look like.

Look forward to hearing your thoughts, and thanks for your help so far!

 

[plog]

The VBA would be a DLookup(http://www.techonthenet.com/access/functions/domain/dlookup.php) into the query I gave you to determine an item's availability. If its available you do nothing, if its unavailable you shoot out your message. Google "VBA Access Tutorial" and read through a few.

You're explanation of what you want is raising my suspicions of how your tables are structured. Can you post a screenshot of your Relationships window?