Update fields after update (1 Viewer)

[mshoems]

I am trying to update fields on a form from a different record source. I have a form driven by a table and a user wants to update one field and have two others autopopulate with values from another table based on the value that was entered. I was trying an After Update event and a Dlookup but with no luck. Any help would be great.

Thanks.

 

[pbaldy]

Why don't you post your attempted code in the after update event and we'll sort out where you're going wrong.

 

[mshoems]

I know this is really bad code but I am still getting to know this part of access.

Private Sub txtInvestorID_AfterUpdate()
Dim intSearch As Integer
Dim varX As Variant
intSearch = 1
varX = DLookup("[InvestorID]", "tblInvestor_Update", _
"[GSE_Code] = " & intSearch)

End Sub

What I am trying to do here is when a user adds an Investor ID into the field, the field needs to lookup the value from a different table and grab the GSE Code and add it to a new field. I was just initially trying to do the lookup to see if it worked. Obviously it didn't.

 

[pbaldy]

Did it error? For starters, that wouldn't appear to do anything, since all it does is set a variable. You'd want to use that to put the value in another textbox. I'd also get rid of the line continuation to see if that's the problem. Try:

Dim intSearch As Integer
Dim varX As Variant
intSearch = 1
varX = DLookup("[InvestorID]", "tblInvestor_Update", "[GSE_Code] = " & intSearch)
Me.OtherTextboxName = varX

 

[mshoems]

Okay, I added the new code and I am getting a Run Time error that there is a Data Type Mismatch in expression:

varX = DLookup("[Investor_ID]", "tblInvestor_Update", "[GSE_Code] = " & intSearch)

My field names are correct and my table name is correct.

Is there another way to do this that you know of off the top of your head?

Thanks.

 

[pbaldy]

A data type mismatch probably means the field is text:

http://www.mvps.org/access/general/gen0018.htm

An alternative method if appropriate is to use a combo box to list the values, and have the related values in the combo rowsource. Then you can get the values related to the selected item with:

ComboName.Column(x)

where x is the column containing the desired value (note the column property is zero based).

 

[mshoems]

Thanks for the help. I ended up using the combo box alternative. Much easier than trying to do the Dlookup and it worked perfect without racking my brain any further.

 

[pbaldy]

No problem, and welcome to the site by the way!