Can’t find field (Access2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks, I think I will try that!

Reply | Quote

Thanks, I think I will try that!

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote