Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Olympia, WA, Washington, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Design View problem (2002 sp 2 on windows xp sp1)

    Hi, I have a weird problem. I recently had computer problems and decided to re-install windows xp pro. I deleted c drive and started over. After installing all my programs everything seemed to be working just fine. Today I opened a database and attempted to view a form in design view and access crashed and sent a report to Microsoft. I have the same file on a laptop that had not been started for a couple weeks. I started the laptop (same version of Office and Windows) and everything works just fine. I then opened the file on the main computer from the laptop and there were no problems. I have copied the file from the laptop to the main computer and it crashes Access. Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    It would seem that there is something wrong with the installation of Access on the main computer. The problem is not caused by the database, since you can open it from the laptop.

    <UL><LI>What happens if you create a new database on the main computer, and then create a simple table, query, form, ... in that database?
    <LI>Have you tried Detect and Repair (in the Help menu)?[/list]

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Olympia, WA, Washington, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    Hans, thank you for the prompt reply. I had already tried a detect and repair. I just uninstalled the Access portion of office xp and then re-installed. Still no luck. I suppose I could remove office xp completely and start over. BTW the database is in Access 2000 format (I don't think that would matter). Other databases seem to work okay. My folders in the database in question are launched from the switchboard. All 4 are identical but refer to a different query to bring up different members of the group (there should be a way to do this with one form but I haven't been able to do it). The first form that I use most works fine but the others open okay but crash access when I try to view them in design view. As I said all is well on the laptop. WEIRD.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    If the four forms are identical except for the record source, you should be able to use one form by changing the Record Source property in code.

    But as an interim experiment you can try the following on a copy of the database: keep the form that works OK and delete the three forms that crash Access (I hope that you *can* delete them). Next, copy the remaining form three times, open the copies in design view and set the Record Source to one of the other queries. If this crashes Access, the problem would seem to be with the queries; if the forms work OK, you may be in luck...

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Olympia, WA, Washington, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    Hans, prior to reading your last post I uninstalled office xp and then re-installed. No luck. I then tried your suggestion and deleted one of the forms and copied the good form (this is the way I make the four anyway) and changed the record source. I also have a combo box so I can type in last name and find member records and I have to change that record source also. It WORKED! I found that I could open the forms in design view from the table/form list but not when they were open. I open the other two and clicked on the record source to look at them and then closed. It asked me to save changes and I did and now they work too. WEIRD. I did find a little macro that would open the same form from the switchboard and apply the appropriate filter to the records but it does not change the record source of the combo box so all members still show there. It is easier if only the members from the form record source show in the combo box. How would I do that in code? It would be nice to have controls on the main form to click so it would requery and bring up the the other filters. I need to use 2 fields to determine who is shown.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    Just as you can change the RecordSource of the form in code, you can also change the RowSource property of a combo box in code. You can set it to the name of a stored table or query, or to an SQL string that you construct in code. It is hard to say what you need without knowing the exact situation. Take a look at the Row Source property of the combo box, and see if you can adapt it in code. If you need more help, provide more details.

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Olympia, WA, Washington, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    Okay here we go. I presently have 4 queries and 4 forms. Each form refers to one of the queries but each form displays exactly the same info, just for different groups of people. In my queries I use 2 fields to determine who is shown. The first field is status and in the table holds numbers 1,2,3 or 4. These correspond to and option group on the form so only one value can be entered. The relate to a member, honorary member, candidate or petitioner. The second field is called Archived. A person is archived if he is no longer and active member for a number of reasons (death, he quit paying dues, suspended etc) the reason doesnt matter here. I therefore have forms that show status=1 archived=0 and so on. I also use one with no criteria entered for these fields and can view everyone who has ever been a member. When I modify a form I have to modify all of them (I use your method of copy and paste and then change record source). All of these forms are tabbed and contain a couple hundred controls. (I know I should divide them but this does work). I have a combo box in the header where I can type the last name and select who I want to see. The row source for this combo box comes from the same query that is used by the form. It would be much simpler if I could open one form (with default being current members only) and then click a control to requery and change the filter to view other groups.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    For illustration purposes, I have assumed that your data are in a table tblMembers. Instead of tblMembers, you can use the name of a query that returns all records.

    I also assume that you have created a combo box cbxSelect with 5 options:
    1 - Status = 1 and Archived = 0
    2 - Status = 2 and Archived = 0
    3 - Status = 3 and Archived = 0
    4 - Status = 4 and Archived = 0
    5 - All records

    Instead of a combo box, you could also use an option group to select the type of records you want to display.

    The combo box to search for a person is named cbxSearch.

    You will have to adapt the code below to your situation, of course. In particular, I don't know how many columns your search combo box has.

    This is the AfterUpdate event procedure for the cbxSelect combo box; it changes the record source of the form and the row source of the search combo box.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Sub cbxSelect_AfterUpdate()
    Dim strRecordSource As String
    Dim strRowSource As String
    Dim strWhere As String

    ' Record and row source for all records
    strRecordSource = "SELECT * FROM tblMembers"
    strRowSource = "SELECT MemberID, LastName FROM tblMembers"

    Select Case Me.[cbxSelect]
    Case 1 To 4 ' Status = 1..4, Archived = 0
    strWhere = " WHERE Status=" & Me.[cbxSelect] & " And Archived=0"
    strRecordSource = strRecordSource & strWhere
    strRowSource = strRowSource & strWhere
    Case 5 ' All records
    ' No need to add a where condition
    End Select

    Me.RecordSource = strRecordSource
    Me.[cbxSearch].RowSource = strRowSource
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  9. #9
    Star Lounger
    Join Date
    Feb 2001
    Location
    Olympia, WA, Washington, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    Hans, sorry but I can't get this to work. I have my form tied to a a query (Member Query) from which I removed all criteria so the form now shows everyone ( I know there should be no spaces but...). I am unsure how to create the combo box with the criteria you mention for qrySelect. Doesn't the form have to requery the query to change the data? I guess I am not as good at this as I thought :-) BTW my combo box to select the member to display has only one column that refers to an expression on the query call Lookupname which gives Lastname, Firstname, Middle, and suffix. I actually have three members with the same first middle and last names and can distinguish them only by going all the way to suffix which in this case are Jr. III and IV. I would like the combo box cbxSelect to show Active Members, Honorary Members, Candidates, Petitioner and All Members or something like that when the little triangle is selected so we know what we are selecting.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    To make cbxSelect show Active Members etc., create a small table with two fields: ID (Numeric, Long Integer, this is the Primary Key) and Description (Text, 20 characters).

    <table border=1><td>ID</td><td>Description</td><td align=right>1</td><td>Active Members</td><td align=right>2</td><td>Honorary Members</td><td align=right>3</td><td>Candidates</td><td align=right>4</td><td>Petitioner</td><td align=right>5</td><td>All Members</td></table>
    Set the row source of cbxSelect to the name of this table, set the number of columns to 2 and set the column widths to 0;1 to hide the ID column.

    Status and Archived should be fields of Member Query and of LookupName. If they aren't, add them to the query grid, for cbxSelect uses them to select the appropriate records.

    Here is a modified version of the AfterUpdate procedure for cbxSelect:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Sub cbxSelect_AfterUpdate()
    Dim strRecordSource As String
    Dim strRowSource As String
    Dim strWhere As String

    ' Record and row source for all records
    strRecordSource = "SELECT * FROM [Member Query]"
    strRowSource = "SELECT * FROM [LookupName]"

    Select Case Me.[cbxSelect]
    Case 1 To 4 ' Status = 1..4, Archived = 0
    strWhere = " WHERE Status=" & Me.[cbxSelect] & " And Archived=0"
    strRecordSource = strRecordSource & strWhere
    strRowSource = strRowSource & strWhere
    Case 5 ' All records
    ' No need to add a where condition
    End Select

    Me.RecordSource = strRecordSource
    Me.[cbxSearch].RowSource = strRowSource
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  11. #11
    Star Lounger
    Join Date
    Feb 2001
    Location
    Olympia, WA, Washington, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    Well, I still can't get this to work. Just for information Lookupname is a field in Member Query which combins lastname, firstname etc. It is not a querry of its own. Also, the line at the bottom Me.RecordSource = strRecordSource causes everything to halt with the message tha "you canceled the previous operation" If I take the last two lines out nothing happens when I click on a selection in cbxSelect.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    We could go round and round the mulberry bush. Maybe post a cutdown version of your DB.
    Are you in the midst of a dirty record? If so, either UNDO or SAVE the record prior to using the combobox.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    Is cbxSelect bound to a field in the record source? It should be unbound, i.e. its control source should be empty.

    I agree with Pat that it might save time to post a stripped down version of the database:
    <UL><LI>Make a copy of the database.
    <LI>Delete all tables, queries, forms etc. that are not relevant to the problem.
    <LI>Delete most records from the tables; leave only the bare minimum needed to demonstrate the quirky behavior you described.
    <LI>Remove or modify sensitive information.
    <LI>Compact and Repair the database.
    <LI>Create a zip file containing the database - it should be less than 100 K.
    <LI>Attach the zip file to a reply. Warning: if you preview a post, the attachment box gets cleared.[/list]

  14. #14
    Star Lounger
    Join Date
    Feb 2001
    Location
    Olympia, WA, Washington, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    I can only get it down to 353kb. I cannot send it this way. I could send it as an e-mail attachment if you would send me your e-mail. My e-mail is listed in my profile.
    Thanks

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design View problem (2002 sp 2 on windows xp sp1)

    Please excuse me for being persistent, but did you really throw away everything that is not needed to demonstrate the problem? As far as I can see, the stripped down version should contain two or three tables with only a few records, a couple of queries and one form. It seems unusual that this would be over 350 K after compacting and zipping. Zipping usually compresses Access databases by 75% or more...

    By attaching your database to a post, more Loungers will have a chance to have a look at it.

Page 1 of 2 12 LastLast

Posting Permissions

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