Insert a field value using VBA when adding record using a data entry form

[gangu]

Hi all,
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
**********************************************************
Public Function GetTargetType() As Variant

GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]")

End Function

**********************************************************
Which works fine when I test in the immediate window.

Then I have this form event. This however does not insert this value when I am adding records using my continuous form.

**********************************************************
Private Sub Form_BeforeInsert(Cancel As Integer)

Me!target_group = GetTargetType()
'Forms!frmNmsConsumptionEntry!target_group = GetTargetType()
'[tblNmsConsumption.target_group] = GetTargetType()

End Sub
**********************************************************
Need help with making sure I can insert this value once retrived and suggestions on the best way to solve this are welcome

 

[llkhoutx]

sub FormName_OnCurrent()
Forms!frmNmsConsumptionEntry!target_group = GetTargetType()
end sub

 

[Galaxiom]

Looking up another field value based on an entry sounds like a normalization error to me.

BTW "Type" is a VBA reserved word and best avoided for object names.

 

[JHB]

Keeping in mind what Galaxiom wrote:
If formulation is a text field type then:

GetTargetType = DLookup("type", "tblFormulations", "[formulation]='" & Forms![frmNmsConsumptionEntry]![formulation] & "'")

If number:

GetTargetType = DLookup("type", "tblFormulations", "[formulation]=" & Forms![frmNmsConsumptionEntry]![formulation])

 

[gangu]

I have tried your suggestions but no luck yet, it does not insert when creating a record. I get the normalization hint but the reason why I want it included is purely because of the client....I read something like I have to use ADO when using vba to set values......how can I do that? Thanks in advance

 

[JHB]

I have tried your suggestions but no luck yet, it does not insert when creating a record...

Sorry it doesn't help, good luck.

 

[smig]

Keeping in mind what Galaxiom wrote:
Can you explain in plain English what are you trying to do

 

[RainLover]

I have tried your suggestions but no luck yet, it does not insert when creating a record. I get the normalization hint but the reason why I want it included is purely because of the client....I read something like I have to use ADO when using vba to set values......how can I do that? Thanks in advance

What does this mean?

Are you saying that the client is helping develop the database.

I get the normalization hint

It appears to me that you don't get the hint.

I agree with Galaxiom and most professional developers. Suggest you rethink.