Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access location table (2003)

    I'm sure I'm missing something obvious. My customer has a variety of locations, so I created a table where all the locations are listed. A copy of my database will be sent to each location, and certain features will be enabled or disabled depending on the location. The administrator will check a Yes/No field in the location table to indicate which location currently applies. I cannot seem to figure out a simple way of ensuring that all check marks are unchecked when the current one is checked. I created a macro to run some SQL, but it pops a box up to confirm. I'd like to get rid of this confirmation box (at the very least), and I'd be open to suggestions of how to set this up cleanly. I'm a long-time programmer, but new to Access and frustrated when I know what I want to do but not how to do it. Here's the SQL command I'm using:
    UPDATE tblLocation SET tblLocation.CurrentPlant = False;
    Any help would be appreciated. You've always come through in the past.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access location table (2003)

    Does your table contain one record with a field for each of the locations, or do you have a record for each location? The process would be different depending on the design you have used. The multiple record approach is probably the simplest for several reasons - you simply run an update query for every location but the one that has been selected, and set the location to false. We actually use a similar approach with one of our clients, and have a single record table that contains the ID number for the default location.

    If on the other hand, you have a single record with fields for each unique location, you would have to resort to a VBA process and use either DAO or ADO to set each of the non-default fields in the record to false. Give us the details on the approach you are using, and we'll try to fill in some of the nitty-gritty details.
    Wendell

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access location table (2003)

    My table contains one record for each location. I can understand how to run an update query, but how do I hide that from the end-user who expects to see a form? I suppose I can create a form with a command button, but how do I hide the message box that appears saying "you are about to update "n" records"? I'd be interested to see what you're doing with your existing customer. I'm trying to make this look fairly polished.

    Thanks for your quick input.

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

    Re: Access location table (2003)

    You can set a form to be opened automatically when the database is opened, in Tools | Startup...
    You can run code when this form is opened, in the On Open or On Load event.
    You can execute SQL code in this event; if you use

    Dim strSQL As String
    strSQL = "UPDATE ..."
    CurrentDb.Execute strSQL

    there will be no "You are about to update ..." prompt.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access location table (2003)

    Hans has pretty well covered your questions, and your table design sounds right. The only real difference with our customer is that we set the default location at the time we install the back-end, and it's not something the customer would be likely to ever change. The possible challenge I see with your arrangement is that an administrator could possible check two of the Yes/No check boxes. Have you considered using the option group approach? That control can be used to record the ID number of the default location selected, but only allows one radio button to be selected. You could either store that selection in a one-record table, or simply use the value on the form. I believe you will need a command button or the like to run your query however.
    Wendell

  6. #6
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access location table (2003)

    Both suggestions are excellent. I'm sure I'll use something like that. My situation is that I'm passing the database off to my customer who will then take it and send it out to the various locations. (pretty scary that I have a "customer" with my Access skills....nonetheless..) I would like a way for him to go in and easily change the location once he has made a copy of the database. They're looking at acquisitions, so the number of locations will change. That's why I set the table up the way I did so it would be easy to maintain a list of locations. He will send out a front-end and a back-end to each location. The back-end, of course, needs the ability to set the location. There will be an "administrator" at each location - each with considerably less Access knowledge than I have! I'll play with the option group and see what I come up with.

    Thanks for your help, as always.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access location table (2003)

    I think you'd be better off setting a registry entry, rather than rely on a correct entry in a table. In your startup form, you'd check for the existence of the setting, if not there then ask for it. This way you never have to worry about that table being correctly checked.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access location table (2003)

    The solution that looks the best for my situation is the suggestion from Hans. When I try this, though, I get a runtime error 3061 "Too few parameters. Expected 1". Here's what my code looks like (more or less)
    Dim strSQLAddCheck
    strSQLAddCheck = "UPDATE tblLocation SET tblLocation.CurrentPlant = True WHERE tblLocation.LocationID =( [Forms]![frmLocation]![cboLocation])"
    Currentdb.execute strSQLAddCheck

    I actually figured I would run two versions of this. One to uncheck everything and once again to add a check to the appropriate record. What am I doing wrong?

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

    Re: Access location table (2003)

    CurrentDb.Execute is a DAO method; DAO doesn't know about Access forms and contols, since it operates at a lower level. Change the code to

    strSQLAddCheck = "UPDATE tblLocation SET tblLocation.CurrentPlant = True WHERE tblLocation.LocationID = " & Forms!frmLocation!cboLocation

    By placing the reference to the control outside the quotes, you turn it into a literal value in strSQLAddCheck.

  10. #10
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access location table (2003)

    YESS!!!

    That works perfectly! Exactly what I needed. Thanks again Hans! I owe you one.

Posting Permissions

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