Fill in textbox based on value in different textbox

[yschuerm]

I have got an article Database in which I want to add new articles.
In the input form I need to fill in some specifics about the article.

What I want is the following. In case the article nr is known, the article description needs to be filled in automatically in different textbox. In case the article nr is not known, the focus needs to go to the article description textbox, so I can fill it in. This needs to happen on exit of the article nr textfield.

I tried somethings, but it either works on the first article in the table or it works on all articles but freezes when article nr is not known.

The code I currently have is the following :

Private Sub txtNewCompArtNr_Exit(Cancel As Integer)

 Dim db As Database
 Dim rs As Recordset

 Set db = CurrentDb
 'Set rs = db.OpenRecordset("SELECT CompetitorArtNr,BernerArtNr, [Competitor Art Description] FROM Competitors")
 Set rs = db.OpenRecordset("Competitors")

 rs.MoveFirst
 Do Until rs.EOF
 If rs.Fields("CompetitorArtNr").Value = Me.txtNewCompArtNr.Value Then
 
 Me.txtNewCompArtDes.Value = rs.Fields("Competitor Art Description").Value
 Me.txtBernArt.Value = rs.Fields("BernerArtNr").Value
 Me.cbSource.SetFocus
 
 Exit Sub
 Else
 rs.MoveNext
 Exit Do
 End If
rs.MoveNext
 Loop

 rs.Close
 db.Close


End Sub

 

[yschuerm]

Nobody ?

 

[Micron]

I'm not understanding your issue completely. It's hard to equate "article nr" with anything in your code sample. What doesn't look right is that if this is not true
If rs.Fields("CompetitorArtNr").Value = Me.txtNewCompArtNr.Value Then
you are moving to the next record, exiting the loop, then closing the recordset. I have no idea why you'd choose to close the db after that.
I can only suggest that after you close the recordset, move the focus to the desired control and don't close the db.