Show table data based on date field?

[adh123]

I have a form that each day needs to be filled in by staff of their activities.

By selecting a date, I want to the textbox to display the contents of the comments memo pad field in the table (tblToday...columns are t_date and t_comments).

My very limited access and previous SQL knowledge has eluded me and cannot fathom how to get the text box to show data based on the date selected, any suggestions?

 

[CJ_London]

you would have a criteria along the following lines

WHERE tblMemo.MemoDate=[Enter Date Required]

 

[adh123]

Hi CJ

Sorry I could not get this to work :s

I have redesigned the form to perhaps make this simpler...
frmNotesAdd allows someone to add notes to a date not yet entered.
frmNotes will allow someone to update existing notes - this is the one I am stuck on.

User needs to select a date using combobox cmbDate, which updates the t_comments box. However the onchange event against cmbDate using me.t_comments.requery does not select the comment from the matching date in the tblToday, only shows the first record?

 

[jdraw]

Why did you choose the OnChange event?

What is the recordsource for each of your Forms?

 

[adh123]

On change event as I want the t_comments field to update straight away, as opposed to tabbing or a button click.

The recordsource is tblToday, which is where all the data is stored.

 

[jdraw]

Forget the form and update until you get a query to display data.
We are trying to visualize what tables you have and what fields in those tables.

Have you tried a query just to see the data? Try putting this in the sql view of a new query.

SELECT * from tblToday where t_date = [Enter your Date]

 

[adh123]

jdraw

That works, I have dummy data in 2nd, 3rd, 4th and 5th feb which appears when running that query.

 

[jdraw]

Great!

So tell us now about the form. Sounds like it's a Form with a subform.
---something along these lines -------totally untested
User info on a Form, and a combo of Dates.
User selects a date from the combo, and in the afterUpdate event of the combo

you run the query where you have changed the [Enter Your Date] and adjusted the code in the event to use the value in the combo

in general terms(you need to work on the details)
Create form with subform -- linking fields up to you depending on relationships

SELECT * from tblToday where t_date = Me.YourComboName
and then add
me.requery ' should update the form and subform with the proper values.

 

[adh123]

Hi Jdraw

If I re-explain hopefully I can word it better this time around!

It is just 1 form with 4 fields:

txtDate - unbound, format short date
txtComments - unbound
t_date - linked to t_date in table tblToday
t_comments - linked to t_comments in table tblToday

My aim is to have 1 form where the user selects a date in txtDate (I do not think it can be a combobox as this would only show existing table data??), if that date already exists in tblToday then it should autofill t_comments and txtComments text box. The user can apply changes to txtComments and save (updating t_comments in tblToday) or cancel. (The end users cannot have direct access to the table and I have found this has worked on other forms)

If the date does not exist then it would allow the user to put new notes in the txtComment box and when pressing save it would insert into tblToday.

Hopefully this is a bit better? Any suggestions?

 

[adh123]

Have found an alternative way of checking if today is in the table already so I can now use the 3rd option when creating a combo box, so the fields now update as they should, thanks for your help