CODEWITHSUNDEEP
formsvbams-accessexpression
bomba
bomba

Reputation: 1

How to store DLookup from [Form] to Table Field in MsAccess 2013?

I have two MS Access tables: IdMhs and T_UKT. I want to automatically fill in [IdMhs].[SPP] from some criteria in [T_UKT].[UKT]. I cannot put it in query relationship, because the row cannot be edited on a form.

So I make a form and I get the number SPP (a textbox on form) automatically by using and it works fine:

=DLookUp("SPP";"T_UKT";"UKT = " & [Form].[UKT])

The problem is: the lookup value just shows on the form, and the results are not filled in the field in table ([IdMhs].[SPP]).

Any idea what an expression I should write to fill it in IdMhs table automatically?

Upvotes: 0

Views: 1200

Answers (1)

June7
June7

Reputation: 21370

Need macro or VBA code to save value into table. If form is bound to IdMhs table, simply like:

Me!SPP = Me.tbxSPP

The real trick is figuring out what event to put the code into. You have a control (possibly combobox) where the UKT value is selected? Use its AfterUpdate event.

Why do you need to save the SPP value and not just retrieve it in a query that joins tables?

What is nature of table relationship? Is T_UKT a lookup table? If so should be able to include it in form RecordSource and then the SPP value would be available for display in textbox and DLookup not needed. Domain aggregate functions can perform slowly on forms and reports and should be a last resort option. The same code would be used to save the value. The query join should be 'show all records from IdMhs and only those from T_UKT that match'. Set textbox bound to T_UKT SPP field as Locked Yes, TabStop No.

Upvotes: 1

Related Questions