Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Can't find field (Access2000)

    I'm trying to build code that fills an address into an 'Orders' form from an 'Address' table. I keep getting the message "Microsoft Access can't find the field [Address] referred to in your expression. This is the code I have below.

    Private Sub CustomerID_AfterUpdate()
    ' Update ShipTo controls based on value selected in CustomerID combo box.
    Me!ShipName = Me![CustomerID].Column(1)
    Me!ShipAddress = Me!Address
    Me!ShipCity = Me!City
    Me!ShipState = Me!State
    Me!ShipPostalCode = Me!PostalCode
    Me!ShipCountry = Me!Country

    End Sub

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Can't find field (Access2000)

    Usually this means that there is a typing mistake somewhere, so that the field name you use in code is slightly different from the actual field name.

    Another thought. Do you have a field Address in a table also called Address? It is best to avoid using the same name for different things. Try renaming one or the other.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can't find field (Access2000)

    Where is Me!Address coming from? You said you wanted to fill in the address from an Address table. Actually, you would be better off inserting a key to the Address value instead of copying the address info over.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field (Access2000)

    I'm new to programming, so I hope you have patience with me. I'm not sure what you mean by inserting a key to the Address value. I was gone from the office over the weekend and thought of another trick I'm going to try on the "Orders" Form. In our office setting, we have four different address tables. Each table has it's own unique addresses, for example we send literature to prisons, and overseas and both of those have different address needs. For our office we need just one "Orders" form. How can I pull CustomerID's from 4 different tables into one Order's table? Any ideas? I'm trying to learn, so if this is too elementary, don't even worry about it.

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

    Re: Can't find field (Access2000)

    It's probably not a very good idea to have four different address tables. Unless the requirements are really completely different for each, it would be more efficient to have a single address table with an AddressType field that identifies what kind of address (prison, ...) the record represents.

    Instead of copying all address fields into your orders table, just set the ID for the address (CustomerID?). You can then retrieve the address details from the address table in a query: create a query based on the orders table and the address table, linked on the ID field. You can add the address detail fields from the address table to the query design grid.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field (Access2000)

    Thanks, I think I will try that!

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field (Access2000)

    I know I have resurrected an old post, but I wanted some ideas on how I should proceed. I have included an empty database with the tables of the different kinds of addresses I need. I really need the structure they are in. How can I use 1 order form for all of them?
    Attached Files Attached Files

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

    Re: Can't find field (Access2000)

    See the attached modified database. I created four queries to serve as row source for a combo box on the form.

    The form has two combo boxes to select a customer:
    cboType lets the user select an address table; it sets the row source of the second combo box to the appropriate query.
    cboCustomerID lets the user select a customer from the address table selected in cboType.
    There is a VBA procedure that gets called from both the After Update event of the combo box (to handle selecting another table) and from the On Current event of the form (to handle moving from record to record.)

    Notes:
    - The ValidationRule for the Bible Request Form field in the Inmates table was wrong way round so that it didn't accept any input. I corrected it.
    - My modifications are only an example; you will have to adapt and expand them for your purposes.
    Attached Files Attached Files

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field (Access2000)

    Thanks a lot for that suggestion HansV, and thanks for the correction on the Bible Request Form. I discovered yesterday that what I had in there didn't work. Now, I am planning on building my form similar to how Northwind has it in their sample database. Is there someway that I can make the different addresses work on the one form using the example that HansV gave in the last post? I am still in the beginning stages of this database so the form isn't complete with the subform for products, etc. Appreciate any help or suggestions. I don't know VBA very well, so I may have a lot of questions.
    Attached Files Attached Files

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

    Re: Can't find field (Access2000)

    I have attached a demo with an incomplete form. It uses a tab control without visible tabs. Each tab page contains a subform bound to one of the four address tables. The subforms are linked to the main form by the customer combo box. The After Update code for the customer type combo box sets the active tab page.
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field (Access2000)

    Thanks a lot HansV. That's what I had in mind. Appreciate your help!

Posting Permissions

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