[italos]

Hello,

on my main form I have several fields, the one I need for this is a calculated field, that takes [StartDate] and [EndDate] and converts it into a number with DateDiff("d";[StartDate];[EndDate]). So far so good.
Now on a sub form I have listed 16 fields (the date range is between a min of 3 and a max of 16 days).
For the first field, I've assigned the Value of StartDate to it (=[Forms]![frmMain]![StartDate] as the control source, and then subsequentially added to that a +1,+2... until +15 (=[Forms]![frmMain]![StartDate]+1 for the second day... ) for the following days to display the date of that day. They do show the correct date (wonder if there is a better way of doing that).
What I want to do is to show only the date fields (on the subform) that correspond to the number of days shown in the field that calculates the days. So instead of showing 16 entries for each record, I want to show only as many days as the calculated field shows.
Not sure if I made myself clear, but I hope I did :)

[ScottGem]

Can you explain the business reasons behind this? I'm not following what you are trying to do or what your purpose is. A screen shot might help as well.

[italos]

Hello Scott,

Attachment 47244

This DB is for a local Tour Operator that asked me to create something to make it easier to manage the booked clients. Each Client (Group) stays here for not more than 2 weeks (thus 16 days).
Once the StartDate and EndDate fields are populated, there's a field on my Main form (Total Days) that counts the days. Based on that, I want to be able to show only the fields Date and Destination on the subforms that fall within this range (some Groups may only stay for 4 or 5 days and there's no point in showing the dates of all 16 days and Destinations on the subfrom after the StartDate).

Attachment 47245

[italos]

Hello Scott,

Attachment 47244

This DB is for a local Tour Operator that asked me to create something to make it easier to manage the booked clients. Each Client (Group) stays here for not more than 2 weeks (thus 16 days).
Once the StartDate and EndDate fields are populated, there's a field on my Main form (Total Days) that counts the days. Based on that, I want to be able to show only the fields Date and Destination on the subforms that fall within this range (some Groups may only stay for 4 or 5 days and there's no point in showing the dates of all 16 days and Destinations on the subfrom after the StartDate).

Attachment 47245

Is it possible to convert the txBoxes into an array?
My thought behind this is something like this
Private Sub Form_Load()
Dim x As Integer
Do While x <= 16
txtD[x].Visible = True 'txtDay1-txtDay16
txtDestination[x].Visible = True 'txtDestination1 - txtDestination16
Next x
End Sub

VBA automatically puts a space between the name and the brackets, resulting in an Invalid qualifier... Is there a workaround in some way?

[ScottGem]

Based on that, I want to be able to show only the fields Date and Destination on the subforms that fall within this range

Ok, that's simple, but you are going about it the wrong way. You should have have at least two tables here. One for the general Tour info and one for the daily information. The Subform should be bound to the daily table with a Foreign Key for the TourID. The Recordsource of the subform (which should be a continuous form) would be a query like this:

SELECT * FROM tblTourDaily WHERE TourDate BETWEEN Forms!mainformname!StartdateControl AND Forms!mainformname!EnddateControl;

The subform would then only display the daily records for that date range. That's what subforms are primarily for, to display data related to the record in the main form.

[italos]

OK I managed to only show the entries that correspond to each Group and it seems to be working fine so far.
However when trying to insert/select something in either field on the subfrom, it gives me this error: You can't assign a value to this object.


The way things are set up:
I've created the subform(sfrmDestinationDetails) as a continuous form (like you suggested) and three tables:
tblDestination - ID(PK), DestinationList(with 33 different values)
tblService - ID(PK), Service(17 values)
tblDailyTours - ID(PK), Day(Date), Destination(lookup->tblDestination multi value), Service(lookup->tblService multi value), EntranceFee(Currency), PaymentType(value list lookup)


under Record source on the subform is this:
SELECT * FROM tblDailyTours WHERE (((tblDailyTours.Day) Between [Forms]![frmInsertGroupDetails]![StartDate] And [Forms]![frmInsertGroupDetails]![EndDate]));

Link Master Fileds: ID
Link Child Fields: ID

Any ideas?

[ScottGem]

First, do not accept the default name for your PK fields. That will only cause confusion, which I believe is the problem here. I name my PKs tablenameID so you would have DestinationID, ServiceID, DailyToursID, etc. I also name my foreign keys the same to make the links clear, so tblDailyTours.Destimation should be DestinationID. You also appear to have another structural problem in that you have controls for Hotel1, Hotel2, etc. If the tour will stop at multiple Hotels you should have a child table like so:

tblTourHotel
TourHotelID (PK Autonumber)
TourID (Foreign Key)
HotelID (FK)

And this should be displayed in a subform.

I'm assuming here that the mainform is bound to a tblGroupDetails which has its own PK. So you should have a foreign key in tblDailyTours; GroupDetailsID. You have a one to many relationship between the Group and the Daily Tours, but the way you have it, it's a one to one relationship which would cause that error.

[italos]

Thank you for your quick response. I'll try to make some modifications and report back then.

EDIT: So I made the adjustments to the tables (named all ID's based on their table) and build the relationship from tblGroupDetailsID to DailyToursID as a one to many and that works. I can select dates and destinations, etc.

Could you elaborate further why the Hotel controls appear to be a structural problem?
I have four fields on the tblGroupDetails (Hotel1, Hotel2, Hotel3, and Hotel4) which get their values from tblAccomodation consisting of one field HotelName(PK) that contains the Hotel list and that seems to be working as I can select different Hotels for each Group.

[italos]

Hey Scott,
you've been incredibly helpful so far and I really hope I'm not starting to annoy you just yet :)
I'm trying to implement what you suggested i.e. getting rid of the structural problems and putting the hotel controls into a subform. I've created the childtable like you said and set the relationships as follows.
Attachment 47278
Is that set up correctly?
I've tried different methods of linking the subform to the mainform (it makes sense to have linked it over the unique ID on the mainform with the unique ID on the subform = GroupDetailsID as Master and HotelID as Child field) but it seems no matter how I do it, I can pick different hotels, but they don't get saved... so I must be doing something wrong.
Could you give me a hand again, please? :)

[ScottGem]

Not quite, tblDailyTours should be a child of tblGroupDetails. HotelID is an attribute of the day, since it could change per day.

I also don't like the use of MVFs.

[italos]

MVFs = MultiValueFields?

[ScottGem]

Yes. To do an analysis by of the items selected in an MVF is more cumbersome then using the normal method of a junction table.