Very hard to impossible? combobox challenge. 1

[newyorkny]

Good morning:

I want to create a combobox that selects from two dates...sounds simple?

Here is the way things are:

SfmHoliday has what is currently a txtbox: txtHolidayDate which relates to HolidayDate in tblHoliday.

tblHotel has TWO fields containing the dates that are available: Date1 and Date2.

HotelCode is a foreign key in tblHoliday and primary key in tblHotel.

Is there some way to feed a combobox with Date1 and Date 2 on sfmHoliday?

Thank you very much for your thoughts! (yes, I understand this isn't the greatest design, but we work with what we inheirit sometimes).

Thanks again! NY

 

[TheAceMan1]

How are ya newyorkny . . . . .

Have you tried using the [blue]Combobox Wizard![/blue]

See Ya! . . . . . .

 

[eksortso]

I'm making a few assumptions here, newyorkny. Correct me if I'm wrong.

First, Is HolidayDate the primary key on the table tblHoliday? If not, we could end up with more dates than we really want.

Second, do you want your combobox to show only those two values of Date1 and Date2 that relate to the HolidayDate and its HotelCode? The way that you presented your facts suggested that to me.

Here's what I came up with. Make your combobox (say, cmbHotelDate), set its Row Source Type to "Table/Query", and set RowSource to the following UNION query:

SELECT tblHotel.Date1 AS HotelDate
FROM tblHoliday
INNER JOIN tblHotel ON tblHoliday.HotelCode = tblHotel.HotelCode
WHERE tblHoliday.HolidayDate = [txtHolidayDate]
UNION ALL
SELECT tblHotel.Date2 AS HotelDate
FROM tblHoliday
INNER JOIN tblHotel ON tblHoliday.HotelCode = tblHotel.HotelCode
WHERE tblHoliday.HolidayDate = [txtHolidayDate]

Now, in the AfterUpdate event procedure for txtHolidayDate, include this line:

Me.cmbHotelDate.Requery

Also make sure to requery cmbHotelDate whenever the value in txtHolidayDate is changed directly in the form's code.

Hope this helps.

 

[newyorkny]

ekortso:

Thanks for your great efforts. I will take a peak at the code in one sec.

tblHoliday:
HolidayDate is NOT a primary key here. HolidayDate is just a humble field that is currently fed by txtHolidayDate.

It is not linked in any way to Date1 and Date2 currently.

Our specialists are just taking one of the two dates and typing it in right now.

tblHoliday and tblHotel are related via the HotelCode field, which is a primary key in tblHotel and a for key in tblHoliday.

In plain english, our people have been assigned a hotel near them. We store which hotel that is in the field HotelCode which is as above. In the table tblHotel, there are two dates (which have been preset) and the person chooses one of them. The data entry specialist then types this date in in HolidayDate!

Sorry if that isn't clear...I realize it's somewhat of a whacky arrangement, which is why I am seeking to get the combo to "pull" the dates from tblHotel rather than to have this manual deal.

Thanks for your time!

NY

 

[eksortso]

newyorkny:

Okay, then we should be fairly close to what you want.

We need to get to the HotelCode in some efficient manner. Since HotelCode is the primary key in tblHotel, we could make this a lot simpler if you've got the HotelCode on the same form.

Or if you have all the fields in the primary key on tblHoliday readily available, you would just need to change the WHERE clause in the SQL code in two places so that you can get the correct HotelCode:

WHERE tblHoliday.HolidayDate = [txtHolidayDate]
  AND tblHoliday.OtherKeyField1 = [txtOtherKeyField1]
  AND tblHoliday.OtherKeyField2 = [txtOtherKeyField2]
  ...

 

[TheAceMan1]

newyorkny . . . . .

If by chance your looking to make a single column out of Date1 & Date2, then you need to have a look at [purple]Union Queries[/purple].

See Ya! . . . . . .

 

[newyorkny]

Thanks for the awesome help, guys.

I've been having trouble with the board all day or would have been back sooner.

Ekorts: that's perfect.

Thanks again! I learned a lot. NY

 

[eksortso]

Glad to help out, NY!