stevens said:
Dirk:
Here is the code I am using. As you can see, I have tried different
things. The recordsets that give me the problem are rsProd and
rsSetup.
Private Sub cmdDisplay_Click()
Dim db As DAO.Database
Dim rsProd As DAO.Recordset
Dim rsSetup As DAO.Recordset
Dim rsMbom As DAO.Recordset
Dim strSQLProd As String
Dim strSQLSetup As String
Dim strSQLMbom As String
Dim mbomno$, mbomrev$, testval$
strSQLMbom = "SELECT fpartno, fcpartrev FROM dbo_inbomm"
strSQLProd = "SELECT * FROM qryCTProdTimes WHERE fpartno = " &
Me.txtpartno & " AND fcpartrev = '" & Me.txtrev & "'"
strSQLSetup = "SELECT * FROM qryCTSUTimes WHERE fpartno = " &
Me.txtpartno & " AND fcpartrev = '" & Me.txtrev & "'"
Set db = CurrentDb
'This code opens a recorset from the inbomm table and fetches the
master assembly
'numbers and revisions from the inbomm table
Set rsMbom = db.OpenRecordset("dbo_inbomm")
'This code writes the assembly and revision to two master
variables to be used for
'further searches
'MsgBox "Number of records =" & rsMbom.RecordCount
rsMbom.MoveFirst
Do While Not rsMbom.EOF
With rsMbom
mbomno = !fpartno
txtpartno = !fpartno
mbomrev = !fcpartrev
txtrev = !fcpartrev
End With
'rsMbom.MoveNext
Set rsProd = db.OpenRecordset(strSQLProd, dbOpenSnapshot)
Set rsSetup = db.OpenRecordset(strSQLSetup, dbOpenSnapshot)
'Debug.Print mbomno
'Debug.Print mbomrev
'Debug.Print rsProd!fpartno
testval = rsProd!fpartno
If testval = "" Then
With rsProd
Debug.Print !fpartno
Debug.Print !fcpartrev
txtPrOPS = ![2OPS]
txtPrBox = !BOX
txtPrFinal = !FINAL
txtPrFunct = !FUNCTNL
txtPrGenrad = !GENRAD
txtPrHPTest = ![HP TEST]
txtPrKit = !KIT
txtPrSetup = !SETUP
txtPrSMT = !SMT
txtPrSMTCP = ![SMT CP]
txtPrSMTIP = ![SMT IP]
txtPrTest = !TEST
txtPrTH = !TH
End With
With rsSetup
txtSUOPS = ![2OPS]
txtsubox = !BOX
txtsufinal = !FINAL
txtsufunct = !FUNCTNL
txtsugenrad = !GENRAD
txtsuhptest = ![HP TEST]
txtsukit = !KIT
txtsusetup = !SETUP
txtsusmt = !SMT
txtsusmtcp = ![SMT CP]
txtsusmtip = ![SMT IP]
txtsutest = !TEST
txtsuth = !TH
End With
rsMbom.MoveNext
Else
rsMbom.MoveNext
End If
Loop
Dirk Goldgar said:
I have a form with unbound text boxes which I am using an SQL
statement to populate through a "with -- end with" statement. The
problem I am having is that occassionally the SQL statement will not
find a record and the debugger keeps stopping the code saying that
"There is no current record". How can I set up an if statement, (or
some other statement) that will check for a valid record and bypass
the "with---endwith" statement if there is no valid record?
Thanks
Steve
Please post the code you're using. I guess you're opening a
recordset, in which case you can test whether the recordset's EOF
property is True immediately after opening it. But without the
details I can't be more specific.
I have a question for you, Steve. Are the recordsets being opened as
rsProd and rsSetup supposed to be related to the current rsMbom record?
In the WHERE clauses you specify for them,
WHERE fpartno = " &
Me.txtpartno & " AND fcpartrev = '" & Me.txtrev & "'"
are Me.txtpartno and Me.txtrev supposed to be holding the values from
the current rsMbom record? That's not going to happen the way you have
this written, because you're building the SQL statements outside the
loop. My guess is that this is a mistake on your part, but I could be
wrong about that.
If I have guessed correctly, you need something roughly along these
lines:
'----- start of revised code -----
Dim db As DAO.Database
Dim rsProd As DAO.Recordset
Dim rsSetup As DAO.Recordset
Dim rsMbom As DAO.Recordset
Dim strSQLProd As String
Dim strSQLSetup As String
Dim strSQLMbom As String
Dim mbomno$, mbomrev$, testval$
strSQLMbom = _
"SELECT fpartno, fcpartrev FROM dbo_inbomm"
'** DG Note: the above SQL statement doesn't appear to be used.
'This code opens a recorset from the inbomm table and fetches the
master
'assembly numbers and revisions from the inbomm table
Set db = CurrentDb
Set rsMbom = db.OpenRecordset("dbo_inbomm")
'This code writes the assembly and revision to two master variables
to
'be used for further searches
Do Until rsMbom.EOF
With rsMbom
mbomno = !fpartno
txtpartno = !fpartno
mbomrev = !fcpartrev
txtrev = !fcpartrev
End With
strSQLProd = _
"SELECT * FROM qryCTProdTimes WHERE " & _
"fpartno = " & Me.txtpartno & _
" AND fcpartrev = '" & Me.txtrev & "'"
strSQLSetup = _
"SELECT * FROM qryCTSUTimes WHERE " &
"fpartno = " & Me.txtpartno & _
" AND fcpartrev = '" & Me.txtrev & "'"
Set rsProd = db.OpenRecordset(strSQLProd, dbOpenSnapshot)
Set rsSetup = db.OpenRecordset(strSQLSetup, dbOpenSnapshot)
With rsProd
If Not .EOF Then
Debug.Print !fpartno
Debug.Print !fcpartrev
txtPrOPS = ![2OPS]
txtPrBox = !BOX
txtPrFinal = !FINAL
txtPrFunct = !FUNCTNL
txtPrGenrad = !GENRAD
txtPrHPTest = ![HP TEST]
txtPrKit = !KIT
txtPrSetup = !SETUP
txtPrSMT = !SMT
txtPrSMTCP = ![SMT CP]
txtPrSMTIP = ![SMT IP]
txtPrTest = !TEST
txtPrTH = !TH
End If
End With
With rsSetup
If Not .EOF Then
txtSUOPS = ![2OPS]
txtsubox = !BOX
txtsufinal = !FINAL
txtsufunct = !FUNCTNL
txtsugenrad = !GENRAD
txtsuhptest = ![HP TEST]
txtsukit = !KIT
txtsusetup = !SETUP
txtsusmt = !SMT
txtsusmtcp = ![SMT CP]
txtsusmtip = ![SMT IP]
txtsutest = !TEST
txtsuth = !TH
End If
End With
rsMbom.MoveNext
Loop
'----- end of revised code -----
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
