taking over values from another table automatically

[STarDavos]

Hi folks...Here I come again...



With my form I want to enter data in 'tblAufwand' containing the fields 'IDAufwand', 'valObjekt', 'Konto'
I have a ComboBox 'cboObjekt' where I can choose my object from 'tblObjekt' containigng: 'IDObjekt', 'txtObjekt', 'Konto' saving '[tblObjekt].[IDObjekt]' in '[tblAufwand].[valObjekt]'

Is this understandable so far (Excuse my poor english...)?

Now, how can I get it to autmatically take over the account number that is deposited in [tblObjekt].[Konto] into [tblAufwand].[Konto] as it is the same number for that particular object?

thanks in advance for your help

[June7]

Seems to me, Konto is duplicating data to multiple tables. Why do you want to do that?

Save IDObjekt into tblAufwand then build query that joins related tables to display all data in report output.

[STarDavos]

I probably dindn't explain this right....

I use this DB for booking payments to diffenrent accounts. I have several tables to pull standart data from with ComboBoxes (Object information, Account Information, Supplier Information, Machine Information, Staff Information etc...)
In my table 'tblBuchung' (Bookings) I enter data with a from: Date, Amount, Account# (cboKonto), Supplier (cboLieferant), and if it's for a certain machine, then I can select it in 'cboMaschinen' which is synchronized with 'cboKonto', so I can only choose between the machines that "belong" to the choosen account... , the same with different Objects (cboObjekt)... (I hope this is understandable... my english is not that great as you can tell...)

Now in my table 'tblAufwand' (expenses) I use a form with: Date, Amount Hours, Employée (cboStaff), Object (cboObjekt) and again Account#.... Now here the Account# always stays the same for the same Object, so I have a column in my Object table 'tblObjekt', with the Account #. I found out how to display that number in my form when I choose a certain object, but I can't get it to copy and save that value in my Account column in 'tblAufwand'

any suggestions?

[June7]

My point is you shouldn't - if I understand data structure correctly. But if you really must, then this requires code (macro or VBA). The real trick is figuring out what event to use. Possibly combobox AfterUpdate.

Me![Account#] = Me.cboObjekt.Column(1)

Strongly advise not to use punctuation/special characters in naming convention. Better would be AccountNum.

[STarDavos]

Again... my bad english... sorry...

My field with the accountnumber is actually called 'Konto'

So it should be

Me![Konto] = Me.cboObjekt.Column(3) since the Column with the accountnumber in my 'tblObjekt' is the third column there?

[June7]

Almost. Column index begins with 0 so third column is index 2.

[STarDavos]

aha.... thank you

This works.... thank you ever so much...