Previously working code not working now!

[dBNovice]

Hi all, I am reusing code that was used in a previous copy of the DB.
I am trying to extract data from other tables, queries and a form and
put it in a table. The code was used previously and worked without any
problems. I am trying to figure out what is causing the problem. I
presume there is probably a missing reference but I can't tell. Parts
of the code works but it appears the following portion is not working.

'Action3: If the Proposal is already added to tblPanel, then Update the
record. Else, add new Record in tblPanel and update the fields

strSQLChair = "SELECT * FROM qrychair" & _
" WHERE [proposalid] = '" & strProposalID & "'"
strSQLOrganizer = "SELECT * from tblOrganizer" & _
" WHERE [proposalid] = '" & strProposalID & "'"
strSQLDisc1 = "SELECT * FROM qryDiscussant1" & _
" WHERE [proposalid] = '" & strProposalID & "'"
strSQLDisc2 = "SELECT * FROM qryDiscussant2" & _
" WHERE [proposalid] = '" & strProposalID & "'"
Set rsChair = db.OpenRecordset(strSQLChair, dbOpenDynaset)
Set rsPanelDetails = db.OpenRecordset("tblpanel", dbOpenDynaset)
Set rsOrganizer = db.OpenRecordset(strSQLOrganizer, dbOpenDynaset)
Set rsDisc1 = db.OpenRecordset(strSQLDisc1, dbOpenDynaset)
Set rsDisc2 = db.OpenRecordset(strSQLDisc2, dbOpenDynaset)
strFound = "SELECT * FROM tblPanel" & _
" WHERE [proposalid] = '" & strProposalID & "'"
Set rsFoundPanel = db.OpenRecordset(strFound, dbOpenDynaset)
If Not (rsFoundPanel.RecordCount = 0) Then
With rsFoundPanel
.Edit
!PanelID = strpanelid
!AREARANK = strRank
!PROPOSALID = strProposalID
!TYPE = rsChair!PROPOSALTYPE
!PANELNAME = rsChair!PROPOSALTITLE
!AREA = rsChair!AREA
If Not rsChair.RecordCount = 0 Then
!CHAIRFIRST = rsChair!FIRST
!CHAIRMI = rsChair!MI
!CHAIRLAST = rsChair!LAST
!CHAIRAFFILIATION = rsChair!AFFILIATION
!CHAIRADDRESS1 = rsChair!ADDRESS1
!CHAIRADDRESS2 = rsChair!ADDRESS2
!CHAIRADDRESS3 = rsChair!ADDRESS3
!CHAIRCITY = rsChair!CITY
!CHAIRSTATE = rsChair!STATE
!CHAIRZIP = rsChair!ZIP
!CHAIRCOUNTRY = rsChair!COUNTRY
!CHAIRTELE = rsChair!PHONE
!CHAIRFAX = rsChair!FAX
!CHAIREMAIL = rsChair!EMAIL
End If
If Not rsDisc1.RecordCount = 0 Then
!DIS1FIRST = rsDisc1!FIRST
!DIS1MI = rsDisc1!MI
!DIS1LAST = rsDisc1!LAST
!DIS1AFFILIATION = rsDisc1!AFFILIATION
!DIS1ADDRESS1 = rsDisc1!ADDRESS1
!DIS1ADDRESS2 = rsDisc1!ADDRESS2
!DIS1ADDRESS3 = rsDisc1!ADDRESS3
!DIS1CITY = rsDisc1!CITY
!DIS1STATE = rsDisc1!STATE
!DIS1ZIP = rsDisc1!ZIP
!DIS1COUNTRY = rsDisc1!COUNTRY
!DIS1TELE = rsDisc1!PHONE
!DIS1FAX = rsDisc1!FAX
!DIS1EMAIL = rsDisc1!EMAIL
End If
If Not rsDisc2.RecordCount = 0 Then
!DIS2FIRST = rsDisc2!FIRST
!DIS2MI = rsDisc2!MI
!DIS2LAST = rsDisc2!LAST
!DIS2AFFILIATION = rsDisc2!AFFILIATION
!DIS2ADDRESS1 = rsDisc2!ADDRESS1
!DIS2ADDRESS2 = rsDisc2!ADDRESS2
!DIS2ADDRESS3 = rsDisc2!ADDRESS3
!DIS2CITY = rsDisc2!CITY
!DIS2STATE = rsDisc2!STATE
!DIS2ZIP = rsDisc2!ZIP
!DIS2COUNTRY = rsDisc2!COUNTRY
!DIS2TELE = rsDisc2!PHONE
!DIS2FAX = rsDisc2!FAX
!DIS2EMAIL = rsDisc2!EMAIL
End If
!Organizer = rsOrganizer!OFIRST & " " & rsOrganizer!OLAST
!ORGANIZERTELE = rsOrganizer!OPHONE
!ORGANIZEREMAIL = rsOrganizer!OEMAIL
.Update
End With

Else
With rsPanelDetails
.AddNew
!PanelID = strpanelid
!AREARANK = strRank
!PROPOSALID = strProposalID
!TYPE = rsChair!PROPOSALTYPE
!PANELNAME = rsChair!PROPOSALTITLE
!AREA = rsChair!AREA
If Not rsChair.RecordCount = 0 Then
!CHAIRFIRST = rsChair!FIRST
!CHAIRMI = rsChair!MI
!CHAIRLAST = rsChair!LAST
!CHAIRAFFILIATION = rsChair!AFFILIATION
!CHAIRADDRESS1 = rsChair!ADDRESS1
!CHAIRADDRESS2 = rsChair!ADDRESS2
!CHAIRADDRESS3 = rsChair!ADDRESS3
!CHAIRCITY = rsChair!CITY
!CHAIRSTATE = rsChair!STATE
!CHAIRZIP = rsChair!ZIP
!CHAIRCOUNTRY = rsChair!COUNTRY
!CHAIRTELE = rsChair!PHONE
!CHAIRFAX = rsChair!FAX
!CHAIREMAIL = rsChair!EMAIL
End If
If Not rsDisc1.RecordCount = 0 Then
!DIS1FIRST = rsDisc1!FIRST
!DIS1MI = rsDisc1!MI
!DIS1LAST = rsDisc1!LAST
!DIS1AFFILIATION = rsDisc1!AFFILIATION
!DIS1ADDRESS1 = rsDisc1!ADDRESS1
!DIS1ADDRESS2 = rsDisc1!ADDRESS2
!DIS1ADDRESS3 = rsDisc1!ADDRESS3
!DIS1CITY = rsDisc1!CITY
!DIS1STATE = rsDisc1!STATE
!DIS1ZIP = rsDisc1!ZIP
!DIS1COUNTRY = rsDisc1!COUNTRY
!DIS1TELE = rsDisc1!PHONE
!DIS1FAX = rsDisc1!FAX
!DIS1EMAIL = rsDisc1!EMAIL
End If
If Not rsDisc2.RecordCount = 0 Then
!DIS2FIRST = rsDisc2!FIRST
!DIS2MI = rsDisc2!MI
!DIS2LAST = rsDisc2!LAST
!DIS2AFFILIATION = rsDisc2!AFFILIATION
!DIS2ADDRESS1 = rsDisc2!ADDRESS1
!DIS2ADDRESS2 = rsDisc2!ADDRESS2
!DIS2ADDRESS3 = rsDisc2!ADDRESS3
!DIS2CITY = rsDisc2!CITY
!DIS2STATE = rsDisc2!STATE
!DIS2ZIP = rsDisc2!ZIP
!DIS2COUNTRY = rsDisc2!COUNTRY
!DIS2TELE = rsDisc2!PHONE
!DIS2FAX = rsDisc2!FAX
!DIS2EMAIL = rsDisc2!EMAIL
End If
!Organizer = rsOrganizer!OFIRST & " " & rsOrganizer!OLAST
!ORGANIZERTELE = rsOrganizer!OPHONE
!ORGANIZEREMAIL = rsOrganizer!OEMAIL
.Update
End With

End If

MsgBox "Proposal Accepted As Is"
Forms!frmBuildSessions.Refresh

errtrap:
End Sub

[manningfan]

Guh! That's a monster, and without the rest of the code or a copy of
the database it'd be hard to tell you what's wrong.

Try adding a break point in the code just before this section. If you
click on the left margin of the code you'll get a little red circle;
that's a breakpoint.

Once you hit the breakpoint Access will jump into the code. Hit the F8
key and you'll "step" through the code. Keep hitting F8 and take note
of what's happening and what's being processed. You should be able to
narrow down the issue pretty quickly doing that.

Also, each time you hit F8 let your mousepointer hover above the
variables your code just processed. It should show up on your screen
what the variables were set to. If you see something out of the
ordinary, look at what the variable was set to and try to figure out
what it's doing.

I know that's not the answer you were looking for, but after you do it
a few times you'll be able to figure out alot of this stuff on your own
pretty quickly.

[dBNovice]

Thank you, I'll try that.

[dBNovice]

I tried to debug by stepping through the code and I don't think the
code is working in the following lines:

Set rsChair = db.OpenRecordset(strSQLChair, dbOpenDynaset)
Set rsPanelDetails = db.OpenRecordset("tblpanel", dbOpenDynaset)
Set rsOrganizer = db.OpenRecordset(strSQLOrganizer, dbOpenDynaset)
Set rsDisc1 = db.OpenRecordset(strSQLDisc1, dbOpenDynaset)
Set rsDisc2 = db.OpenRecordset(strSQLDisc2, dbOpenDynaset)
' tells you what function the strings are equal to but give no value
strFound = "SELECT * FROM tblPanel" & _
" WHERE [proposalid] = '" & strProposalID & "'"
Set rsFoundPanel = db.OpenRecordset(strFound, dbOpenDynaset)
' here the string is equal to ""
If Not (rsFoundPanel.RecordCount = 0) Then
' get the error msg: object variable or with block variable not set. I
am not sure how to correct this error; please help!

[Terry Kreft]

In the original code there will be a partial line similar to
Set rsFoundPanel =

This is not happening in your ode before the line where the error is
occurring. You need to correct this.

--

Terry Kreft
"dBNovice" <lu*****@hotmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

I tried to debug by stepping through the code and I don't think the
code is working in the following lines:

Set rsChair = db.OpenRecordset(strSQLChair, dbOpenDynaset)
Set rsPanelDetails = db.OpenRecordset("tblpanel", dbOpenDynaset)
Set rsOrganizer = db.OpenRecordset(strSQLOrganizer, dbOpenDynaset)
Set rsDisc1 = db.OpenRecordset(strSQLDisc1, dbOpenDynaset)
Set rsDisc2 = db.OpenRecordset(strSQLDisc2, dbOpenDynaset)
' tells you what function the strings are equal to but give no value
strFound = "SELECT * FROM tblPanel" & _
" WHERE [proposalid] = '" & strProposalID & "'"
Set rsFoundPanel = db.OpenRecordset(strFound, dbOpenDynaset)
' here the string is equal to ""
If Not (rsFoundPanel.RecordCount = 0) Then
' get the error msg: object variable or with block variable not set. I
am not sure how to correct this error; please help!

[dBNovice]

This line of code does exists. Note the 16th line above:"Set
rsFoundPanel = db.OpenRecordset(strFound, dbOpenDynaset) "
I think the problem exists when I set the recordsets to equal the
corresponding SQL string. For example, after the following line of
code, the code still get the msg strSQL...= Empty.

i.e. strSQLChair = "SELECT * FROM qrychair" & _
" WHERE [proposalid] = '" & strProposalID & "'"
Set rsChair = db.OpenRecordset(strSQLChair, dbOpenDynaset) '
here strSQLChair = Empty