Multiple tables

[neileg]

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

So on your form have the combo box set as line 5, with the ID column and columns 3 to 6 set to zero width so they are invisible. Place 4 text boxes under the combo and in the datasource put =Me.MyComboBoxName.Column(X) where X is the number of the relevant column in the combo. Note that the column number is zero based, so the first column is 0, the second is 1 and so on.

 

[the bard]

the bard

Sorry for delay in replying - been decorating. I do not know if I don't understand, but cannot get this to work at all.

 

[the bard]

the bard

Alternatively, if I enter each address on just one line with comma separators, is there any way (Macro etc) that @, (return) ?

 

[neileg]

A sample might be easier! I've used the After Update event of the combo to set the text box values.

 

[the bard]

Sorry, you have lost me!

 

[the bard]

the bard

Sorry, attachment did not show first attempt. Yes, that is exactly what I want, but I cannot see how it is different to what I have done. I have created query on addresses and seem to have done the same as you. Apologies for being so thick!!!

 

[neileg]

Have you looked at the VBA attached to the after update event of the combo?

 

[the bard]

the bard

Not doing very well. As you may have guessed, I am very much a beginner! I mastered setting up tables etc fairly easily although being used to DTP programmes I found layout very 'clunky'. I have not got as far as VBA, as I will not need that until I get to the invoice calculation part. I am not sure how to achieve this code also, how do you get the unbound lines for the other address lines?

 

[neileg]

The form I provided uses VBA, though only a few lines. Have you looked at the 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

 

[the bard]

What is cboCo ? Also, how do you get unbound boxes for other address lines? I have cut and pasted vba code but I still cannot get even near to your sample.

 

[neileg]

cboCoName is the name of the combo box control. I use the naming convention that prefixes object names with an indication of the type of object. So cbo means combo box.

Me is the shorthand way of refering to the current form.

All text boxes are unbound when you insert them. If you have used the form wizard, it only creates controls for the fields in the underlying dataset so these are always bound.

Why don't you post your database?

 

[the bard]

the bard

I have been working very hard at this and really thought I had 'cracked' it. I have got Address1 etc labels but vba is stopping at cboCoName. I am sure it is something very easy. I use Access 2007. I hope attachment is ok, I am working on 'order'. Many thanks.

 

[neileg]

I don't do A2007 I'm afraid. If you can save it as A2003 or earlier, that's OK.

No attachment by the way.

Do you have a combo box called cboCoName, or is it called something different?

 

[the bard]

the bard

2007 will upgrade from earlier versions, but will not 'downgrade' (although it says it will). I have named combo box as shown, but it keeps reverting to combo 343, even though this does not exist now.

 

[the bard]

the bard

Will try to attach again. If it fails, would you be prepared to let me have your email address?

Zip file too big

 

[neileg]

Our firewall wouldn't let a zip file in. If it's in A2007, I couldn't even open it!

If you're stuck with combo 343 then just change the code to match.

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

 

[the bard]

the bard

I think I might be making some progress! I have noticed that, several times, alterations made are not saved. Normally I have to delete and re-make. I have altered code to accomodate programme's name for combo box and run, but it stops at "address1". I am just using text box tool and naming it as "Address1" etc, is this correct? You might be able to open - Microsoft were adamant that I couldn't update from 97 to 2007, but no problem.

 

[neileg]

No, I can't open A2007 files.

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.

Post back the VBA code that you have in your form.

 

[the bard]

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

Programme insists on calling it Combo349 so I altered accordingly. I created it on 'Order form' using tool on ribbon. I use text box tool and re-named "address1" etc.

 

[the bard]

the bard

I have found the difference, but do not know how to correct. My Property Sheet, Row Source against the combo box reads as follows
SELECT [Addresses Query].[CoName] FROM [Addresses Query];