Make an item of equipment unavailable in a list when it is selected on a date

[Lily21]

Hello,
I'd like to know if there is a way to block the user from choosing a equipment from a drop-down list if it has already been selected at a date range.



So I have a form where the user can choose an equipment and a start and end date for its use. So I would like the equipment to no longer be available in the list if we choose another date that falls within the date range when the equipment is already picked up.

Ps: I'm really not comfortable with VBA

thanks a lot for your help

[Minty]

What is the structure of your tables / data?
This could be quite simple or complicated depending on your tables and the relationships?

Do you have a list of equipment and a separate table of equipment rentals?

[davegri]

Afraid you'll need to prepare yourself for some discomfort.
We need to know more about your table structure, showing how an equipment is already selected, and how the form appears to the user making selections.
Best way if for you to upload your DB here for analysis.

[Micron]

I think you would have to filter the combo list with a query or sql that checks if the user entered date falls between any start and end date for that equipment. You could build and test this query and verify it works correctly before setting the combo row source to that query. There might be other options, such as a subform showing the in/out dates for the chosen equipment. Then users could see what's available at a glance.

[Lily21]

Hi,
Heres my base, I put pictures of the tables who are related to what I want to do (sorry its in french)

Équipement = the table of equipment (id, name of the equipment)
Réservation_équipement = the table related with the équipement table (id, id_equipment, start date of reservation, end date of reservation)


And ''créer_projet'' its the table and the form ''créer_projet Form'' where the user can entry the dates and the equipments and others stuff that it dont impact anything in what I want to do.


Thank you for helping me

[Lily21]

Hi,
I put my DB down for analysis
Thank you

[Lily21]

What is the structure of your tables / data?
This could be quite simple or complicated depending on your tables and the relationships?

Do you have a list of equipment and a separate table of equipment rentals?

Yes I have something like that, I put my DB down below if you can look at the structure, I dont know if I need to change something in the structure to make it more easier.

[davegri]

Main table has 2 multivalue fields holding the reserved equipment and the startdate-enddate all in the same record.
Making comparisons against that structure for new reservation conflicts would be monumental task, i.e. comparing items in a multivalue field versus items in another multivalue field involving 2 date ranges.
Have a look at this: Multivalued Fields - Mendip Data Systems

[Lily21]

How I can do differently? Because I really need that the user can choose more than 1 equipment

[davegri]

You need to set up a main table, the projects, and a child table to hold reserved equipment. The child table would hold ONE piece of equipment in each record.
Your current structure is complicated by having equipment in two tables. Much simpler to combine into one table.

[Lily21]

You need to set up a main table, the projects, and a child table to hold reserved equipment. The child table would hold ONE piece of equipment in each record.
Your current structure is complicated by having equipment in two tables. Much simpler to combine into one table.

I'm lost
My main table is = créer_projet
My child table ? : do I need to keep equipment table or reserved equipment ? witch one I need to delete ?


If I understand I have to put in my équipement table the field name that I put in my Reservation table ? so I can delete the reservation table?
So my child table will be : Équipement (id,name,start date reservation, end date reservation)

[davegri]

I think creer_project would be the child table, with Projet being the main table. Some fields in creer_Project should be moved to Project.
The table lookups should be removed from creer_project. Those lookups should be done from the form's comboboxes.
I can't respond again until this afternoon, as prior plans interfere.

[Micron]

Well, it should be possible to come up with something or else there'd be absolutely no point in anyone using mvf or nothing would ever work for anyone. If the form is open and on a record that has values in both date fields, this would return a record matching the record on the form:

SELECT Créer_Projet.Id, Créer_Projet.[Chef_d'équipe].Value, Créer_Projet.Date_Début, Créer_Projet.Date_Fin
FROM Créer_Projet
WHERE (((Date_Début)>=Forms![Créer_Projet Form].Date_Début) AND ((Date_Fin)<=Forms![Créer_Projet Form].Date_Fin));

So if DLookup in code returned a record, then it is loaned out. Not saying the db should not be re-designed, just saying it's quite possible to work with mv fields. Such queries need to use the .Value property of a mvf.

[davegri]

Perhaps Micron is on the right track. I've never worked with MVFs, so if his approach is workable, it appears to be much simpler than redesigning the DB.

[Lily21]

Thank you so much this query work and it is perfect for what I want to do.
But what I can do so that the user can not choose the equipment that the query return?