Setting a default value in a form via table lookup

[Guest]

I would like to set the default value of a field using a table entry
retrieval based upon the value of another field entered earlier on the same
form. I'm trying to do this with an expression directly in one of the two
fields' event properties -- i.e., without having to explicitly create a
macro. I'm comfortable writing SQL but the rest of it -- Access event
properties, etc. -- esacpes me. For what it's worth, the target field is a
Yes/No data type.

Any help would be greatly appreciated. Thanks very much,

-- Paul

 

[John Vinson]

On Tue, 28 Sep 2004 16:43:03 -0700, "Paul Scheer" <Paul

I would like to set the default value of a field using a table entry
retrieval based upon the value of another field entered earlier on the same
form. I'm trying to do this with an expression directly in one of the two
fields' event properties -- i.e., without having to explicitly create a
macro. I'm comfortable writing SQL but the rest of it -- Access event
properties, etc. -- esacpes me. For what it's worth, the target field is a
Yes/No data type.

Any help would be greatly appreciated. Thanks very much,

-- Paul

Well, you can't escape code to do this; SQL and table default
properties cannot do it.

Form events are worth the effort to learn. In this case, you have two
controls (NOT fields, controls are *bound* to fields but the fields
exist only in the Table); after you update one of them you want to set
the value of the other one. Therefore, you need to use the first
control's AfterUpdate event.

If you want to use VBA code, which is what I'm most comfortable with,
you could open the form in design view. Select the AfterUpdate
property of the control (I'll call it txtMyTextbox, just for example).
Click the ... icon and choose Code Builder. Access will give you a Sub
and End Sub line and put you into the VBA editor. Edit the result
(using your own control names of course) to:

Private Sub txtMyTextbox_AfterUpdate()
If Me!txtMyTextbox = "SomeDesiredValue" Then
Me!chkMyCheckbox = True
Else
Me!chkMyCheckbox = False
End If
End Sub

If the user enters "SomeDesiredValue" into the textbox, the "True"
branch of the IF expression will run; otherwise, the "False" branch.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps