Change Field Depending on Combo Box

[Damo1412]

Hi,

I have been asked to create a database for my boss. Whilst I know a little bit about databases I know nothing about programming.

In the database, there is a table called "Jobs" which sales enquiries are entered into. In this table, there are fields called "JobStatus" which has the default text of "Quotation Pending" and a combo box "JobLive" which is a "yes/no" field. Normally a job will come in as quotation request and the "JobsLive" field will be set to "no".

Occasionally, a job will come in as confirmed from the outset. In this case, the job will be marked as "live" in the combo box "JobLive". Is it possible to change the "JobStatus" field to "Job Live" when this combo box is changed to "yes"?

At any other stage, the "JobStatus" field will be changed using an update query as and when changes are made.

From previous advice I've had I am pretty sure that this will be done using the "afterupdate" feature but I am not sure of the syntax for it.

Thanks in advance

 

[EternalMyrtle]

Hi, yes you would use the AfterUpdate for the JobLive control. Make sure you are referring to the control name in your code. Select the control in design view and go to AfterUpdate in the Events tab.

Put in this code:

If Me.JobLiveControlName = "Yes" Then
Me.JobStatus = "Live"
End If

This assumes that the data type for both the combo boxes are text.

Hope this helps.

 

[namliam]

Sounds like status is a derived/calculated field, in proper database design we do not store these fields.

 

[Damo1412]

Thanks EternalMyrtle.

Unfortunately my laptop has just died but as soon as I get it up and running again I'll test that.

Hi namliam, I have heard a little bit about not storing derived and calculated fields although I am not sure if this would fall into that category. The field "JobStatus" will change depending on what is happening with the job.

Although its initial status will depend on the "JobLive" field, it will then be updated by macros as the job progresses through its various stags. This field is the only way the user can ascertain what stage the job is at. Its statuses will include: "Quote Submitted, "Job Live", "Live with Subcontractor", "Completed" and "Invoiced".

If this would still be classed as derived or calculated field can you explain the best way to avoid this. I am always keen to learn the best way to build a database.

 

[namliam]

This field is UTTERLY USELESS...

Quote submitted is probably dependand on a flag or date being set
Job life on this combobox
etc... etc....
Invoiced based on "Invoice date" being filled

It is a simple IIF or if need be simple function, this kind of thing should NEVER be an actual UPDATE on your database. or even be an actual field in your tables.

 

[EternalMyrtle]

Namliam is correct that this doesn't sound like it should be stored in a table.

If this would still be classed as derived or calculated field can you explain the best way to avoid this. I am always keen to learn the best way to build a database.

You could use an unbound text box and make it a calculated control. Or you could make it a calculated field in a query (which is probably preferable if you want to use this throughout) so

Status: =IIf([JobLive]="Yes","Job is Live", "Pending")

And then use that calculated field as your control source. You can use code too.

 

[Damo1412]

Hi namliam,

Thank you for this, I think I can see what you mean.

As the only place the job status would appear is in a report or unbound field in a form and as this is only to advise the user of the status of the job, this does not need to be stored in the database. Instead, the job status can be calculated depending on other entries in the database such as invoice date or subcontractor authorised.

If I have understood this correctly, I would use an IIF command to ascertain what the status is. In other words, if a subcontractor has been entered then the status changes to that, once the work has come back from the subcontractor and a completed date has been entered the status changes to completed etc. When I say "status" I mean what the status field in the report would read not an actual field called "status"

If that is correct, is there a guide to using the IIF command that I can try and work my way through in particular how to order the command so that it will present the latest "status" depending on the last update.

Many thanks

 

[namliam]

See if this helps you
http://office.microsoft.com/en-001/access-help/iif-function-HA001228853.aspx

If the status is dependand on "many" things making the IIF perhaps too complex, you can also very validly consider using user defined function

 

[Damo1412]

Thank you so much for all of your help. As soon as I have a fully working computer again I will give all of this a try.

Thanks again

 

[Damo1412]

Hi everyone,

Thanks so much for the help. This is working fine.

I initially had a few issues as the "JobsLive" field was initially set as a "yes/no" field which didn't work but when I changed the field to text with the combo box restricted to "yes" and "no" it worked fine.

Thanks again

 

[EternalMyrtle]

Sorry, this lead me to believe it was a combo box:

s it possible to change the "JobStatus" field to "Job Live" when this combo box is changed to "yes"?

For yes/no (i.e. checkbox field) you need to use -1 (yes) or 0 (no) or true /false.

 

[Damo1412]

Hi EternalMyrtle,

I'm sure that this is showing my inexperience with databases. The field for the user to select IS a combo box however I created the original field as a "yes/no" one. I'm guessing that that is not best practice.