×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Changing the field default via a form

Changing the field default via a form

Changing the field default via a form

(OP)
I have a field on a form which is not tabbed to and contains a default value (which is a foreign key to the main table which the form is based on). This value will stay constant for hundreds of records, then the user will need to change it for the next few hundred, and on and on. I would like to find a way to change the default value without the user having to go into design mode to edit the form, and/or edit the table. Can this be done? Thanks in advance. -- Carenne

RE: Changing the field default via a form

in access 2000

insertControlName.DefaultValue can be changed programmatically.

Me.Text0.DefaultValue = myNewValue

you can also do this for subform defaults too

RE: Changing the field default via a form

(OP)
Thanks for the info, Rafe - it looks like it is indeed doable, and I'm happy about that. I'm sorry to be pesky, but need a little more info. First, this particular app is in Access 97, so I hope it's doable in there as well. Second, I'm not clear exactly where to put those program statements within the form (I assume somewhere in the properties for that field?), and how to incorporate my custom info into the statement. The name of the field is faPhonebook and the name of the control source is fapbDesc. If you could elaborate a little more, I would be ever so grateful!

Thanks again! -- Carenne

RE: Changing the field default via a form

Carenne,

Sorry for being terse. Mmm, 2000 sits on my desk but in ’97 it should work fine.  I did this thru VBA.  Do you know VB? You could use macros too, but I prefer VBA because you can get more done with it.

Point 1: I don’t change the table field’s default value. I’m changing the form control’s default value, which for data entry amounts to the same thing.  What I normally do is set the default value of a subform field based upon data entry in the main form and table look-ups I also tend to used unlinked forms…. but enough about me.

Point 2: I linked the form’s control default value change to a button click.  You could use whatever event Access allows; it’s just the one I chose.  

Now for the mock up I created a form called “Form2” linked to a table (table1) with just a Key value (autonumber) and the faPhoneBook field I made it a long integer for this demo and a text field called “junk.” On the form there were just three controls a disabled text box linked to faPhoneBook, a text box linked to “junk”, and a command button to change the default by incrementing it by one on each click. Yea I know not really stellar but it’s the idea that counts here.

For the command button’s on click event  (command2)… in the properties window/event tab to the right of the "On Click" event I clicked on the "..."

Access then asked me if I wanted to use the "Expression Builder, Macro Builder, or Code Builder”. I prefer to use code but a macro may work too. Now for the code I entered…

Private Sub Command2_Click()
    Me.fapbDesc.DefaultValue = Me.fapbDesc.DefaultValue + 1
End Sub


OK, I only typed in the middle line.

Now I could add records with the default value changing as I clicked the button.  Worked just fine.

Again this is only for idea purposes & not a real application but I hope this helps.

rafe

RE: Changing the field default via a form

rafe, carrene, et al

just set the default value of the control to the current value of the control in the after update event of hte control.

When the user changes the value in the (textbox) control, the afterupdate event fires and changes the default value for the control.  Next 'activation' of the form will use the new default value.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

RE: Changing the field default via a form

(OP)
Sorry for the time lapse here - I've been spinning a lot of pie plates at once these past few weeks.  Rafe and Michael, for the great replies. Terse is just fine, except for folks like me who need a response more a la Hemingway style. [g] I appreciate you coming back with more very helpful details!

In any case, I tried both methods, and Rafe, with yours, I got as close as getting it to produce the numerical ID (the primary key) of the phonebook field, but could not get it to display the text. Michael, with yours, I'm embarrassed to have to admit that after numerous

RE: Changing the field default via a form

(OP)
[ARGGHHH - I don't know what I'm doing wrong, but this is the third time I've submitted a post and only part of it has showed up. Any hints as to what is causing that would be appreciated! Meanwhile, here is the post I just sent in its entirety.]

Sorry for the time lapse here - I've been spinning a lot of pie plates at once these past few weeks.  Rafe and Michael, for the great replies. Terse is just fine, except for folks like me who need a response more a la Hemingway style. [g] I appreciate you coming back with more very helpful details!

In any case, I tried both methods, and Rafe, with yours, I got as close as getting it to produce the numerical ID (the primary key) of the phonebook field, but could not get it to display the text. Michael, with yours, I'm embarrassed to have to admit that after numerous "best guess approaches" as to where to put the code you described, the closest I could get was that it would change ALL the records to that new "default" value (including old records that should not have been changed), or I would get an error in the field. So I struck out both times.

Rafe, FYI, I do try to use VB and event procedures as much as possible. Up until recently, I've survived with a LOT of beginner's dumb luck, mostly copying and pasting code from elsewhere in the existing or other databases, and that has shielded me from the pain of realizing what I don't know. But that big gap is closing in on me and now my frustration level is at a peak and I know I need to do something about it - hence, my question in another post about VB and Access classes.

I really want to make this form work with the desired behavior, and I'm "this close" but still pulling my hair out. So Michael and Rafe, if you could spare another post in this subject, I'd be ever so grateful!

Rafe, one thing that confused me about your resopnse is whether or not you have yet another table *in addition to* the existing phonebook table I already have? If so, does it have a relationship to the existing table? The way my phonebook table is set up, the pbDesc field is the ONLY field in the table; it is a text field and primary key, and it has a one-to-many relationship with the tblFullAddress table (via the fapbDesc field). (The field control name is fapbDesc.) Can I keep it like that or do I need to create a numerical primary key field to accomplish it via your method? Also, I discovered an error after I posted my original post where I said the control name was faPhonebook - it should have stated it was fapbDesc.

Michael, with your method, could you tell me exactly where you would put the code (I assume as an event procedure) and what the code should say, based on the field names I've given?

Rafe, please note a question I've addressed to you in another post (in an effort to avoid thread drift) regarding unlinked forms.

Thanks so much for all your help!

Carenne

RE: Changing the field default via a form

Carrene,

The update to the "default" value needs to go in the Control's After_Update event.  It should change it's OWN default value, NOT the recordsource field default value.

e.g.

The CONTROL name is 'txtPnh'

in the Afterupdate event of 'txtPhn':
    txtPhn.DefaultValue = txtPhn

Of course, you need to replace txtPhn with the name of your control, as it exist in your app/form.

My post is really just a minor variation on what rafe posted.  There is also some confusion regarding the names used. "faPhonebook" is refered to as a "Field", which often/usually refers to an element of a table.  The 'soloutions' proposed should refer to the control on the form, NOT the table!Field arrangememt.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close