Are Dynamic Default Values possible?

[Nevermoor]

Is there any way that I can set up a table (or form) so that the default value of a new data item is the value of the previous item in that column?

The tracking database I'm working on is largely repetitive, so any way to create an intelligent default value would be MUCH appreciated.

Thanks in advance

 

[LarryDeLaruelle]

Couple of ways to do this. In the table design view, set the default values of the fields to whatever is most common. Won't give you the last value entered but the field will contain a value.

If you are using a form for data entry and are familiar with using VBA, you can capture the values of the data as they are entered using variables (dimmed as Module level).

Create a separate variable of the appropriate type for each field you want to fill then simply use the After Update event for the field to assign the value to the variable -- varName = FieldName.

When a new record is added assign those values to the fields as appropriate. You will need to use your own command button for adding a record; in the On Click event of the button, after the add record action put in the code to set the value -- FieldName = varName. This method will give the last values entered.

In both cases, the user can override the value displayed to whatever it should be.

If you are using the table view to enter the data you can use the 'ditto' key (control-quote) to copy the data from the cell immediately above. Not so slick but servicable. The ditto will also work when entering data via a form and will give the value from the last record entered.

Hope this helps. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[Darrylles]

This'll work if you have an autonumber primary key. If you don't, then start using 'em for EVERY table.....

Create a simple select query for the primary key and the
field in question from your table.
Order the primary key descending.
Edit the SQL and insert 'TOP 1' after the 'SELECT' key word.
This will return the last entry of the field in question.

e.g:

SELECT TOP 1 MyField.MyTable
FROM MyTable
ORDER BY MyTable.pk DESC;

If you don't have an 'autonumber' PK field, then I can only
guess that you'll be looking at system tables.
"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Darrylles]

P.S.

If your table doesn't have an autonumber primary key, then just add a new field of type AUTONUMBER - and don't define it as primary key.
This will enable you to use my last post, and it won't affect your current application.

P.P.S. Following my last post: you would have to decide on when to run the query (when user enters field?) and assign it's returned value to the field in question. "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[cmmrfrds]

You should be able to reference the previous value when you go to a new record. Something like.
In the oncurrent event
Check for newrecord.
If me.NewRecord then
me.fld1 = me.fld1.OldValue
me.fld2 = me.fld2.OldValue
etc.....
end if

 

[SBendBuckeye]

Try setting MyControl.DefaultValue = MyControl.Value in the appropriate event procedure.

Good Luck!

 

[Nevermoor]

Thanks guys,

And yes I do have a key on the table.

This site is amazing, and if you ever wonder why, look to yourselves

 

[Nevermoor]

Just so you know what I ended up doing,

I created a set of global variables in VBA and initialized them to valid values in the form's onOpen event, then on the form's onCurrent event, i set the field values to to variables and on the afterUpdate events I updated the variables.

Sadly the most elegant solution Me.DefaultValue = Me.Value didn't work b/c Me.DefaultValue is a get and not a set property.

Now my only problem is getting the VBA to play nicely with my input masks =(

Again thanks to everyone who replied

 

[Nevermoor]

P.S. the onCurrent call included the if NewRecord statement.

 

[SBendBuckeye]

Hello Nevermmoor,

Try this code in your On_Current procedure:

If NewRecord Then
Control1 = Control1.DefaultValue
Control2 = Control2.DefaultValue
. = .
. = .
. = .
Controln = Controln.DefaultValue
Else
Control1.DefaultValue = Control1.Value
Control2.DefaultValue = Control2.Value
Control3.DefaultValue = Control3.Value
. = .
. = .
. = .
Controln.DefaultValue = Controln.Value
End If

This will set the controls to the current record every time you go to the next record. To get updated information, you could also put the else portion of the above code in the form's After_Update code.

I only tested this with text boxes containing strings and numbers. You may have to do something slightly different for boolean fields, I don't know.

If you can't get it to work, my email address is in my profile. If you send me a message, I will send you a little test database I used for the above.

Good Luck!

 

[SBendBuckeye]

Here is something else you might try. I used the code below to lock or unlock all of the controls on a form. The On Error logic traps things that aren't lockable like labels, etc. You might try something like this for your default problem. Use print.debug statements until you know it will work the way you want it to work.

REM Use the form control collection to lock and unlock the REM controls on a form. The calling program is responsible REM for any exceptions.
Sub LockFormControls(pobjMe As Object, pbooLock As Boolean)
Dim ctlControl As Control

On Error GoTo Err_LockFormControls

For Each ctlControl In pobjMe
print.debug ctlControl.name, ctlControl.Locked
ctlControl.Locked = pbooLock
print.debug ctlControl.name, ctlControl.Locked
Next

Exit_LockFormControls:
Exit Sub

Err_LockFormControls:

Select Case Err
'Object doesn't support this property or method - labels, etc.
Case 438
Resume Next
Case Else
MsgBox Err.Description & vbCrLf & Err.Number
Resume Exit_LockFormControls
End Select

End Sub

 

[Nevermoor]

Once again, thanks.

I don't know why it didn't occur to me that control.defaultvalue wouldn't have the same restrictions as the defaultvalue key in the expression builder...

Using defaultvalue, and setting them based on the first record rather than other defaults magically fixed all the other minor issues I thought i'd need to deal with

Very nice, and thank you to everyone who posted