Combo box update in a form

[dimitris9]

Hi, I need some help please.

I have a form with a combo box in it.
The combo box takes its values from a table through a one to many relationship.

What I need, if possible, is when I select a value from the combo box, in the next form entry this value to be gone from the combo box.

For example, say I have 4 values 10, 11, 12, 13 in the combo box. I chose 12 for my entry. In the next entry, when I go to the combo box I want it to have 3 values, 10,11,13. And so on. In the next to have 2 values and so on.

I hope you can understand what I am asking.

Thank you in advance for the help.

 

[burrina]

I don't understand the reasoning behind this! What will you do when you run out of values?
Here is a link that may be what your looking for.
https://support.microsoft.com/kb/132026?wa=wsignin1.0

 

[dimitris9]

Thanks for the reply.
Let me explain a bit better why I need this.
I have build an inventory database. My job is fur coat manufacture.
So every day I add to my inventory new products I made. They all have a barcode which for ease of use in the database I made it to be the primary key of the coats table which is a five digit autonumber field.
These coats are being shipped every week to shops I work with.
So I have an export table containing the fields: shop name (takes values from clients table), coatid(takes values from coats table, coatid), shop index Number, and date of export.
So,coatid is a unique value and can only be one coatid in all the shops and my factory.
So when I choose it for export it shouldn't be available to choose it again.
I hope you can see what I am doing here.

If you believe there is a better way to do it I am all ears!

Thank you.

 

[AlexN]

I would create a query for the available coats, that would have a Yes/No field. Everytime I’d send a coat to a shop this field would be set to No.
Then the coatid value in the export form would take values from this query WHERE the availability field is "Yes".
Hope it’s understandable.

 

[dimitris9]

If I do it like that, I would have to add another field to my coats table in order to make the query, right?
Every time I am making an export though I would have to tick the checkbox to indicate that the coat is gone.
This might work.
I will try and let you know.
Thanks.

 

[dimitris9]

It doesn't work.
I tried adding a new yes/no field which I called availability, made the query with all the fields from export table and the availability field from coats table (they are linked by the relationship between CoatID), marked to show only avalable items, but when I run the query the field coatID shows everything.
Any suggestions?

 

[AlexN]

I'm perfectly sure it has to work. Maybe you forgot to make the availability field, criteria for the query.

 

[burrina]

I suggest a Status field for the Coats table. After the order is shipped, then update the status to shipped,sold or whatever you choose. The form is set to show ONLY coats that have a status other than shipped,sold or whatever.

P.S. You can use a update query after adding the newly created field and set the orders as you wish. I would also make status a number field with a default value of 1.

HTH

 

[AlexN]

I suggest a Status field for the Coats table. After the order is shipped, then update the status to shipped,sold or whatever you choose. The form is set to show ONLY coats that have a status other than shipped,sold or whatever.

P.S. You can use a update query after adding the newly created field and set the orders as you wish.

HTH

This is a more professional and more user friendly solution.
Thanks

 

[dimitris9]

I am a little lost!
Say the status field, is the availability field I already made.
The query with the availability field and everything from the export table doesn't work for some reason.
AlexN, how do you make the availability field as a criteria?
Burrina, how do you make the update query to work?
Thank you all.

 

[AlexN]

I am a little lost!
Say the status field, is the availability field I already made.
The query with the availability field and everything from the export table doesn't work for some reason.
AlexN, how do you make the availability field as a criteria?
Burrina, how do you make the update query to work?
Thank you all.

Well, if that is not a problem for you, upload your little database (attach it) and I will show you both ways (mine and Burrina's).

 

[burrina]

So, your form's record source is set to only display records that have a status of not shipped or whatever your criteria needs by default.

You have use a update query to change the status after your status change.

DoCmd.SetWarnings False
DoCmd.OpenQuery "YourQueryNameHere"
DoCmd.SetWarnings True

HTH

 

[dimitris9]

This is the database.
Thank you

 

[burrina]

I don't download db's, sorry! I'm sure someone will, they always do.

 

[AlexN]

This is the database.
Thank you

Good morning dimitris9. Got the database, I'll come with a solution as soon as I have one.

 

[AlexN]

Well, in the attached file you'll find what I came up with, at a first approach. Lucky I'm Greek too. Check out the qryAvailablePalta and the frmExport.
The problem is, that each time you input a new export, you have to run to the Palta TBL to un-tick the Available field.
Your database needs a completely different structure.
More time given, and with a detailed description of what you want (now and in the future) from your database, I'll come up with a more effective and more useful solution.

 

[dimitris9]

Thank you, I just saw your message as I was away in a business trip.
I will check it a bit and let you know.

P.S. I know you are Greek but for the sake of the forum I kept all answers in English.
Eυχαριστώ

 

[dimitris9]

I finally solved it! (Actually in a very similar manner, not what I wanted exactly, but pretty close)
It is a good thing to leave everything for a while to clear your head and come back fresh.
So, after all, what I did was very simple.
I have a value "available jackets" in my coats table. It is a yes or No field. I then created a query to find the available jackets. Then in my export form I made the CoatID select its value from the coatID field from the available jackets query.
Anyway, thank you all for the help.