473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Field value change on data entry in another field

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
8 8448
NeoPa
32,556 Expert Mod 16PB
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
3,532 Expert 2GB
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
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
32,556 Expert Mod 16PB
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
Stewart Ross
2,545 Expert Mod 2GB
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
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
32,556 Expert Mod 16PB
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
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

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

Similar topics

2
by: ehm | last post by:
I am working on creating an editable grid (for use in adding, deleting, and editing rows back to an Oracle database). I have a JSP that posts back to a servlet, which in turns posts to a WebLogic...
2
by: JC Mugs | last post by:
I have a form that when you tab from field to field that the entry point is blank, but if you point and click in the field it brings up a 0 and data entry is started to the left of the zero. ...
1
by: Manton | last post by:
Hi. I'd like to know if after the records have been ticked , whether one can then have those same records unticked - in case a mistake had been made the first time to tick them....
6
by: Sami | last post by:
Problem: Social Security number being used in a database. First problem is that it will not permit numbers beginning in zero to be entered - it sees it as a null or empty space from what I can...
2
by: Kevin Myers | last post by:
Hello, Have yet another problem on a form that has me stumped. In a table that I am working with, the values for one of the fields are stored in meters. However the values that are supplied for...
2
by: Megan | last post by:
Hi everybody- I have 2 tables, Hearings and Rulings. Both have primary keys called, CaseID, that are autonumbers. I don't want both tables to have the same autonumber. For example, if Hearings...
2
by: AA Arens | last post by:
Somebody willing to assist me in the following? I want to have values from one field copied to another field when I choose choose a item from a drop down menu. I have 2 forms: 1 Company...
9
by: sellcraig | last post by:
Microsoft access 2 tables table "data main" contains a field called "code" table "ddw1" is created from a make table query of "data main" Goal- the data in "code" field in needs to...
8
by: ivijayan | last post by:
HI. I have the following query:- How do i create field which updates its contents on the basis of data in another field in same record. especially if I have a Date field, how can I change the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.