Re-using last value entered

Hi all,
This seems like something that should be simple, but I can't figure it out. I have a database with a table of orders for surveying services requested. We enter the data at the time of the request. I have two fields that I just added to the table for when the field crew actually goes to do the job, one is called CREW and the other is DATE_IN (for date-in-field). I created a form for the field coordinator to use that allows him to enter the data in those two fields only. It uses a combo box for him to select the file number of the existing order, which pops up address, subdivision, etc. data so that he can verify that he entered the right file number (but doesn't allow him to alter that data). I want to simplify things even further for him, because he will have to enter this stuff each morning. I already have the DATE_IF field set to =Date(). Now what I want to do is have the CREW field automatically keep the value that he last entered. For example, if he selects file number 2007-6577 from the combo box, then he enters "Mike & Steve" as the CREW taking the job in the field, then he selects file number 2007-3845...I want "Mike & Steve" to automatically be in the CREW field. That way he can just keep on selecting file numbers that a certain crew is taking, until he is ready to enter a new crew and their series of file numbers. I can't create a combo box for the crew because who goes on what crew is not always the same.

I have a typo above, the Date-In-Field field is called DATE_IF, not DATE_IN.

I have a typo above, the Date-In-Field field is called DATE_IF, not DATE_IN.

Sorry I keep replying to myself, but I have one more clarification that might help:

I would prefer to have the CREW field automatically fill in the same value as last entered on a lost-focus or after-change event from the File number combo box field, unless you have a better idea.
Also, keep in mind that it wouldn't work for it to auto fill using the last record because these won't be in that order. It would only work if it uses what was last entered.

I would create a frmCrew based on your CREW table.
Now add a subform for the orders (accept the proposed link filed in the wizard!) and all your user needs to do is to go to the correct crew and Access will filter the subform to show just the rows for the selected CREW.

Idea ?

Nic;o)

Hi all,
This seems like something that should be simple, but I can't figure it out. I have a database with a table of orders for surveying services requested. We enter the data at the time of the request. I have two fields that I just added to the table for when the field crew actually goes to do the job, one is called CREW and the other is DATE_IN (for date-in-field). I created a form for the field coordinator to use that allows him to enter the data in those two fields only. It uses a combo box for him to select the file number of the existing order, which pops up address, subdivision, etc. data so that he can verify that he entered the right file number (but doesn't allow him to alter that data). I want to simplify things even further for him, because he will have to enter this stuff each morning. I already have the DATE_IF field set to =Date(). Now what I want to do is have the CREW field automatically keep the value that he last entered. For example, if he selects file number 2007-6577 from the combo box, then he enters "Mike & Steve" as the CREW taking the job in the field, then he selects file number 2007-3845...I want "Mike & Steve" to automatically be in the CREW field. That way he can just keep on selecting file numbers that a certain crew is taking, until he is ready to enter a new crew and their series of file numbers. I can't create a combo box for the crew because who goes on what crew is not always the same.

In the Change() Event of the Field that you want to repeat the last value entered into, place the following code. As soon as the User types a single character into this Field, the value from the previously Record is automatically retrieved:

In the Change() Event of the Field that you want to repeat the last value entered into, place the following code. As soon as the User types a single character into this Field, the value from the previously Record is automatically retrieved:

Expand|Select|Wrap|Line Numbers

1. Private Sub CREW_Change()
2.  'Sendkeys "CTRL+apostrophe" - this line is a Comment only
3.   SendKeys "^'"
4. End Sub

That makes a lot of sense (I didn't know how to mimick the keystroke CTRL+apostrophe). But it's not working for some reason. I tried it with a few different events, and here's what it does:

On Change: it puts nothing in the CREW text box, but it also doesn't let me type anything. Besides, I would need to be able to change it to a different crew, just don't want to have to type the same crew each time (we will be entering all the jobs going with one crew, then all the jobs going with another, etc.).

On Enter: nothing happens, but it lets me type.

Before Update: Only repeats last entered crew when I open the form (in this case, it was when I switched from Design View to Form View, the value popped in the box, but not subsequently).

On GotFocus: nothing happens, but it lets me type.

The weird thing that happens on all of the above is that my Logitech wireless keyboard sends the message "NumLock is On" to my screen!

As far as the subform suggestion, I'm not sure I understand exactly what you are picturing as far as the layout of the form. What I have is, in tab order, Combo box: "File Number" (unbound, goes to record selected based on the FILE_NO field of that table), Text box: "Crew" (bound to CREW field), and Text box: "Date In Field" (Bound to DATE_IF field, default value today's date). Also, keep in mind that it wouldn't work to have a list of "crews" to select from because these 2 to 3-man crews are not always the same people working with eachother (an example of a crew that might be entered: "Mike & Sam"

Sorry again for the reply to myself, but I should add one more comment.
Doesn't CTRL+apostrophe just repeat the value from the previous record ? That must be the problem, it isn't going to be consecutive records. The records are entered as we receive the survey requests. Because some jobs are needed sooner than others, they don't go out in the field in the order received. So when adding a crew and date-in-field, it will definitely not be in order. That's why I'm hoping to re-use the last value entered, rather than the value of that field in the last record. Am I making sense?

How about saving the value in a static field every time it is saved then loading it from here at the appropriate time. This is not so obvious but you could try OnDirty so that it's only added in if someone tries to enter new data.

How about saving the value in a static field every time it is saved then loading it from here at the appropriate time. This is not so obvious but you could try OnDirty so that it's only added in if someone tries to enter new data.

As in maybe creating a hidden text box, unbound, that the value is copied into, with the default value of the CREW field set to be a copy of what is in the hidden text box? I'm not sure if that's what you meant, but I will try it!

Sorry again for the reply to myself, but I should add one more comment.
Doesn't CTRL+apostrophe just repeat the value from the previous record ? That must be the problem, it isn't going to be consecutive records. The records are entered as we receive the survey requests. Because some jobs are needed sooner than others, they don't go out in the field in the order received. So when adding a crew and date-in-field, it will definitely not be in order. That's why I'm hoping to re-use the last value entered, rather than the value of that field in the last record. Am I making sense?

Once the Field Co-ordinator enters a value in the Crew Team Field, it can easily be stored in the Registry, then retrieved and used to populate subsequent Records. It will just populate the [CREW] Field, and a new Crew can overwrite this value, then that value will be stored in the Registry. Before I start this coding, is this what you want?

Once the Field Co-ordinator enters a value in the Crew Team Field, it can easily be stored in the Registry, then retrieved and used to populate subsequent Records. It will just populate the [CREW] Field, and a new Crew can overwrite this value, then that value will be stored in the Registry. Before I start this coding, is this what you want?

It sounds perfect, as long as it won't mess anything up on the computer or database. With this solution, would it be computer specific, meaning if Todd enters stuff on his computer, that value won't also show up on my computer when I am in that form? Either way, yes, I think that sounds like the right solution.

It sounds perfect, as long as it won't mess anything up on the computer or database. With this solution, would it be computer specific, meaning if Todd enters stuff on his computer, that value won't also show up on my computer when I am in that form? Either way, yes, I think that sounds like the right solution.

Here is the code which you requested.
__1. After the Supervisor enters a Crew Team in [txtCREW], this value is written to the Registry. If a Registry entry already exists, it is overwritten by this new Team value. If there is a NULL value in [txtCREW], then nothing is written:
__2. After the Supervisor selects a File Number in cboFileNumber, if a value exists in the Registry for a Crew Team, the following code retrieves it and places it into txtCREW. This happens in the AfterUpdatet() Event of the Combo Box as indicated:
__3. Should you enter a new Crew Team, existing values in the Table will persist, but choosing a new File Number will populate txtCREW with the new Team.
__4. If you are interested in viewing the actual Key, you can find it in My Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Surveying Services\Crew Information. I 'strongly' suggest that you look, but do not touch, anything in the System Registry!

I've entered the code, though I've changed the titles (i.e. ORDERS DATABASE instead of Surveying Services, Jobs In Field instead of Crew Information, and CREW instead of Crew Team). Now I'm getting a Update or CancelUpdate without AddNew or Edit. (Error 3020)
Does it matter what I call the section and key (I assume that the application has to match the name of my database file) as long as it's consistent in all of the code?
What could be causing that error message?

I've entered the code, though I've changed the titles (i.e. ORDERS DATABASE instead of Surveying Services, Jobs In Field instead of Crew Information, and CREW instead of Crew Team). Now I'm getting a Update or CancelUpdate without AddNew or Edit. (Error 3020)
Does it matter what I call the section and key (I assume that the application has to match the name of my database file) as long as it's consistent in all of the code?
What could be causing that error message?

Substituting your values would make no difference whatsoever. Are you absolutely sure you are posting the code in the appropriate Events? If so, then post your code and I'll have a look because the code is definately functional. Also. double check the code syntax.

As in maybe creating a hidden text box, unbound, that the value is copied into, with the default value of the CREW field set to be a copy of what is in the hidden text box? I'm not sure if that's what you meant, but I will try it!

Not really no.
Use a static variable in the code. That way, when you assign it in one of your event procedures, it will be easily available.

Once the Field Co-ordinator enters a value in the Crew Team Field, it can easily be stored in the Registry, then retrieved and used to populate subsequent Records. It will just populate the [CREW] Field, and a new Crew can overwrite this value, then that value will be stored in the Registry. Before I start this coding, is this what you want?

Not a good idea to use the Registry for this ADezii. That is complicating unnecessarily.
This should be stored in session level storage - that would be a control on the form or a variable in the form's module.

Sorry I didn't respond earlier but I've only just caught up.

Substituting your values would make no difference whatsoever. Are you absolutely sure you are posting the code in the appropriate Events? If so, then post your code and I'll have a look because the code is definately functional. Also. double check the code syntax.

The error message occurs upon trying to tab, click, etc. out of the txtCREW box, if any text is it in.

I can't find anything wrong, syntax-wise, and I think I have your code in there correctly. Usually when I can't find something, it means that it is right in my face in an obvious place. :-)
Here is the whole code for the form:

SNAP.
I posted a couple of replies before this you both might want to look at.

SNAP.
I posted a couple of replies before this you both might want to look at.

Yes you did! Sorry, it got hidden among the rest. :-)
Are you suggesting that I have the value copy over to, say, a hidden control on the form?

Not a good idea to use the Registry for this ADezii. That is complicating unnecessarily.
This should be stored in session level storage - that would be a control on the form or a variable in the form's module.

Sorry I didn't respond earlier but I've only just caught up.

Point taken, Neo, but I'll have to disagree to an extent. The Registry approach can consist of exactly 6 lines of code, is non-evasive in that the User is buffered from the Registry itself, and is secure in that the average User would never have any idea of how to navigate the Registry to get to this value. The value must persist, and I thought that this was a good approach. On the other hand, it wouldn't be the first time that I was wrong. Good to see you again.

Yes you did! Sorry, it got hidden among the rest. :-)
Are you suggesting that I have the value copy over to, say, a hidden control on the form?

No TDW. I'll refer you back to Post #15 (Copied below for convenience) for the way I'd recommend. I still need to review ADezzi's response about why the Registry isn't a bad idea, but until then, that would certainly be my recommendation.

As in maybe creating a hidden text box, unbound, that the value is copied into, with the default value of the CREW field set to be a copy of what is in the hidden text box? I'm not sure if that's what you meant, but I will try it!

Not really no.
Use a static variable in the code. That way, when you assign it in one of your event procedures, it will be easily available.

Point taken, Neo, but I'll have to disagree to an extent. The Registry approach can consist of exactly 6 lines of code, is non-evasive in that the User is buffered from the Registry itself, and is secure in that the average User would never have any idea of how to navigate the Registry to get to this value. The value must persist, and I thought that this was a good approach. On the other hand, it wouldn't be the first time that I was wrong. Good to see you again.

Good to see you too ADezii :)

This mainly revolves around the necessity for the data to persist beyond the current session.
I would say, from the question, that it wouldn't need to, and my solution works on that basis.
Obviously your's makes much more sense seen from the perspective that it does need to, but I'd consider using a table in the db for holding such data unless the requirement is specifically for the PC rather than the database itself.
Having said that, your solution is clever and unusual (which in itself is typical ;)) and certainly worth consideration if the data does need to persist across sessions.

Good to see you too ADezii :)

This mainly revolves around the necessity for the data to persist beyond the current session.
I would say, from the question, that it wouldn't need to, and my solution works on that basis.
Obviously your's makes much more sense seen from the perspective that it does need to, but I'd consider using a table in the db for holding such data unless the requirement is specifically for the PC rather than the database itself.
Having said that, your solution is clever and unusual (which in itself is typical ;)) and certainly worth consideration if the data does need to persist across sessions.

Always an interesting debate, Neo. See you around.

Good to see you too ADezii :)

This mainly revolves around the necessity for the data to persist beyond the current session.
I would say, from the question, that it wouldn't need to, and my solution works on that basis.
Obviously your's makes much more sense seen from the perspective that it does need to, but I'd consider using a table in the db for holding such data unless the requirement is specifically for the PC rather than the database itself.
Having said that, your solution is clever and unusual (which in itself is typical ;)) and certainly worth consideration if the data does need to persist across sessions.

I apologize if I am sounding dense, but while I understand the concepts, I don't always understand some of the technical terminology. So I'll try to paraphrase what I think you are suggesting: are you saying to use a string? Should I save the value to a string upon an AfterUpdate event, then on a GotFocus event have it retrieve the string, if it's not null?
There would be nothing wrong with having the value saved between sessions, but it's not neccessary. And yes, computer specific is all I need, not database-wide.

Did you see post #8?

How about saving the value in a static field every time it is saved then loading it from here at the appropriate time. This is not so obvious but you could try OnDirty so that it's only added in if someone tries to enter new data.

A static variable in you code (the module for the form) would have something like the following near the top (Outside of any individual procedure) :
Obviously the type of the variable (String; Int; Long; Boolean; etc) would depend on your needs (the type of the field being saved).
Let us know if further explanation is required.

Thank you all for your help.
I have it working perfectly now. That error I was getting with the code for storing the value in the registry was ALSO occurring with the code for storing it as a string. I ended up adding a command that I don't know what it does, just that it stopped the error: Recordset.Edit
Here is the final code for the form:

You're probably better off setting up the control rather than the field of the record set, but if it's working for you then I wouldn't worry.
Access will do a lot for you automatically, but if you go around that (as you're doing here) then it's still possible, but it gets more complicated.

I'm very pleased to hear you have a solution anyway :)

You're probably better off setting up the control rather than the field of the record set, but if it's working for you then I wouldn't worry.
Access will do a lot for you automatically, but if you go around that (as you're doing here) then it's still possible, but it gets more complicated.

I'm very pleased to hear you have a solution anyway :)

Is that what I've done? Like I said, I don't know what that RecordSet.Edit line does, just that it stopped the error. I experimented with stuff to see when I would get the error message. Turned out I would not get it if I did not have any code trying to save the value of the field to anything. As soon as I put in the string, or the code for using the registry, the error would pop up when I tried to leave the control for the CREW field, if it was not null.

You've gone with ADezii's suggestion which is fine.
ADezii & I often differ in our ways of going about things, but that doesn't mean either is wrong (necessarily).
I think that ADezii & I both respect each other well enough to understand that the other, though different, is nevertheless a very experienced and useful asset in these forums.
Perhaps I'll take back my earlier comment. It's not the way I'd have done it, but that's not to say that it was wrong. If you're happy with the results then I'd stay happy and continue to use the way that works for you.

You've gone with ADezii's suggestion which is fine.
ADezii & I often differ in our ways of going about things, but that doesn't mean either is wrong (necessarily).
I think that ADezii & I both respect each other well enough to understand that the other, though different, is nevertheless a very experienced and useful asset in these forums.
Perhaps I'll take back my earlier comment. It's not the way I'd have done it, but that's not to say that it was wrong. If you're happy with the results then I'd stay happy and continue to use the way that works for you.

My thanks go to both of you for your help. The reality is that I don't know enough about it to form a personal preference for one suggestion over the other. I was working on both suggestions, and just happened to get ADezii's to work first. As I said earlier, the error message I got was clearly not a result of your coding, because it happened with ADezii's too. It was something unrelated to either of your codes as far as I can tell. So I threw in that extra line based on something I found in the help file, and it worked. It so happened that ADezii's code was the latest attempt at the time that I got it working.
Again, thank you both!

As I say, as long as you got what you were after we're both happy :)

My thanks go to both of you for your help. The reality is that I don't know enough about it to form a personal preference for one suggestion over the other. I was working on both suggestions, and just happened to get ADezii's to work first. As I said earlier, the error message I got was clearly not a result of your coding, because it happened with ADezii's too. It was something unrelated to either of your codes as far as I can tell. So I threw in that extra line based on something I found in the help file, and it worked. It so happened that ADezii's code was the latest attempt at the time that I got it working.
Again, thank you both!

Glad to help.