Copying data from one form to another automatically

[angi35]

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

[ADezii]

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.
[CODE=vb]
'Transferring Field Values from FormA to FormB via the Click() Event of a Command Button
DoCmd.OpenForm "FormB"

Forms!FormB![Field1] = Me![Field1]
Forms!FormB![Field2] = Me![Field2]
Forms!FormB![Field3] = Me![Field3]
Forms!FormB![Field4] = Me![Field4][/CODE]

[angi35]

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.
[CODE=vb]
'Transferring Field Values from FormA to FormB via the Click() Event of a Command Button
DoCmd.OpenForm "FormB"

Forms!FormB![Field1] = Me![Field1]
Forms!FormB![Field2] = Me![Field2]
Forms!FormB![Field3] = Me![Field3]
Forms!FormB![Field4] = Me![Field4][/CODE]

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?

[ADezii]

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?

[CODE=vb]DoCmd.OpenForm "FormB", acNormal, , , acFormAdd, acWindowNormal[/CODE]

[angi35]

[CODE=vb]DoCmd.OpenForm "FormB", acNormal, , , acFormAdd, acWindowNormal[/CODE]

Thanks for the help!

Angi

[angi35]

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

[ADezii]

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:
[CODE=vb]
If DCount("*", "<Record Source for Form B>", "[ID] = " & Forms![FormB]![Control5]) > 0 Then
'ID already exists
Else
'proceed as Normal
End If[/CODE]

[angi35]

You could test the Record Source of Form B, something like:
[CODE=vb]
If DCount("*", "<Record Source for Form B>", "[ID] = " & Forms![FormB]![Control5]) > 0 Then
'ID already exists
Else
'proceed as Normal
End If[/CODE]

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?

[angi35]

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?

[ADezii]

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?

[angi35]

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:

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:

Private Sub Button1_Click()
On Error GoTo Err_Button1_Click
   
If DCount("*", "Query B", "[ID] = " & Forms![FormB]![txtID]) > 0 Then
   Dim stDocName As String
   Dim stLinkCriteria As String

   stDocName = "FormB"
    
   stLinkCriteria = "[txtID]=" & Me![ID]
   DoCmd.OpenForm stDocName, , , stLinkCriteria


Else
    DoCmd.OpenForm "FormB", acNormal, , , acFormAdd, acWindowNormal
    Forms![FormB]![txtSales] = Me![Source]
    Forms![FormB]![txtContact] = Me![Staff]
    Forms![FormB]![txtID] = Me![ID]
    
End If

Exit_Button1_Click:
    Exit Sub

(Incidentally, how do you get your post to indicate "Code: (vb)" rather than "Code: (text)"?)

Angi

[angi35]

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:

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?

[ADezii]

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:

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).

[angi35]

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.

[ADezii]

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.

[CODE=vb]
DoCmd.Close acForm, "FormB", acSaveNo
'OR
DoCmd.Close acForm, "FormB", acSavePrompt
'OR
DoCmd.Close acForm, "FormB", acSaveYes[/CODE]