Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community Forums > Databases > Microsoft Access Development
Reload this Page

RowSource Problems

Discuss RowSource Problems in the Microsoft Access Development forum on Dev Articles.
RowSource Problems Microsoft Access Development forum to discuss problems and solutions with this popular DBMS. Use Access to build and modify database tables, or full-featured applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old June 4th, 2004, 10:03 AM
NoWhereMan NoWhereMan is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jun 2004
Posts: 4 NoWhereMan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
RowSource Problems
I'm using VBA to dynamically write an SQL query which then forms the query for a RowSource within a list box. The SQL is generated by the user choosing a couple of options via some combo boxes. These options are placed into an SQL statement which is then used to generate the contents of a list box.

Several times I have written the code and it seems to work fine for a short period but then suddenly stops working.

When it stops the RowSource in the list box just doesn't change anymore - it seems to freeze at a particular set of values. Up to the point of 'freezing' the whole thing has worked fine.

Can anyone suggest what might be going wrong - or what I need to check?

I know the SQL is OK and is being formed correctly since I use a debug MSgBox to see what is being created. But for some reason the list box just doesn't seem to pick up any changes being made to the SQL

All help so gratefully recieved - this is really causing me a headache.

Thanks, Neil
Reply With Quote
  #2  
Old June 4th, 2004, 12:05 PM
StealthKK StealthKK is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jun 2004
Posts: 9 StealthKK User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Need more
Can you post the code/statement?
Reply With Quote
  #3  
Old June 4th, 2004, 02:09 PM
NoWhereMan NoWhereMan is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jun 2004
Posts: 4 NoWhereMan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Here is the code I am using in the first form. I have left out the first part where I 'dim' the variables etc..
It will also be obvious that I am new to this game - but please don't be too critical - I am trying!
Code:
StaffName = Me.cboMathsStaff.Value
    
    If StaffName = "All" Then
        StaffName = "Like '*'"    
        Else: StaffName = " = '" & StaffName & "'"    
    End If

    Course = Me.cboCourse.Value
    
    If Course = "All" Then
       Course = "Like '*'"            
    Else: Course = " = '" & Course & "'"
   
    End If
    
    Group = Me.cboBlock.Value
    
    If Group = "All" Then
        Group = "Like '*'"
   Else: Group = " = '" & Group & "'"
    
    End If

    SearchCriteria = "MathsStaff " & StaffName & " AND Level " & Course & " AND Block " & Group & ""

    
    strSQL = "SELECT tblRecords.ID, tblRecords.FirstName, tblRecords.Surname " & _
    "FROM tblRecords " & _
    "WHERE tblRecords.MathsStaff " & StaffName & " AND tblRecords.Level " & Course & " " & _
    "AND tblRecords.Block " & Group & " " & _
    "ORDER BY tblRecords.Surname;"
            
    Form_frmRecords.lstStudents.RowSource = strSQL

    
    DoCmd.OpenForm "frmRecords", , , SearchCriteria, acFormEdit

Thanks again,

Neil
Reply With Quote
  #4  
Old June 4th, 2004, 02:21 PM
NoWhereMan NoWhereMan is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jun 2004
Posts: 4 NoWhereMan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
....does this help?
I have found one thing that will always cause the problem.

When I shut down the application it asks if I want to save changes made to the form? Regardless of how I reply the two forms don't work 'correctly' when I run Access again.

However if I play around with the code - eg insert REM marks against the code, then delete them again, it all starts working again!

Does this make any sense?

Neil
Reply With Quote
  #5  
Old June 5th, 2004, 05:51 AM
NoWhereMan NoWhereMan is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jun 2004
Posts: 4 NoWhereMan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Smile Problem Resolved -well nearly
Thanks to anyone who gave this problem brain time. I have now managed to resolve the issue by approaching it from a different angle.

Instead of trying to sqiurt the data directly into the RowSource I open a query using SQL which allows me to put in the variables I need. Then I point the list box to the query.

This works no matter what I do to the data.

Only one thing though - when I open the form with the target list box it contains old data ie data from the previous time the query was run. If I want to view the records from the latest run of the query I have to refresh the form, which I do by pressing a refresh button. Is there a way to auto refresh the list?

I know I'm not very experienced in these matters but I do find it fun - and slowly I'm learning, so all help gratefully appreciated.

Thanks,

Neil
Reply With Quote
  #6  
Old June 13th, 2004, 01:40 AM
smercer smercer is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jun 2004
Posts: 11 smercer User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
use the "RepaintObject" command at the end of the macro if you are using a macro. It has been a while since I used this option. after the "RepaintObject" is selected, at the bottom of the screen you will find a text box "Object type". choose "Form" for this box and for "Object Name" choose the form you wish to refresh

Good luck
Reply With Quote
Reply

Viewing: Dev Articles Community Forums > Databases > Microsoft Access Development > RowSource Problems

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway. 
Stay green...Green IT