3 Clicks or 2 Clicks?

[reddevil1]

On frmExpenditure I have the following ComboBoxes:-

TransactionTypeID
ContractID
WorkOrderID (eg. maintenance work)

I am trying to think if there is a user-friendly way to enter data into only 2 of the comboboxes, instead of entering data into all 3?
Note: all the Tables have a Relationship.
Not all Expenditure will be related to a WorkOrder.

Currently, the User has to input 3 fields of data:-

1. Select the TransactionType (I expect there to be many WorkOrders)
2. Then select the ContractID.
3. And then, select the WorkOrderID (but ONLY IF the TransactionType is “WorkOrder”). This field should be left empty if it is not WorkOrder related).

I would appreciate any thoughts you have about if there is a way to only click 2 fields of data, like such:-

1. Select the TransactionType
2. If the Transaction Type is “WorkOrder” then input the WorkOrderID (and the ContractID automatically updates itself).
3. If the TransactionType is NOT “WorkOrder” then only need to input the ContractID.

I have attached a basic database to show the problem better.

 

[Alansidman]

Perhaps, set the on load event for the form to hide the work order controls and then in the after update event for the Transaction ID control, an IF-Then statement to unhide the Work Order control if the transaction id says WorkOrder.

Alan

 

[reddevil1]

Thanks very much for your comments, Alan. Yes it is a good suggestion to hide the WorkOrder control until the TransactionID says WorkOrder. I will probably go down that route.

But, I was also thinking of potential conflicts when the WorkOrder control is populated:-

a) How to remove any data in the Contract control (that may have been populated earlier - maybe by user error etc).

b) What coding to use to remove any Contract data and automatically populate it with the correct ContractID which is linked to the WorkOrder ID.

I hope my explanation is clear enough to understand and I hope there can be some more cool guidance?

Thanks again.

 

[Alansidman]

In response to item a), you could run an update query and set the criteria as needed and update to a null field in the record.

I would do the same for b), run an update query to populate appropriately.

If you need guidance on update queries, look at this link.

http://search.atomz.com/search/?sp-...//www.databasedev.co.uk/table-of-contents.htm

This video, while it concentrates on manipulating data includes a demo on update queries which helps to understand what happens.

http://www.datapigtechnologies.com/flashfiles/datamanipulate.html

Alan

 

[reddevil1]

thanks again, Alan.

have i understood correctly that an update query will update all records?
in this instance, it is only the current record that needs to be checked?

 

[Alansidman]

You can set an update query to update whichever records you want by setting the criteria. In your case then one of the criteria would be the unique record identifier.

 

[reddevil1]

thanks for the clarifications