By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,938 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

Pulling data from my subform into main form on the fly...

theaybaras
P: 52
Hi everyone,
You've all been such a huge help to me since joining, and I'd just like to take a second to let you know how much I appreciate it! That said, I have another supplication! ;)

I have a db of scientific article citations and data extracted from the papers. When I first made this db I knew nothing of normalization, and that has been a MAJOR pain, as you can well imagine. I have worked to get this normalized and I have just one last hurdle (as far as I can tell...) I am currently using an unbound label to concatenate the values of fields entered in my form into proper APA format citations for the articles that are just generated on the fly as data is entered. I am just not sure how to have this done once my author names are not in the main form in comboboxes that are bound to SEPARATE columns in my db. grr... Wish I hadn't done that to start with! haha.

Anyway, I'll start with table structure (normalization need will be evident!)
and then keep going until I think I've asked my question! If you need any other info or more clarification let me know! I have inserted a before/after image, half to show the part of my form before and half, after normalization. Just to help elaborate. it will be at the very end of the message

<B>table name = tblCitation</b>

Expand|Select|Wrap|Line Numbers
  1. article_key; autonumber; PK
  2. a_author1; numeric; (looks up: tbl_author_list)
  3. a_author2; numeric; (looks up: tbl_author_list)
  4. a_author3; numeric; (looks up: tbl_author_list)
  5. a_author4; numeric; (looks up: tbl_author_list)
  6. a_author5; numeric; (looks up: tbl_author_list)
  7. a_author6; numeric; (looks up: tbl_author_list)
  8. a_author7; numeric; (looks up: tbl_author_list)
  9. a_author8; numeric; (looks up: tbl_author_list)
  10. a_author9; numeric; (looks up: tbl_author_list)
  11. a_author10; numeric; (looks up: tbl_author_list)
  12. a_author11; numeric; (looks up: tbl_author_list)
  13. a_author12; numeric; (looks up: tbl_author_list)
  14. a_year; numeric
  15. a_title; memo
  16. a_journal; numeric; (queries another table)
  17. a_volume; numeric;
  18. a_issue; numeric;
  19. a_pages; string;
  20.  
  21.  
<b>form name = frmCitation</b>
I have 12 comboboxes for the names of authors
text boxes for year, title, journal, volume, issue, and pages.

I also have an unbound textbox to concatenate the proper APA format
citation for the information entered above.
Expand|Select|Wrap|Line Numbers
  1. [a_author1] & (IIf([a_author2] Is Null,'',', ' & [a_author2])) & (IIf([a_author3] Is Null,'',', ' & [a_author3])) & (IIf([a_author4] Is Null,'',', ' & [a_author4])) & (IIf([a_author5] Is Null,'',', ' & [a_author5])) & (IIf([a_author6] Is Null,'',', ' & [a_author6])) & (IIf([a_author7] Is Null,'',', ' & 'et al.')) & (IIf([a_year] Is Null,'',' (' & [a_year] & '). ')) & (IIf([a_title] Is Null,'',[a_title] & '. ')) & (IIf([a_journal] Is Null,'',[a_journal] & ', ')) & (IIf([a_volume] Is Null,'',[a_volume])) & (IIf([a_issue] Is Null,(IIf([a_author1] Is Null,'',',')),'(' & [a_issue] & ')')) & (IIf([a_pages] Is Null,(IIf([a_author1] Is Null,'','.')),', ' & [a_pages] & '.'))
I would LOVE LOVE LOVE to normalize this table structure dealing with
authors... but I need to generate the citation as above.

My question (finally) is:

How can I make this concatenation in the form 'on the fly' during data entry? If I have one field in a subform/table tblAuthors frmAuthors.
How can I pull the authors from the subtable into this unbound label in the main form? My concatenation has to take the authors in the order they are entered.

My concatenation merely takes the first 7 authors listed in the order they were listed in the subform. If there are 6 or fewer authors it will do one thing, and if there is 7 or more authors, another.

For instance: 1-6 author concatenation result (Citation1) would be:


Davis, M., Barad, M., Otto, M., Southwick, S. (2006). Combining Pharmacology With Cognitive Behavioral Therapy: Traditional and New Approaches. Journal of Traumatic Stress, 19(5), 571-581.




7+ authors would be
Monson, C. M., Schnurr, P. P., Resick, P. A., Friedman, M. J., Young-Xu, Y., Stevens, S. P., et al. (2006). Cognitive Processing Therapy for Veterans With Military-Related Posttraumatic Stress Disorder. Journal of Consulting and Clinical Psychology, 74(5), 898-907.
Right now I just pull the field value from the form. I don't know how to do this in the subform.

Please forgive if this wasn't clear. Let me know and I'll better present my information!

Thanks so much!

theAybaras

here's the pic:
Attached Images
File Type: jpg database.jpg (40.7 KB, 336 views)
Jun 3 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,634
You seem to have the Concatenation angle down. You would need to build the concatenated String from the AfterUpdate() Event of the [Author] Field in the Sub-Form, fully referencing the Label Control on the Main Form. This would be the only way to make it 'dynamic'. As an example, in the AfterUpdate() Event of the [a_author2] Field on your Sub-Form, place the following code. Ideally, instead of the code being duplicated for each Control on the Sub-Form, it would pass the Control to a Procedure that would do the dirty work, but that can be ironed out later.
Expand|Select|Wrap|Line Numbers
  1. Private Sub a_author2_AfterUpdate()
  2.     If Not IsNull(Me![a_author2]) Then
  3.        Me.Parent!lblCitation.Caption = Me.Parent!lblCitation.Caption & Me![a_author2] & etc. & etc.
  4.     End If
  5. End Sub
I hope I interpreted your request correctly, if not please let me know.
Jun 3 '07 #2

FishVal
Expert 2.5K+
P: 2,653
You can get a string containing a list of authors having written a simple routine.

Public Function ArticleAuthors(lngArticleID As Long) As String

Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT txtAuthorName FROM tblArticleAuthors WHERE" & _
"keyArticleID=" & lngArticleID & ";"
With rs
.Open strSQL, CurrentProject.Connection, _
adOpenForwardOnly, adLockReadOnly
While Not .EOF
ArticleAuthors = ArticleAuthors & ![txtAuthorName] & ", "
.MoveNext
Wend
.Close
End With

Set rs = Nothing

End Function

tblArticleAuthors:
Primary key: keyID as Long Autonumber (for example)
Author Name: txtAuthorName as Text
Article foreign key: keyArticleID as Long

This assumes that you have table containing author names as text and article id as lookup column with source in separate articles table.
If you have 3 tables (and this should be so if your database is really normalized):
1) table of authors
2) table of articles
3) table of article authors linked with relationship many-to-one to the 2 tables above
you should build a query which returns article ID and corresponding author name and use it instead "tblArticleAutors" in code example.

By the way having write this function and placed it in a module you can use it in queries and expressions.

Concatenation of the latter string are too simple to explain.

GoodL
Jun 3 '07 #3

theaybaras
P: 52
Expand|Select|Wrap|Line Numbers
  1. Private Sub a_author2_AfterUpdate()
  2.     If Not IsNull(Me![a_author2]) Then
  3.        Me.Parent!lblCitation.Caption = Me.Parent!lblCitation.Caption & Me![a_author2] & etc. & etc.
  4.     End If
  5. End Sub
I hope I interpreted your request correctly, if not please let me know.
It seems that you interpreted correctly :) As a bit more clarification, I want to move to 3 tables. the table structure for the first table after normalization would be the same as before just without 12 author spots. Then I have my table of Author names (author_key; PK , author_name; string), I want to have a third table that will have (c_newauth_key; PK, c_articles_key; FK; links to main article table, and c_author; numeric; looks up author_name)

So I would have just one field in my subForm for authors... c_author. I like the sound of your solution, ADezii, but I'm not quite sure how to iron out the rest of it.

Thanks!

theAybaras
Jun 3 '07 #4

theaybaras
P: 52
You can get a string containing a list of authors having written a simple routine.

Public Function ArticleAuthors(lngArticleID As Long) As String

Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT txtAuthorName FROM tblArticleAuthors WHERE" & _
"keyArticleID=" & lngArticleID & ";"
With rs
.Open strSQL, CurrentProject.Connection, _
adOpenForwardOnly, adLockReadOnly
While Not .EOF
ArticleAuthors = ArticleAuthors & ![txtAuthorName] & ", "
.MoveNext
Wend
.Close
End With

Set rs = Nothing

End Function

tblArticleAuthors:
Primary key: keyID as Long Autonumber (for example)
Author Name: txtAuthorName as Text
Article foreign key: keyArticleID as Long

This assumes that you have table containing author names as text and article id as lookup column with source in separate articles table.
If you have 3 tables (and this should be so if your database is really normalized):
1) table of authors
2) table of articles
3) table of article authors linked with relationship many-to-one to the 2 tables above
you should build a query which returns article ID and corresponding author name and use it instead "tblArticleAutors" in code example.

By the way having write this function and placed it in a module you can use it in queries and expressions.

Concatenation of the latter string are too simple to explain.

GoodL
Hi there FishVal I am trying to move to the three table structure as you mentioned in your post. I like the sound of your solution too, although I must admit I'm still a bit ... dumb... when it comes to implementation of routines such as you've suggested. I think I just get a little intimidated by the code since I'm still foreign to it.

Oh, man, I've gotta run! Thanks so much for the suggestion! I will try to figure it out later! Thanks

theAybaras
Jun 3 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.