Code not working - Open a form and place a number

[Douglasrac]

Hi,

I have a button in a form that opens one form, coping a number and pasting in the new form field.
It works perfectly, but not for everything.

A) I press the button.
B) It opens the other form and place the number in the field.
C) I save.

The next time I go there, I will open the form1, I press the button and the record in form2 will be there. Perfect.

I have 2 cases of this. So 3 Forms.

Form 1 have the number.
Form 2 receive the number.


Form 3 (also opened by Form 1), receive the number.
* They both receive the same number from Form1.

Here is the code of the button that opens Form 2 and Form 3. (of course for Form 3 the code is a bit different, changing Form2 to Form3, but the rest is exactly the same).

Private Sub OpenForm2_Click()

On Error GoTo Err_OpenForm2_Click

	Dim stDocName As String
	Dim strCriteria As String

strCriteria = "[Number to copy] = " & Me.[Number to copy]

DoCmd.OpenForm "Form2", _
	WhereCondition:=strCriteria, _
	WindowMode:=acDialog, _
	OpenArgs:=(Me.[Number to copy])

Exit_OpenForm2_Click:
	Exit Sub
	
Err_OpenForm2_Click:
	MsgBox Err.Description
	Resume Exit_OpenForm2_Click
   
End Sub

Now here is the problem:

On Form 2, I can create new records, and the number will be also in the new records. The same number. So, I can have 1000 records on Form2, and they will be all connected to the same record on Form1.

On Form2, that does not happen. When I create a new record on Form3, the "Number to copy" field will be empty. So I have to put the number manually to relate the record to the record on Form1.

The relationships between the tables are exactly the same. The all have One-To-Many Referential Integrity relationship. They are AutoNumber connected to Number.
I checked everything! Exactly the same. All the codes for new record, all events associate, and all the parameters. Its almost a copy! How come it works for one it doesnt work for another one?

[RuralGuy]

See if this link helps at all: http://www.baldyweb.com/OpenArgs.htm

[Douglasrac]

Ok, I think I know what is the problem.

Where can I see the real name of a field? Where can I change this name?

On the tables, they all have "Number to copy" names.
Table1, 2 and 3 have a field "Number to copy".

For some reason, on Form3, the name is "Number to copy_Form3".

On Table3 I put "Number to copy", not "Number to copy_Form3".

So the code to open Form3 should be changed.
Both code below. But I don´t know how, because I don´t know which one refers to Form1 and which one refers to Form3.

strCriteria = "[Number to copy] = " & Me.[Number to copy]

	OpenArgs:=(Me.[Number to copy])

But besides that, how can I change, so everything have the same name? Since they should all refer to the same thing, they should be the same. It´s just a connection between tables.

[RuralGuy]

Having an OpenArgs argument *only* makes sense if you have code in the next form to do something with it. Do you?

[Douglasrac]

Having an OpenArgs argument *only* makes sense if you have code in the next form to do something with it. Do you?

The code is useful just to connect both records.

[RuralGuy]

It does nothing to "connect" records by itself.

[Douglasrac]

It does nothing to "connect" records by itself.

In that case I don´t know how to connect records on Access.
I relate one field to another and thats how I relate records. One filed as reference, as I see on relationships panel.

One form is Name the other is Product. How do you relate the product to that name, if you don´t have a common field?

[RuralGuy]

Normally you would relate records by placing one table's PrimaryKey in another table as a ForeignKey.

[Douglasrac]

Normally you would relate records by placing one table's PrimaryKey in another table as a ForeignKey.

Thats exactly what I do. "Number to copy" is primary key on Table1 and foreign key on Table2 and 3.

And then how do you make an automatic relationship, if you don´t insert the primary key of table1 on table2? You have to insert it somehow. Manually or automatically, and thats what I try do to with that code. Copying.

[RuralGuy]

Do you have the relationships defined in the Relationship menu? Are they a 1:m relationship? These are normally handled with a Form/SubForm arrangement.

[Douglasrac]

Do you have the relationships defined in the Relationship menu? Are they a 1:m relationship? These are normally handled with a Form/SubForm arrangement.

I`m not sure with they are subforms. How do I check that?

But sure they have relationships on Database Tools > Relationships.
They are One-To-Many relationship with Referential integrity, and Join Type number 2.

[Douglasrac]

Im not sure but by the time I create the database I think I deliberately choose to not go with subForms because the view was horrible. It´s a lot of data to input and the subForm seems to not be the nicest way to input, so I choose to copy with the code the number of reference.

I think its the same result at the end, no?

[RuralGuy]

Unless you use the LinkChild/MasterFields properties of a SubForm, there is nothing in Access to update a ForeignKey. You would need to do it yourself as you have tried but there is code required in the next form which it seems you do not have.

[RuralGuy]

Im not sure but by the time I create the database I think I deliberately choose to not go with subForms because the view was horrible. It´s a lot of data to input and the subForm seems to not be the nicest way to input, so I choose to copy with the code the number of reference.

I think its the same result at the end, no?

Yes, but you need code to update the ForeignKey field. The link I posted shows how to do that using the OpenArgs passed to the next form.

[Douglasrac]

Well I tried to make SubForms but its horrible. The automatic way makes the first form as it suppose to be and the second is a table. I don't want a table.

So I tried to get the command of the button but its very confused. It's not only one command session.

So, I would like to keep my forms the way it is. And I create a button, using the interface, that will open the other form and the code is exactly what I'm using.

Private Sub Comando19_Click()
On Error GoTo Err_Comando19_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Dados dos Condutores"
    
    stLinkCriteria = "[Number to Copy]=" & Me![Number to copy]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Comando19_Click:
    Exit Sub

Err_Comando19_Click:
    MsgBox Err.Description
    Resume Exit_Comando19_Click
    
End Sub

So I'm in the right path.

I just need help on how to change the command below, so access will understand which one is the correct field.

stLinkCriteria = "[Number to Copy]=" & Me![Number to copy]

Or I need to change the name of that field, which seems impossible. Where can I see the real name of a field? Where can I change this name?