473,324 Members | 2,178 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,324 software developers and data experts.

How do I keep a combo box value from being changed back to previous value?

Seth Schrock
2,965 Expert 2GB
I have a combo box that keeps track of the status of my jobs. The different options are:

1. New
2. In Progress
3. Completed

When I create a job, the default value is New. What I'm wanting is make it so that once the status has been changed to In Progress or Completed, the ability to change the status back to new is removed. I've gotten as far as to figure out that I need to do this in the Before_Update event of the combo box, but I can't figure out how to test the old value to see if it was greater than 1 (meaning it had a status of either In Progress or Completed) and if the new value is 1 (New) in which case I would cancel the update with a message explaining why the status couldn't be changed to New.

Part of my problem is that I'm not sure what the value of Me.JobStatus (the name of the combo box) is in the Before_Update event. For example, if the status is 2 - In Progress and the user attempts to change it to 3 - Completed, what is the value of Me.JobStatus before the update; 2 or 3? I hope that makes sense.
Aug 17 '12 #1

✓ answered by Rabbit

You may have to Nz() the value.

Edit: NeoPa, true, I think you would would have to requery in the on current event.

13 4849
TheSmileyCoder
2,322 Expert Mod 2GB
Actually the combobox (and textbox) has a property called .OldValue

As I recall the .Oldvalue contains the value of the field when the record was loaded, and retains the value until record is saved, or a new record is loaded.

I think that should satisfy your needs just fine.

Something like this should work (untested aircode):
Expand|Select|Wrap|Line Numbers
  1. Private Sub JobStatus_BeforeUpdate(Cancel as Integer)
  2.  If Me.NewRecord then
  3.    Exit Sub
  4.  End If
  5.  If me.JobStatus.OldValue>Me.JobStatus.Value then
  6.    Cancel=True
  7.    MsgBox "This change is not allowed"
  8.  End if
  9.  
  10. End Sub 
Aug 17 '12 #2
NeoPa
32,556 Expert Mod 16PB
Alternatively, set a WHERE clause in your Control Source SQL such that only values greater than the current value are included in the list. That way only valid selections are even available.
Aug 17 '12 #3
Seth Schrock
2,965 Expert 2GB
@NeoPa, I just tried your suggestion, but the problem is that it make the combo box blank when you are just viewing the job.

@TheSmileyCoder, For some reason the value isn't changing back to the previous value. This keeps me from being able to move to another record. I thought that the Cancel = True part would change the value back to the previous value. I tried changing the value back to be the .OldValue, but I got an error. Here is what I've got:

Expand|Select|Wrap|Line Numbers
  1. Private Sub JobStatus_BeforeUpdate(Cancel As Integer)
  2.  
  3. If Me.NewRecord Then
  4.     Exit Sub
  5. End If
  6.  
  7. If Me.JobStatus.OldValue > Me.JobStatus.Value Then
  8.     Cancel = True
  9.     'Me.JobStatus.Value = Me.JobStatus.OldValue
  10.     MsgBox "You cannot change the status to a previous level."
  11. End If
  12.  
  13. End Sub
  14.  
I have line 9 commented out so that the code could execute.
Aug 20 '12 #4
TheSmileyCoder
2,322 Expert Mod 2GB
Sorry, should have realised that would give trouble. Try replacing line 9 with
Expand|Select|Wrap|Line Numbers
  1. Me.Jobstatus.Undo
Aug 20 '12 #5
Seth Schrock
2,965 Expert 2GB
Bingo! Thanks for your help. This is only the second time that I have worked with the Cancel ability and I haven't gotten used to all the things that are necessary to have everything back to the way it was.
Aug 20 '12 #6
NeoPa
32,556 Expert Mod 16PB
Seth Shrock:
@NeoPa, I just tried your suggestion, but the problem is that it make the combo box blank when you are just viewing the job.
You're absolutely right Seth. I didn't consider that (Ooops).
Aug 20 '12 #7
Rabbit
12,516 Expert Mod 8TB
Seth Schrock
@NeoPa, I just tried your suggestion, but the problem is that it make the combo box blank when you are just viewing the job.
A greater than or equal to should solve that problem.
Aug 20 '12 #8
Seth Schrock
2,965 Expert 2GB
Well, that worked for existing records, but when creating a new record, it is only showing status 2 and 3 (default value is 1).
Aug 20 '12 #9
NeoPa
32,556 Expert Mod 16PB
Rabbit:
A greater than or equal to should solve that problem.
I don't believe that would work reliably in all scenarios. A list of records would only have one control setup to work with and that would require it to be set up specifically for each record.
Aug 20 '12 #10
Rabbit
12,516 Expert Mod 8TB
You may have to Nz() the value.

Edit: NeoPa, true, I think you would would have to requery in the on current event.
Aug 20 '12 #11
Seth Schrock
2,965 Expert 2GB
That worked. I just put the following in the WHERE clause:
Expand|Select|Wrap|Line Numbers
  1. WHERE JobStatusID >= NZ(Forms!frmJob!JobStatus,1)
and then did a requery of the combo box in the form's On_Current event. BINGO! I do like this solution better than my original plan because, as NeoPa said, it makes it so that the wrong values aren't even shown. Thanks Rabbit for the solution and NeoPa for the suggestion.
Aug 21 '12 #12
TheSmileyCoder
2,322 Expert Mod 2GB
I must admit I do like that solution better then my own, since as you mention, it does not present the invalid values.
Aug 21 '12 #13
NeoPa
32,556 Expert Mod 16PB
Always a pleasure Seth.

PS. I like your avatar. V 8-)
Aug 22 '12 #14

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

Similar topics

2
by: Jason McDonald | last post by:
I have a PDF form that has a combo box "no_wireless_mics" with the values "1" "2" and " " where " " is the default. I need a JavaScript that will cause the check box "wireless_mics" to be checked...
8
by: asd | last post by:
I need to find the value/index of the previously selected item of a select list. That is, when the user selects an item from the list and a certain condition elsewhere in the form is not met, I...
1
by: Blake Versiga | last post by:
When editing a datagrid (C#) I need to know if the user changed the value of cell AND what the previous value was. Is this possible? If so how do I get the previous value or at least if the...
6
by: Nad | last post by:
Hello, In order to go back to the previous page from a page with possible postbacks I keep Request.Referrer in ViewState and then use it to redirect to the previous page. However I am losing all...
9
by: MR | last post by:
Why does the string value assigned to the RequestElementName in the SoapDocumentMethodAttribute get changed? The value below with a colon is changed to the hex value surrounded by underscores....
0
by: northshore | last post by:
Hello, I am creating a windows application database. I have a primary table 'Individuals' and a lookup table 'Prefixes.' In the Individuals table, I have a column 'PrefixID' that references...
6
by: Dixie | last post by:
I have asked this question before, but I could not get the suggested solution work. So I will give more details this time. I have an append query that adds several hundred records to a table...
9
by: oriondcv | last post by:
hi, please help me on this issue that im facing... I have created a filtered combo box in ms access. It works but the value it inputs in the database is the primary key value like in my program...
1
by: Roach | last post by:
VB.NET 2005 2.0 Framework application is using a DataGridView for SQL data access and user data entry. As part of exception/error handling, the app wants to advise the user when he/she enters a...
1
by: =?Utf-8?B?cmxt?= | last post by:
I have a gridview, and in the RowDataBound event I do some processing on the data. I need to know if the one of the columns has changed from its previous value, but I do not see how to get the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: 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: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.