Hi There,

I created a Note/Diary form. Where I would like to show the recorded notes in a list box.

Now, the notes as stored in table called tblNotes along with Date and Time entered

How can I or is it possible to show this information in the list box as follow:

Friday, July 01, 2011 at 12:05 PM
Note goes Here
Friday, July 01, 2011 at 1:30 PM
Next Note Goes Here

Basically is it possible to have one row just for Data/Time and Next the Note?

Thanks

Yes.

Set the RowSourceType of the listbox to Value List.
Then you can create a routine which browses your table and for each record add two lines to the listbox

(check the Online Help for details, it's fairly easy.

Just keep in mind: Since you probably want that listbox to do something with that data (display/select a record by clicking or doubleclicking) you might want to add an ID (primary key) to the listbox.


(and of course set ColumnCount to 2 and ColumnWidth of the column with the ID to 0)

Thomas

Why use a listbox though, this doesn't sound like listbox behaviour (one record accross two rows) so you surely aren't requiring a user to select any of the values. So if this is just for display then use a large textbox, look at concatenating records into a long string, separating the date and info with a paragraph break and each record with two.

Okay, Then that's why I was asking.

Do you have an example of how this can be done?

Thanks

I'd have to agree, this is not a job for a Listbox! You could use a Datasheet View Form, but I suspect a Continuous View Form would be the most apt way to do this. Perhaps even using a Main Form/Subform scenario with the notes listed in the Continuous View/Subform.

Some questions

• What type of Field are you using for the Notes? A Text Field is limited to 255 characters, and the Timestamp can take up as many as 30 characters alone. This type of thing is normally done with a Memo Field, which has a limit of about 65K characters, when entered thru the keyboard.
• Is this a diary for a single user, or multiple users? The above mentioned Main Form/Subform scenario would be ideal for this. The Main Form would hold a Record for each user and the Subform would hold just that user's Notes.
• What is the purpose of these Notes? Are they merely for the user to reference, i.e. look at on-screen? Or will they be printed out?
• Are you planning on doing any data manipulation of the Notes?

All of these things effect whether you should use Memo Field and how you set up your display.

Linq ;0)>

If that is directed at me then yes I definately do, I cannot sort it out for you right now but will endeavour to tomorrow assuming someone has not beaten me to it.

Jon Smith

Thanks for taking the time for such detail explanation.

Well,

1. I’m using the note field as MEMO. I think they are better for holding this much data.
2. I was just going to use a single form but if you recommend two, I’m with you. You guys are the experts.
3. The main purpose is to keep records. Phone Calls, Notes – you know just to keep what’s going on with this customer. They will be for multiple users.
4. Not really just to see them, they will be print out via reports

Thanks

What you're describing here is the classic one-to-many scenario, with the 'one' side being the Customers and the 'many' side being the Notes. This is usually handled with a Main Form/Subform set up, as mentioned before.

In general terms the Main Form would be a Single View Form and based on a CustomerTable. The Subform would probably be a Continuous View Form (to better display the 'Note' Field) based on a NotesTable.

The Fields would typically be something like this:

CustomerTable
CustomerID 'Primary Key
CustomerName
CustomerStreetAddress
CustomerCity
CustomerState
...and so forth

NotesTable
CustomerID 'Foreign Key
NotesID 'Primary Key
NoteDateTime
NotesPersonContacted
ContactNotes
...and so forth

Once you've designed your CustomerForm (Main Form) and NotesForm (Subform) based on CustomerTable and NotesTable, respectively, open the Main Form in Form Design View and add a Subform Control to it. The Subform Wizard will walk you through the process. When asked, base your Subform on the NotesForm. Seeing that both Tables the Forms are based on have a Field named CustomerID, the Wizard should ask if you want to link the Main Form /Subform using these Fields. Answer OK and Bob's your uncle!

As you move through the Customer Records in the Main Form, Access will automatically update the Notes Records in the Subform and only show those Notes related to that Customer.

As I said, a Memo Field is the appropriate type of Field for these Notes. And they are fine for Viewing and Printing.

As this is intended for multiple-users, you'll eventually have to split your database into a Front End/Back End configuration. All Tables and inherent data will reside in the Back End, with a single copy on a shared drive, and everything else, Forms, Modules, Queries, etc, will reside in the Front End. A copy of the Front End will then need to be placed on the hard drive of each user's PC.

For now I'd concentrate on getting everything else set up. Once you have the app working like you want, you can split the file into the Front End/Back End configuration. Access will do most of it for you, using the built-in Database Splitter. If you have problems with that, come on back here and someone will help you out.

Good luck with your project!

Linq ;0)>

Thank you so very much. That’s what I call an explanation.
Thanks a lot again. That covers everything. A+

Glad we could help!

Linq ;0)>