ADO Recordset Question...

[sanders720]

See = H E R E => Below...

The code reads...

Set rs2 = New ADODB.Recordset
rs2.Open sql2, CurrentProject.Connection
Debug.Print "NextBubbleNumber = "; rs2.Fields("NextBubbleNumber&quot

Something is wrong in my logic and I don't know what... Any help would be greatly appreciated...


Private Sub cmdGetData_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As Recordset
Dim connString As String
Dim sql As String
Dim sql2 As String
Dim sql3 As String
Dim sqlPL As String
Dim sqlBOM As String

connString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=\\DEEPBLUE\EngineeringBOM\VIA-WD User Files\" & cboJobNo.Value & ".mdb"

MsgBox connString

Set cn = New ADODB.Connection
cn.Open connString
Debug.Print "cn = "; cn

sql = "SELECT COMP.CAT, COMP.DESC1, COMP.DESC2, COMP.MFG, COMP.LOC FROM [COMP] WHERE (((COMP.CAT) Is Not Null) AND ((COMP.LOC) = '" & Me.cboSubAssy.Value & "'))"
Debug.Print "sql = "; sql

sql2 = "SELECT tblBubbleNumbers.NextBubbleNumber FROM [tblBubbleNumbers] WHERE ((tblBubbleNumbers.JobNo) = " & Me.cboJobNo.Value & &quot"
Debug.Print "sql2 = "; sql2

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient ' Moves records into local memory
rs.Open sql, cn, adOpenStatic, adLockOptimistic ' Only locks on update
Debug.Print "eof = "; rs.EOF

= H E R E => Set rs2 = New ADODB.Recordset
rs2.Open sql2, CurrentProject.Connection
Debug.Print "NextBubbleNumber = "; rs2.Fields("NextBubbleNumber&quot

sqlPL = "INSERT INTO tblPartsListing " & _
"(PartNo, PartDescription, ManufacturedBy) " & _
"VALUES (" & _
"'" & rs.Fields("CAT&quot & "', " & _
"'" & rs.Fields("DESC1&quot & " " & rs.Fields("DESC2&quot & "', " & _
"'" & rs.Fields("MFG&quot & "'" & _
&quot"
Debug.Print "sqlPL = "; sqlPL

sqlBOM = "INSERT INTO tblBOM " & _
"(JobNo, SubAssy, PartNo, BubbleNumber, QTY, AddedViaAutoCAD) " & _
"VALUES (" & _
"" & cboJobNo.Value & ", " & _
"'" & rs.Fields("LOC&quot & "', " & _
"'" & rs.Fields("CAT&quot & "', " & _
"" & rs2.Fields("NextBubbleNumber&quot & ", " & _
"1, " & _
"1" & _
&quot"
Debug.Print "sqlBOM = "; sqlBOM

sql3 = "UPDATE tblBubbleNumbers SET NextBubbleNumber = NextBubbleNumber + 1 WHERE JobNo = " & cboJobNo.Value & "'"
Debug.Print "sql3 = "; sql3

While Not rs.EOF
Debug.Print rs.Fields("CAT&quot, rs.Fields("DESC1&quot, rs.Fields("DESC2&quot, rs.Fields("MFG&quot, rs.Fields("LOC&quot
DoCmd.RunSQL sqlPL
DoCmd.RunSQL sqlBOM
DoCmd.RunSQL sql3

rs.MoveNext
Wend

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

rs2.Close
Set rs2 = Nothing

MsgBox ("Program Completed Successfully...&quot

End Sub

 

[cmmrfrds]

Dim rs2 As Recordset
try
Dim rs2 As ADODB.Recordset

 

[sanders720]

Thanks... I take it you spend a lot of your time here! Now the code is locking up here ====>, when I try running sql3. The string value takes, it just does not apply. Basically, I am reading a value from a table and incrementing it by one - kind of a manual auto-number thing. The debug.print produces this result...

cn = Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;User ID=Admin;Data Source=\\DEEPBLUE\EngineeringBOM\VIA-WD User Files\36709.mdb;Mode=Share Deny None;Extended Properties=";COUNTRY=0;CP=1252;LANGID=0x0409";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDBatabase Password="";Jet OLEDB:Global Partial Bulk Ops=2
sql = SELECT COMP.CAT, COMP.DESC1, COMP.DESC2, COMP.MFG, COMP.LOC FROM [COMP] WHERE (((COMP.CAT) Is Not Null) AND ((COMP.LOC) = 'C01'))
sql2 = SELECT tblBubbleNumbers.NextBubbleNumber FROM [tblBubbleNumbers] WHERE ((tblBubbleNumbers.JobNo) = 36709)
eof = False
NextBubbleNumber = 11
sqlPL = INSERT INTO tblPartsListing (PartNo, PartDescription, ManufacturedBy) VALUES ('1492-CB1G150', '1492-CB1G150 ', '')
sqlBOM = INSERT INTO tblBOM (JobNo, SubAssy, PartNo, BubbleNumber, QTY, AddedViaAutoCAD) VALUES (36709, 'C01', '1492-CB1G150', 11, 1, 1)
sql3 = UPDATE tblBubbleNumbers SET NextBubbleNumber = NextBubbleNumber + 1 WHERE JobNo = 36709'
====> 1492-CB1G150 1492-CB1G150 Null Null C01

Thanks again for your help...


sql3 = "UPDATE tblBubbleNumbers SET NextBubbleNumber = NextBubbleNumber + 1 WHERE JobNo = " & cboJobNo.Value & "'"
Debug.Print "sql3 = "; sql3

While Not rs.EOF
Debug.Print rs.Fields("CAT&quot, rs.Fields("DESC1&quot, rs.Fields("DESC2&quot, rs.Fields("MFG&quot, rs.Fields("LOC&quot
DoCmd.RunSQL sqlPL
DoCmd.RunSQL sqlBOM
====> DoCmd.RunSQL sql3

rs.MoveNext
Wend

 

[JeremyNYC]

sql3 = "UPDATE tblBubbleNumbers SET NextBubbleNumber = NextBubbleNumber + 1 WHERE JobNo = " & cboJobNo.Value & "'"

You've got a single quote after the JobNo, but not before. Add one before, if it's a text field, or take out the one after, if it's a numeric.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.