By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Field value change on data entry in another field

P: 6
Hi there ppl, I hope you guys can help me.

I have two fields: [Haulier Invoice Number] and [POD Status]. I've set the POD status' default value as "Awaiting". There are only 2 possible values: "Awaiting" and "Recieved". What I'd like to do is: If the [Haulier Invoice No] is empty the [POD Status] should be "Awaiting". But if [HIN] gets a value [POD S] should change to "Recieved Automatically.

Please guys I really need help.

Thanx
Oct 27 '08 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,273
Are we talking about changing it whenever found?

Is this on a data entry / update form?

Is this simply about defaulting, or is the value to be determined by changes to [Haulier Invoice Number] alone.

NB. Spelling of received. This can cause problems if you're not careful.
Oct 27 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
Assuming that this is a data entry form, with [POS Status] Default Value set to "Awaiting" this code will set it according to your specs:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Hauler_Invoice_Number_AfterUpdate()
  2.  If IsNull(Me.Hauler_Invoice_Number) Then
  3.    Me.POD_Status = "Awaiting"
  4.  Else
  5.    Me.POD_Status = "Received"
  6.  End If
  7.  End Sub
Welcome to Bytes!

Linq ;0)>
Oct 27 '08 #3

P: 6
Thanx 4 the reply.

It is a data entry form. With a bunch of fields, but the fields concerned are [Haulier Invoice] and [POD] status. The [POD Status] field is set to "Awaiting" as default because the [Haulier Invoice] is empty on entering data and for a few days afterwards. But as soon as a value is entered into [Haulier Invoice] I need the [POD status] to change to "Recieved". Thanx for ur help so far. Where should I enter that code?

Thanx.
Oct 28 '08 #4

NeoPa
Expert Mod 15k+
P: 31,273
Linq's logic will work for you then. You will need to be careful as you seem to write the field names differently every time - even when you're trying to explain their exact names.

You can take take the code and change it to match the actual names in your project. This code would be put in the form's own module.

Remember, "Received" is spelled as I have, and not with the i before the e (as I brought to your attention previously). Attention to detail is a fundamental requirement for working with databases. Ignore it at your peril.
Oct 28 '08 #5

Expert Mod 2.5K+
P: 2,545
Hi. A further observation is that if the only circumstances in which [POD Status] takes the value Received is when there is an invoice number recorded there is no need to store the value as a discrete field. In such circumstances you could use a query in which you include a calculated field to indicate the POD Status. The calculated field can either use an In-line IF (IIF) statement to test the invoice number field for null, or a custom function based on Linq's example to do so.

The advantage of using a calculated field is that it is calculated automatically on entry or clearance of the invoice number. This does away with the need to use the After Update event to set the field contents altogether, and it prevents anomalies that could arise from the status field not accurately reflecting the real status of the record concerned. This can arise if, for example, a user records an invoice number against the wrong record, then subsequently corrects the record by removal of the value from the invoice number field. Doing this will leave the discrete status field still at its Received setting unless it too is changed manually. This possibility for error does not arise if a calculated field is used instead.

-Stewart
Oct 28 '08 #6

P: 6
Guys got it working. Ended up using the following code: IIF([Haulier Invoice No]="","Awaiting","Recieved"). It did the job perfectly. Thanx to all of you who helped me, and thanx for all the spelling tips ;-).

Cheers 4 now
Oct 29 '08 #7

NeoPa
Expert Mod 15k+
P: 31,273
Pooh,

Pleased you got it working, but don't think the spelling tips were about me being anally retentive (I am, but that's not the point). They are about creating problems for yourself later on down the line.

Consider a name like [a6705gh]. Not easily recognisable, therefore always spelled as seen. A word that is recognised, on the other hand, is likely to be entered wrongly (by a user or by a developer working on your project sometime in the future). If they recognise the word, they will assume it is spelled in the normal way and you then have problems.

This is your choice of course, but it would be remiss of me not to point out this obvious problem in your coding if I see it.
Oct 29 '08 #8

P: 6
Pooh,

Pleased you got it working, but don't think the spelling tips were about me being anally retentive (I am, but that's not the point). They are about creating problems for yourself later on down the line.

Consider a name like [a6705gh]. Not easily recognisable, therefore always spelled as seen. A word that is recognised, on the other hand, is likely to be entered wrongly (by a user or by a developer working on your project sometime in the future). If they recognise the word, they will assume it is spelled in the normal way and you then have problems.

This is your choice of course, but it would be remiss of me not to point out this obvious problem in your coding if I see it.
Thanx man, I truly understand ur point. Will implement it in the future.
Oct 29 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.