Problem with query

[Addie]

Hi Everyone,


I am facing a problem with my recordset. I have a query which should fetch 2 records and then insert them into a table which in turn will be displayed into list box. But my query is fetching only one record and hence the same record is then displayed in listbox. I don't know why its not fetching two records. I ma writing the code here. Please tell me if I am wrong somewhere .

sql = "SELECT LOCATION_1.LOCATION_ID from LOCATION_1 where LOCATION_1.LOC_DESC = '" & Loc & "'"
Set rs1 = db.OpenRecordset(sql)
If rs1.RecordCount <> 0 Then
Loc_ID = rs1(0)
End If
Dim sql1 As String
sql1 = "Select ROUTELOC_1.ROUTE_ID,ROUTELOC_1.SUBROUTE_NO from ROUTELOC_1 where ROUTELOC_1.LOCATION_ID = '" & Loc_ID & "'"
Set rs1 = db.OpenRecordset(sql1)
R_ID = rs1(0)
SR_ID = rs1(1)
Dim sql2 As String
If Frame10.Value = -1 Then
sql2 = "Select Taker_1.Taker_ID, Taker_1.Taker_Name,Taker_1.TIME_ST_SAP, Taker_1.CAP_AVAL, Taker_1.Phone_NO, Taker_1.VEH_NO, Taker_1.VEH_DETAILS from TAKER_1 where TAKER_1.ROUTE_ID = '" & rs1(0) & "'" & " and TAKER_1.SUBROUTE_NO <= '" & rs1(1) & "'" & " and TAKER_1.ACTIVE = '-1' and Taker_1.CAPACITY > Taker_1.CAP_AVAL and Taker_1.CAP_AVAL > 0"
Else
sql2 = "Select Taker_1.Taker_ID, Taker_1.Taker_Name, Taker_1.TIME_ST, Taker_1.CAP_AVAL, Taker_1.Phone_NO, Taker_1.VEH_NO, Taker_1.VEH_DETAILS from TAKER_1 where TAKER_1.ROUTE_ID = '" & rs1(0) & "'" & " and TAKER_1.SUBROUTE_NO <= '" & rs1(1) & "'" & " and TAKER_1.ACTIVE = '-1'"
End If
Set rs2 = db.OpenRecordset(sql2)
If rs2.RecordCount <> 0 Then
sql2 = "INSERT INTO IntTaker ([TAKER_ID], " & _
"[Taker_Name],[START_Time], " & _
"[CAP_AVAL], [PHONE_NO],[VEH_NO],[VEH_DETAILS],[ROUTE_ID]) " & _
" VALUES ('" & rs2(0) & "', '" & rs2(1) & "', " & _
" '" & rs2(2) & "', " & rs2(3) & ", " & _
" " & rs2(4) & ", '" & rs2(5) & "', " & _
" '" & rs2(6) & "','" & rs1(0) & "') "

DoCmd.RunSQL sql2
lbxTaker.Requery

lbxTaker.Visible = True
Else
MsgBox "No Taker found, Try later"
End If


Please let me know as I am new to Access.
Thanks for your help.

Regards,
Aditi.

 

[wazz]

could you rephrase the question? tell us about your tables, how they are related, which records you need, from which tables, and the criteria. you might be able to do this with the query designer where you can see everything clearly.

fetch 2 records and then insert them into a table which in turn will be displayed into list box.

- do the records have to inserted into a table before putting them into the list box?
- try the question again without the code, if possible. tnx.

 

[MagicMan]

A few things stand out. 1st, you are not checking for a successful retrieval on rs1. If this is a loop, you could be using the first and only successful retrieval in all subsequent iterations. 2nd...you are mixing DAO and SQL for the insert...just different. 3rd...your declaring variables mid stream...the usual practice is to have the Dim's at the top of the process. I think your problem is the 1st item I identified. Oh, for code you can use {code} to start and {/code} at the end of the code...replace the {} with [].
Hope that helps....
Bob
Also...first DAO is rs1...2nd DAO is RS1 as well without a close referencing different tables....strange it didn't fail.