Record not found

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I use a data base to store items in a table called so. I want when I type an
item code on a combo box and that item is already stored, to bring that
record on an open subform and fill the values. To do so I have the code:

Dim strFilter As String
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strFilter = "ItemCode = '" & Me!cboItemCode & "'"
strSQL = "SELECT Kind, Model, Type FROM Items WHERE ItemCode =
'strFilter' ;"
Set rsCurr = CurrentDb().OpenRecordset(strSQL)

If rsCurr.EOF = False Then
Me!cboKind = rsCurr!Kind
Me!cboType = rsCurr!Type
Me!cboModel = rsCurr!Model
End If

Although I type or select on cboItemCode a value that is already stored I
never get the rest combos auto filled. Do you know what is wrong?

Thanks a lot

GL
 
ItemCode is a string by the look of your single quotes? If
it is numeric you will need to lose those. The way that you
are combining your filter and sql lines have a couple of
problems, one with concantenation and the other ends up
using 'ItemCode =' twice. Try revising this line to the
following and also insert a MoveFirst command into your
recordset once it is open:

strSQL = "SELECT Kind, Model, Type FROM Items WHERE ItemCode
= '" Me!cboItemCode & "'"
Set rsCurr = CurrentDb().OpenRecordset(strSQL)

If rsCurr.EOF = False Then
rsCurr.MoveFirst
Me!cboKind = rsCurr!Kind

Here is another variation for your perusal as well...

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
With rsCurr
If Not .BOF And Not .EOF Then
.MoveFirst
Me!cboKind = !Kind
etc...
End If
.Close
End With
Set rsCurr = Nothing
 
Thanks it works although I cannot understand what is the difference bettween
Dim strFilter As String
strSQL = "SELECT Kind, Model, Type FROM Items WHERE ItemCode = 'strFilter' ;"
and
strSQL = "SELECT Kind, Model, Type FROM Items WHERE ItemCode = '" Me! '"
Me!cboItemCode & "'"
 
In the first line you haven't used any ampersands to
concantenate the actual string stored in the variable
'strFilter' to the rest of the SQL line so that Access is
trying to look for an Item Code with the value strFilter. In
the strFilter line you use the ampersands correctly, but
missed the fact that they were also needed to tie the
variable into the rest of the strSQL text string.

As I mentioned earlier your code also brought in "ItemCode =
" into the string two times when you only really want it
once. Your approach was a valid one, the devil was in the
details. I will include a revision of your approach here
with the detail problems ironed out.

Dim strFilter As String
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strFilter = "ItemCode = '" & Me!cboItemCode & "'"
strSQL = "SELECT Kind, Model, Type FROM Items WHERE " &
strFilter
Set rsCurr = CurrentDb().OpenRecordset(strSQL)


Gary Miller
Sisters, OR
 
You have made it very clear,

Gary Miller said:
In the first line you haven't used any ampersands to
concantenate the actual string stored in the variable
'strFilter' to the rest of the SQL line so that Access is
trying to look for an Item Code with the value strFilter. In
the strFilter line you use the ampersands correctly, but
missed the fact that they were also needed to tie the
variable into the rest of the strSQL text string.

As I mentioned earlier your code also brought in "ItemCode =
" into the string two times when you only really want it
once. Your approach was a valid one, the devil was in the
details. I will include a revision of your approach here
with the detail problems ironed out.

Dim strFilter As String
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strFilter = "ItemCode = '" & Me!cboItemCode & "'"
strSQL = "SELECT Kind, Model, Type FROM Items WHERE " &
strFilter
Set rsCurr = CurrentDb().OpenRecordset(strSQL)


Gary Miller
Sisters, OR
Click to expand...
 
Back
Top