By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,459 Members | 1,235 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,459 IT Pros & Developers. It's quick & easy.

Copying data from one form to another automatically

P: 55
I've been puzzling over something, and hopefully someone here can help me figure it out.

In Access 2000, I have two forms... let's call them formA and formB. Users enter data first into formA. Then in certain circumstances, they need to complete formB. Four of the controls on FormB pull identical information as 4 of the controls on formA. I want to set up a command button on formA that opens formB and automatically populates these 4 controls.

My question is how to make this happen.

Can it be done with formA based on tableA, and formB based on a query combining tableB with the 4 fields from tableA, joining on the primary key [ID] from tableA and foreign key [IDtableA] from tableB? I can't get this to work.

At best, I've created a control on formB with [IDtableA] as source -- when I manually enter the corresponding [ID] number, the other corresponding fields will populate automatically. But I don't want to have to enter that number manually.

Do both forms have to be constructed based on the same table? Do my fields from tableB have to be incorporated into tableA to make this work? If formB were a subform directly on formA, it would automatically pick up that ID number. Isn't there some way to make this happen when opening formB through a command button on formA? It's kind of an 'external subform', at least in my mind...

Angi
Feb 27 '08 #1
Share this Question
Share on Google+
18 Replies


ADezii
Expert 5K+
P: 8,701
I've been puzzling over something, and hopefully someone here can help me figure it out.

In Access 2000, I have two forms... let's call them formA and formB. Users enter data first into formA. Then in certain circumstances, they need to complete formB. Four of the controls on FormB pull identical information as 4 of the controls on formA. I want to set up a command button on formA that opens formB and automatically populates these 4 controls.

My question is how to make this happen.

Can it be done with formA based on tableA, and formB based on a query combining tableB with the 4 fields from tableA, joining on the primary key [ID] from tableA and foreign key [IDtableA] from tableB? I can't get this to work.

At best, I've created a control on formB with [IDtableA] as source -- when I manually enter the corresponding [ID] number, the other corresponding fields will populate automatically. But I don't want to have to enter that number manually.

Do both forms have to be constructed based on the same table? Do my fields from tableB have to be incorporated into tableA to make this work? If formB were a subform directly on formA, it would automatically pick up that ID number. Isn't there some way to make this happen when opening formB through a command button on formA? It's kind of an 'external subform', at least in my mind...

Angi
There need be no correlation between the Forms whatsoever, or they may be related, the choice is yours. You are simply transferring data from one Form to the next. The one thing you must be aware of, however, is to make sure the Data Types of the underlying Fields are consistent within the transfer. For instance, if you are transferring the Text Value "One" from [Field1] in FormA to [Field1] in FormB, where the Control Source in [Field1]/FormB is Numeric, you will eventually get a Data type mismatch Error.
Expand|Select|Wrap|Line Numbers
  1. 'Transferring Field Values from FormA to FormB via the Click() Event of a Command Button
  2. DoCmd.OpenForm "FormB"
  3.  
  4. Forms!FormB![Field1] = Me![Field1]
  5. Forms!FormB![Field2] = Me![Field2]
  6. Forms!FormB![Field3] = Me![Field3]
  7. Forms!FormB![Field4] = Me![Field4]
Feb 28 '08 #2

P: 55
There need be no correlation between the Forms whatsoever, or they may be related, the choice is yours. You are simply transferring data from one Form to the next. The one thing you must be aware of, however, is to make sure the Data Types of the underlying Fields are consistent within the transfer. For instance, if you are transferring the Text Value "One" from [Field1] in FormA to [Field1] in FormB, where the Control Source in [Field1]/FormB is Numeric, you will eventually get a Data type mismatch Error.
Expand|Select|Wrap|Line Numbers
  1. 'Transferring Field Values from FormA to FormB via the Click() Event of a Command Button
  2. DoCmd.OpenForm "FormB"
  3.  
  4. Forms!FormB![Field1] = Me![Field1]
  5. Forms!FormB![Field2] = Me![Field2]
  6. Forms!FormB![Field3] = Me![Field3]
  7. Forms!FormB![Field4] = Me![Field4]

I think there needs to be one more step in the code... is there a way to make it open FormB to a new record for data entry? Otherwise it appears to be overriding the data in an existing record.

Or do I need to set up FormB with the data entry property set to "yes", and save the same form as FormC with data entry set to "no" for reviewing all records?
Feb 28 '08 #3

ADezii
Expert 5K+
P: 8,701
I think there needs to be one more step in the code... is there a way to make it open FormB to a new record for data entry? Otherwise it appears to be overriding the data in an existing record.

Or do I need to set up FormB with the data entry property set to "yes", and save the same form as FormC with data entry set to "no" for reviewing all records?
is there a way to make it open FormB to a new record for data entry?
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FormB", acNormal, , , acFormAdd, acWindowNormal
Feb 29 '08 #4

P: 55
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FormB", acNormal, , , acFormAdd, acWindowNormal
Thanks for the help!

Angi
Feb 29 '08 #5

P: 55
Thanks for the help!

Angi

It occurs to me that there should only be one FormB for each FormA. I don't want a user to create a second FormB if one already exists. I've set it so that FormB contains a control (let's call it control5) that contains the primary key for FormA, and allows no duplicates. But a user could go about entering lots of data in FormB before trying to save it and getting an error message. So I'm wondering if it's possible to create an if-then statement that says "If this ID number already exists in control5 of any record of FormB, then open FormB to that record; else open to a new record."

Can this be done?

Angi
Feb 29 '08 #6

ADezii
Expert 5K+
P: 8,701
It occurs to me that there should only be one FormB for each FormA. I don't want a user to create a second FormB if one already exists. I've set it so that FormB contains a control (let's call it control5) that contains the primary key for FormA, and allows no duplicates. But a user could go about entering lots of data in FormB before trying to save it and getting an error message. So I'm wondering if it's possible to create an if-then statement that says "If this ID number already exists in control5 of any record of FormB, then open FormB to that record; else open to a new record."

Can this be done?

Angi
You could test the Record Source of Form B, something like:
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", "<Record Source for Form B>", "[ID] = " & Forms![FormB]![Control5]) > 0 Then
  2.   'ID already exists
  3. Else
  4.   'proceed as Normal
  5. End If
Mar 1 '08 #7

P: 55
You could test the Record Source of Form B, something like:
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", "<Record Source for Form B>", "[ID] = " & Forms![FormB]![Control5]) > 0 Then
  2.   'ID already exists
  3. Else
  4.   'proceed as Normal
  5. End If
Adding this code, I'm getting an error message saying that the database can't find FormB. I know I've got it spelled correctly. Any ideas?
Mar 3 '08 #8

P: 55
Adding this code, I'm getting an error message saying that the database can't find FormB. I know I've got it spelled correctly. Any ideas?
I've been trying to work around this, but I'm still having this problem - the database can't find FormB. Can anyone help me out?
Mar 7 '08 #9

ADezii
Expert 5K+
P: 8,701
I've been trying to work around this, but I'm still having this problem - the database can't find FormB. Can anyone help me out?
Does a FormB physically exist in your Database?
Mar 8 '08 #10

P: 55
Does a FormB physically exist in your Database?
Yes - it's called something else (and like I said, I've double-checked the spelling of the real name in the code), but yes, that form exists, but the database isn't recognizing it in this line of code. Everything worked up through:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FormB", acNormal, , , acFormAdd, acWindowNormal
But trying to insert the latest code you gave me caused the error message to appear. Here's what I have:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Button1_Click()
  2. On Error GoTo Err_Button1_Click
  3.  
  4. If DCount("*", "Query B", "[ID] = " & Forms![FormB]![txtID]) > 0 Then
  5.    Dim stDocName As String
  6.    Dim stLinkCriteria As String
  7.  
  8.    stDocName = "FormB"
  9.  
  10.    stLinkCriteria = "[txtID]=" & Me![ID]
  11.    DoCmd.OpenForm stDocName, , , stLinkCriteria
  12.  
  13.  
  14. Else
  15.     DoCmd.OpenForm "FormB", acNormal, , , acFormAdd, acWindowNormal
  16.     Forms![FormB]![txtSales] = Me![Source]
  17.     Forms![FormB]![txtContact] = Me![Staff]
  18.     Forms![FormB]![txtID] = Me![ID]
  19.  
  20. End If
  21.  
  22. Exit_Button1_Click:
  23.     Exit Sub
(Incidentally, how do you get your post to indicate "Code: (vb)" rather than "Code: (text)"?)

Angi
Mar 10 '08 #11

P: 55
I've figured part of it out. The database can find FormB if FormB is already open. So it would seem to be a simple thing of opening FormB first. I put this line as the first line in the code for the command button:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FormB", acNormal,,,,acHidden
However, I'm now back to a problem with it overwriting an existing record, rather than creating a new record if there is no existing txtID in FormB for the ID in FormA. This must be because the form is already open, so the "DoCmd.OpenForm" commands in the If/Else clauses don't work. Is there some command other than "OpenForm"... something like "ShowRecord", that would work here?
Mar 10 '08 #12

ADezii
Expert 5K+
P: 8,701
I've figured part of it out. The database can find FormB if FormB is already open. So it would seem to be a simple thing of opening FormB first. I put this line as the first line in the code for the command button:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FormB", acNormal,,,,acHidden
However, I'm now back to a problem with it overwriting an existing record, rather than creating a new record if there is no existing txtID in FormB for the ID in FormA. This must be because the form is already open, so the "DoCmd.OpenForm" commands in the If/Else clauses don't work. Is there some command other than "OpenForm"... something like "ShowRecord", that would work here?
If there is no existing txtID in FormB for the ID in FormA, try closing FormB immediately after the Else Line (between Lines 14 and 15).
Mar 11 '08 #13

P: 55
If there is no existing txtID in FormB for the ID in FormA, try closing FormB immediately after the Else Line (between Lines 14 and 15).
That's an idea. What would be the code for this? DoCmd.Close would close FormA. I haven't found another command that would close a different form.
Mar 11 '08 #14

ADezii
Expert 5K+
P: 8,701
That's an idea. What would be the code for this? DoCmd.Close would close FormA. I haven't found another command that would close a different form.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close acForm, "FormB", acSaveNo
  2.                 'OR
  3. DoCmd.Close acForm, "FormB", acSavePrompt
  4.                 'OR
  5. DoCmd.Close acForm, "FormB", acSaveYes
Mar 11 '08 #15

P: 55
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close acForm, "FormB", acSaveNo
  2.                 'OR
  3. DoCmd.Close acForm, "FormB", acSavePrompt
  4.                 'OR
  5. DoCmd.Close acForm, "FormB", acSaveYes

Thanks ADezii - I tried this quickly today and it didn't fix the problem, but I'll work on it more later next week and see if I can tame it. I have a more pressing deadline with a different database at the moment...

Angi
Mar 14 '08 #16

P: 55
To follow up...

I never did get this to work, but I worked around it. On FormA, I have two buttons: "Create FormB" (open FormB in data entry mode, copying certain data from one form to the other) and "View FormB" (open FormB to the existing record linked to the active record on FormA). I created a prominently placed command button on FormB called "Assign Number" -- to the user it looks like the database automatically assigns a record number, but of course what it really does is saves the record. The record number from FormA is indexed in FormB with no duplicates. If a user tries to create a duplicate record, she'll at least be able to figure that out early on by clicking the "Assign #" button and getting an error message, rather than entering all her info in the form and then being unable to save it.

Angi
Mar 20 '08 #17

P: 55
For the sake of knowledge sharing, while solving another problem, I finally got code that works for this problem. The trick is to use OpenArgs.

Code "on click" for the command button on FormA:

Expand|Select|Wrap|Line Numbers
  1.     Dim stDocName As String
  2.     Dim stLinkCriteria As String
  3.  
  4.     stDocName = "FormB"
  5.  
  6.     stLinkCriteria = "[IDtableA]=" & Me![ID]
  7.  
  8.     '[IDtableA] is the source field for [txtID] in FormB
  9.  
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.ID
  11.  
  12.     Forms![FormB]![Control1] = Me![Control1]
  13.     Forms![FormB]![Control2] = Me![Control2]
  14.     Forms![FormB]![Control3] = Me![Control3]
  15.  
Code "On Open" for FormB:

Expand|Select|Wrap|Line Numbers
  1.      If Not IsNull(Me.OpenArgs) Then
  2.  
  3.      Me.txtID.DefaultValue = "=" & con_Quote & Me.OpenArgs & con_Quote
  4.  
  5. End If
  6.  
Now, when I click the button on FormA, it will either open to the existing corresponding record in FormB, or if there is no existing record, it will open a new record; and data from A will be copied into B. FormB can also be opened directly, independent of FormA, for viewing existing records or for adding new records that do not have a corresponding FormA record.

Angi
Mar 25 '08 #18

ADezii
Expert 5K+
P: 8,701
For the sake of knowledge sharing, while solving another problem, I finally got code that works for this problem. The trick is to use OpenArgs.

Code "on click" for the command button on FormA:

Expand|Select|Wrap|Line Numbers
  1.     Dim stDocName As String
  2.     Dim stLinkCriteria As String
  3.  
  4.     stDocName = "FormB"
  5.  
  6.     stLinkCriteria = "[IDtableA]=" & Me![ID]
  7.  
  8.     '[IDtableA] is the source field for [txtID] in FormB
  9.  
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.ID
  11.  
  12.     Forms![FormB]![Control1] = Me![Control1]
  13.     Forms![FormB]![Control2] = Me![Control2]
  14.     Forms![FormB]![Control3] = Me![Control3]
  15.  
Code "On Open" for FormB:

Expand|Select|Wrap|Line Numbers
  1.      If Not IsNull(Me.OpenArgs) Then
  2.  
  3.      Me.txtID.DefaultValue = "=" & con_Quote & Me.OpenArgs & con_Quote
  4.  
  5. End If
  6.  
Now, when I click the button on FormA, it will either open to the existing corresponding record in FormB, or if there is no existing record, it will open a new record; and data from A will be copied into B. FormB can also be opened directly, independent of FormA, for viewing existing records or for adding new records that do not have a corresponding FormA record.

Angi
Thanks for sharing your solution with us.
Mar 25 '08 #19

Post your reply

Sign in to post your reply or Sign up for a free account.