Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Switch Between List Boxes (Access 2000)

    I having problems with the attached app. Basically I have three list boxes, pallets, boxes and samples. On a pallet there are boxes and in the boxes there are samples. When the form loads it selects the last sample in the last box of the last pallet. I'm having problems when I delete or create new pallets and boxes, the list boxes will not refresh. I have tried to requery on the click events but nothing. So I would like

    When a new pallet is created it clears the boxes and samples.

    When a new box is created it clears the samples.

    When a box or pallet is deleted it goes to the above box or pallet and refresh the other two list boxes.

    How can I do this? Do I create one function that calls from each event?

    Thanks in advance.

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

    Re: Switch Between List Boxes (Access 2000)

    You posted two questions earlier today (<post:=590,035>post 590,035</post:> and <post:=590,057>post 590,057</post:>). You haven't acknowledged the replies in any way yet...

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switch Between List Boxes (Access 2000)

    Hi Hans,

    Sorry for not repling. Both of your replies fixed the problems I was having.

    Regards,

    Deni

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

    Re: Switch Between List Boxes (Access 2000)

    Each of the list boxes displays potentially non-unique information, for the Date field in tblPallets does not have a unique index, and neither has the BoxNumber field in tblBoxes or the SampleNumber field in tblSamplesNew. You can see this for example if you select 08/24/2006 in the Pallets list box and E2 in the Boxes list box. You'll see the same Sample thrice. What is supposed to happen if the user clicks Delete Sample?

    Before solving your question about updating the list boxes, you should re-think the data design carefully. What should be unique and what shouldn't? If, for example, Date should be unique in tblPallets, why not make it the primary key? And if it doesn't need to be unique, a list box displaying the Date field only will cause problems, because the date doesn't identify a record uniquely. This is a fundamental problem with the database as it is now.

    Some other remarks:
    <UL><LI>You should turn off "Track name AutoCorrect info" in the General tab of Tools | Options... in each of your databases. Name AutoCorrect causes all kinds of problems.
    <LI>You should set Subdatasheet Name to [None] for each table in each of your databases. Having it set to the default [Auto] has a negative impact on performance.
    <LI>You should turn on "Enforce referential integrity" for the relationships between the tables. Before you do so, you should remove records from tblBoxes with PalletIDs that don't exist in tblPallets, and records from tblSamplesNew with BoxIDs that don't exist in tblBoxes.
    <LI>You should turn on "Require variable declaration" in Tools | Options... in the Visual Basic Editor, and insert a line Option Explicit at the top of all existing modules. This will force you to declare all variables explicitly.
    <LI>You have declarations of the form

    Dim a, b As String

    In the above declaration, only b is declared as a string, while a is not declared as a specific type, hence it becomes a Variant (the default type). If you want both to be Strings, you should use

    Dim a As String, b As String

    or (my preference) declare each on a separate line:

    Dim a As String
    Dim b As String

    <LI>You used Format(..., "dd/mm/yyyy") in your code. It should be Format(..., "mm/dd/yyyy") because VBA and SQL are strictly US-orientated.
    <LI>Do you have a specific reason for using GUIDs as IDs? If you need them, why not use an AutoNumber field with Field Size set to Replication ID? Access will then automatically create GUIDs for you.[/list]HTH

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switch Between List Boxes (Access 2000)

    Hans,

    Thank you for all your suggestions, I have implemented majority of them. I didn't want to make the date field in tblPallets unique incase we wanted to edit the date or if there was two pallets with the same date. I tryed to used autotext but ran into problems so I went with the GUIDs. Isn't each record unique since it has a GUID attached with it. I haven't enforced referential integrity as I haven't decided if I will delete rows or just mark them as delete.

    I hope we can solve the updating the list boxes without changing the structure too much.

    Thanks for the help.

    Deni

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

    Re: Switch Between List Boxes (Access 2000)

    See if you can adapt the attached version to your needs.

    The list boxes now have two columns. The first one, which is hidden, corresponds to the ID field in the table. This makes is MUCH easier to refer to a record: the value of the list box is the ID, so you don't need to open a recordset to retrieve the ID.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switch Between List Boxes (Access 2000)

    Thanks Hans, you're awsome.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switch Between List Boxes (Access 2000)

    Hi Hans,

    I'm almost done. Last question I hope. How do I sort the listboxes.

    Thanks,

    Deni

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

    Re: Switch Between List Boxes (Access 2000)

    By setting the Row Source of the list boxes to an SQL statement that includes an ORDER BY clause. You must do this in the properties of the list boxes and in the code that changes the row source. See attached verison.

  10. #10
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switch Between List Boxes (Access 2000)

    Again, thank you.

    I wish I could attach final version for comments but it is over 100k zipped up.

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

    Re: Switch Between List Boxes (Access 2000)

    Have you compacted the database before zipping it (Tools | Database Utilities | Compact and Repair)? Also see <post#=401925>post 401925</post#>.

  12. #12
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switch Between List Boxes (Access 2000)

    I compacted it, saved it in a previous version and zipped it up.

    Thanks again.

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

    Re: Switch Between List Boxes (Access 2000)

    I haven't tested everything, but it looks good. Here are some minor points.
    <UL><LI>Set the Allow Design Changes property of the forms to Design view only (it's the last property in the Other tab of the Properties window). End users shouldn't see the Properties window while working with the forms.
    <LI>In Tools | Startup..., clear the check box Display Database Window. End users shouldn't need to see the database window. You can press F11 to open it, unless you clear the check box Use Access Special Keys. As you undoubtedly know, you can bypass the startup options by holding down the Shift key while opening the database.
    <LI>I am a stickler for consistent indentation of VBA code. It makes reading and maintaining the code much easier. Most of your code is fine in this respect, except for the formfield module. There are utilities that will indent existing code for you, for example Smart Indenter; it's free and customizable.[/list]Good luck with further development!

Posting Permissions

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