I will give a hug to whoever figures this out.

[jwaguile]

... ... ...

I just spent about 30 minutes writing a post here, taking minutes just trying to come up with the words to accurately describe my problem, just to have the page "you do not have permissions to do this" appear when I try to preview my post, and because this wonderful company thinks internet explorer is good for us, I go back and my entire post is gone.

Here's the short version:

There's a combobox and a listbox. You can choose something from the combobox, hit some other button to add it to the listbox, and after that, that particular thing will no longer show up on the combobox.

The combobox's main row source is a table with 6 elements. So the combobox will have anywhere from 0-6 things to choose from, depending on how many things have already been added to the listbox.

Now for the problem.

I have two tables, table1 and table2. table1 contains the six things that the combobox displays, and nothing else. table2 contains multiple entries per record of my main form.

I am able to get my combobox to display only the entries for which there is not already an entry in table2 for the current record of my main form. But if the current record on my main form doesn't already have at least one entry in table2, then my combobox will display multiple duplicates and stupid crap.

I'm really frustrated now, and this post sucks, maybe I'll update it tomorrow the way I had wrote it the first time, with accurate, specific detail and names and labels for everything.

...

 

[KenHigg]

a. Can you post a picture of yourself so we can determine how much effort we need to put into getting this 'Hug' -

b. Search 'cascading combo boxes' and see if some of that helps.

 

[jwaguile]

cascading combo boxes... I was trying for the life of me to not have to rely on visual basic code for this, I wanted to be able to do it just with the stupid access query...

hm

 

[raskew]

Ken -

You big old sweet thing! Where do you stand on Chapter 8?

Best wishes - Bob

 

[KenHigg]

Ken -

You big old sweet thing! Where do you stand on Chapter 8?

Best wishes - Bob

Where do I need to stand -

 

[raskew]

Wherever you want to, big guy!

Toodles

 

[wiklendt]

i assume you have your combobox bound to a query?

try this: open that query (you can do it from your form: click on the combo box, then go to the rowsource (or similar)), view the query's properties. under "unique values" select "yes".

without knowing your exact setup, this might not work, and depends on what you mean by 'stupid crap', but it's worth a try anyhow. if that doesn't work, are you able to post a copy of your db?

 

[KenHigg]

Wherever you want to, big guy!

Toodles

As long as it's not in the Ozarks?

 

[WayneRyan]

j,


Table1
======
ItemName

Table2
======
id - PK
ParentID - FK to main table
ItemName

MainTable
=========
id - PK
OtherFields


Combo Box query:

Select Distinct A.ItemName
From   Table1 As A Left Join Table2 As B On
       A.ItemName = B.ItemName
Where  B.ItemName Is Null And
       B.ParentID = Me.ParentID

hth,
Wayne

 

[jwaguile]

i assume you have your combobox bound to a query?

try this: open that query (you can do it from your form: click on the combo box, then go to the rowsource (or similar)), view the query's properties. under "unique values" select "yes".

without knowing your exact setup, this might not work, and depends on what you mean by 'stupid crap', but it's worth a try anyhow. if that doesn't work, are you able to post a copy of your db?

!!! !!! !!!

That seems to have fixed it o__o

and i was looking for some kind of "unique values only" property in my combobox properties. ._.

...then i read the last part of wiklendt's signature, like holy crap, i really can give a hug. xD



...

j,


Table1
======
ItemName

Table2
======
id - PK
ParentID - FK to main table
ItemName

MainTable
=========
id - PK
OtherFields


Combo Box query:

Select Distinct A.ItemName
From   Table1 As A Left Join Table2 As B On
       A.ItemName = B.ItemName
Where  B.ItemName Is Null And
       B.ParentID = Me.ParentID

hth,
Wayne

and as far as this is concerned... i didn't think the "Me" keyword was allowed in access query criteria?

 

[WayneRyan]

J,

The "Me." is only valid if you are building a query in VBA.

In a query (Design View) it would be --> Forms!YourForm!ParentID

But either way, you want the Distinct (In SQL-speak), or as you've
seen "Unique" in the Form-Design view.

Glad you have it sorted out though,
Wayne