How to populate field on a new form with value from another form

Hi Experts,

I need to have the values typed in or selected on one form populate the same values on another form.  How can I  do this?
daintysallyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
depends on how you are opening the 2nd form and what you are using it for?  You would not generally have two data entry forms that contain the same information, so perhaps you need to open the 2nd form to a particular record that has already been created.

If you can explain a bit more about what you are trying to accomplish, I'm sure we can help you find a acceptable solution.
daintysallyAuthor Commented:
Each form that is opened has a customer first name and customer last name field.  Instead of typing that info on every form, can it auto populate the forms with the info keyed in on the first form?
Paul Cook-GilesSenior Application DeveloperCommented:
Set the Default property of the LastNa field on your second form to

=[Forms]![MyFirstForm]![LastNa].[Text]

when you open the second form, whatever is in the LastNa field on MyFirstForm will be populated into the LastNa field on the second form.

But Dale's question is a good one:  why are you entering the same data a second time?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Dale FyeOwner, Developing Solutions LLCCommented:
If you are simply displaying the data, so there is no confusion, then Paul's technique should work, except the reference to .text.  In Access you can only refer to the text property of the control if that control has the focus, so it would look like:

= [Forms]![MyFirstForm]![LastName]

Where "MyFirstForm" is the name of your first form.
daintysallyAuthor Commented:
The data is being entered on separate forms that will be printed.  And the values will be uploaded to separate tables.  What about populating the field with a selection made from a combo box...can that be done?
daintysallyAuthor Commented:
I have tried =[Forms]![Myfirstform]![LastName] and it only gives me the default "select" value instead of the value chosen from the drop down.
Paul Cook-GilesSenior Application DeveloperCommented:
Dale, does she need .Value?
daintysallyAuthor Commented:
.value isn't doing anything either
daintysallyAuthor Commented:
It doesn't give me the value selected from the combo box on the first form
Dale FyeOwner, Developing Solutions LLCCommented:
Is the first form still open?  Many times, when you use control wizards for creating the application, it will open the new form and close the original.  If the original form closes, there will be no value and the 2nd form will popup an inputbox assuming that the reference to the other form is a parameter.

1.  If you are going to be printing the 2nd form, then you probably need a report, not a form.

2.  "And the values will be uploaded to separate tables."    You would not normally enter the same values in multiple tables.  Doing so would require you to edit data from multiple tables if you needed to change the information.  Normally, you would enter the data into one table and then use a unique value (usually an autonumber field) to refer to the data in the 1st table in subsequent table.  As an example, you would create an employees table with EmpID (autonumber), LastName, FirstName and other fields.  If you need to reference a specific employee in another table, then you would use the EmpID field as a long integer field (foreign key) in the 2nd, 3rd, ... table.
daintysallyAuthor Commented:
There are foreign keys set up on the tables...and the second form is not uploading to another table....I misspoke.  I need the values from the first form, so that the selections won't have to be made again in the second form.
Dale FyeOwner, Developing Solutions LLCCommented:
Did you answer the question: Is the 1st form still open?

Can you post the code you are using to open the 2nd form?  

Since you refer above to a combo box, what is that combo box used for?  Does the combo include multiple columns?  Are you trying to get the value of the primary key from the combo, or the values of the other fields displayed in the combo?
Helen FeddemaCommented:
I would suggest saving the values from the first form (when selected from a combo box, or entered manually) into custom database properties.  That lets you retrieve them anywhere else in the database, even if the first form has been closed.  Here is some sample code for working with custom db properties:

Option Compare Database
Option Explicit

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private strPropertyName As String
Private strPropertyValue As String
Private lngDataType as Long
Private varPropertyValue As Variant


Public Sub SetProperty(strName As String, lngType As Long, _
   varValue As Variant)
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler

   'Attempt to set the specified property
   Set dbs = CurrentDb
   Set prps = dbs.Properties
   prps(strName) = varValue

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
    If Err.Number = 3270 Then
      'The property was not found; create it
      Set prp = dbs.CreateProperty(Name:=strName, _
         Type:=lngType, Value:=varValue)
      dbs.Properties.Append prp
      Resume Next
   Else
   MsgBox "Error No: " & Err.Number _
      & " in SetProperty procedure; " _
      & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Public Function GetProperty(strName As String, strDefault As String) _
   As Variant
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler
   
   'Attempt to get the value of the specified property
   Set dbs = CurrentDb
   GetProperty = dbs.Properties(strName).Value

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3270 Then
      'The property was not found; use default value
      GetProperty = strDefault
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in GetProperty procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Public Function ListCustomProps()
'Created by Helen Feddema 3-Oct-2006
'Modified by Helen Feddema 3-Oct-2006
'Lists DB properties created in code (as well as built-in properties)

On Error Resume Next
   
   Set dbs = CurrentDb
   Debug.Print "Database properties:"
   
   For Each prp In dbs.Properties
      Debug.Print vbTab & prp.Name & ": " & prp.Value
   Next prp

End Function

==================================
Usage examples:

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private lngDataType As Long
Private strPropertyName As String
Private strPropertyValue as String
Private varPropertyValue As Variant

Date
====
   If IsDate(Me![txtStartDate].Value) = False Then
      strTitle = "Invalid date"
      strPrompt = "Please enter a valid start date"
      GoTo ErrorHandlerExit
   Else
      dteStart = CDate(Me![txtStartDate].Value)
   End If
   
   strPropertyName = "PropName"
   lngDataType = dbDate
   Call SetProperty(strPropertyName, lngDataType, dteStart)

   GetStartDate = CDate(GetProperty("PropName", #12:00:00 AM#))

Text
====
   strPropertyName = "PropName"
   strPropertyValue = CStr(cbo.Value)
   lngDataType = dbText
   Call SetProperty(strPropertyName, lngDataType, _
      strPropertyValue )

   strDocsPath = GetProperty("PropName", "")

Long
====
   strPropertyName = "PropName"
   lngDataType = dbLong
   Call SetProperty(strPropertyName, lngDataType, lngID)

   lngID = CLng(GetProperty("PropName", ""))

Integer
=======
   strPropertyName = "PropName"
   lngDataType = dbInteger
   Call SetProperty(strPropertyName, lngDataType, intMonth)

   intID = CInt(GetProperty("PropName", ""))

Boolean
=======
   strPropertyName = "PropName"
   lngDataType = dbBoolean
   Call SetProperty(strPropertyName, lngDataType, "True")

   blnValue = CBln(GetProperty("PropName", ""))


Saving to a custom property from a control's AfterUpdate event
==============================================================
Private Sub txtDate_AfterUpdate()
'Created by Helen Feddema 2-Sep-2009
'Last modified 2-Sep-2009

On Error GoTo ErrorHandler
   
   If IsDate(Me![txtDate].Value) = True Then
      dteSingle = CDate(Me![txtDate].Value)
      strPropertyName = "SingleDate"
      Call SetProperty(strName:=strPropertyName, _
         lngType:=dbDate, varValue:=dteSingle)
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Another issue is the data sources of the various forms.  Are they all based on the same table, or on different tables?  If different tables, how are they linked?  If the second form is based on a table linked to the first form's record source table, then you could pull the name values through the link.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.