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

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

DoCmd.Close acForm, "FormB", acSaveNo
                'OR
DoCmd.Close acForm, "FormB", acSavePrompt
                'OR
DoCmd.Close acForm, "FormB", acSaveYes

[angi35]

DoCmd.Close acForm, "FormB", acSaveNo
                'OR
DoCmd.Close acForm, "FormB", acSavePrompt
                'OR
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

[angi35]

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

[angi35]

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:

    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "FormB"
 
    stLinkCriteria = "[IDtableA]=" & Me![ID]
 
    '[IDtableA] is the source field for [txtID] in FormB
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.ID
 
    Forms![FormB]![Control1] = Me![Control1]
    Forms![FormB]![Control2] = Me![Control2]
    Forms![FormB]![Control3] = Me![Control3]
 

Code "On Open" for FormB:

     If Not IsNull(Me.OpenArgs) Then
 
     Me.txtID.DefaultValue = "=" & con_Quote & Me.OpenArgs & con_Quote
 
End If
 

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

[ADezii]

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:

    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "FormB"
 
    stLinkCriteria = "[IDtableA]=" & Me![ID]
 
    '[IDtableA] is the source field for [txtID] in FormB
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.ID
 
    Forms![FormB]![Control1] = Me![Control1]
    Forms![FormB]![Control2] = Me![Control2]
    Forms![FormB]![Control3] = Me![Control3]
 

Code "On Open" for FormB:

     If Not IsNull(Me.OpenArgs) Then
 
     Me.txtID.DefaultValue = "=" & con_Quote & Me.OpenArgs & con_Quote
 
End If
 

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.