If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Calling two records from one table in one form
"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 |
#3
|
|||
|
|||
Calling two records from one table in one form
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 |
#4
|
|||
|
|||
Calling two records from one table in one form
"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 |
#5
|
|||
|
|||
Calling two records from one table in one form
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 |
#6
|
|||
|
|||
Calling two records from one table in one form
"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 |
#7
|
|||
|
|||
Calling two records from one table in one form
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 |
#8
|
|||
|
|||
Calling two records from one table in one form
"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 |
#9
|
|||
|
|||
Calling two records from one table in one form
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 |
#10
|
|||
|
|||
Calling two records from one table in one form
"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 |
|
Thread Tools | |
Display Modes | |
|
|