Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Choosing columns to show

Posted on 2004-09-29
23
Medium Priority
?
203 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1500 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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