from textbox to textbox on ANOTHER form

[Gilberto]

Hello

I have two forms: Projects and Engineering
In PROJECTS i have a textbox (VARIANT1) where the user will enter some text. I want this text to automatically appear as text on another TEXTBOX (VARIANT1LABEL) on the second form (ENGINEERING).

How can i do this???

[Rabbit]

Forms!FormName!ControlName

[Gilberto]

Forms!FormName!ControlName

This worked perfectly, thank you.

My only issue now is that the first form (PROJECTS) need to be running so that i dont get the ERROR '2450' CANT FIND THE FORM (PROJECTS) when i open the second form (ENGINEERING).

Is there a way that instead of displaying the value from the textbox from the form (PROJECTS) that it displays the value on the PROJECTS TABLE?? which is already stored and doesnt need to be OPEN so that the whole thing works?

ive tried:

Private Sub Form_open(Cancel As Integer)
Forms!Engineering!Variant1 = Tables!Projects!Variant1
End Sub

but it doesnt work (error 424, object required).

I also found out that the textbox on the second form (ENGINEERING) has to be UNBOUND so that this works. I NEED to BOUND this textbox so that the input displayed gets stored on this forms table (ENGINEERING TABLE), is there any way to accomplish this?


Thanks again,
Gilberto

[Stwange]

-----------------------------------------------

[Stwange]

Gilberto:
try this code, if I have understood you correctly it should do what you need:

Dim rs as DAO.recordSet
Set rs = DBEngine(0)(0).openRecordSet("SELECT Variant1 FROM Projects WHERE primary_key = '" & txtkey.value & "';")
'replace Variant1 with your field name, Projects with your table name, and txtkey.value with wherever your primary key is stored.
'Also, if your primary key is stored as a number in the table, change the WHERE clause to WHERE primary_key = " & txtkey.value & ";")
'you can now refer to the field as such:
dim var1 as String: var1 = rs!Variant1
'now, if the Engineering form is open, updating the text field should work whether it is bound or not, and so update the table. If it doesn't, you can leave it unbound and work around this:
var1 = Forms!Engineering!Variant1 'to store the old value as a parameter (for updating) - only needed if the field has to stay unbound.
Forms!Engineering!Variant1 = rs!Variant1 'this should be the only line you need.
DoCmd.runSQL("UPDATE tableName SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';") 'Updates the table (use if the text box is unbound)
'if Variant1 is stored as a number in the tables, again replace the WHERE with WHERE Variant1 = " & var1 & ";") 
 

Hope this helps.

[Gilberto]

Gilberto:
try this code, if I have understood you correctly it should do what you need:

Dim rs as DAO.recordSet
Set rs = DBEngine(0)(0).openRecordSet("SELECT Variant1 FROM Projects WHERE primary_key = '" & txtkey.value & "';")
'replace Variant1 with your field name, Projects with your table name, and txtkey.value with wherever your primary key is stored.
'Also, if your primary key is stored as a number in the table, change the WHERE clause to WHERE primary_key = " & txtkey.value & ";")
'you can now refer to the field as such:
dim var1 as String: var1 = rs!Variant1
'now, if the Engineering form is open, updating the text field should work whether it is bound or not, and so update the table. If it doesn't, you can leave it unbound and work around this:
var1 = Forms!Engineering!Variant1 'to store the old value as a parameter (for updating) - only needed if the field has to stay unbound.
Forms!Engineering!Variant1 = rs!Variant1 'this should be the only line you need.
DoCmd.runSQL("UPDATE tableName SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';") 'Updates the table (use if the text box is unbound)
'if Variant1 is stored as a number in the tables, again replace the WHERE with WHERE Variant1 = " & var1 & ";") 
 

Hope this helps.

Thanks for the fast reply. Im using:

1 Private Sub Form_open(Cancel As Integer)
2 Forms!Engineering!ProjectName = Forms!Projects!ProjectName

3 Dim rs As DAO.Recordset
4 Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE primary_key = " & EngineeringID.Value & ";")
5 Dim var1 As String: var1 = rs!Variant1
6 Forms!Engineering!Variant1 = rs!Variant1
7 DoCmd.RunSQL ("UPDATE tableName SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';")

End Sub


but the error 3061 TOO FEW PARAMETERS. EXPECTED 1 pups up and the line number 4 its underlined.

How can i fix this?

[Stwange]

Thanks for the fast reply. Im using:

1 Private Sub Form_open(Cancel As Integer)
2 Forms!Engineering!ProjectName = Forms!Projects!ProjectName

3 Dim rs As DAO.Recordset
4 Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE primary_key = " & EngineeringID.Value & ";")
5 Dim var1 As String: var1 = rs!Variant1
6 Forms!Engineering!Variant1 = rs!Variant1
7 DoCmd.RunSQL ("UPDATE tableName SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';")

End Sub


but the error 3061 TOO FEW PARAMETERS. EXPECTED 1 pups up and the line number 4 its underlined.

How can i fix this?

Sorry, I should have made this explicitly obvious: Replace primary_key with the field name of the primary key in your table, so at a guess:

Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE EngineeringID = " & EngineeringID.Value & ";")

[Gilberto]

Sorry, I should have made this explicitly obvious: Replace primary_key with the field name of the primary key in your table, so at a guess:

Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE EngineeringID = " & EngineeringID.Value & ";")

hehehe i should have guessed that...thanks BUT still not working :(

Im using:

Private Sub Form_open(Cancel As Integer)

Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE EngineeringID = " & EngineeringID.Value & ";")
Dim var1 As String: var1 = rs!Variant1
Forms!Engineering!Variant1 = rs!Variant1
DoCmd.RunSQL ("UPDATE tableName SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';")

End Sub

and still gettng the "ERROR 3061 Too few parameters. Expected 1"

any idea??

thanks again

[Stwange]

Only a slight change, but try this:

Private Sub Form_open(Cancel As Integer)
 
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE EngineeringID = " & EngineeringID.Value & ";")
If Not rs.EOF Then
    Dim var1 As String: var1 = rs!Variant1
    Forms!Engineering!Variant1 = rs!Variant1
    DoCmd.RunSQL ("UPDATE Projects SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';")
End If
End Sub
 

MAKE SURE: Variant1 is actually a field in this table. If you still get an error, tell me what error and which line.

Hope this helps.

[Gilberto]

Only a slight change, but try this:

Private Sub Form_open(Cancel As Integer)
 
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE EngineeringID = " & EngineeringID.Value & ";")
If Not rs.EOF Then
    Dim var1 As String: var1 = rs!Variant1
    Forms!Engineering!Variant1 = rs!Variant1
    DoCmd.RunSQL ("UPDATE Projects SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';")
End If
End Sub
 

MAKE SURE: Variant1 is actually a field in this table. If you still get an error, tell me what error and which line.

Hope this helps.

Hello again,

Its still not working :(

Just to confirm:

which primary key should i enter in the code? the one from the table where the user inputs the text or the one from the table where the text its going to be displayed???
im entering this code into the ENGINEERING form which is where the textbox is supposed to DISPLAY the text entered on form PROJECTS
names of BOTH textboxes are VARIANT1 both bounded to VARIANT1 field on their corresponding table.

I tried both ways and the errors are the following:

OPTION 1

1 Private Sub Form_open(Cancel As Integer)
2 Dim rs As DAO.Recordset
3 Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE EngineeringID = " & EngineeringID.Value & ";")
4 If Not rs.EOF Then
5 Dim var1 As String: var1 = rs!Variant1
6 Forms!Engineering!Variant1 = rs!Variant1
7 DoCmd.RunSQL ("UPDATE Projects SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';")
8 End If
9 End Sub

With ENGINEERINGID (where the text should be displayed) the error is: '3061' "too few parameters. Expected 1" with line #3 highlighted


OPTION 2

1 Private Sub Form_open(Cancel As Integer)
2 Dim rs As DAO.Recordset
3 Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE ProjectID = " & ProjectID.Value & ";")
4 If Not rs.EOF Then
5 Dim var1 As String: var1 = rs!Variant1
6 Forms!Engineering!Variant1 = rs!Variant1
7 DoCmd.RunSQL ("UPDATE Projects SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';")
8 End If
9 End Sub

with PROJECTID (table where the user enters the text) the error is: '424' "object required" with the same line (#3) highlighted

thanks again

[Stwange]

Hello again,

Its still not working :(

Just to confirm:

which primary key should i enter in the code? the one from the table where the user inputs the text or the one from the table where the text its going to be displayed???
im entering this code into the ENGINEERING form which is where the textbox is supposed to DISPLAY the text entered on form PROJECTS
names of BOTH textboxes are VARIANT1 both bounded to VARIANT1 field on their corresponding table.

I tried both ways and the errors are the following:

OPTION 1

1 Private Sub Form_open(Cancel As Integer)
2 Dim rs As DAO.Recordset
3 Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE EngineeringID = " & EngineeringID.Value & ";")
4 If Not rs.EOF Then
5 Dim var1 As String: var1 = rs!Variant1
6 Forms!Engineering!Variant1 = rs!Variant1
7 DoCmd.RunSQL ("UPDATE Projects SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';")
8 End If
9 End Sub

With ENGINEERINGID (where the text should be displayed) the error is: '3061' "too few parameters. Expected 1" with line #3 highlighted


OPTION 2

1 Private Sub Form_open(Cancel As Integer)
2 Dim rs As DAO.Recordset
3 Set rs = DBEngine(0)(0).OpenRecordset("SELECT Variant1 FROM Projects WHERE ProjectID = " & ProjectID.Value & ";")
4 If Not rs.EOF Then
5 Dim var1 As String: var1 = rs!Variant1
6 Forms!Engineering!Variant1 = rs!Variant1
7 DoCmd.RunSQL ("UPDATE Projects SET Variant1 = '" & rs!Variant1 & "' WHERE Variant1 = '" & var1 & "';")
8 End If
9 End Sub

with PROJECTID (table where the user enters the text) the error is: '424' "object required" with the same line (#3) highlighted

thanks again

I think I know what's causing these errors, but I'm having a bit of trouble understanding your logic to help you fix them. It doesn't necessarily have to be a primary key you check, just whatever you are using to identify which fields need updating.
Ie. if you want ALL the Variant1s updating, you don't need the WHERE clause, if you want the Variant1 updating on Projects, you need the Projects primary key there (which I presumed was currently displayed in the bound text box, hence the code).

An explanation of your errors:
Option 1. I'm guessing there is no EngineeringID on the table Projects. Replace the EngineeringID with ProjectID.

Option 2. It doesn't seem to be finding ProjectID (from the ProjectID.value), does this control exist? If so, is it on a different form? If it is you need to refer to it as [Forms]![insertFormNameHere]![ProjectID].value

Try those, and have a think about what you are trying to update. If I can get a clearer picture I might be able to help you more.

[Gilberto]

I think I know what's causing these errors, but I'm having a bit of trouble understanding your logic to help you fix them. It doesn't necessarily have to be a primary key you check, just whatever you are using to identify which fields need updating.
Ie. if you want ALL the Variant1s updating, you don't need the WHERE clause, if you want the Variant1 updating on Projects, you need the Projects primary key there (which I presumed was currently displayed in the bound text box, hence the code).

An explanation of your errors:
Option 1. I'm guessing there is no EngineeringID on the table Projects. Replace the EngineeringID with ProjectID.

Option 2. It doesn't seem to be finding ProjectID (from the ProjectID.value), does this control exist? If so, is it on a different form? If it is you need to refer to it as [Forms]![insertFormNameHere]![ProjectID].value

Try those, and have a think about what you are trying to update. If I can get a clearer picture I might be able to help you more.

That worked perfectly. I just took out the WERE clause and it WORKED!!! Now i can read on the second form a text which the user entered on the first form.
My only problem now is that this text displayed on the second form doesnt get stored anywhere on thats form table. This is not a problem as i dont require this however now i have a similar issue with another textbox.

On the first form (PROJECTS) i have a textbox (PROJECTNAME) which the user enters when opening the database.

On a second form (ENGINEERING) i have a list of products (PRODUCT NAME FRENCH) with some fields describing each product along with a field named PROJECTNAME which will "classify" evey product under a specific project.

I need the text (the name of the project) entered by the user on the PROJECT form (on the PROJECTNAME textbox) to be stored automatically as a record on every PRODUCT (under the field PROJECTNAME on the ENGINEERING table).

So that i can design a query that will give the user every product wich belongs to THAT project name he first specified.

Can this be done? Could you help me with some code?

I WILL CONTINUE THIS NEW THREAD WILL CONTINUE UNDER THE TITTLE "automatically store as a record the value of a textbox of another form"