Input Form Field Names in VBA

I plan to write some VBA code for a command button on an Input Form.  Can
anyone help with answers to the following questions:

1) In VBA, how do you reference the values (field names) of the fields in
the previous Input Form that was displayed?

2) In VBA, how do you reference the values (field names) of the fields in
the Input Form that is currently displayed?

3) Are the field values from the previously displayed Input Form stored in a
named buffer?  If so, what is the VBA buffer name?

4) Are the field values of the current Input Form stored in a named buffer?
If so, what is the VBA buffer name?

Thanx in advance,

George L.



0
Starwood
4/19/2007 5:27:09 PM
access.forms 6864 articles. 1 followers. Follow

7 Replies
3418 Views

Similar Articles

[PageSpeed] 8

"Starwood" <starwoode@earthlink.net> wrote in message 
news:NDNVh.6451$3P3.4776@newsread3.news.pas.earthlink.net...
>I plan to write some VBA code for a command button on an Input Form.  Can
> anyone help with answers to the following questions:
>
> 1) In VBA, how do you reference the values (field names) of the fields in
> the previous Input Form that was displayed?

are you saying that the current form was called (opened) by the previous 
form?

Sure, just declare a variable in the current form (module level)

dim frmPrevous        as form


Now, in your on-load event, or on-open event, go:


set frmPrevous = screen.ActiveForm

(activeform does not change until the on-open, and on-load is complete..so, 
at this point in time, activeForm is the previous calling form).

>
> 2) In VBA, how do you reference the values (field names) of the fields in
> the Input Form that is currently displayed?

simply go:

me.NameOfContorlOnform

or

me!FieldName (if the control is not on the form)

The above is for code *in* the current form. If you need to reference field 
values in another open form, then you can go

forms!NameOfForm!NameOfField

> 3) Are the field values from the previously displayed Input Form stored in 
> a
> named buffer?  If so, what is the VBA buffer name?

Do you mean previous form, or pervious record? (that is a GRAND CANYON OF A 
DIFFERENT ISSUE!!!).

Or, do you mean the current record, and can we reference "old" values, and 
updated (edited/changed) values?

For previous form, as mentioned, just reference the form name, or setup a 
variable as per my example.

if you talking about previous record...no, there is not such a thing...

If you talking about previous values for the current record we are editing, 
then yes, you can go:

me.NameOfContorl.OldValue

>
> 4) Are the field values of the current Input Form stored in a named 
> buffer?
> If so, what is the VBA buffer name?

There is no buffer, but all of the controls and values can be referenced, 
and they not been committed to the table (or query) until the users moves to 
another record, closes the form etc.. You can also force a disk write of the 
current data (edited) values by going:

if me.Dirty = True then
    ' changes have been made...write to disk
   me.dirty = false
end if

So, if any editing has occurred, the me.Dirty property will be true. And, if 
any editing has occurred, the forms before update event will fire before the 
disk write. (this means you rarely have to use me.dirty to check if things 
been changed, since any code in the before update event will not run if no 
changes to the form been made.).

Normally, you don't need the above code to force a disk write, since moving 
to a new record, closing the form etc will write out the data for you.

-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com 


0
Albert
4/19/2007 5:59:38 PM
Sure, just declare a variable in the current form (module level)

dim frmPrevous        as form


Now, in your on-load event, or on-open event, go:


set frmPrevous = screen.ActiveForm

when you do the above, then in code you can go:

frmPrevous!LastName 


0
Albert
4/19/2007 6:01:55 PM
On Thu, 19 Apr 2007 17:27:09 GMT, "Starwood" <starwoode@earthlink.net> wrote:

>I plan to write some VBA code for a command button on an Input Form.  Can
>anyone help with answers to the following questions:
>
>1) In VBA, how do you reference the values (field names) of the fields in
>the previous Input Form that was displayed?

You can't, unless that form is open.

>2) In VBA, how do you reference the values (field names) of the fields in
>the Input Form that is currently displayed?

Forms don't contain fields - tables do. Forms contain Controls, which may or
may not be bound to a field. Not all fields in a table are necessarily bound
to controls on a form. To reference a Control on a form use

[Forms]![NameOfTheForm]![NameOfTheControl]

or, if the code is on the currently active form, 

Me![NameOfTheControl]

>3) Are the field values from the previously displayed Input Form stored in a
>named buffer?  If so, what is the VBA buffer name?

No.

>4) Are the field values of the current Input Form stored in a named buffer?
>If so, what is the VBA buffer name?

No.

You may be applying the logic of some different program to Access. A Form is
just a tool to display and edit data stored in a Table, often via a query. The
data is not stored in a Form, nor in a buffer; it's stored in the Table which
is designated in the form's Recordsource property.

             John W. Vinson [MVP]
0
John
4/19/2007 7:01:15 PM
Thanks for your answers.  Yes, I am used to programming in conventional
programming languages and have done so since 1962 (dozens of other
languages).

At any rate.  What I want to do should be very simple no matter whether you
use the term "controls" or "field values".  The sequence goes something like
this:

1) Using an Input Form, display a current record in the database.
2) Use a command button (Save_Values) on the Input Form to somehow and
somewhere, save the values in this database record's fields.
3) Open a blank Input Form to enter a new ( but similar) record.

4) Use a command button (Duplicate_Values) on the Input Form to retrieve the
values saved in step 2 (or step 7) and make them the field values of the
current record in the Input Form.
5) Manually modify the fields that require it, including (of course) the key
field.
6) Use the Save_Values command button to save the new set of field values.
7) Add the new record to the database.
6) Loop back to step 4).

What I'm trying to accomplish is what we called in the "old days" batch data
entry of a number of similar records without having to re-key numerous
amounts of duplicate data.

If there is a standard way to do this in Access, I've never found out how.
Therefore it seems like VBA code and two command buttons should be one way
to accomplish this.

Thanx again.

George L.

"Starwood" <starwoode@earthlink.net> wrote in message
news:NDNVh.6451$3P3.4776@newsread3.news.pas.earthlink.net...
> I plan to write some VBA code for a command button on an Input Form.  Can
> anyone help with answers to the following questions:
>
> 1) In VBA, how do you reference the values (field names) of the fields in
> the previous Input Form that was displayed?
>
> 2) In VBA, how do you reference the values (field names) of the fields in
> the Input Form that is currently displayed?
>
> 3) Are the field values from the previously displayed Input Form stored in
a
> named buffer?  If so, what is the VBA buffer name?
>
> 4) Are the field values of the current Input Form stored in a named
buffer?
> If so, what is the VBA buffer name?
>
> Thanx in advance,
>
> George L.
>
>
>


0
Starwood
4/19/2007 10:59:37 PM
On Thu, 19 Apr 2007 22:59:37 GMT, "Starwood" <starwoode@earthlink.net> wrote:

>Thanks for your answers.  Yes, I am used to programming in conventional
>programming languages and have done so since 1962 (dozens of other
>languages).

Well... I remember having to "unlearn" a lot of the procedural thinking that I
was used to when I made the transition from programming languages to
databases. It really is a different  mindset.

>At any rate.  What I want to do should be very simple no matter whether you
>use the term "controls" or "field values".  

I'm using the terms because *these are different things, with different
properties, with different uses*. A Field in a table is stored data; a Control
on a form is a tool for interacting with that stored data. They are NOT
interchangable!

>The sequence goes something like
>this:
>
>1) Using an Input Form, display a current record in the database.
>2) Use a command button (Save_Values) on the Input Form to somehow and
>somewhere, save the values in this database record's fields.
>3) Open a blank Input Form to enter a new ( but similar) record.
>4) Use a command button (Duplicate_Values) on the Input Form to retrieve the
>values saved in step 2 (or step 7) and make them the field values of the
>current record in the Input Form.
>5) Manually modify the fields that require it, including (of course) the key
>field.
>6) Use the Save_Values command button to save the new set of field values.
>7) Add the new record to the database.
>6) Loop back to step 4).
>What I'm trying to accomplish is what we called in the "old days" batch data
>entry of a number of similar records without having to re-key numerous
>amounts of duplicate data.
>
>If there is a standard way to do this in Access, I've never found out how.

There is, though it's not builtin. Ordinarily - with properly normalized
tables - this will not be needed all that often, and only for a few fields;
having many repeating values in a table suggests that you may need to have two
tables in a one to many relationship.

It is, of course, sometimes needed and it's not hard to do. On each Form
Control that you want to have auto-duplicated, put the following code in its
AfterUpdate event:

Private Sub controlname_AfterUpdate()
Me.controlname.DefaultValue = Chr(34) & Me.controlname & Chr(34)
End Sub


If the current value in the form is (say) 813, this will put 

"813"

(with the quotemarks, Chr(34)) into the control's Default Value property.
Regardless of the field datatype, the Default Value needs to be a string.

Sorry I didn't clearly understand what your first post was trying to
communicate!

             John W. Vinson [MVP]
0
John
4/19/2007 11:51:23 PM
In addition to the answer of setting the field default value as John 
suggested, in Excel, or ms-access, you can also hit ctrl-' and it will copy 
the previous value into the current field....

Interesting, I not aware that we have program access to this previous 
value.....

And, you could "code" a solution that saves the list of current values into 
a array, or collection. The code would be placed in the after update event 
of the form. And, then for the new record, you could use the before insert 
event, and have that code fill in the values.

However, since you need to control, define, and layout which controls on the 
form are to be copied (and of course skip the primary key), then John's 
suggestion is going to be much less code, and gives you the "choice" of 
which controls on he form you want to copy...


-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com


0
Albert
4/20/2007 3:17:58 PM
On Fri, 20 Apr 2007 09:17:58 -0600, "Albert D. Kallal"
<PleaseNOOOsPAMmkallal@msn.com> wrote:

>Interesting, I not aware that we have program access to this previous 
>value.....

Me.Controlname.PreviousValue - though that isn't really quite the same thing
(it's the value that was in the control at the form's Current event).

             John W. Vinson [MVP]
0
John
4/20/2007 5:45:00 PM
Reply:

Similar Artilces:

Automatically print the LAN ID into a form
I'm developing a form that will collect data from various users. To verify which user the data is coming from, I want the form to automatically input the user's LAN ID when the form is printed. Does anyone know if this is possible and how you would do it? ...

How to Enter Same Text in 1000s of Fields
I have a an Access 2003 db of 11,000+ records. Each record has a field named Product Type. The field is now blank and I would like to enter the phrase audio books into each field. Can I do this with a query? How? You could use an update query with a SQL view like: UPDATE [an Access 2003 db of 11,000+ records] SET [Product Type]="audio books"; -- Duane Hookom Microsoft Access MVP "lbohen" wrote: > I have a an Access 2003 db of 11,000+ records. Each record has a field named > Product Type. The field is now blank and I would like to enter the phrase > audio...

Preventing Names in Drop-Down Address Shortcut List After Sending Mass Mailings
I have to send out mass mailings, using Outlook 2003, for a professional organization I'm involved with. After sending out these mailings, the e-mail addresses for all of the people on the mass mailing list show up in the drop-down e-mail address list that appears when I start typing into the "To" field when sending a new e-mail. After each mass mailing, I have to type through each letter in the alphabet in the To box, deleting all of the addresses I don't want, so that I can have easy shortcuts to the people to whom I frequently send e-mails. Is there any way to preve...

Making a search form
I need help writting code behind a search form i made. It has combo boxes of different things you can search by account number, Date etc what is the code behind the button that will make another form pop up with the info i asked to search by in it? iLevy wrote: >I need help writting code behind a search form i made. >It has combo boxes of different things you can search by account number, >Date etc what is the code behind the button that will make another form pop >up with the info i asked to search by in it? Here's a article on that topic: http://allenbrowne.com/ser-...

Macro pause for input
I would like a macro to insert a footer with some data already in a worksheet. I can do that, but I want it to pause and offer choices, e.g. a dropdown box or something, so that the choice is between two or more cells/named ranges in the worksheet, or choices such as left or right footer and Active.Workbook.FullName or "Date" such as date is "Date: &D" Suggestions greatly appreciated. Have you tried using Forms in your macro? Or even using the msgbox command will probably suffice. Look msgbox up in the help from your macro >-----Original Message----- >...

Calling a value from a form
I have a form that when a button is used opens a report. The form has a field that references a value in a table the is needed on the report. On the report control source I have the following: =DLookUp("[CCIT]","CLIENT NAMES","[CACCTN] = " & [Forms]![f_New_Case_Input_97]![CACCTN]) It gives me a #ERROR value. The following line returns the correct result where "104" (a string, not a number is correct) =DLookUp("[CCNAM]","CLIENT NAMES","[CACCTN] = '104'") I am out of ideas of this what should be a simple re...

Accessing Exchange 2003 server form another site
Hi, Here's the situation: - User's Outlook is configured to his local Exchange server. User is connected to the network with DHCP. - User goes to another office and would like to get connected on his Exchange serveur. User is connected to the network with different DHCP server and range. Users can only access resources that are locally. - Both Exchange servers can see each other and are part of the same domain. Is there a way for the user to get access to his Exchange mailbox even though he is not connected locally? Would opening the Exchange ports on the firewall would be the bes...

Visible control based on other inputs
Hi I have a form where the user choose shift, Sektor, Cell, Date from 4 different comboboxes. The cell combobox is based on a query that gives you the cells that corisponds to a sektor when the sektor is choosed. The query looks like this SELECT TblCelle.CelleNr, TblCelle.CelleTxt, TblCelle.Sektor FROM TblCelle WHERE (((TblCelle.Sektor)=[Forms]![FrmReg]![Sektor])) ORDER BY TblCelle.CelleNr; TblCelle has the following fields CellNo, CellTxt, CellVar, CellCat, Sektor The CellVar can have two input either L or H I have a text box and a label that should not be visib...

Show renamed user fields in PWA
In Project Pro 2007 I've defined the "text1" field to display as "Task Type". How do I get this to carry over into PWA? The published project shows "Text1" as the field heading. Pete Hay Hi Pete, You need to do this via the Enterprise Custom Fields in Server settings. Create a task field of type text and call it Task Type. Open up project, and you'll find it's available to everyone. -- Regards, Ben. http://www.applepark.co.uk http://appleparkltd.spaces.live.com/ "Pete Hay" wrote: > In Project Pro 2007 I'...

Input music to WMP?
I know you can enter music by indicating what folder to watch, but I want more control. Amazon Downloader is a free program to do this, but it is done programmatically. Is there a non-programmatic way to do this? Thank you. Jim Cassilly On Mon, 29 Mar 2010 17:50:28 -0400, "Jim Cassilly" <jcass@tampabay.rr.com> wrote: >I know you can enter music by indicating what folder to watch, but I want >more control. Amazon Downloader is a free program to do this, but it is done >programmatically. Is there a non-programmatic way to do this? Hi, it's not...

adding navigational buttons to a form in datasheet view.
I'm creating two forms with navigational buttons to move inbetween the two of them. One form is Columnar, and the other is in Datasheet. The button shows up on the first form, but unfortunately I can't get the button to show up on the form that is in datasheet view. Can anyone tell me how? You can't! With a datasheet form you get what Access gives you; data fields in rows! You can do all you want in Design View, add command buttons, labels, comboboxes, etc, format fields with color and so forth, but they're not going to show up when the form is run! The best you can do is ...

Combining two fields
I'm trying to combine two fields in a particular way, and am trying to write a function to do so. However, I'm getting caught up in how to actually grab the contents of the fields. I've tried going the DAO route, which I think was misguided anyway, and am now trying to get to them directly from the table. The following function should manipulate the values of the the two fields in the specific table into a new string. Function fCombineSemis(strTable As String, _ strFirstField As String, _ strSecondField As String) _ ...

Data Input
I have a spreadsheet set up containing products with a uniq reference code. I need to input sales quantities against these products each week. At present this is done manually. Has anyone any ideas how to auto mate this function. The sales quantities can be downloaded into an excel file Regard Blueman Hi you may have a look at pivot tables if I understood you correctly >-----Original Message----- >I have a spreadsheet set up containing products with a uniq reference code. I need to input sales quantities against these products each week. At present this is done manually. Has an...

Display value of a VBA variable inside a cell
Hi, I am using Excel 2007. I have created a VBA module called MyModule. Inside this module, I have defined a public variable as follows: Public blnMyBoolean As Boolean Next, I created a Function to return the value of this variable as follows: Public Function GetMyBoolean() As Boolean GetMyBoolean = blnMyBoolean End Function Next, I used my function as a formula for a cell on one of my worksheets as follows: =GetMyBoolean() The formula in this cell works, but it does not update automatically. If my code changes the value of blnMyBoolean, the only way I can s...

forms in Excel
Hi, I would like to create a form in Excel that can be used as a source for an Access table. I don't necessarily want to create the form in the VBA editor. There are two form features in an Excel sheet: The Control toolbox and the Forms toolbox. How should i decide which one to use? Thanks! Jake My question is very simple (I hope): in MS Works, you can toggle between List View and Form View, and print an individual record in the latter. Can this be done in Excel? "Jake" wrote: > Hi, > I would like to create a form in Excel that can be used as a source for an ...

User input
I've used this forum before, and found it very useful. I need to set up a worksheet, (worksheet1), to allow a user to input surname,initials,id,location,date for office files that move around the office. The result will be stored in worksheet2 in 1 row and 5 columns called Surname - Inits - ID - Location - Date Each entry will be recorded in the next empty row in worksheet2. I think I have found a way to identify the next empty row using the Indirect function. But, how can I set up worksheet1 to allow the user to input the data he has which is then automatically recorded into wo...

send email from VB with minimum user input, mapi, cdo?
I want to add the ability for the user to email an attachment after generating a report, but I don't want to have to rely on them for much help in setting up the email, a lot of them would have trouble finding their outgoing mail server, whether it requres authentication etc. Is it possible to send mail from VB after looking up the outgoing server and any authentication that may be required from the registry, just asking the user to enter the destination email address? And is there a recommended method for sending email that will work on XP through W7? On Aug 13, 6:1...

VBA problem when recording sound using Sound Recorder (> 4 Gig mem
I jave a VBA application in Word that records sound using Sound recorder. UNFORTUNATELY Sound Recorder does not work correctrly in a machine which has more than 4 Gig of memory http://support.microsoft.com/default.aspx/kb/284893?p=1 Can I test the amount of memory using VBA in Word so that I can give the user an appropriate message? Any assistance very gratefully received, PeterEvans Peter wrote: > Can I test the amount of memory using VBA in Word so that I can give the > user an appropriate message? Absolutely, using the GlobalMemoryStatusEx API. See: http:/...

order form
help please.... i am trying to create an order form. normally this would be easy enough...item price x quanity etc, but there are some other variables. allow me to explain.... 1. let's say we are selling products 1 2 3 etc all the way to 250. 2. let's say that products 1 through 50 only should display options (styles) of a b c d e f and g, while products 51 -100 should display options (styles) of h i j k l m etc. I was hoping the two things above could be in drop down boxes... so..for example..a customer orders product 22, only the a to g options or styles are shown. Now..t...

user defined fields #3
I would like to change the field name Comment 1 to Pledge Amt. I am not able to do this. We have modifier. When I open the customer card window and click on Tools > Customize the modifer opens. But I am not able to change the Comment 1 Description. I can select the field, but I cannot seem to change it. I have looked into the properties field without any sucess. I am able to change it globally, but I would like to restrict it to this window only. I am not sure what I am missing here. Thanks nfp nfp, You are not missing anything. Just go to the modified window, select the Commen...

Changing email recipient based on Order field
We are using CRM 3.0, Win 2003 Server and the Workflow Manager. We want to alert an internal resource to start working an order via email, upon order creation. The internal resource's email address is entered as a field in the order. Using Workflow Manager we seem to be able to either use a template, or create a custom email. Using a template does not allow us to control who the email goes to - CRM automatically decides to send the email to the account contact. Not what we want. Using a custom email, we can select from a finite number of recipients, including internal CRM users (of whi...

update data in table through form
i have created customer data in table and im updating data through form, in the form at one field i have created a formula [bill amt]-[paid amt] and it works but is not updating in the table, kindly help in this i also want to run query in form like i want to see reports from from date to end date of due amt -- srinivas On Fri, 5 Mar 2010 04:09:01 -0800, Srinivas <Srinivas@discussions.microsoft.com> wrote: Formulas like that don't belong in a table, but in a query. So if you ever need this value simply create a query, select your table, and set one of the field...

Re: Locking textbox input
Is there a way to enter info in a textbox ONCE and as soon as Tab or Enter is pressed it locks automatically... so it wont be altered.... ??? --- Message posted from http://www.ExcelForum.com/ how about not only a textbox but a plain old cell?? --- Message posted from http://www.ExcelForum.com/ How about anytime you get out of it? I added a textbox from the control toolbox toolbar to a worksheet. I double clicked on it and replaced this code: Option Explicit Private Sub TextBox1_Change() End Sub Option Explicit Private Sub TextBox1_LostFocus() Me.TextBox1.Enabled = CBool(Me.T...

.OFT FORM file not opening
Is there any setting in outlook that would prevent me from running an .OFT Form file.. Beginning with Outlook 2003 SP2, users can work with most .oft form = templates files only by opening the template from the Tools | Forms | = Choose Form dialog, browsing for User Templates in File System. This = means that if the .oft file is sent as a message attachment, the user = must save it to the local hard drive first.=20 The article at http://support.microsoft.com/?id=3D907985 explains this = change in more detail, suggests best practices, and provides registry = keys that can return Outlook 2003 ...

VBA code needed to AUTOMATE SELECTION of names within VALIDATION dropdown then print
I have searched the blogs and cannot seem to locate anything related to my challenge. I know you guys are gurus when It comes to things like this. I have a worksheet that tracks employee productivity for each cost center with a region. My excel worksheet contains three validation dropdowns that use the "indirect" function with various named ranges that link these dropdowns . So for instance lets say that the first dropdown relates to a region, the second relates to a cost center (there can be many cost centers in a region), and the third is a worker in that cost center (there can...