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 [sig][/sig]
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Changing the field default via a form
- Thread starter Carenne
- Start date
- Thread starter
- #3
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 [sig][/sig]
Thanks again! -- Carenne [sig][/sig]
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…
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
[sig][/sig]
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…
Code:
Private Sub Command2_Click()
Me.fapbDesc.DefaultValue = Me.fapbDesc.DefaultValue + 1
End SubOK, 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
[sig][/sig]
MichaelRed
Programmer
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.
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]
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.
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]
- Thread starter
- #6
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 [sig][/sig]
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 [sig][/sig]
- Thread starter
- #7
[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 [sig][/sig]
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 [sig][/sig]
MichaelRed
Programmer
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 '[red]txtPhn[/red]':
[tab][red]txtPhn[/red].DefaultValue = [red]txtPhn[/red]
Of course, you need to replace [red]txtPhn[/red] 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.
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]
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 '[red]txtPhn[/red]':
[tab][red]txtPhn[/red].DefaultValue = [red]txtPhn[/red]
Of course, you need to replace [red]txtPhn[/red] 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.
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]
- Status
Similar threads
