Bad Code Needs Help

[darnnews]

I have a form.

When a person selects a publication from a listbox, this snippet of
code is supposed to look up authors that correspond to that publication
and populate the Author List Box, but it doesn't work. I have been
hacking away at it for a while to no avail. Can anyone spot the bug(s)?

The RowSource property of Authors gets set to the following by this
code, if that helps you help me:

SELECT Author FROM Authors WHERE [Publication 1]=127 Or [Publication
2]=127 Or [Publication 3]=127;

Private Sub Publication_AfterUpdate()

Dim dbsCurrent As Database
Dim rstPublications As Recordset

Dim qdfExists As QueryDef

Dim intPublication As Integer
Dim intPubMasterID As Integer

Set dbsCurrent = OpenDatabase("c:\documents and
settings\administrator\desktop\PR.mdb")

intPublication = [Forms]![new clips]!Publication

Set qdfExists = dbsCurrent.CreateQueryDef("")
With qdfExists
.SQL = "SELECT * FROM Publications " & _
"WHERE PublicationID = " & intPublication
Set rstPublications = .OpenRecordset()
End With

With rstPublications
intPubMasterID = !PubMasterID
End With
rstPublications.Close

Set qdfExists = dbsCurrent.CreateQueryDef("")
With qdfExists
.SQL = "SELECT Author FROM Authors " & _
"WHERE [Publication 1] = " & intPubMasterID & _
"OR [Publication 2] = " & intPubMasterID & _
"OR [Publication 3] = " & intPubMasterID
End With

Me.Author.RowSource = qdfExists.SQL
Me.Author.Requery
dbsCurrent.Close
End Sub
Thanks!

Andrew

[darnnews]

An addendum to this...

When I start typing in the Author list box the name I expect, it
appears.

How do I make it visible after I requery?

Thanks,
Andrew
darnnews wrote:

I have a form.

When a person selects a publication from a listbox, this snippet of
code is supposed to look up authors that correspond to that publication
and populate the Author List Box, but it doesn't work. I have been
hacking away at it for a while to no avail. Can anyone spot the bug(s)?

The RowSource property of Authors gets set to the following by this
code, if that helps you help me:

SELECT Author FROM Authors WHERE [Publication 1]=127 Or [Publication
2]=127 Or [Publication 3]=127;

Private Sub Publication_AfterUpdate()

Dim dbsCurrent As Database
Dim rstPublications As Recordset

Dim qdfExists As QueryDef

Dim intPublication As Integer
Dim intPubMasterID As Integer

Set dbsCurrent = OpenDatabase("c:\documents and
settings\administrator\desktop\PR.mdb")

intPublication = [Forms]![new clips]!Publication

Set qdfExists = dbsCurrent.CreateQueryDef("")
With qdfExists
.SQL = "SELECT * FROM Publications " & _
"WHERE PublicationID = " & intPublication
Set rstPublications = .OpenRecordset()
End With

With rstPublications
intPubMasterID = !PubMasterID
End With
rstPublications.Close

Set qdfExists = dbsCurrent.CreateQueryDef("")
With qdfExists
.SQL = "SELECT Author FROM Authors " & _
"WHERE [Publication 1] = " & intPubMasterID & _
"OR [Publication 2] = " & intPubMasterID & _
"OR [Publication 3] = " & intPubMasterID
End With

Me.Author.RowSource = qdfExists.SQL
Me.Author.Requery
dbsCurrent.Close
End Sub
Thanks!

Andrew

[darnnews]

Addendum 2: I fixed it.


darnnews wrote:

An addendum to this...

When I start typing in the Author list box the name I expect, it
appears.

How do I make it visible after I requery?

Thanks,
Andrew
darnnews wrote:

I have a form.

When a person selects a publication from a listbox, this snippet of
code is supposed to look up authors that correspond to that publication
and populate the Author List Box, but it doesn't work. I have been
hacking away at it for a while to no avail. Can anyone spot the bug(s)?

The RowSource property of Authors gets set to the following by this
code, if that helps you help me:

SELECT Author FROM Authors WHERE [Publication 1]=127 Or [Publication
2]=127 Or [Publication 3]=127;

Private Sub Publication_AfterUpdate()

Dim dbsCurrent As Database
Dim rstPublications As Recordset

Dim qdfExists As QueryDef

Dim intPublication As Integer
Dim intPubMasterID As Integer

Set dbsCurrent = OpenDatabase("c:\documents and
settings\administrator\desktop\PR.mdb")

intPublication = [Forms]![new clips]!Publication

Set qdfExists = dbsCurrent.CreateQueryDef("")
With qdfExists
.SQL = "SELECT * FROM Publications " & _
"WHERE PublicationID = " & intPublication
Set rstPublications = .OpenRecordset()
End With

With rstPublications
intPubMasterID = !PubMasterID
End With
rstPublications.Close

Set qdfExists = dbsCurrent.CreateQueryDef("")
With qdfExists
.SQL = "SELECT Author FROM Authors " & _
"WHERE [Publication 1] = " & intPubMasterID & _
"OR [Publication 2] = " & intPubMasterID & _
"OR [Publication 3] = " & intPubMasterID
End With

Me.Author.RowSource = qdfExists.SQL
Me.Author.Requery
dbsCurrent.Close
End Sub
Thanks!

Andrew

[Larry Linson]

"darnnews" <an************@gmail.comwrote in message
news:11**********************@l70g2000cwa.googlegr oups.com...

Addendum 2: I fixed it.

Want to share _how_ so that others can learn, too?

BTW, with Publication1, Publication2, and Publication3 all being Fields in
the Authors Table, it would appear your Table Design is not Normalized. I
suggest you consider moving Publication information to a separate Table,
with Foreign Keys pointing back to the Record in the Authors Table. If you
can have multiple Publications per Author and multiple Authors per
Publication, then you likely need a junction Table with Foreign Keys to both
Authors and Publications to handle the many-to-many relationship.

The un-normalized nature of the Table is what led to the "clumsy" WHERE
clause in the Query, checking for the same value in three fields.

Larry Linson
Microsoft Access MVP

[darnnews]

No doubt you are correct... I will think about it and also try and
learn more. If anyone would like to see and/or critique my entire DB,
please drop me a line and I will happily oblige
(an************@gmail.com)

Below the following table list is my undocumented code for my "Add New
Press Clippings" form.

Related to this form, I have the following tables in the DB:

AUTHORS Table:
AuthorID Author First Name Middle Initial Last Name Email Phone
Number Publication 1 Publication 2 Publication 3 Notes

CLIENTS Table (This is non-relational, used purely for driving a menu.)
ClientID Client

CLIPS Table
ClipID Client AuthorID PublicationID Headline Article Text Publication
Date Description Slant Notes Clip Date URL Value Measurement

MEDIUMS Table (This is non-relational, used purely for driving a menu.
I may use it to drive some value calculations later.)
MediumID Medium Rule

PUBMASTER Table (Whenever a user enters new publications, the DB makes
an entry in this table, and then it makes multiple entries in
PUBLICATIONS. The user can enter up to five children, one for each
medium: web, print, enewsletter, TV, radio)
PubMasterID Publication URL

PUBLICATIONS Table
PublicationID Publication PubMasterID Medium ValuePerInch Circulation

Option Compare Database

Private Sub Add_New_Publication_Click()

On Error GoTo Err_Add_New_Publication_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "New Publications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Add_New_Publication_Click:
Exit Sub

Err_Add_New_Publication_Click:
MsgBox Err.Description
Resume Exit_Add_New_Publication_Click

End Sub
Private Sub Clear_Click()

Me.Client = Null
Me.Medium = Null
Me.Publication = Null
Me.Author = Null
Me.Notes = Null
Me.Slant = Null
Me.[Publication Date] = Null
Me.URL = "http://"
Me.Headline = Null
Me.[Article Text] = Null
Me.Description = Null
Me.Measurement = Null

End Sub

Private Sub NoURL_AfterUpdate()

If NoURL Then
Me.URL = "No electronic link is available."
Else
Me.URL = "http://"
End If
End Sub

Public Sub Publication_AfterUpdate()

Dim dbsCurrent As Database
Dim rstPublications As Recordset

Dim qdfExists As QueryDef

Dim intPublication As Integer
Dim intPubMasterID As Integer

Set dbsCurrent = OpenDatabase("c:\documents and
settings\administrator\desktop\PR.mdb")

intPublication = [Forms]![new clips]!Publication

Set qdfExists = dbsCurrent.CreateQueryDef("")
With qdfExists
.SQL = "SELECT * FROM Publications " & _
"WHERE PublicationID = " & intPublication
Set rstPublications = .OpenRecordset()
End With

With rstPublications
intPubMasterID = !PubMasterID
End With
rstPublications.Close

Set qdfExists = dbsCurrent.CreateQueryDef("")
With qdfExists
.SQL = "SELECT Author, AuthorID FROM Authors " & _
" WHERE [Publication 1] = " & intPubMasterID & _
" OR [Publication 2] = " & intPubMasterID & _
" OR [Publication 3] = " & intPubMasterID & _
" ORDER BY Author"
End With

Me.Author.RowSource = qdfExists.SQL
Me.Author.Requery
Me.Author = Me.Author.ItemData(0)
dbsCurrent.Close
End Sub

Private Sub Save_Click()

Dim intCheck As Integer
intCheck = 0
If IsNull(Me.Client) Then
Me.Client.BackColor = 8421631
intCheck = intCheck + 1
Else
Me.Client.BackColor = 16777215
End If

If IsNull(Me.Medium) Then
Me.Medium.BackColor = 8421631
intCheck = intCheck + 1
Else
Me.Medium.BackColor = 16777215
End If

If IsNull(Me.Publication) Then
Me.Publication.BackColor = 8421631
intCheck = intCheck + 1
Else
Me.Publication.BackColor = 16777215
End If

If IsNull(Me.Author) Then
Me.Author.BackColor = 8421631
intCheck = intCheck + 1
Else
Me.Author.BackColor = 16777215
End If

If IsNull(Me.Publication_Date) Then
Me.Publication_Date.BackColor = 8421631
intCheck = intCheck + 1
Else
Me.Publication_Date.BackColor = 16777215
End If

If IsNull(Me.URL) Or Me.URL = "http://" Then
Me.URL.BackColor = 8421631
intCheck = intCheck + 1
Else
Me.URL.BackColor = 16777215
End If

If IsNull(Me.Headline) Then
Me.Headline.BackColor = 8421631
intCheck = intCheck + 1
Else
Me.Headline.BackColor = 16777215
End If

If IsNull(Me.Article_Text) Then
Me.Article_Text.BackColor = 8421631
intCheck = intCheck + 1
Else
Me.Article_Text.BackColor = 16777215
End If

If IsNull(Me.Description) Then
Me.Description.BackColor = 8421631
intCheck = intCheck + 1
Else
Me.Description.BackColor = 16777215
End If

If IsNull(Me.Measurement) Then
Me.Measurement.BackColor = 8421631
intCheck = intCheck + 1
Else
Me.Measurement.BackColor = 16777215
End If

If intCheck 0 Then
MsgBox ("Please entered the all required fields. The ones you
are missing are highlighted in red.")

Else

Dim dbsPR As Database
Dim rstClips As Recordset

Set dbsPR = OpenDatabase("c:\documents and
settings\administrator\desktop\PR.mdb")
Set rstClips = dbsPR.OpenRecordset("Clips")

With rstClips
.AddNew
!Client = [Forms]![new clips]![Client]
![Clip Date] = [Forms]![new clips]![Clip Date]
!PublicationID = [Forms]![new clips]![Publication]
!AuthorID = [Forms]![new clips]![Author]
!Notes = [Forms]![new clips]!Notes

If Me.Slant = "3" Then
!Slant = "Positive"
Else
If Me.Slant = "2" Then
!Slant = "Neutral"
Else
If Me.Slant = "1" Then
!Slant = "Negative"
End If
End If
End If

![Publication Date] = [Forms]![new clips]![Publication
Date]
!URL = [Forms]![new clips]![url]
!Headline = [Forms]![new clips]![Headline]
![Article Text] = [Forms]![new clips]![Article Text]
!Description = [Forms]![new clips]![Description]
!Measurement = [Forms]![new clips]![Measurement]

Dim rstPublications As Recordset
Dim LngPricePerInch As Long
Dim intPubID As Integer
Set rstPublications = dbsPR.OpenRecordset("Publications",
dbReadOnly)
intPubID = [Forms]![new clips]![Publication]

With rstPublications
.FindFirst ("PublicationID = " & intPubID)
LngPricePerInch = !ValuePerInch
End With
!Value = LngPricePerInch * [Forms]![new
clips]![Measurement]
.Update
End With
dbsPR.Close
End If
End Sub
Private Sub Medium_AfterUpdate()

Me.Publication = Null
Me.Publication.Requery
Me.Publication = Me.Publication.ItemData(0)

Me.Description = Null
Me.Description.Requery
Me.Measurement = Null

Me.Author.RowSource = ""
If Me.Publication <"" Then
Me.Author = "staff writer"
Else
Me.Author = Null
End If

Select Case Me.Medium
Case "Web site"
Me.Description = "The online article "
Me.Measurement_Label = "Square Inches"
Case "e-Newsletter"
Me.Description = "The e-newsletter article "
Me.Measurement_Label = "Square Inches"
Case "Print"
Me.Description = "The print article "
Me.Measurement_Label = "Square Inches"
Case "Radio"
Me.Description = "The radio program "
Me.Measurement_Label = "Seconds"
Case "Television"
Me.Description = "The television program "
Me.Controls("Measurement_Label") = "Seconds"
End Select
Publication_AfterUpdate

End Sub
Private Sub Exit_Click()
On Error GoTo Err_Exit_Click
DoCmd.Close
DoCmd.Quit acQuitPrompt
Exit_Exit_Click:
Exit Sub

Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click

End Sub

Private Sub Add_New_Author_Click()
On Error GoTo Err_Add_New_Author_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "New Authors"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Add_New_Author_Click:
Exit Sub

Err_Add_New_Author_Click:
MsgBox Err.Description
Resume Exit_Add_New_Author_Click

End Sub


Larry Linson wrote:

"darnnews" <an************@gmail.comwrote in message
news:11**********************@l70g2000cwa.googlegr oups.com...

Addendum 2: I fixed it.

Want to share _how_ so that others can learn, too?

BTW, with Publication1, Publication2, and Publication3 all being Fields in
the Authors Table, it would appear your Table Design is not Normalized. I
suggest you consider moving Publication information to a separate Table,
with Foreign Keys pointing back to the Record in the Authors Table. If you
can have multiple Publications per Author and multiple Authors per
Publication, then you likely need a junction Table with Foreign Keys to both
Authors and Publications to handle the many-to-many relationship.

The un-normalized nature of the Table is what led to the "clumsy" WHERE
clause in the Query, checking for the same value in three fields

Larry Linson
Microsoft Access MVP

[pietlinden]

darnnews wrote:

No doubt you are correct... I will think about it and also try and
learn more. If anyone would like to see and/or critique my entire DB,
please drop me a line and I will happily oblige
(an************@gmail.com)

Below the following table list is my undocumented code for my "Add New
Press Clippings" form.

Related to this form, I have the following tables in the DB:

AUTHORS Table:
AuthorID Author First Name Middle Initial Last Name Email Phone
Number Publication 1 Publication 2 Publication 3 Notes

CLIENTS Table (This is non-relational, used purely for driving a menu.)
ClientID Client

CLIPS Table
ClipID Client AuthorID PublicationID Headline Article Text Publication
Date Description Slant Notes Clip Date URL Value Measurement

MEDIUMS Table (This is non-relational, used purely for driving a menu.
I may use it to drive some value calculations later.)
MediumID Medium Rule

PUBMASTER Table (Whenever a user enters new publications, the DB makes
an entry in this table, and then it makes multiple entries in
PUBLICATIONS. The user can enter up to five children, one for each
medium: web, print, enewsletter, TV, radio)
PubMasterID Publication URL

PUBLICATIONS Table
PublicationID Publication PubMasterID Medium ValuePerInch Circulation

First, just explain how the real-world entities are related. Not how
they're related in terms of tables, but in real life. (What in the
real world does the PubMaster table relate to? What purpose does it
have?)

Can you explain in plain English how the entities (table names) are
related? One way of going about this is to play fill in the blanks...

Each <nouncan have <1 or many<other noun>

for example:
Each <authorcan write MANY <articles>.

then flip the nouns around.

EACH <articlecan be written by ONE/MANY <authors>

(You use this to determine if you have a many-to-many relationship.)
do that for each pair of "things" in your database that are related in
the real world.
Some will not be directly related, e.g. Author and say Publisher.
They're related through Book or something like that.

Author <writesbook <which is published byPublisher.

If you do this, it's easier to draw out how the DB should look. once
you have the plain english version, find the verbs, then the nouns and
connect the dots. literally draw the pieces into your relationship
diagram. IMO, until you have that down, the rest is a waste of time,
because you can't prove that you can get the answers you're looking for
from the structure you have.

Just my two cents.

[darnnews]

I was just taking a few minutes to respond to Larry's question. I ahve
neither teh time nor the inclination to explain my DB in detail. And I
don't knwo what is a waste of time. The database works well for what I
want it to do.

As I said, anyone who's interested in how it works can email me for the
file.

Regards,
Andrew

pi********@hotmail.com wrote:

darnnews wrote:

No doubt you are correct... I will think about it and also try and
learn more. If anyone would like to see and/or critique my entire DB,
please drop me a line and I will happily oblige
(an************@gmail.com)

Below the following table list is my undocumented code for my "Add New
Press Clippings" form.

Related to this form, I have the following tables in the DB:

AUTHORS Table:
AuthorID Author First Name Middle Initial Last Name Email Phone
Number Publication 1 Publication 2 Publication 3 Notes

CLIENTS Table (This is non-relational, used purely for driving a menu.)
ClientID Client

CLIPS Table
ClipID Client AuthorID PublicationID Headline Article Text Publication
Date Description Slant Notes Clip Date URL Value Measurement

MEDIUMS Table (This is non-relational, used purely for driving a menu.
I may use it to drive some value calculations later.)
MediumID Medium Rule

PUBMASTER Table (Whenever a user enters new publications, the DB makes
an entry in this table, and then it makes multiple entries in
PUBLICATIONS. The user can enter up to five children, one for each
medium: web, print, enewsletter, TV, radio)
PubMasterID Publication URL

PUBLICATIONS Table
PublicationID Publication PubMasterID Medium ValuePerInch Circulation

First, just explain how the real-world entities are related. Not how
they're related in terms of tables, but in real life. (What in the
real world does the PubMaster table relate to? What purpose does it
have?)

Can you explain in plain English how the entities (table names) are
related? One way of going about this is to play fill in the blanks...

Each <nouncan have <1 or many<other noun>

for example:
Each <authorcan write MANY <articles>.

then flip the nouns around.

EACH <articlecan be written by ONE/MANY <authors>

(You use this to determine if you have a many-to-many relationship.)
do that for each pair of "things" in your database that are related in
the real world.
Some will not be directly related, e.g. Author and say Publisher.
They're related through Book or something like that.

Author <writesbook <which is published byPublisher.

If you do this, it's easier to draw out how the DB should look. once
you have the plain english version, find the verbs, then the nouns and
connect the dots. literally draw the pieces into your relationship
diagram. IMO, until you have that down, the rest is a waste of time,
because you can't prove that you can get the answers you're looking for
from the structure you have.

Just my two cents.

[Larry Linson]

"darnnews" wrote

I was just taking a few minutes to respond
to Larry's question. . . .

That's what I thought you were doing.

I ahve neither teh time
nor the inclination to explain my DB in detail.
And I don't knwo what is a waste of time.
The database works well for what I
want it to do.

I didn't try to analyze in detail what you posted, but wondered why you
needed a Clear function, unless it was just to give the user a quick way to
'start over' or unless the Form was unbound.

A database does not _have_ to be normalized to be useful*, but it does make
life easier when you have to do additional querying later.

* And, it's a good thing, considering how many
years after PCs were introduced that we only
had "flat file" databases!

Larry Linson
Microsoft Access MVP

[darnnews]

You are correct... the Clear function is just to give the user a way to
clear all the fields in the form.

Your thoughts on normalizing my authors table made me realize that I
shouldn't even have a "Publication 2" or "Publication 3" since the
logic behind my forms makes authors unique to each publication, and
there is no reason why I need to assign Authors to multiple
publications, so I'm just deleting those fields and editing my code and
a form.

Despite some bugs I know about, I'm happy to say this thing went over
very well with the PR folks at my firm today, and promises to make all
of our lives a lot easier in the near future.

A couple more touches and I can forget about coding for another five
years or so and get back to writing.

The biggest issue I have left is creating RTF reports. I implemented
Lebans' solution to convert reports, until I realized it produced
snapshots.

Then I implemented some code to create an HTML document, and used a DLL
I found to convert the HTML to RTF (DLL courtesy of Max Sautin). But
then I realized that I would need to purchase the complete DLL to
produce large enough documents (30000 symbol limit on freeware).

So now, I just produce HTML documents and let my users copy and paste
as needed, for now.

If anyone has a library to write RTF on the fly, or to convert HTML to
RTF, hook a brother up. I may just purchase Max Sautin's DLL, if the
powers that be want to fork over $160. But I'm not convinced yet.

Cheers,
Andrew

Larry Linson wrote:

"darnnews" wrote

I was just taking a few minutes to respond
to Larry's question. . . .

That's what I thought you were doing.

I ahve neither teh time
nor the inclination to explain my DB in detail.
And I don't knwo what is a waste of time.
The database works well for what I
want it to do.

I didn't try to analyze in detail what you posted, but wondered why you
needed a Clear function, unless it was just to give the user a quick way to
'start over' or unless the Form was unbound.

A database does not _have_ to be normalized to be useful*, but it does make
life easier when you have to do additional querying later.

* And, it's a good thing, considering how many
years after PCs were introduced that we only
had "flat file" databases!

Larry Linson
Microsoft Access MVP