Solved

Choosing columns to show

Posted on 2004-09-29
23
197 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

837 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