Carrying Values forward into Subsequent Records 7

[thirty4d]

I don't want to use an Update query if I can avoid it because I need to automate this task and to be as user friendly as possible. I need Access to provide the ability to StoreValues() from the current record and RestoreValues() for the next 9 subsequent records in a recordset.

For example: A user needs to add a subset of records in a form where 3 of the fields has identical information. I don't want the user to loop through all these records and enter the same information for these 3 fields over and over.

Any suggestions?

Thanks!

 

[AlexMidd]

Well, there are ways that you could automate it but it would involve complicated code. A simpler way is that a user can hit Ctlr + @ and the value from the previous record goes into the new record. Not perfect but saves typing.

Have fun! )

Alex Middleton

 

[thirty4d]

Alex Middleton


Thanks for the suggestion and I will give it a try.

I read an article that says if you can avoid writing code the better. If it works, why not!

 

[downwitchyobadself]

The ctl+@ tip is a good one, didn't know about it. But the code is pretty simple to write. Put a check box on the form which says "keep these values", or some such. when ckKeep is checked, on the form's AfterUpdate or BeforeInsert events (up to you to see when):

Private Sub Form_AfterUpdate()

    If ckKeep then
        MyCtl1.DefaultValue = MyCtl1.Value
        MyCtl2.DefaultValue = MyCtl2.Value
        'and so on
    End If

End Sub

Using a multi-select list box, you can even let users decide which values to keep and which to throw away, key your code to the ItemsSelected in the list.

 

[ruthcali]

Alex, does that Ctrl + @ trick only work for Access2000? i am using Access97 and i can't get it to work.

does it work for all forms in single, continous and datasheet views? ruth.jonkman@wcom.com

 

[tempclerk]

Try ctrl + ' (apostrophe) That works in lots of places, many programs, for many years. I never heard of the ctrl + @ before either.

 

[mac318]

This appears to be a perfect place for the 'STATIC STATEMENT'. The below example was copied from Access97 help.

This example uses the Static statement to retain the value of a variable for as long as module code is running.

' Function definition.
Function KeepTotal(Number)
' Only the variable Accumulate preserves its value between calls.
Static Accumulate
Accumulate = Accumulate + Number
KeepTotal = Accumulate
End Function

' Static function definition.
Static Function MyFunction(Arg1, Arg2, Arg3)
' All local variables preserve value between function calls.
Accumulate = Arg1 + Arg2 + Arg3
Half = Accumulate / 2
MyFunction = Half
End Function

mac

 

[mac318]

In Access97:
The ANSI number for the keyboard combination CTRL+@ is 0. Because Microsoft Access recognizes a KeyAscii value of 0 as a zero-length string (" &quot, you should avoid using CTRL+@ in your applications.

mac

 

[AlexMidd]

The Ctrl + @ works in Access 97 as my users use it regularly. They have to input several thousand items, many of which have similar or identical descriptions. It saves them a lot of time and has never caused any problems. Have fun! )

Alex Middleton

 

[Frink]

Hallo,

On my keyboard @ and ' are the same key,
on, and thirty4d, you can never have too much code
provided it's written right,

- Frink

 

[mac318]

You're right Alex, in that, the Ctrl+@ does work. The problem arises with previous fields containing Null. It may, or may not, give you what you expect.

I prefer using module level variables to hold the value of the previous field. In the AfterUpdate event, I set the variable with the field entry. In the LostFocus event, I test for Null. If the entry is Null then I replace the Null value with the variable. Data entry is much quicker using this method because the user only needs to hit the enter key to advance to the next field.

FieldName_AfterUpdate()
VariableName = FieldName

FieldName_LostFocus()
If IsNull(FieldName) then
FieldName = VariableName
End If

Much faster than locating the Ctrl+@ keys in each required field.

mac

 

[AlexMidd]

This is true, mac. My suggestion was just a quick fix. The code options are certainly more reliable. Have fun! )

Alex Middleton

 

[raskew]

A fairly simple way to carry forward a control's value is, in the AfterUpdate event of the control, set the default for that control as its current value. Here's an example of a text box containing a numeric entry. Note that the format will change slightly if referring to text or date (examples shown).

Private Sub FacilityID_AfterUpdate()
If Not IsNull(Me.FacilityID) Then
Me.FacilityID.DefaultValue = "=" & Me.FacilityID
'Use
'For text fields 'Me.Operatorid.DefaultValue = "='" & Me.OperatorID & "'"
'For date fields 'Me.Operatorid.DefaultValue = "=#" & Me.Operatorid & "#"
'For number fields 'Me.Operatorid.DefaultValue = "=" & Me.Operatorid

Me.FacilityID.TabStop = False
Else
Me.FacilityID.TabStop = True
End If

End Sub

 

[mac318]

Since we are on a roll, let's embelish our code a bit. If we know that the default fields remain the same for a large number of entries, here's a suggestion which I often use.

The first record would require all the fields be entered to establish the defaults. Next, turn the tabs off on all defaulted fields and set those values in the Current_Form event. The user will only have to work with the few remaining fields which require individual input. If a defaulted field requires a change, the user can use the mouse to enter the field(s) to make the change(s).

I also test the required entry fields in the LostFocus event. If the user failed to enter a value, I sound the beep and set focus back to the field.

Thanks for the smile, Alex. It makes my day.

mac

 

[AlexMidd]

Well, mac, it makes my day to hear you say that. Have fun! )

Alex Middleton

 

[ruthcali]

Great suggestions above!!

but i still can't get the CTRL@ to work!

i tried it with single, continous and datasheet forms and i can't get it to work with any of them.
ruth.jonkman@wcom.com

 

[thirty4d]

I know for a fact that Control + apostrophe will work on TABLES as I use it all the time but never heard to work on FORMS

I want to thank each and everyone for all the excellent suggestions. I haven't tried any of them yet. But will post the result!

 

[tempclerk]

I use Ctrl+' in forms all the time. There does have to be a previous record to copy from, though - it won't work when you first open the form in data entry mode, etc.

 

[ruthcali]

i think i got it. i had been trying with the ` key instead of the ' key.

but it only copies the value that is first in the tab order. it doesn't copy all the values.

is that correct? ruth.jonkman@wcom.com

 

[tempclerk]

It copies the value in the same field of the previous record - You would have to hit it again for each value you want to copy down. I think that's why the other solutions go at it programatically - you can have as many fields as you want copied automatically. I'm going to try some of those as soon as I get a chance.