Calling two records from one table in one form

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

--
Message posted via http://www.accessmonster.com

"rigor via AccessMonster.com" u45328@uwe wrote in message
news:918ab72d9d6f7@uwe...
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

--
Message posted via http://www.accessmonster.com



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

Brendan Reynolds wrote:
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200902/1

"rigor via AccessMonster.com" u45328@uwe wrote in message
news:918b31aa15323@uwe...
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.


Brendan Reynolds wrote:
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200902/1




--
Brendan Reynolds

Brendan Reynolds wrote:
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?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200902/1

"rigor via AccessMonster.com" u45328@uwe wrote in message
news:918c628cfcd14@uwe...
snip
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 & "'))"

--
Brendan Reynolds

rigor wrote:
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 - 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'
Too few parameters. Expected 1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200902/1

"rigor via AccessMonster.com" u45328@uwe wrote in message
news:918ca0974ba58@uwe...
snip

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?

--
Brendan Reynolds

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

Brendan Reynolds wrote:
snip

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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200902/1

"rigor via AccessMonster.com" u45328@uwe wrote in message
news:918d129d2782a@uwe...
Yes, but only i don't using combo then unbound textbox. Textbox receive
data
from listbox.

Brendan Reynolds wrote:
snip

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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200902/1



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.

--
Brendan Reynolds