Selecting records

S

Steve

Hello ,
I have problem with selecting one record from rsPonudjenaOprema and puting
it to another rsOdabranaOprema, with this code I transfer everything what is
in rsPonudjenaOprema to rsOdabranaOprema.
I would like to select 1 or 2 records which I want, can you help me with
this?

thanks

Private Sub lstOdabir_DblClick(Cancel As Integer)

Dim strRedak As String
Dim strOdabranaReferenca As String
Dim strSQL As String
Dim rsPonudjenaOprema As Recordset
Dim rsOdabranaOprema As Recordset
Dim db As Database
Dim xlTroskovnik As Excel.Application
Dim intRedak As Integer
Dim strSQLOdabir As String
Dim strBrojRJ45Prikljucnica As String
Dim intNapajanje As Integer
Dim intSvijetlo As Integer
Dim intBrojDolaznihPanela24 As Integer
Dim intBrojOdlaznihPanela As Integer

Select Case TXTOdabirOpreme

Case "NosaciKabela"

Set db = CurrentDb

strRedak = lstOdabir.Value

strOdabranaReferenca = Mid(strRedak, 2, 15)

strOdabranaReferenca = Trim(strOdabranaReferenca)

strSQL = "SELECT HorizontalniDrzaciKabela.Referenca AS Referenca, " & _
" HorizontalniDrzaciKabela.Opis AS Opis, " & _
" HorizontalniDrzaciKabela.Visina AS Visina, " & _
" HorizontalniDrzaciKabela.Napomena AS Napomena" & _
" FROM HorizontalniDrzaciKabela " & _
" ORDER BY HorizontalniDrzaciKabela.Referenca;"

strSQLOdabir = "Select OdabranaOprema.Referenca, OdabranaOprema.Opis, " & _
" OdabranaOprema.Kolicina, OdabranaOprema.Napomena, OdabranaOprema.Visina "
& _
" From OdabranaOprema"

Set rsPonudjenaOprema = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

Set rsOdabranaOprema = db.OpenRecordset(strSQLOdabir)

Do Until rsPonudjenaOprema.EOF

rsOdabranaOprema.AddNew

rsOdabranaOprema.Fields("Referenca").Value =
rsPonudjenaOprema.Fields("Referenca").Value
rsOdabranaOprema.Fields("Opis").Value =
rsPonudjenaOprema.Fields("Opis").Value
rsOdabranaOprema.Fields("Visina").Value =
rsPonudjenaOprema.Fields("Visina").Value
rsOdabranaOprema.Fields("Kolicina").Value = txtDrzaciKabela.Value

rsPonudjenaOprema.MoveNext
rsOdabranaOprema.Update

Loop

Set rsPonudjenaOprema = Nothing
Set rsOdabranaOprema = Nothing
 
J

John Nurick

Hi Steve,

Surely all you need is an append query. Something like this:

Dim strSQL As String

Const SQL_1 = "INSERT INTO OdabranaOprema " & vbCrLf _
& " (Referenca, Opis, Kolicina, Napomena, Visina) " & vbCrLf _
& "SELECT Referenca, Opis, Visina, "

Const SQL_2 = vbCrLf & "FROM HorizontalniDrzaciKabela " & vbCrLf

strSQL = SQL_1 & _
"""" & Me.txtDrzaciKabela.Value & """ AS Kolicina" _
& SQL_2 _
& "WHERE .... & ";"

CurrentDb.Execute strSQL, dbFailOnError

You don't say what criteria you'll use to select the records you want,
but all you have to do is to write code to include them in place of
WHERE ....
above.
 
S

Steve

Hello John, thank you your advice.

I am just beginner in Access programming so if you can help me with
following example (small one!).With my code I can load records from table
Napajanej (rsNapajanje) to table OdabranaOprema (rsOdabranaOprema) - all
the records!How can I use append query in my example to put only records I
want from table Napajanje to table OdabranaOprema?


Private Sub lstNapajanje_DblClick(Cancel As Integer)

Dim rsNapajanje As New ADODB.Recordset
Dim rsOdabranaOprema As New ADODB.Recordset
Dim db As Database

Set db = CurrentDb

rsOdabranaOprema.Open "OdabranaOprema", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
rsNapajanje.Open "Napajanje", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

rsNapajanje.MoveNext

Do Until rsNapajanje.EOF

rsOdabranaOprema.AddNew
rsOdabranaOprema!Referenca = rsNapajanje!Referenca
rsOdabranaOprema!Opis = rsNapajanje!Opis
rsOdabranaOprema.Update
rsNapajanje.MoveNext

Loop

rsOdabranaOprema.Close
rsNapajanje.Close

Set rsOdabranaOprema = Nothing
Set rsNapajanje = Nothing

End Sub


thanks!
 
J

John Nurick

How can you tell which records you want?

Hello John, thank you your advice.

I am just beginner in Access programming so if you can help me with
following example (small one!).With my code I can load records from table
Napajanej (rsNapajanje) to table OdabranaOprema (rsOdabranaOprema) - all
the records!How can I use append query in my example to put only records I
want from table Napajanje to table OdabranaOprema?


Private Sub lstNapajanje_DblClick(Cancel As Integer)

Dim rsNapajanje As New ADODB.Recordset
Dim rsOdabranaOprema As New ADODB.Recordset
Dim db As Database

Set db = CurrentDb

rsOdabranaOprema.Open "OdabranaOprema", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
rsNapajanje.Open "Napajanje", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

rsNapajanje.MoveNext

Do Until rsNapajanje.EOF

rsOdabranaOprema.AddNew
rsOdabranaOprema!Referenca = rsNapajanje!Referenca
rsOdabranaOprema!Opis = rsNapajanje!Opis
rsOdabranaOprema.Update
rsNapajanje.MoveNext

Loop

rsOdabranaOprema.Close
rsNapajanje.Close

Set rsOdabranaOprema = Nothing
Set rsNapajanje = Nothing

End Sub


thanks!
Click to expand...
 
J

John Nurick

Steve, I feel that my last message may be difficult to understand if
your first language is not English. What I meant was,

What criteria can be used to distinguish the records you want? Do they
have a particular value in some field, or a range of values, or what?
 
S

Steve

Hello John, sorry for misunderstanding,

situation is this,I take records from table Napajanje and show it on
ListBox, then, I select one of records on ListBox and I want to put it in
another table OdabranaOprema !

thanks
 
J

John Nurick

You have to do this with a WHERE clause. If you use an append query as I
suggested, the WHERE clause goes in the append query; if you use
recordset operations you have to use it in a SELECT query when you open
the recordset on table Napajanje.

For example, if the name of the form is frmAAA listbox is lbNapajanje
and its RowSource is
SELECT FieldXX FROM Napajanje ORDER BY FieldXX

and the user selects the item "Henry" from the listbox, the actual WHERE
clause must be
WHERE FieldXX = "Henry"

In VBA you build that with an expression like this (if FieldXX is text)

" WHERE FieldXXX = """ & Me.lbNapajanje.Value & """ "

or if Field XXX is a number field you use fewer quotation marks:

" WHERE FieldXX = " & Me.lbNapajanje.Value & ";"

So in your code you could use something like this:

...
Dim strSQL As String

strSQL = "SELECT * FROM Napajanje WHERE FieldXXX = """ _
& Me.lbNapajanje.Value & """;"
rsNapajanje.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
 
Top