Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #11 (permalink)  
Old June 2nd, 2008, 10:18 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You'll want to add some code to remove the final "; " from your sLangString. Forgot to mention that.

mmcdonal

Look it up at: http://wrox.books24x7.com
  #12 (permalink)  
Old June 2nd, 2008, 10:31 AM
Authorized User
 
Join Date: Dec 2006
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks so much for your quick response. Elegance does not matter to me, as long as it works!

So just to clarify before I begin (because I am not 100% proficient in VBA) I would have to add a count for each language, ie:

iEnglish = 0
iFrench = 0
etc for all 100+ languages

and repeat the IF statement for the same 100+ languages 7 times (once for each column)?

Also I am not ENTIRELY sure how to incorporate the beginning of the code, from creating the recordsets.
  #13 (permalink)  
Old June 2nd, 2008, 12:23 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Yes, because of the flat table design and number of columns, you will need to create a counter for each language, and you will need to update the code if a language is added.

I would suggest doing this code in modules to reduce the repetitiveness and limit the number of places that you need to do you updates.

Use Public variables so they can be passed between your private Subs and your Public Functions.

I still need you to post table names and column names, and likely values. Is it possible the language names can be misspelled?

I still think you should create a permanent table linked to this main table with a PK, FK to your main table, Text column to hold Area numbers, and check boxes to allow you to check off languages. That would mean redoing some other parts of your application, but the current design is flawed. We can update this table and start using it right away.



mmcdonal

Look it up at: http://wrox.books24x7.com
  #14 (permalink)  
Old June 2nd, 2008, 01:17 PM
Authorized User
 
Join Date: Dec 2006
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Unfortunately I did not create the original table, it is a direct feed from another department that comes in the form of:

Employee Area Language
Smith, John 1 English,French,Italian
Jones, Mary 1 English,Cantonese

I formatted it to list just areas and have each lang in a separate column (I thought it would be easier to manipulate). So I cannot change the source table. The langauges cannot be mis-spelled as they are all selected from a list.

My table names are:
  • tbl_MasterLang which houses the unique areas ("areas") and one column for languages ("lang").
  • tbl_Original which lists all areas ("areas") multiple times, followed by 8 columns ("lang1", "lang2", etc) which may or may not contain a languages in all or some of the columns.

I think you asked me to post the possible languages that can be found? If so, here they are:

Aboriginal - Other
African - Kikuyu
African - Luganda
African - Niger-Congo
African - Other
African - Tigrinya
African - Yoruba
Afrikaans
Albanian
American Sign Language
Amharic
Arabic
Armenian
Assyrian
Bengali
Bosnian
Bulgarian
Cambodian
Cantonese
Cebuano
Chao Zhou
Cree
Creole
Croatian
Czech
Danish
Dutch
English
Estonian
Farsi
Fijian
Filipino
Finnish
Fooklen
French
Fujianese
German
Greek
Gujarati
Gujrati
Hakka
Hebrew
Hindi
Hindko
Hmong
Hokkien
Hungarian
Ilokano
Indonesian
Inuktitut
Italian
Japanese
Kachchi
Kannada
Kazakh
Konkani (GOA)
Korean
Kurdish
Kutchi
Laotian
Latvian
Lebanese
Lithuanian
llongo
Macedonian
Malayalam
Malaysian (Also Malay)
Maltese
Mandarin
Marathi
Nepali
Norwegian
Ojibway
Other
Pashto
Patawa
Persian
Polish
Portuguese
Punjabi
Quebec Sign Language
Romanian
Russian
Serbian
Shanghalese
Sindhi
Singhalese
Slovak
Slovenian
Somali
Spanish
Swahili
Swedish
Tagalog
Taiwanese
Tamil
Telugu
Thai
Tibetan
Toisanese
Turkish
Ukrainian
Urdu
Uzbek
Vietnamese
Yiddish

I have already created all the Select Case and IF statements for each language (simple code in excel), but I am really stuck on the rest...sorry I am not as proficient in VBA as I thought!
  #15 (permalink)  
Old June 2nd, 2008, 02:08 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I think it would be easier to take the discussion off-line. Can you email me you rlast post and we can exchange code and ideas from there?

mmcdonal

Look it up at: http://wrox.books24x7.com
  #16 (permalink)  
Old June 3rd, 2008, 07:19 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I created a table where you can store the languages, and you will need to update this table when a new language is added. I put a Count field in this table to use to hold your counts as we loop through the records.

Based on the tbl_Original with your sample data, I was able to get this to work using this code:

'================================================= ===
'Turn off Query Warnings
DoCmd.SetWarnings False
'Clean out old data
DoCmd.OpenQuery "qryDELETE_MasterLang"
'Append Unique Area Values
DoCmd.OpenQuery "qryAPPEND_Areas"
'Turn on Query Warnings
DoCmd.SetWarnings True

Dim rs As ADODB.Recordset
Dim rsLang As ADODB.Recordset
Dim rsWrite As ADODB.Recordset
Dim sSQL As String
Dim sLang As String
Dim sWrite As String
Dim lArea As Long
Dim sLang1 As Variant
Dim sLang2 As Variant
Dim sLang3 As Variant
Dim sLang4 As Variant
Dim sLang5 As Variant
Dim sLang6 As Variant
Dim sLang7 As Variant
Dim sFinalString As String

'Open the Language table
sLang = "SELECT * FROM tbl_Language" 'for Select Case loop
Set rsLang = New ADODB.Recordset
rsLang.Open sLang, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open outer loop = Each Individual Area
sWrite = "SELECT * FROM tbl_MasterLang"
Set rsWrite = New ADODB.Recordset
rsWrite.Open sWrite, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rsWrite.MoveFirst
Do Until rsWrite.EOF
    'Take first Area number
    lArea = rsWrite("Area")
           'Now open Original where Area = lArea
           sSQL = "SELECT * FROM tbl_Original WHERE [Area] = " & lArea
           Set rs = New ADODB.Recordset
           rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
           'Clear out count in tbl_Language
           DoCmd.SetWarnings False
           DoCmd.OpenQuery "qryUPDATE_LangCount"
           DoCmd.SetWarnings True

           rs.MoveFirst
                Do Until rs.EOF
                       sLang1 = rs("Lang1")
                       sLang2 = rs("Lang2")
                       sLang3 = rs("Lang3")
                       sLang4 = rs("Lang4")
                       sLang5 = rs("Lang5")
                       sLang6 = rs("Lang6")
                       sLang7 = rs("Lang7")
                            rsLang.MoveFirst
                            Do Until rsLang.EOF
                                If sLang1 = rsLang("Language") Then
                                    rsLang("lCount") = rsLang("lCount") + 1
                                End If
                                If sLang2 = rsLang("Language") Then
                                    rsLang("lCount") = rsLang("lCount") + 1
                                End If
                                If sLang3 = rsLang("Language") Then
                                    rsLang("lCount") = rsLang("lCount") + 1
                                End If
                                If sLang4 = rsLang("Language") Then
                                    rsLang("lCount") = rsLang("lCount") + 1
                                End If
                                If sLang5 = rsLang("Language") Then
                                    rsLang("lCount") = rsLang("lCount") + 1
                                End If
                                If sLang6 = rsLang("Language") Then
                                    rsLang("lCount") = rsLang("lCount") + 1
                                End If
                                If sLang7 = rsLang("Language") Then
                                    rsLang("lCount") = rsLang("lCount") + 1
                                End If
                            rsLang.MoveNext
                            Loop
                rs.MoveNext
                Loop

            'Build string from tbl_Language where lcount is greater than 1
            rsLang.MoveFirst
                Do Until rsLang.EOF
                    If rsLang("lCount") > 1 Then
                        sFinalString = sFinalString & rsLang("Language") & "; "
                    End If
                rsLang.MoveNext
                Loop
            'Remove last two characters
            sFinalString = Left(sFinalString, (Len(sFinalString) - 2))

    'Update Table
    rsWrite("Lang") = sFinalString
    rsWrite.Update
    sFinalString = ""



rsWrite.MoveNext
Loop
rsWrite.Close
'================================================= ===

This is a little more elegant than originally suggested. You must maintaint the Language table, though.



mmcdonal

Look it up at: http://wrox.books24x7.com
  #17 (permalink)  
Old June 3rd, 2008, 07:23 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I just noticed here that there is no rsLang.Update statement in the code, so apprently the values are being kept in memory, and are still flushed out with the use of the query before each area is run.

Does this sound right to everyone (query flushing un-updated recordset?) It works accurately anyway.

mmcdonal

Look it up at: http://wrox.books24x7.com
  #18 (permalink)  
Old June 3rd, 2008, 09:42 AM
Authorized User
 
Join Date: Dec 2006
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

For some reason I am getting an error on:

'Remove last two characters
   sFinalString = Left(sFinalString, (Len(sFinalString) - 2))

But when I comment this line out the code runs and outputs correctly. What is the purpose of this line. Can I just leave it out?
  #19 (permalink)  
Old June 3rd, 2008, 09:46 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You can leave it out. It just removes the "; " from the end of the string.

mmcdonal

Look it up at: http://wrox.books24x7.com
  #20 (permalink)  
Old June 3rd, 2008, 09:52 AM
Authorized User
 
Join Date: Dec 2006
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well then the code works absolutely great!! What used to take me DAYS now takes me minutes, I cannot thank you enough mmcdonal!!!




Similar Threads
Thread Thread Starter Forum Replies Last Post
Parameter Entry Page Still Appears yazzy Crystal Reports 1 June 26th, 2007 02:53 AM
(Collection) appears in the list box Tracey BOOK: Beginning Visual Basic 2005 ISBN: 978-0-7645-7401-6 1 April 30th, 2007 09:53 AM
difference between include file & include virtual crmpicco Classic ASP Basics 2 January 23rd, 2006 11:50 AM
Message Appears Only Once When Form Is Opened Ben Horne Access VBA 2 July 5th, 2004 05:15 PM
"Ghost Form Appears" dbkester Access 2 October 3rd, 2003 09:19 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.