Display 'Important Message' On Contiuous Form

[mseeker22]

I have a restaurant bookings diary (See attached image) which is currently showing data from 'Restaurant Bookings' table. I have a seperate table named 'Important Dates' which has information regarding important dates within the calender year i.e. Mother Day, Christmas, Jazz Night etc to warn users of potential busy periods when taking new bookings.



I would like to display this message in a text box on the top left hand side of the 'Restaurant Bookings Diary' form when that date in the diary is displayed but can't seem to get it to do it.

It seems I can't add a subform to a continuous form and when I change the record source to the Table and Field in the 'Important Dates' table it shows #Name? in the text box.

Any ideas?

[RuralGuy]

Is that picture you posted a Continuous Form or a Single Form with a Continuous Form SubForm?

[mseeker22]

It's single form with the upper parts in 'form header' and the bookings section in the 'Detail' part of the form . . . there was a reason for this, but its so long ago I forget why! :-)

[RuralGuy]

That's what I thought. Have you considered using a DLookup() in the Current Event of the form to fill that "Important" TextBox?

[mseeker22]

I had, but my VBA is v sketchy and not sure how to write the code for it. I am doing as much VBA code as I can but kinda learning each new function as I come across it!?

[mseeker22]

I presume something like =DLookUp("[ImportantDateName]", "Important Dates") . . . but I don't know how to link that to the actual text box?

[RuralGuy]

I'll help you. What are all of the object names involved? [Important Dates] table and field names, [Enter Date] control name on this form, [Important Message] control name on this form,

[RuralGuy]

You might even be able to join the two tables on the date and eliminate the DLookup() code completely.

[mseeker22]

Ok Thanks, the details are as follows:

Tbl Name: Important Dates
Field Names: ImportantDateID - Auto, ImportantDateName - Text, ImportantDateNotes - Text and ImportantDate - Date/Time formatted to 'Short Date'

Frm Name: Restaurant Bookings Diary
Data Source: Restaurant Bookings Query Criteria -[Forms]![Restaurant Bookings Diary].[DiaryDateEntry]
Date field: DiaryDateEntry
Important Date Name tbo: ImportantDateName
Important Date Notes tbo: ImportantDateNotes

When creating the tables I'd hoped that would have simply been a case of using the relationship between the dates on the two tables to link the data together. I may have done something wrong but can't figure out what exactly.

I tried adding the 'Important Dates' table into the 'Restaurant Bookings Query' so that the dates what link together but again it didn't work properly.

I appreciate your help, thanks

[RuralGuy]

Can you use the Relationship menu to join the two tables on DiaryDateEntry/ImportantDate fields? You are going to want an outer join so that *all* the records from the [Restaurant Bookings Diary] table are included and only matching records from the [Important Dates] table.

[RuralGuy]

I'm going to be leaving soon for the rest of the day.

[mseeker22]

The tbo 'DiaryDateEntry' is UnBound, the user enters the chosen date into that tbo and on the 'OnClick' function of the 'Search' command button the form requeries it . . . hmmm, maybe thats the problem . . should I be using the 'Booking Date' field which is in the the 'Restaurant Bookings Query' to link to the 'Important Date'?? The reason I didn't do this is because of the continuous form, i.e. the Important date message is linked to the date, not the booking record . . if that makes any sense.

[RuralGuy]

Isn't there a datefield in the [Restaurant Bookings Diary] *table*?

[mseeker22]

Yes there is, but . . . when linked to that, two things happen. 1) It stills displays #Name? and 2) it only displays it when the date that has been searched for actually has records in it . . . this is what I meant on the last post, the message needs to show regardless of whether there are any records.

Because of this I assumed that there was/is no point in linking 'Important Dates' to 'Restaurant Bookings' in any way.

In some respects they are not actually related anyway . . . I just a little confused

[RuralGuy]

Will the relationship menu allow you to join the two tables on the date field?