remove from list

[ACCESS NEWB]

i have item assignement db with 3 tables ,employees,items and assign.
On my form u chose from combo box item u want to assign to certain person and enter date when it is assigned.
Now in my assign table i have takedate and returndate fields.
Is it posible to remove from combo box items that already have takedate but not returndate entered .( I just want to eliminate chance same item is assigned on 2 persons)

 

[namliam]

Sure it is, just add a where clause to your combobox rowsource (presuming the rowsource is a query)

 

[ACCESS NEWB]

actually its not from query.
Its from item table where all items are.
Is it doable that way,if not could u be bit more specific,cause my vba knowledge (or sql) isnt very impresive

 

[namliam]

"Its from item table where all items are."

That means that if an item is added to your table the new record is automagicaly beeing added to the Combo as well... Isnt it??
Offcourse you have to "refresh" the combobox for the new value to appear

Combobox.requery will refresh/requery the combobox to fetch any new values.

 

[ACCESS NEWB]

All items i am choosing from are already in table ITEMS.I just pick 1 of them and assign it to PERSON from other table,and add date when i did that.
So I just use form to link those 2 and add date.I would like to not see items that are already signed for but not returned.

 

[namliam]

So your rowsource is a table... change it to a query then... and include the fact you dont want to see antything that is allready signed for.

 

[ACCESS NEWB]

Is there any way u can be more detailed.
Ok I create query from which I will take items.
Each items have 2 dates assigned to it,date when some1 pick it up and say when someone is turn it in.So how would that look in my query (code pls) if I want to exclude items which does not have Returndate entered.

 

[namliam]

You can make the query in the query designer and use that query as rowsource for your combo...

 

[ACCESS NEWB]

i made 1 query and use it as subform on turn in form so i can see all items some1 signed for.
Now was wonderinf could i do the same for sign in ,kinda check to see if item doesnt have sign in date filled in ASSIGN table or something?

 

[namliam]

Well kinda... something like that I guess... except different.

If you can make the one you can make the other?

 

[ACCESS NEWB]

but if i make it that way,another problem occur,the items i already assigned once before will have both dates filled so I donno how to bypass that problem

 

[namliam]

And what would have to happen to those things???

What should happen if they have allready been assigned??

 

[ACCESS NEWB]

ok
in my sign out form i have subform which is made by query which check if item assigned to some1 have anything writen in returndate ,if do then it doesnt show in that subform,
but how to do similar in sign in?

 

[ACCESS NEWB]

nothing ,i need em for history
but what criteria should i use to remove ones i already assigned before?

 

[namliam]

Will you please stop useing some1 and em and things like that ?? I find it very annoying

It is just a little tweaking you need to do to your first query...
Try to get it, then if you cannot figure it out. Post what you got and we go from there.

 

[ACCESS NEWB]

Now please go through your posts and tell me how did you helped me?
All the time I thought that you do not understand my problem,but you never said that and all you did at the end is complaining on my typing shorcuts.

 

[namliam]

Now please go through your posts and tell me how did you helped me?
All the time I thought that you do not understand my problem,but you never said that and all you did at the end is complaining on my typing shorcuts.

Sorry?

I think each and every post has been helping you one step down the line...

My last suggestion even was for you to post what code you DO have and see if we can go from there...

As I understand your problem you have Items that need assigning.
Some Items are not assigned yet, you want to see these in the combobox
Some Items are assigned allready, you dont want to see these in the combo.
Some Items have been assigned in the past, but have returned it. I still dont know what you want to do with these? I presume you want to see them (again) as they may be reassigned, right?

Now you say you have something that comes close but is not quite good enough. So it is just a matter of twaeking that query.

However you seem to want me to magicaly make up a query that will enable you to copy paste my solution into your DB.

 

[ACCESS NEWB]

First of all I owe you apologie.
I am on this DB of mine for last few days ,and getting close to solution and then again opening new problems sometimes can be frustrated.
I kinda redesign my DB,what I did I add another field in my items table which can have values 0 and 1 ,representing is that item available or not.I created a query to extract only available items.I then use that query in form for entering new assignment and thats where i get stucked again.
Once I chose item from a list of available items and filling all other fields in form, how can i automatically update that query that contains available items (present problem is that if i dont close form and want to do another item assignment item i just assign before will still be in a list as available).
So how can i update query ( i presume) with a click on ADD NEW button on my form

P.S.sorry once again if I offend you in anyway

 

[namliam]

P.S.sorry once again if I offend you in anyway

You post 2 sentence questions which are hard to comprehend and then EVEN stick me with a bad review while I am seriously investing time to try and help you???
You are presuming I know all about your DB that you do. However in reality your are a the keyboard and I have never even seen the database in question.
So sorry if I do not have another talent in Telepathy so I can "beam" accross the interstellar galaxy what it is you want/need/must do!
Now that that is out of my system....

Redesigning your db probably wasnt necesary as you can appearently select the records you need/do not need in your combo by the dates that are filled or not filled in.

To answer your question you are asking now

So how can i update query ( i presume) with a click on ADD NEW button on my form

I only need to quote one of my earlier (but appearently uninformative/inpolite/unhelpfull/whatever ! ! ! ! ) posts for you...
Please see post #4 to find

Combobox.requery will refresh/requery the combobox to fetch any new values.

 

[ACCESS NEWB]

thats it now just be so kind ,even without super powers,tell me where do i enter that combobox.requery