Results 1 to 15 of 15
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: screenupdating property

    I would like to freeze the form when it opens up. Execute the filter for the form. if a record is found form opens up if not form never appears. I'm thinking to apply screenupdating property on the form but I'm not sure what object variable I would need to declare it. I'm using Access 2000.

    Thanks!

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: screenupdating property

    Originally posted by alicejwz
    I would like to freeze the form when it opens up. Execute the filter for the form. if a record is found form opens up if not form never appears. I'm thinking to apply screenupdating property on the form but I'm not sure what object variable I would need to declare it. I'm using Access 2000.

    Thanks!
    I would just use the RecordsetClone property in the Open event for the form... Something like...

    Me.Visible = False 'or set the form property in design mode

    If Me.RecordsetClone.RecordCount > 0 then
    Me.Visible = True
    Else
    MsgBox "No data... Closing form... "
    Docmd.Close acForm "FormName"
    End if

  3. #3
    Join Date
    Dec 2003
    Posts
    172
    Here's a slight variation...

    Just cancel the the Form_Open event. First, check the RecordsetClone. If there are no records, then set Cancel=True. This kills the form before it opens. A little less work than opening it, hiding it, and closing it.

    Option Explicit
    Private Sub Form_Open(Cancel As Integer)

    Dim rstTemp As DAO.Recordset

    Set rstTemp = Me.RecordsetClone
    If rstTemp.RecordCount = 0 Then
    MsgBox "No records to view."
    Cancel = True
    End If
    Set rstTemp = Nothing

    End Sub

    Personal Tip:

    Don't even bother opening the form before you filter it. Build your data first into a table. You can use an action query to do this. Then check this table to see if there are any records there. If there aren't any, you don't have to open the form ever.

    As far as filters go, try building a SQL statement instead that limits the records to only those records satisfying your search criteria. Filters are okay for some purposes but they have quirks and limitations and can be slow. Also you have to work a little harder to prevent the user from removing the filter and unmasking the entire recordset.

    Let me know if you have any additional questions.

    Joe G

  4. #4
    Join Date
    May 2002
    Posts
    395

    screenUpdating property

    Originally posted by JoeG
    Here's a slight variation...

    Just cancel the the Form_Open event. First, check the RecordsetClone. If there are no records, then set Cancel=True. This kills the form before it opens. A little less work than opening it, hiding it, and closing it.

    Option Explicit
    Private Sub Form_Open(Cancel As Integer)

    Dim rstTemp As DAO.Recordset

    Set rstTemp = Me.RecordsetClone
    If rstTemp.RecordCount = 0 Then
    MsgBox "No records to view."
    Cancel = True
    End If
    Set rstTemp = Nothing

    End Sub

    Personal Tip:

    Don't even bother opening the form before you filter it. Build your data first into a table. You can use an action query to do this. Then check this table to see if there are any records there. If there aren't any, you don't have to open the form ever.

    As far as filters go, try building a SQL statement instead that limits the records to only those records satisfying your search criteria. Filters are okay for some purposes but they have quirks and limitations and can be slow. Also you have to work a little harder to prevent the user from removing the filter and unmasking the entire recordset.

    Let me know if you have any additional questions.

    Joe G
    Thanks for all the good advice from the previous post.
    I would like to find out how I can stop the screen from scrolling to the found record in the subform. I believe screenupdating property would be the solution but I am not sure the syntax. I using Access 2000 and SQL Server as backend.

    This is my code:
    sub btn_click()
    Dim f As Form, formname As String
    Dim rst As New ADODB.Recordset
    Dim strmark As String
    Dim next_work_ord_num As String, line_num As String

    strmark = ""
    'formname = "shipping_sched_list"

    Forms!edit_shipping_sched!shipping_sched_list_subf orm.SetFocus
    Forms!edit_shipping_sched!shipping_sched_list_subf orm!work_ord_num.SetFocus


    Set f = Form_shipping_sched_list
    Set rst = f.Recordset

    next_work_ord_num = "329560-01"
    'next_work_ord_num = "330573-00"
    'line_num = "002"
    rst.MoveFirst


    Do While Not rst.EOF

    If rst(1) = next_work_ord_num Then 'And rst(2) = line_num Then

    rstmark = rst.Bookmark
    Exit Do
    Else
    rst.MoveNext
    End If
    Loop

    Exit Sub
    End Sub


    Thanks!

  5. #5
    Join Date
    Dec 2003
    Posts
    172
    Alice,

    Let's see if I understand. When the subform opens it scrolls to the first record? The subform is usually linked to the parent form so the subform would display any related records.

    For example:

    The parent form for customer John Smith customer id 3004 opens.
    Then the subform for John Smith shows his orders:
    order id 12
    order id 14
    order id 25

    Are you saying you want to stop the cursor from going to order 12?

    One thing you can do is to not set the recordsource property of the subform until you deem it appropriate. One of the tricks I do is to create a query that points to no records at all and I set that query to the subform so it can't scroll to anything. Once I want the user to go there, I set the subform property to the SQL code or query desired to display the scrollable records.

    You can also disable the subform (or even make individual controls on the subform datasheet disabled). For example if your subform has first name and last name and you disable first name, the cursor will not stop at first name.

    Screen updating does prevent the screen from updating to show the latest information. Guess I'm just confused as to what you are trying to prevent. I'm having trouble visualizing what is going on - I don't see anything particularly troubling about your code or description but maybe I'm missing it entirely.

    Thanks again.

    Joe G

  6. #6
    Join Date
    May 2002
    Posts
    395

    screenupdating property

    Originally posted by JoeG
    Alice,

    Let's see if I understand. When the subform opens it scrolls to the first record? The subform is usually linked to the parent form so the subform would display any related records.

    For example:

    The parent form for customer John Smith customer id 3004 opens.
    Then the subform for John Smith shows his orders:
    order id 12
    order id 14
    order id 25

    Are you saying you want to stop the cursor from going to order 12?

    One thing you can do is to not set the recordsource property of the subform until you deem it appropriate. One of the tricks I do is to create a query that points to no records at all and I set that query to the subform so it can't scroll to anything. Once I want the user to go there, I set the subform property to the SQL code or query desired to display the scrollable records.

    You can also disable the subform (or even make individual controls on the subform datasheet disabled). For example if your subform has first name and last name and you disable first name, the cursor will not stop at first name.

    Screen updating does prevent the screen from updating to show the latest information. Guess I'm just confused as to what you are trying to prevent. I'm having trouble visualizing what is going on - I don't see anything particularly troubling about your code or description but maybe I'm missing it entirely.

    Thanks again.

    Joe G

    Hello Joe,

    Thanks for your quick reply.

    What I am trying to automate a search to find a variable(pass in) match to a field in the subform. Once the record is found in the subform I used the bookmark property to make that the current record in the subform. The search works and the subform displays the found record as the current record. But the problem is while it is searching for the matching record the screen shows the subform moving through the recordset until it finds the record. How can I get the screen of the subform just show the record found without scrolling to it.

    In the code first I focus on the subform, set obj var(rst) pointing to the subform's recordset. bookmark the location of the found record. I think if I can just turnoff the screen updating before the search is done and turn it back on when it is done.

    To use the screenupdating property I need an object variable for this property. The syntax is object.screenupdateing = false. The example I read Spreadsheet1screenupdateing = false in Access help. I am developing in Access 2000 referencing ADO library. I tried to declare object as Access but it does not work and there isn't database object.
    Any idea or suggestions. Thanks!

  7. #7
    Join Date
    Dec 2003
    Posts
    172
    Alice,

    Sounds like the loop you are cycling through is visually displaying each record as you scroll through.

    Have you considered using DoCmd.GoToRecord to go to the record instead?

    Alternatively, you could create a recordset based on the recordsetclone of the form and then use the find method. You then test to see if the find was successful and set the bookmark if found. It should take you immediately to that record without that scrolling you are observsing.

    As far as the screen updating property - the usual object for that is a window object or application object (ie Access). If screen updating doesn't work, try making the subform itself invisible (visible=false) and put the visible=true code after the looping code you have that reaches the proper row.

    (Of course set focus won't work at this point.) As a rule, never try to mimic anything in VB that users do manually with the keyboard or clicking of the mouse. For example, if you were in Excel and wanted to bold a set of cells, you use the range property to select the cells and then apply bold formatting. A lot of people will, instead, attempt to use the select method to visually highlight the cells first and then bold them. I think that is what may be happening with your code (but I may be wrong).

    From personal experience I generally have never used the screenupdating property in Access but have used it extensively in other Office products like MS Word. It is possible the command is shown but is not exposed in the Access object library.

    You may also want to look at some of the articles out on the web for Windows API tricks and tips. With the proper calls to Windows DLL functions, you can do almost anything with Access windows and forms (such as moving the form around, centering it, removing the title bar/caption, etc.)

    I recently had a question on this forum on how to change the gray background that comes with Access' MDI main window. I found a routine that actually lets you change the color or set a bitmap image on it (without having to go to the control panel and change it from there - which of course will affect all programs - not just Access).


    Another side effect of scrolling manually through each record is that as your SQL Server record count grows larger the loop will take longer to execute.

    One thing a lot of folks do is they write code to hand-write the SQL statement on the fly to go right to a particular record or group of records. That way the only row(s) that display in the subform are they ones you want the user to go to.

    Am I getting closer to helping you? Let me know.

    BTW: Try this link:

    http://www.google.com/microsoft

    It contains the entire index of Microsoft related knowledge base articles, tech support forums, and outside articles on all of the Access and Office products.

    Joe G
    Last edited by JoeG; 01-30-04 at 14:43.

  8. #8
    Join Date
    May 2002
    Posts
    395

    find method and combination

    Originally posted by JoeG
    Alice,

    Sounds like the loop you are cycling through is visually displaying each record as you scroll through.

    Have you considered using DoCmd.GoToRecord to go to the record instead?

    Alternatively, you could create a recordset based on the recordsetclone of the form and then use the find method. You then test to see if the find was successful and set the bookmark if found. It should take you immediately to that record without that scrolling you are observsing.

    As far as the screen updating property - the usual object for that is a window object or application object (ie Access). If screen updating doesn't work, try making the subform itself invisible (visible=false) and put the visible=true code after the looping code you have that reaches the proper row.

    (Of course set focus won't work at this point.) As a rule, never try to mimic anything in VB that users do manually with the keyboard or clicking of the mouse. For example, if you were in Excel and wanted to bold a set of cells, you use the range property to select the cells and then apply bold formatting. A lot of people will, instead, attempt to use the select method to visually highlight the cells first and then bold them. I think that is what may be happening with your code (but I may be wrong).

    From personal experience I generally have never used the screenupdating property in Access but have used it extensively in other Office products like MS Word. It is possible the command is shown but is not exposed in the Access object library.

    You may also want to look at some of the articles out on the web for Windows API tricks and tips. With the proper calls to Windows DLL functions, you can do almost anything with Access windows and forms (such as moving the form around, centering it, removing the title bar/caption, etc.)

    I recently had a question on this forum on how to change the gray background that comes with Access' MDI main window. I found a routine that actually lets you change the color or set a bitmap image on it (without having to go to the control panel and change it from there - which of course will affect all programs - not just Access).


    Another side effect of scrolling manually through each record is that as your SQL Server record count grows larger the loop will take longer to execute.

    One thing a lot of folks do is they write code to hand-write the SQL statement on the fly to go right to a particular record or group of records. That way the only row(s) that display in the subform are they ones you want the user to go to.

    Am I getting closer to helping you? Let me know.

    BTW: Try this link:

    http://www.google.com/microsoft

    It contains the entire index of Microsoft related knowledge base articles, tech support forums, and outside articles on all of the Access and Office products.

    Joe G
    I have tried what you suggested for more than a day and could not get working. Used Find method, bookmark it and used DoCmd.GoToRecord

    Have you considered using DoCmd.GoToRecord to go to the record instead?

    Alternatively, you could create a recordset based on the recordsetclone of the form and then use the find method. You then test to see if the find was successful and set the bookmark if found. It should take you immediately to that record without that scrolling you are observsing.


    The bookmark doesn't seem like it'll work with find.
    Is any way you can get me a sample code?

    Thanks!

  9. #9
    Join Date
    Dec 2003
    Posts
    172
    I can upload a sample but my recommendation would be to upload the part of your database in question here so that I can see how the form and subform connect to the table so that I can duplicate the problem you are experiencing.

    Of course I wouldn't have the SQL connection but I could mock up an Access or ODBC connection to another table in its place.

    I've created a sample for you and attached it.

    Joe G
    Attached Files Attached Files

  10. #10
    Join Date
    May 2002
    Posts
    395
    Originally posted by JoeG
    I can upload a sample but my recommendation would be to upload the part of your database in question here so that I can see how the form and subform connect to the table so that I can duplicate the problem you are experiencing.

    Of course I wouldn't have the SQL connection but I could mock up an Access or ODBC connection to another table in its place.

    I've created a sample for you and attached it.

    Joe G
    Thanks very much!
    I'll see I can modify my code to your sample. Hopefully I wont need to send my part of the database to you .

  11. #11
    Join Date
    Dec 2003
    Posts
    172
    good luck. let me know how it goes.

    joeg

  12. #12
    Join Date
    May 2002
    Posts
    395
    Originally posted by JoeG
    good luck. let me know how it goes.

    joeg

    Yes!! That is the code I need. It is going exactly what I wanted.
    Thank you so Very Very MUCH!!

  13. #13
    Join Date
    Dec 2003
    Posts
    172
    Glad to help

  14. #14
    Join Date
    May 2002
    Posts
    395

    syntax error in Find method

    Originally posted by JoeG
    Glad to help
    sorry to bother you again. You are better in code especially the syntax.
    I need to add another field to match in the criteria in Find method.
    this one is working : rst.Find "work_ord_num = " & next_work_ord_num

    I tried rst.Find "work_ord_num = " & next_work_ord_num &" and work_ord_line_num = " & line_num &";"
    but it is not working.

    Dim rst As ADODB.Recordset
    Dim f As Form, formname As String
    Dim next_work_ord_num As String, line_num As String

    Set f = Form_shipping_sched_list
    Set rst = f.RecordsetClone

    next_work_ord_num = "329560-01"
    'next_work_ord_num = "330573-00"
    line_num = "002"

    rst.Find "work_ord_num = " & next_work_ord_num
    Form_shipping_sched_list.Form.Bookmark = rst.Bookmark
    end sub

  15. #15
    Join Date
    Dec 2003
    Posts
    172
    i think ADO find only allows one criteria - let me check on that... yes here it is:

    http://support.microsoft.com/default...NoWebContent=1

    you might want to try the ADO filter instead which allows for multiple criteria (or use DAO Find or Seek)

    or build the SQL statement you are passing to the recordset instead and in the SQL statement use the WHERE clause such as:

    & " WHERE tblMyTable.CustomerId = " & Cstr(txtField1) & " AND tblMyTable.CustomerId = " & Cstr(txtField2)

    (My example didn't show the whole SQL string - just the WHERE clause part of it.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •