Solved

Choosing columns to show

Posted on 2004-09-29
23
199 Views
Last Modified: 2006-11-17
Hi !

I have queries that returns all the info I need.
However I have different columns for both french and english description.
I'd need code to change the columns I show in my listboxes upon the click of a "english" or "french" radio button....
I guess i'd need to modify columns width and the such as well...
I'd need a sub for it because i'd need to do the same routine for 7 listboxes....where I could pass the listbox name as parameter and the name of both the french and english description fields used in the queries. I'd also need to pass the query name as parameter because each listbox has a different query (and different fields names for the descriptions..)
I'd need code for both a listbox and combo box please.

Thanks
0
Comment
Question by:rouxjean
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
  • 2
23 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 12179863
try this

you need an option group to dothis and name your listboxes as Listbox1...7

Private Sub OptionLanguage_AfterUpdate()

Select Case OptionLanguage

     Case 1    'English
     for i = 1 to 7

    Me("Listbox" & i).RowSource="Select....english query
    next i

    Case 2 'French
       for i = 1 to 7

    Me("Listbox" & i).RowSource="Select....french query
    next i

end case

end sub    
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 12179906
sorry
end case
s/b
end select
0
 

Author Comment

by:rouxjean
ID: 12179985
That won't work ! I said different queries for each listbox...
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 12179993
Create a new, calculated field in the query used by the listbox:

Desc: Iif(Forms!MyForm!MyOptionFrame=1,[EnglishDesc],[FrenchDesc])

MyForm is your form, MyOptionFrame is the option frame where the user selects English or French. I'm assuming here that 1 is English. Now just display the Desc field in your listbox. It will select the correct field from the underlying table/query.

You'll need to requery the combos/listboxes if the user changes the language option.
0
 

Author Comment

by:rouxjean
ID: 12180620
When I load my form, it asks for a query parameter; Forms!MyForm!MyOptionFrame...
If I input 1 or 2, it works but on the form load it just asks it even if my default value is one in my frame...
I'm using this in my query :
===========================================================================
DESCRIPTION: VraiFaux([Formulaires]![frmGenerique]![fraLanguage]=1;[PRODUCT TYPE]![ENGLISH DESCRIPTION];[PRODUCT TYPE]![FRENCH DESCRIPTION])
===========================================================================
I'm also using this to requery when I click on the radio button (it doesn't work)..
=====================================
Private Sub fraLanguage_AfterUpdate(Cancel As Integer)
lstType.Requery
End Sub
=====================================
Can you help ?

Thanks
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12180913
That seems to suggest that fraLanguage or frmGenerique is incorrectly spelled - can you confirm the names of the controls?
0
 

Author Comment

by:rouxjean
ID: 12181004
frmGenerique and fraLanguage are correctly spelled !!! The query works when I enter the numbers manually...doesn't that means that they are correctly spelled ? It seems as if on the form load event, the default value of the frame isn't considered...
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12181124
When you enter the number manually, it just uses the value that you entered. When you said this:

> I'm also using this to requery when I click on the radio button (it doesn't work)..

That also suggests that the name is mispelled as well. Try opening the form, leaving it open and press CTRL+G to go into the VBA window. Type in (into the Immediate window):
?Forms!frmGenerique!fraLanguage

What does it give you?

Also, does the listbox use a saved query or is it SQL directly in the RowSource?
0
 

Author Comment

by:rouxjean
ID: 12181205
It returns a 4006 error if I paste your line or 404 if I paste this : ?Formulaire!frmGenerique!fraLanguage
Listbox use a saved query, frmGénérique (with accents) is the name !
0
 

Author Comment

by:rouxjean
ID: 12181244
I just dont get how I could have done a typo because I used the expression generator to get the objects syntax in the query module...
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12181306
Did you try replacing the form name in the query with the version with accents?

fraLanguage is an option frame right? Does it have option/radio buttons actually attached to it?
0
 

Author Comment

by:rouxjean
ID: 12183097
For the frame, yes it is option, I did it with the wizard.
I changed the query name for "Type" and the form name to "frmSelection" to avoid any confusion....still having the same results...
Query expression :
===========================================================================
DESCRIPTION: VraiFaux([Formulaires]![frmSelection]![fraLanguage]=1;[PRODUCT TYPE]![ENGLISH DESCRIPTION];[PRODUCT TYPE]![FRENCH DESCRIPTION])
===========================================================================
Listbox is named "lstType", option frame is named "fraLanguage"...code used in the fraLanguage is :
=====================================
Private Sub fraLanguage_AfterUpdate(Cancel As Integer)
lstType.Requery
End Sub
=====================================
lstType source's is a query....which is set to "Type". Both options are names "optEnglish" & "optFrench"...optEnglish value is set to 1, while the value of optFrench is 2.

I just don't know what's happening !!!
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12183157
Is the form a subform by any chance?
0
 

Author Comment

by:rouxjean
ID: 12183375
Nope only a form....
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12183541
OK, I'm a little stumped now...

Try putting a textbox on the form. Set its control source to =1. Now change the reference in the query to read:

[Formulaires]![frmSelection]![MyTextBox]

where MyTextbox is the name of the textbox that you just created. Does this still prompt for a parameter?
0
 

Author Comment

by:rouxjean
ID: 12183601
Yes....when the form is load, it's even written 1 in the textbox :
DESCRIPTION: VraiFaux([Formulaires]![frmSelection]![Texte20]=1;[PRODUCT TYPE]![ENGLISH DESCRIPTION];[PRODUCT TYPE]![FRENCH DESCRIPTION])
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12183659
Hmm........

OK, would you be able to change the listbox to use an SQL statement instead? Something like this:

SELECT SomeField1, IIf(Form!fraLanguage=1,[ENGLISH DESCRIPTION],[FRENCH DESCRIPTION]) As DESCRIPTION FROM MyQuery

Obviously, put it as many fields as you need into the statement.
0
 

Author Comment

by:rouxjean
ID: 12183690
Pasted the same query as a sql statement :
==============================================================================
SELECT IIf([Formulaires]![frmSelection]![Texte20]=1,[PRODUCT TYPE]![ENGLISH DESCRIPTION],[PRODUCT TYPE]![FRENCH DESCRIPTION]) AS DESCRIPTION, CATEGORY.[ENGLISH DESCRIPTION]
FROM PFCF031INF RIGHT JOIN ((Catalogue INNER JOIN CATEGORY ON Catalogue.CATEGORY = CATEGORY.CATEGORY) INNER JOIN [PRODUCT TYPE] ON Catalogue.[PRODUCT TYPE] = [PRODUCT TYPE].[PRODUCT TYPE]) ON PFCF031INF.PR_CODE_GENERIQUE = Catalogue.GENERIQUE
GROUP BY IIf([Formulaires]![frmSelection]![Texte20]=1,[PRODUCT TYPE]![ENGLISH DESCRIPTION],[PRODUCT TYPE]![FRENCH DESCRIPTION]), CATEGORY.[ENGLISH DESCRIPTION]
HAVING (((IIf([Formulaires]![frmSelection]![Texte20]=1,[PRODUCT TYPE]![ENGLISH DESCRIPTION],[PRODUCT TYPE]![FRENCH DESCRIPTION]))<>""))
ORDER BY CATEGORY.[ENGLISH DESCRIPTION];
==============================================================================
Same results....
Do you have office97 ?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12183757
No - not:
[Formulaires]![frmSelection]![Texte20]=1
change it to:
[Form]![Texte20]

I don't know what that is in french, I assume:
[Formulaire]![Texte20]

I do have Office 97, but I don't look at other people's databases, I'm afraid.
0
 

Author Comment

by:rouxjean
ID: 12183798
Same thing...it asks ! what the heck i'm dond wrong ?
0
 

Author Comment

by:rouxjean
ID: 12183806
The reason i asked if you had office 97 is that maybe you could post a working demo ??
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12183839
Hmm - in SQL view, shouldn't Formulaires be Forms? I thought Formulaires was only when you were in the query design view.

I suppose I could post something, but I will not be able to do it tonight.
0
 

Author Comment

by:rouxjean
ID: 12183856
Even in the SQL view, it's written Formulaire, not form...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question