Solved Forms based on queries (1 Viewer)

[AnilBagga]

I have a form where the RecordSource is a query. In this query I have an expression as under and display this value of QryCode in the form using an unbound text field

QryCode: IIF(Left([itemmastertbl].[internalcode],1) = "B" or Left([itemmastertbl].[internalcode],1) = "F",
Mid([itemmastertbl].[internalcode],3,4),
IIF(Left([tblItemMasterPricingSpecs].[FGCode],1) = "F" or Left([tblItemMasterPricingSpecs].[FGCode],1)= "E" or Left([tblItemMasterPricingSpecs].[FGCode],1)="T",
Left([tblItemMasterPricingSpecs].[FGCode],4)
))

If the QryCode in the form is blank, I need the user to be able to enter a value in another field and store this in the table tblItemMasterPricingSpecs. This is not difficult as this table is part of the query which is the source for the form. The control of this field is "InputCode"

What I want to do is

a) Display the QryCode value in InputCode and store this value in the table
b) If QryCode is Null, allow the user to add a value in InputCode and store this value
c) If QryCode is NOT NULL, and a value is displayed, prevent the user to edit the value in InputCode

I want use this InputCode value in another query

How can one achieve this?

 

[theDBguy]

Hi. Have you tried using the Form's Current Event to do it?

 

[AnilBagga]

I have not and dont know how to do it? Can you give some idea how to approach the problem. I want to do it myself if possible rather than have a solution straightaway

 

[theDBguy]

In the Current Event, you can execute the code to save your calculations into a field; although, storing calculated values is not really recommended. You can also evaluate the result of the calculation in the Current Event to see if you need to enable or disable user input into the other field where you want them to enter a value if the calculated value is empty/null.

 

[Pat Hartman]

You seem to have multiple non-atomic columns. NEVER store multiple values in a single control. If you have to look at the first character of a control to make a decision, you should have made TWO columns. Fix the database sooner rather than later to get rid of normalization issues otherwise they will plague you forever.