Unbound textbox Issue on Tabular Form

[roham]

My tabular form has a Part# associated with the table the form connects with. In the change method of the Part# field, I have a DAO.Recordset that is fetching the Part Description and am trying to place that description on the same line for that specific Part# field that is being changed. However, what is happening is all of the Part Description fields for the entire form is being changed.

Is there a way to identify only the active row for the tablular form?

Here is my code: I believe the problem lies where I have the Me!txtPartDescription = rs!partdes. I envision something here specifying what line (a bookmark or something) to alter with the part description.

Any suggestions?


Private Sub cboPartNum_change()
Dim rs As DAO.Recordset
Dim strRecords As String

strRecords = "SELECT [PARTDES] FROM tPART# WHERE [PARTNUM] = '" & Me!cboPartNum & "'"

Set rs = CurrentDb().OpenRecordset(strRecords, dbOpenDynaset)

Me!txtPartDescription = rs!partdes

rs.Close
End Sub

[schlauberger]

First of all enter your code in the After Update Event for the "Part#" field. The on change event will be triggered after each character entry resulting in wasted processing time.

Secondly you will have to reset the unbound field during the On Current event, you can use the same code. You may want to create a private sub-routine with the forms module and enter the code you have enclosed. And call it from bothe the After update and on current methods.

For the On Current method test to see if you are on a new record "Me.NewRecord = True." If so set the unbound field to "".

Also clear up your memory by adding the following after rs.close:
Set rs = Nothing

Good Luck!!!

[roham]

schlauberger, thank you for your response. Its been a while since I've really programmed in VB/Access and your advise on the different events is good.

Thank you.

Rob