Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Load ListView from a Query 1

Status
Not open for further replies.
ZmrAbdulla

ZmrAbdulla

Technical User
Apr 22, 2003
4,364
AE
Hi,
Can anyone show me sample code to populate a ListView from a Query with Criteria. I am using a code but unable to populate form a query.
Code is below

Code: 
Private Sub ListNames_Click()
    Dim rs As New ADODB.Recordset   
    Dim colHeader As ColumnHeader
    Dim lstItem As ListItem
    Dim SQL As String  
  
    ListView1.ListItems.Clear
    SQL = "SELECT * FROM Ledger;"

    rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   
        
    Me!ListView1.View = lvwReport
  
     'Set up column headers (not using)
   ' With ListView1.ColumnHeaders
    '    .Add , , "Names", 2000
     '  .Add , , "Date", 1000
     '   .Add , , "Category", 2000
     '   .Add , , "Credits", 1000
     '   .Add , , "Debits", 1000
   ' End With
   
    
   rs.MoveFirst
   While Not rs.EOF
    
 ' Add items and subitems to list control.
   
    Set lstItem = ListView1.ListItems.Add()
    lstItem.Text = Nz(Trim(rs!Names))
    lstItem.SubItems(1) = Format(Nz(Trim(rs!PymtDate)), "Medium Date")
    lstItem.SubItems(2) = Nz(Trim(rs!Category))
    lstItem.SubItems(3) = Format(Nz(Trim(rs!Credits), "0.00"), "Fixed")
    lstItem.SubItems(4) = Format(Nz(Trim(rs!Debits), "0.00"), "Fixed")
      
   rs.MoveNext    
     Wend  
     rs.Close 
  Exit Sub
End Sub
I am able to populate without criteria, not with criteria. This gives me an error saying Failed to open recordset.
Code: 
'SQL = " SELECT * FROM Ledger WHERE Names = Forms![MainForm]![ListNames];"


Thanks


Zameer Abdulla

 
Sort by date Sort by votes
Try it like this:
Code: 
SQL = " SELECT * FROM Ledger WHERE Names = """ & Forms![MainForm]![ListNames] & """;"
 
Upvote 0 Downvote
Hi,
There was some problem to access this site for the last two days.... Pardon me....

Thank you for the reply. I tried this still I am getting the error.

Runtime Error '2147467259 (800004005)'
Method 'open' of object_'Recordset' failed

When I try to debug the line line below was highlighted.

rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Any idea?

Regards


Zameer Abdulla

 
Upvote 0 Downvote
Have you tried this ?
SQL = "SELECT * FROM Ledger WHERE Names = '" & Forms![MainForm]![ListNames] & "'"
This syntax is valid if ListNames returns a single text value.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
What is the value of Forms![MainForm]![ListNames] ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
What is the [highlight]value[/highlight] of Forms![MainForm]![ListNames] when you raise the error?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
What is displayed if you insert the statement below before the rs.Open raising the error ?
MsgBox "'" & Forms![MainForm]![ListNames] & "'"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
It prompts me a Msg Box
And what is displayed in this MsgBox ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Hi, PHV
Thank you for bearing with me. I have got the code working now. As I am not a programmer there can be mistakes. Please let me know if there is any modification should be done.
Code: 
Private Sub ListNames_Click()
    Dim rs As DAO.Recordset
    Dim colHeader As ColumnHeader
    Dim DB As Database
    Dim lstItem As ListItem
    Dim SQL As String
  
    ListView1.ListItems.Clear
    Set DB = CurrentDb()
    Set rs = DB.OpenRecordset("SELECT * FROM Ledger WHERE Names = '" & Forms![MainForm]![ListNames] & "'")
      
    Me!ListView1.View = lvwReport
   
     'Set up column headers (not using)
   ' With ListView1.ColumnHeaders
    
    '  .Add , , "Names", 2000
    '  .Add , , "Date", 1000
    '    .Add , , "Category", 2000
    '   .Add , , "Credits", 1000
    '    .Add , , "Debits", 1000
  '  End With
    rs.MoveFirst
  
    Do Until rs.EOF
 ' Add items and subitems to list control.
   
    Set lstItem = ListView1.ListItems.Add()
    lstItem.Text = Nz(Trim(rs!Names))
    lstItem.SubItems(1) = Format(Nz(Trim(rs!PymtDate)), "Medium Date")
    lstItem.SubItems(2) = Nz(Trim(rs!Category))
    lstItem.SubItems(3) = Format(Nz(Trim(rs!Credits), "0.00"), "Fixed")
    lstItem.SubItems(4) = Format(Nz(Trim(rs!Debits), "0.00"), "Fixed")
      
   rs.MoveNext
   
    Loop
     rs.Close
  Exit Sub
End Sub

Thanks

Zameer Abdulla

 
Upvote 0 Downvote
Hi!

I have a similar problem.
While app works fine its ok but when I enter breakpoint (on If Obj = True line) to see what is happening and continue to work on the next time it stops and displays error.
"Run-time error '-2147467259 (80004005)':
The database has been placed in a state by user 'Admin' on machine 'Magic' that prevents it from being opened or locked"

Can you help solving this problem?

Ps: My English is poor and I am sorry if there is some grammatical mistakes.




Public Function fnObjekat(Adresa As Integer)

Set rst = New ADODB.Recordset
Set con = New ADODB.Connection

Dim Obj As Boolean
Dim Response As Integer


con = CurrentProject.Connection

rst.Open "tblAdrese", con, adOpenStatic, adLockOptimistic, adCmdTableDirect

rst.Index = "Adresa_ID"
rst.Seek Adresa, adSeekFirstEQ
Obj = rst!objekat
rst.Close

If Obj = True Then
'Response = MsgBox("Jeste objekat", vbOKOnly, Bravo)

Else
Response = MsgBox("Nije objekat", vbCritical, Paznja)
Forms!frmVoznje_temp.txtBroj.Text = ""
Forms!frmVoznje_temp.txtBroj.SetFocus

End If

End Function
 
Upvote 0 Downvote
One more thing i have forget. If obj=false textbox "txtBroj" is not in focus.
 
Upvote 0 Downvote
try this.

If Obj[bold].Value[/bold] = True Then
'Response = MsgBox("Jeste objekat", vbOKOnly, Bravo)

Else
Response = MsgBox("Nije objekat", vbCritical, Paznja)
Forms!frmVoznje_temp.txtBroj.Text = ""
Forms!frmVoznje_temp.txtBroj.SetFocus

End If

or you have to use this portion in the AfterUpdate Event of the "Obj"
Sorry I can't understand words those are not english
HTH

Zameer Abdulla

 
Upvote 0 Downvote
Hi Zameer Abdulla

Thanks for answering so quickly but with your tip I got the errmsg:”Invalid qualifier”.
I have forgot to say that I think problem is that recordset is still open (break is displayed on this line:

“rst.Open "tblAdrese", con, adOpenStatic, adLockOptimistic, adCmdTableDirect”

and I can’t open it until it is closed but I don’t know why is that (I have rst.Close line in code). Or how to close it if I am making mistake.

For words that you don’t understand I apologies. I will leave them out next time.
 
Upvote 0 Downvote
Perhaps add some errorhandling.

at the top
[tt]on error goto myerr[/tt]

' you current code

[tt]myexit:
If (Not (rst Is Nothing)) Then
If (rst.State = adStateOpen) Then
rst.Close
End If
Set rst = Nothing
End If
set con = nothing
Exit Function
myerr:
msgbox err.description
resume myexit
end function[/tt]

This will check, and "turn of the lights on departure" both if an error occurs, and at the end of the function anyway. The set rst=nothing releases the object variable.

Couple of other things, if a .Seek doesn't find any record, eof is true, perhaps test for that?

[tt]if not rst.eof then
obj = rst!objekat
else
obj = "some other value... or..."
end if[/tt]

Also, at the end, your assigning "" to the .Text property of a form control. Using the .Text property, needs for the control to have focus, so perhaps reverse those two lines, or use the .Value property in stead.

See you're also a relative new member, Welcome to Tek-Tips. Here's a little faq on how to get the most out of the membership faq181-2886. There it's indicated that new topics often are better addressed in a new thread. If the above advise doesn't fix your situation, please consider that. Good Luck!

Roy-Vidar
 
Upvote 0 Downvote
Hi,
I think you are using a code that is used in VB. It will give you many confusions. Change your code to my example. I had same problem and worked on it for many hours finally cleared.


Try to add the following instead of
“rst.Open "tblAdrese", con, adOpenStatic, adLockOptimistic, adCmdTableDirect”
==================================
Dim DB As Database
Dim rs As DAO.Recordset



Set DB = CurrentDb()
Set rs = DB.OpenRecordset("tblAdrese")
=================================
HTH

Zameer Abdulla

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BFroeschlKC
  • Question
Publisher Automation from Access Frustration
Replies
3
Views
1K
dhookom
dhookom
Moss100
  • Locked
  • Question
Vba code I can call from a query to reformat text
Replies
8
Views
284
Moss100
Moss100
Moss100
  • Locked
  • Question
Remove everything including the comma from the last comma of a string. 1
Replies
10
Views
575
Andrzejek
Andrzejek
ziffer68
  • Locked
  • Question
Pass Through query to create an alias for DB2 member
Replies
0
Views
1K
ziffer68
ziffer68
Zyborg
  • Locked
  • Question
Using a variable as reference to a form
Replies
5
Views
310
Zyborg
Zyborg

Part and Inventory Search

Sponsor

Back
Top