Solved

Choosing columns to show

Posted on 2004-09-29
23
194 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 119

Expert Comment

by:Rey Obrero
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
sorry
end case
s/b
end select
0
 

Author Comment

by:rouxjean
Comment Utility
That won't work ! I said different queries for each listbox...
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
That seems to suggest that fraLanguage or frmGenerique is incorrectly spelled - can you confirm the names of the controls?
0
 

Author Comment

by:rouxjean
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rouxjean
Comment Utility
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
Comment Utility
Is the form a subform by any chance?
0
 

Author Comment

by:rouxjean
Comment Utility
Nope only a form....
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Same thing...it asks ! what the heck i'm dond wrong ?
0
 

Author Comment

by:rouxjean
Comment Utility
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
Comment Utility
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
Comment Utility
Even in the SQL view, it's written Formulaire, not form...
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now