Hi Peter, After having studied some books I now understand better what you acyually ment with SQL. Here are the two SQL statements for Northwind:
SELECT Kategorier.Kategorinr, Kategorier.Kategorinamn FROM Kategorier ORDER BY Kategorier.Kategorinamn; SELECT Produkter.Produktnr, Produkter.Produktnamn, Produkter.Kategorinr FROM Produkter WHERE (((Produkter.Kategorinr)=IIf(IsNull([Forms]![frmSelect]![cboKategorival]),[Kategorinr],[Forms]![frmSelect]![cboKategorival]))) ORDER BY Produkter.Produktnamn, Produkter.Kategorinr; Regards/Ove --- In [email protected], "Ove Billing" <[EMAIL PROTECTED]> wrote: > > Sorry! I posted the same message twice. First from OE but as it did > not appear I sent it again directly from this list. But later the OE > message appeared, 3 hours delayed./Ove > > --- In [email protected], "Ove Billing" <oy@> wrote: > > > > Hi Peter, > > These SQL statements are in my two examples and cbo1/cbo2 > (House/Room and Categori/Product) > > Example cbo1 cbo2 > > House ex qryHusLista qryRumsLista > > Northwind qryKategorilista qryProduktlista > > (Kategori/Product) > > > > I hope this is what you meant. > > > > When this works, how do I use it in the form where the user is going > to enter his record values? A new form, I guess, with the main table's > filelds. > > Regards > > Ove > > > > > > ----- Original Message ----- > > From: phoogenb > > To: [email protected] > > Sent: Thursday, April 19, 2007 8:02 PM > > Subject: [ms_access] Re: Form design with choosen records > > > > > > Hi Ove, > > > > I take that back. You shouldn't have to post the whole file. I just > > meant for you to post the SQL statement that you are using for the > > combobox's row source property. Just copy it and paste it into your > > message ;-) > > > > Peter > > > > --- In [email protected], "phoogenb" <phoogenb@> wrote: > > > > > > Hi Ove, > > > > > > Post it at groups.yahoo.com: log in there and go to the ms_access > > > group. On the left, you'll see a "Files" link, where you can > > upload > > > a sample to illustrate your problem. > > > > > > Peter > > > > > > --- In [email protected], "Ove Billing" <oy@> wrote: > > > > > > > > Hi Peter, > > > > Your advice to use semicolon was successful for the criteria, > > that > > > was > > > > now accepted. However, the main problem remains. Now, though, > > > nothing > > > > is displayed in the second combobox. > > > > Many thanks for your invitation to send you data. However, I do > > not > > > > know how to furnish you with data and what you need for helping > > me. > > > My > > > > test.mdb is only 244 kb so it is easy to send but to what address? > > > > Attachments seem not to be allowed here. > > > > Best regards > > > > Ove > > > > > > > > --- In [email protected], "phoogenb" <phoogenb@> wrote: > > > > > > > > > > Hi Ove, > > > > > > > > > > I think it may be because formula arguments need to be > > separated > > > > > with semicolons (;) for locales with a decimal comma (like most > > > of > > > > > the non-English-speaking world, including Sweden). > > > > > > > > > > So instead of IIf(Arg1, Arg2, Arg3) use IIf(Arg1; Arg2; Arg3). > > > > > > > > > > I'm not very well familiar with the Northwind examples. If > > that > > > > > doesn't work, try posting the SQL here. I will have a look at > > it > > > > > (if someone else doesn't get to it before I do). > > > > > > > > > > Good luck, > > > > > > > > > > Peter > > > > > > > > > > --- In [email protected], "Ove Billing" <oy@> wrote: > > > > > > > > > > > > Thanks Peter for your suggestions. > > > > > > I have tested the method given in kb 209576 but have failed. > > > Your > > > > > first mentioned method and the one given by Jackie Home are too > > > > > general for me but the kb being more detailed was easier. > > > > > > After having typed everything correct in my Swedish edition > > of > > > > > Northwind and Acc2000 and thus no error messages appeard any > > more > > > > > the result was that all Rooms appeared in the the second cbo > > > > > irrespective of the setting in cboHouse. I tested both with my > > > own > > > > > tables and with those in Northwind with equal result. > > > > > > Under 3. in the kb a Criteria is given that seems to be > > > incorrect. > > > > > With both bases I get an error message that tells me that the > > > comma > > > > > at the end of the first line is not correct (when trying to > > save > > > the > > > > > query the message comes and the comma is marked. On the other > > > hand > > > > > the criteria is, I guess, not essential for the end result, > > only > > > a > > > > > way to take care of the situation when nothing is selected in > > > cbo1. > > > > > > Do you have any comments on this or could you possibly > > suggest > > > > > another method in some detail, being possible for me use? > > > > > > Best regards > > > > > > Ove > > > > > > > > > > > > ----- Original Message ----- > > > > > > From: phoogenb > > > > > > To: [email protected] > > > > > > Sent: Wednesday, April 11, 2007 4:45 PM > > > > > > Subject: [ms_access] Re: Form design with choosen records > > > > > > > > > > > > > > > > > > Hi Ove, > > > > > > > > > > > > One possible solution: A form displaying tblHouse records, > > > and > > > > > > containing a subform showing tblRoom records. If you set it > > > up > > > > > > properly, Access will handle the filtering of the subform > > > > > automatically. > > > > > > > > > > > > Something else that might be of use: A pair of combo boxes, > > > > > where the > > > > > > user selects a house in the first one, and then the second > > > one > > > > > shows > > > > > > only those rooms that belong to the chosen house. There's a > > > > > > description of how to do this in the MS Knowledge base > > ("How > > > to > > > > > Create > > > > > > Synchronized Combo Boxes"): > > > > > > > > > > > > http://support.microsoft.com/default.aspx?scid=kb;en- > > us;209576 > > > > > > > > > > > > Good luck, > > > > > > > > > > > > Peter Hoogenboom > > > > > > > > > > > > --- In [email protected], "Ove Billing" <oy@> wrote: > > > > > > > > > > > > > > I am not very skilled at Access but try anyway to help a > > > small > > > > > local > > > > > > > museum to erect a base with their objects. > > > > > > > One problem to handle (with simplified examples): I think > > > of > > > > > having a > > > > > > > tblHouse with 4 records (H1, H2, H3 and H4) and a tblRoom > > > with > > > > > many > > > > > > > rooms (R1, R2 ...), some in H1, others in H2 etc. How do > > I > > > > > construct a > > > > > > > form where only rooms in H1 are visible or choosable when > > > first > > > > > > > choosing H1 etc. ? Is this possible? Other ways? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > >
