Solved

Choosing columns to show

Posted on 2004-09-29
23
198 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

733 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