Friday Teaser!

[rookery]

Heres something to take us into the weekend:

I'm creating a Diary facility. I have a Form which has a Calender and a text box on it. I've created a table which contains a Memo table and a date field. I haven't bound the Form to the table. Obviously the text that the user enters into the text box is saved into the memo field in the table etc etc.

When the user enters any notes on a specific date and then moves to another day on the calender, I want the notes to be saved so that when the user clicks on that day again, the notes pertinent to that day are displayed.

Can you call a query from code module? It's hard work trying to find the right section in the book! Any ideas...

 

[JoanneM]

hello again


Yes you can call queries from code but I think I've missed the point here.

A username field would be usefull in the memo table then you could use the date field on the form along with the user name to filter a query sourcing the notes box

and on current of the form type
me!txtnotes.requery

txtnotes being notes box

Have I got the right end of the stick here?

 

[rookery]

Hi Jo

Thanks for your prompt reply but I'm not too sure myself! First of all apologies as I should have explained that I also already have a UserID (Number type) field in my DiaryNotes table.

I'm glad to hear I can call queries from code. I've just tried setting up a Recordset and doing it that way without much success.

I suppose the best way to describe my predicament is as follows:

I have a simple Select Query (based on the DiaryNotes table) which if I type a date into the criteria field and run it, will display the contents of the Memo field. I want to achieve the same effect except what appears in the memo box on the Form is dependent on what day the user clicks on in the Calender. For instance I was considering setting up a variable which would be equal to Calender1.Value, and then somehow putting this into the Query, so as to generate the contents of the memo field, which would then be displayed in the Memo field on the Form.

Basically I dont know if this is viable and if so, how to do it. Confused? Me too...

 

[JoanneM]

I'm Back again

I think I understand

step1

Create your query that will source your memo box in the criteria of the date field type
forms!frmname!calendar1.value
in the criteria of the of the userid type
forms!frmname!txtuserid

replace frmname with the name of your form and the txt box names as appropriate.

go to the SQL of the query

copy it and put it in the control source of the memo box

Your form is still unbound - Any particular reason why it has to be unbound?
and your memo box has its data that can be requeried.


on the after update property of the calendar type

me!Txtnotes.requery

again replacing txtnotes with the name of your notes box
and this will run the query again and source the memo box


regards Jo

 

[rookery]

Hi Jo

I've done what you said but when I return to Form view I'm getting a "#Name?" error continually displayed in the Text box.

FYI the Calender control isnt bound to anything. I've bound the Form to the table as per your suggestion. Obviously the Text/memo box has the SQL as it's control source. Is this all correct?

Please before I pull the rest of my hair out...

 

[JoanneM]

If you' bound your form then
on the control source of the memo box just select the memo field from the list

then

on the after update of the calendar copy this making sure the field names are correct ie I used calldate for the date field in the table - replace this with your field name and the same with the userid field.


Dim Rs As Object
Dim Usr As String
Dim DT As Date
DT = Calender1.value
usr = currentuser

Set Rs = Me.Recordset.Clone
Rs.FindFirst "[callDate] = " & "#" & DT & "#" & "AND [userid] = " & Usr
Me.Bookmark = Rs.Bookmark

this should bring them together


Regards

Jo

 

[rookery]

Jo

I'm sorry to keep banging on about this but I cant bind the control to the field as that would allow the user to type anything into the box and it would be saved without having to click on some Update facility.

I only bound the Form really to see if it might have an effect and amke it work. The first error I was getting said that it was Incorrect Syntax, so I put an "=" at the start of the command and placed the rest of the query in brackets. (starting after the = sign). This still hasn't worked. I feel as if I'm close as if I run the query I created to copy the SQL, the correct result is shown in Datasheet view.

I know you're helping others on here as well and I thank you for your time so far.

 

[JoanneM]

Did I say Control or Row source it should be row source check out the SQL on here

Sorry got my sources mixed up.

Regards

Jo

 

[rookery]

There isnt a RowSource on a TextBox. Any other ideas....

 

[JoanneM]

That will teach me to write before checking - I got your query and another transposed.

 

[rookery]

Thanks for all your help on this Jo. Anyone else have any ideas....?