Hello everyone! (1 Viewer)

[konvar]

I am a new member... I come from Greece, i havent used MS Access for a long time. Now I am trying to develop a small project and i hope i can find some help from the experts. In my project there is one basic table (Table1) which contains an autonumber primary key field (long integer) and also one more field which contains a description (text). There is also one more table (Table2) which one has its own autonumber primary key and a foreign key taking values from Table1, as also two date values, the start_date and the end-date. The relationship between the two tables are one to many from Table1 to Table2. I would like to be able to enter new records in Table2 by selecting first a relative foreign key and then by entering the two date values from a combo box which will contain the available periods of time so there will be no conflict , with an already existing record entry, regarding the specific foreign key appearing on the current row.
Table1.Code, Table1.Descr
1 F
2 G
Table2.Code, Table2.field1(=Table1.Code), Table2.DateFrom, Table2.DateTo
1 1 01/01/2020 31/12/2020
2 1 01/01/2018 31/12/2018
3 2 01/01/2017 31/12/2017
By inserting a new record in Table2, through Tables datasheet view, after choosing the Table2.field1 from a limit to list control, the cursor is put on the Combo Box for selecting Table2.DateFrom and I do expect to see only the available dates, meaning that the range dates should not be the ones between the already taken for the specific selection in Table2.field1.
Minimum starting date in the application will be 01/01/2015 and maximum the today's current date,

 

[pbaldy]

Welcome to AWF. I moved your thread to a more appropriate forum since it asked a technical question. I'm not sure what you mean by "see available dates". You could use criteria like this to test if a record being entered conflicts with another.

Overlapping records

 

[konvar]

Hello pbaldy. Thank you for taking action.
in my case there is not only one desired range to compare with starting and ending dates, but many. There are many bookings for the same item and a new one should not overlap with any other already existing booking. Can i upload an example of my project ? and also formulate my question more clearly?

 

[pbaldy]

I would expect the user to enter their desired date range. If you want them to see available dates, you'd have to have defined date ranges stored in a table or something. In other words, if I'm getting ready to enter for code 2, what "available dates" are you expecting to see?

Unless I'm misunderstanding.

 

[pbaldy]

Oh, and you can certainly attach a sample here.

 

[konvar]

In my test project there is one basic table (Table1) which contains an autonumber field (long integer) and also one more field which contains a description (text).
There is also Table2 which consists of an autonumber field, a foreign key related to the autonumber field of Table1, and two date value fields, the start_date and the end-date.
The relationship between the two tables is one to many from Table1 to Table2 respectively.

As there are no forms in the database yet, i am using Tables datasheet view in order to enter a new record.

when inserting a new record into Table2,
at first i do select
an appropriate foreign key (which relates to Table1)
by using a limited to list drop down control.

According to this selection and in order to enter valid values in the two required date fields (start and end date) i would find it usefull to also use a limited to list drop down control eg. A combo box.

Beginning with the start_date combo box I am expecting that the contained date values (dates) will vary according on the foreign key of the current record, meaning that they will not fall in, or conflict with, or overlap, and should not be the ones between the already taken time periods in Table2 in previously entered or already existing record entries for the same foreign key.

So, for example, if we select to enter a new record for code 2 (G) the available date values for start_date should be 01/01/2015..31/12/2016 and 01/01/2018..31/12/2020. Based on that the available date values for end_date can be if start_date < 01/01/2017 then end_date cannot be > 01/01/2017, if start_date > 31/12/2017 then end_date can be between 01/01/2018 and 31/12/2020. This validation is achieved for every code in the Query6_attempt1.
At first I have tried with the query to return whole date values and because too many records appeared in the combo box and was not user friendly, I have split the date field into 3 new fields YEAR, MONTH and DAY in Table2.

I have then created Query6-attempt1, for use as row source in the lookup parameters tab of the combo box
of the Table2.YEAR_FR field, as you can see in the query's result datasheet, the "available dates" are groupped in year format for each one of the codes.

But, as you can see, when I click on an existing record or when trying to enter a new record in datasheet view on Table2, the combo box doesn’t “filter” the dates for the specific foreign key selection on the current record. It keeps showing the whole Query6-attempt1 result including all the item codes.

I would appreciate if you could help with your skills to overcome this difficulty.

 

[pbaldy]

For starters, you won't be able to do most of what you want directly in the table. You don't have any events to work with or real control over combo sources. You should use a form for data entry/edit. The year/month/day fields in table 2 are unnecessary, all can be derived from the date that gets entered.

Are the dates to be entered fixed, like they'll always be Jan 1 through Dec 31? If so, I suppose you could populate a combo with the unused Jan 1 dates. In fact, you could just have the user enter a year that hasn't been used yet. If not, I can't imagine how this could work for you (displaying unused dates). Is there a reason you don't just let the user enter 2 dates and then test them for overlap?