Multiple tables

[neileg]

Yes, that's what I meant. I presume you have six fields in the combo box, ID plus 5 address lines.

Did you not follow this part? You need all the data in the datasource for the code to work. Currently you only have one column in the combo.

 

[the bard]

I have amended as below, but the coding still stops on box "Address1". I created this by clicking on "Text Box" control and re-naming text label to "Address1" etc - is this correct?
SELECT [Addresses Query].[CoName], [Addresses Query].[Address1], [Addresses Query].[Address2], [Addresses Query].[Address3], [Addresses Query].[Address4], [Addresses Query].[Address5] FROM [Addresses Query];

 

[the bard]

I have found another difference; in the text box for Address1, in the Property Sheet it is shown as "Text 355". I changed the label attached to the box, but this is obviously not right.

 

[neileg]

Edit: You are changing the name in the combo box property sheet and not just the label, aren't you?

Did you not understand this comment?

If the control name is the same as a field name, this can cause problems. You will see that I prefix my text box names with txt so avoiding this problem.

And this one.

 

[the bard]

the bard

I have tried creating a combo box (CoName) in my original database (Sales). This is logical as this is where I would enter all customers' order details. The first line (CoName) appears ok in my Order form, but I still cannot get other address lines (Address1 etc). I am no longer getting error message in vba below. Using Text box I add extra lines to form to create address lines. I name ias Address1 etc in property sheet, but this does not alter label attached to text box, which I have to change manually. Could this be where the problem is occurring?

Private Sub CoName_AfterUpdate()
Me.txtAddress1 = Me.CoName.Column(2)
Me.txtAddress2 = Me.CoName.Column(3)
Me.txtAddress3 = Me.CoName.Column(4)
Me.txtAddress4 = Me.CoName.Column(5)
End Sub

 

[neileg]

The label is purely cosmetic and has absolutely nothing to do with the data or any functionality of the control it relates to.

I told you not to use the same name for the control as you use for the field, yet you are calling your combo CoName when this is the name of your field.

You have said that you have named the text box Address1 but the code uses the name txtAddress1.

Your query only pulls 5 fields. You are ignoring what I have said about having 6 fields.

Either you put the effort into understanding my advice and then interpret it, or you have to do exactly what I have advised and don't deviate from this.

 

[the bard]

My original Address table has "CoName" plus Address1-5 as does the Address query. I have also amended the VBA as below if I understand you correctly.

Private Sub CoName_AfterUpdate()
Me.txtAddress1 = Me.CoName.Column(2)
Me.txtAddress2 = Me.CoName.Column(3)
Me.txtAddress3 = Me.CoName.Column(4)
Me.txtAddress4 = Me.CoName.Column(5)
Me.txtAddress5 = Me.CoName.Column(6)
End Sub

 

[neileg]

Note that the column number is zero based, so the first column is 0, the second is 1 and so on.

Please read my last post properly about the names of your controls.

 

[the bard]

the bard

You really have lost me, I copied your original (working) code

Private Sub cboCoName_AfterUpdate()
Me.txtAdd1 = Me.cboCoName.Column(2)
Me.txtAdd2 = Me.cboCoName.Column(3)
Me.txtAdd3 = Me.cboCoName.Column(4)
Me.txtAdd4 = Me.cboCoName.Column(5)
End Sub

 

[neileg]

Sorry, I'm loosing the will to live...

There's nothing wrong with the code. It works as you have seen in my sample db. What you have to do is make sure that the code and your form are based on the same things. That means checking that the control names in the code match the names in the control properties sheet. txtAddress1 is not the same as Address1.

My code was based on the assumption that you had an ID field and 5 address fields. But you don't. You don't appear to have an ID field, you have a CoName field. So that means you need to adjust the code. Your first address field is the second field in your query so that means it is column 1.

 

[the bard]

the bard

I have been trying for hours, with no success. So I tried from scratch creating a new form, but hit another problem. On an order form two addresses are needed - the supplier's address and the customer's delivery address - but Access will not let you have two combo boxes with the same name on one form (both addresses will be in the same table).

When I originally tried, I had three address tables (SupplierAddress, DeliveryAddress and InvoiceAddress) and three combo boxes on the main data table (Sales). This then enabled me to enter all order data in one go in one table. This meant that all I had to enter later was the despatch date (VAT point) on the invoice. This is what I do at present in my DOS programme.

 

[neileg]

I have been trying for hours, with no success. So I tried from scratch creating a new form, but hit another problem. On an order form two addresses are needed - the supplier's address and the customer's delivery address - but Access will not let you have two combo boxes with the same name on one form (both addresses will be in the same table).

The name of the combo box it not an issue, you can call them Fred and George if you want. See the revised attachment.

I don't understand why you can't save your db as an earlier version so I can have a look at it. Every version of Access that I have used has had this facility.

 

[the bard]

the bard

"You cannot use this database in an earlier version, because it uses features that require the current file format"

 

[neileg]

"You cannot use this database in an earlier version, because it uses features that require the current file format"

Well that settles it then!

 

[the bard]

the bard

I have at the back of my mind it uses a different file system or something like that. I can upgrade to 2007but not vice-versa. I only bought 2007 because I thought it would be easier to learn!
I am obviously not 'with it' enough to sort, would it be easier if I sent you details of the nomencleture I use and perhaps you would be good enough to give me the exact coding and tell me, in simple words how to do it.

 

[neileg]

Yes it does use a different file system, but that's true of all of the Access versions.

Give me that names of the fields in your tables or queries that support the form. Give me the names of the controls in the property sheet (not the labels).

 

[the bard]

the bard

I have been fiddling around creating new forms and suddenly I have done it!!! The problem is I have got so confused that I am not sure how! I will come back to you.

 

[neileg]

You know, I was going to worry about this all weekend...

 

[the bard]

the bard

Don't get too excited, I am not sure how I did it - it did not involve vba - or whether I can do it again!

 

[the bard]

the bard

False alarm! It works when I create a new form based on just an Address table (it doesn't work on a query), but it will not work when I try to create a new form based on two tables (Sales and Addresses).