Calling two records from one table in one form

[rigor via AccessMonster.com]

How to call two records from one table in one form and replace record from
single field to another. For example, i have record with computer
manufecturer, serial number, tipe and number of network port. I need to
replace number of network port between two computers.

Thanks for advance,
rigor

 

[Brendan Reynolds]

How to call two records from one table in one form and replace record from
single field to another. For example, i have record with computer
manufecturer, serial number, tipe and number of network port. I need to
replace number of network port between two computers.

Thanks for advance,
rigor

Dim db As DAO.Database
Dim strSql As String

stgrSql = "UPDATE YourTableName SET YourFieldName = (SELECT YourFieldName
FROM YourTableName WHERE <insert the criteria to select the source record
here>) WHERE <insert the criteria to select the target record here>

Set db = CurrentDb
db.Execute strSql

I can't tell you what the "criteria to select" bits would be, without
knowing which field or combination of fields uniquely identify the source
and target records.

 

[rigor via AccessMonster.com]

Thanks, field which i use to uniquely identify records is serial number

How to call two records from one table in one form and replace record from
single field to another. For example, i have record with computer

[quoted text clipped - 3 lines]

Thanks for advance,
rigor

Dim db As DAO.Database
Dim strSql As String

stgrSql = "UPDATE YourTableName SET YourFieldName = (SELECT YourFieldName
FROM YourTableName WHERE <insert the criteria to select the source record
here>) WHERE <insert the criteria to select the target record here>

Set db = CurrentDb
db.Execute strSql

I can't tell you what the "criteria to select" bits would be, without
knowing which field or combination of fields uniquely identify the source
and target records.

 

[Brendan Reynolds]

Thanks, field which i use to uniquely identify records is serial number

OK, but how are you selecting the source and target records?

Suppose, for example, you've selected the source record in a combo box
called "cboSource" and the target record in a combo box called "cboTarget".
The code to build the SQL statement might then look something like so
(assuming that SerialNumber is a text field) ...

strSql = "UPDATE YourTableName SET YourFieldName = (SELECT YourFieldName
FROM YourTableName WHERE SerialNumber = '" & Me.cboSource & "') WHERE
SerialNumber = '" & Me.cboTarget & "'"

Alternatively, if your combo box includes a column that has the port number,
you can get the source port number from there, and eliminate the subquery
from the SQL statement ...

strSql = "UPDATE YourTableName SET YourFieldName = '" &
me.cboSource.Colmn(1) & "' WHERE SerialNumber = '" & Me.cboTarget & "'"

This assumes that cboSource has two columns and its rowsource is something
like ....

SELECT SerialNumber, PortNumber FROM YourTable

The Column property is zero based, so Column(0) refers to the first column,
Column(1) to the second column, etc.

How to call two records from one table in one form and replace record
from
single field to another. For example, i have record with computer

[quoted text clipped - 3 lines]

Thanks for advance,
rigor

Dim db As DAO.Database
Dim strSql As String

stgrSql = "UPDATE YourTableName SET YourFieldName = (SELECT YourFieldName
FROM YourTableName WHERE <insert the criteria to select the source record
here>) WHERE <insert the criteria to select the target record here>

Set db = CurrentDb
db.Execute strSql

I can't tell you what the "criteria to select" bits would be, without
knowing which field or combination of fields uniquely identify the source
and target records.

 

[rigor via AccessMonster.com]

How to call two records from one table in one form and replace record from
single field to another. For example, i have record with computer

[quoted text clipped - 3 lines]

Thanks for advance,
rigor

Dim db As DAO.Database
Dim strSql As String

stgrSql = "UPDATE YourTableName SET YourFieldName = (SELECT YourFieldName
FROM YourTableName WHERE <insert the criteria to select the source record
here>) WHERE <insert the criteria to select the target record here>

Set db = CurrentDb
db.Execute strSql

I can't tell you what the "criteria to select" bits would be, without
knowing which field or combination of fields uniquely identify the source
and target records.

Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE Computers set [Network] = (select [Network] from Computers
where (((Computers.[Serial]) = [txtNetwork])))where (((Computers.[Serial]) =
[txtNetworkNew]))"

Set db = CurrentDb
db.Execute strSql

When exsecute this code i get message:

"Run-time error '3061'
Too few parameters. Expected 3.

Where does this code go wrong?

 

[Brendan Reynolds]

strSql = "UPDATE Computers set [Network] = (select [Network] from
Computers
where (((Computers.[Serial]) = [txtNetwork])))where (((Computers.[Serial])
=
[txtNetworkNew]))"

Set db = CurrentDb
db.Execute strSql

When exsecute this code i get message:

"Run-time error '3061'
Too few parameters. Expected 3.

Where does this code go wrong?

You've got the names of the controls in the SQL string, you need to
concatenate the values of the controls into the string ...

strSql = "UPDATE Computers set [Network] = (select [Network] from Computers
where (((Computers.[Serial]) = '" & Me.txtNetwork & "')))where
(((Computers.[Serial]) =
'" & Me.txtNetworkNew & "'))"

 

[rigor via AccessMonster.com]

[quoted text clipped - 15 lines]

knowing which field or combination of fields uniquely identify the source
and target records.

Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE Computers set [Network] = (select [Network] from Computers
where (((Computers.[Serial]) = [txtNetwork])))where (((Computers.[Serial]) =
[txtNetworkNew]))"

Set db = CurrentDb
db.Execute strSql

When exsecute this code i get message:

"Run-time error '3061'
Too few parameters. Expected 3.

Where does this code go wrong?

Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE Computers set [Network] = (select [Network] from Computers
where Computers.[Serial] = '" & Me.txtSerial & "') where [Serial] = '" & Me.
txtSerialNew & "'"

Set db = CurrentDb
db.Execute strSql

When exsecute this code i get message:

"Run-time error '3061'

 

[Brendan Reynolds]

strSql = "UPDATE Computers set [Network] = (select [Network] from
Computers
where Computers.[Serial] = '" & Me.txtSerial & "') where [Serial] = '" &
Me.
txtSerialNew & "'"

Set db = CurrentDb
db.Execute strSql

When exsecute this code i get message:

"Run-time error '3061'

Too few parameters. Expected 1

Are you sure the table and field names are all correct?

 

[rigor via AccessMonster.com]

Yes, but only i don't using combo then unbound textbox. Textbox receive data
from listbox.

strSql = "UPDATE Computers set [Network] = (select [Network] from
Computers

[quoted text clipped - 9 lines]

"Run-time error '3061'

Are you sure the table and field names are all correct?

 

[Brendan Reynolds]

Yes, but only i don't using combo then unbound textbox. Textbox receive
data
from listbox.

strSql = "UPDATE Computers set [Network] = (select [Network] from
Computers

[quoted text clipped - 9 lines]

"Run-time error '3061'
Too few parameters. Expected 1

Are you sure the table and field names are all correct?

Beats me then, I'm afraid. There's something in the SQL statement that
Access doesn't recognize, but I can't see what it is.

 

[rigor via AccessMonster.com]

Thanks anyway, this is solution, i will adjust SQL statement and post them
here.
Thanks for assistance!

Yes, but only i don't using combo then unbound textbox. Textbox receive
data

[quoted text clipped - 9 lines]
Beats me then, I'm afraid. There's something in the SQL statement that
Access doesn't recognize, but I can't see what it is.

 

[rigor via AccessMonster.com]

I'm create a update query

UPDATE Computers SET Computers.[Network] = [forms]![frmExistingForm]![Network]

WHERE (((Computers.[Serial])=[forms]![frmExistingForm]![SerialNew]));
This works great.

Edit/Delete Message

Thanks anyway, this is solution, i will adjust SQL statement and post them
here.
Thanks for assistance!
[quoted text clipped - 4 lines]

Beats me then, I'm afraid. There's something in the SQL statement that
Access doesn't recognize, but I can't see what it is.