Just wondering if....

[Hey Lucy]

I have a database concerning real estate. Many objects. One table (and subsequent form) is information on properties. Another is notes on that property. The reason I didn't simply add a Notes field to the main properties table is that the client wants a date for each note. So I created table and form and am using it in main form as a subform.

Here is what I was wondering about ....

A lot of times, when there are a lot of tables with necessary ID numbers, I will assign a formatting to a field in a table that distinguishes that this is the number for xxx. For instance, there is a separate table and form for properties that have been leased, so the auto-assigned ID is formatted as 000-LSD, so that I know that number is the lease info ID #. Just helps from getting confused when you are dealing with tons of tables that each have auto-assigned IDs.

So, I was wondering if there was any way to format the Notes ID field to give me a note # attached to the Property ID. The reason being here, is that there could be multiple notes on one property. I can't do it by date, as there may be several notes entered concerning a single property on a single date.

So, if the note ID is 001 and the Property ID is 01467, I would really like for the format of the note ID to show as 001-01467, and the 2nd note on this property to show as 002-01467, but if it is a different property (02195), then I would want the numbering to start over for THAT property, EX: 001-02195.

That way I would be able to identify each note by the property and be able to distinguish how many notes have been written about this property.

Is there a fairly easy way to do this, or is it even possible at all? Not a great big deal but would be a nice addition for my client.

Barely know VB, so I can't actually write a code myself, but I can alter code to fit my database.

Thanks!

 

[gemma-the-husky]

first, the notes table should have a column to hold the ID of the host property. This is the critical thing

now design a form to show the notes. maybe use a notes query, sorted by data descending. Make this a continous form. open it, and it will show nots for all the properties. This is not a problem at this point.

Now design your properties form, and drag the new notes form on to the properties form, positioning and sizing it to suit.

THis form should now automatically set the fact that the id of the host property corresponds with the the same id in the notes, but if not, set the link details manaually.

Now when you open the properties form, you will see all the notes filtered for each property automatically, and it will automatically manage the link for new notes as you add them.

You do not really need the complicated numbering system you described.

No code at all.

Try it and see.


Please note this only works if the properties form is a single form, rather than a continuous form.You can still use 2 continuous forms, but you need a few lines of code.

As you use the record navigation buttons to move to different properties, the notes will automatically update.



heres an example. open the form "properties"

View attachment properties.zip



-----
the real thing is that the artificial index conventions are often restrictive best avoided, and can often be a "holdover" from the previous system. A properly designed database can handle the data itself, and effectively hide all the linking information, as users do not need to be aware of it. Your reference naming convention may still be necessary for some internal business purposes, but it tends to be a matter of taste.

 

[Hey Lucy]

Got it! Thanks! Worked beautifully!

 

[Steve C]

Very useful Post Dave Thank you

-I've been trying to find a way to DoCmd.OpenForm,acViewCONTINUOUSFORMS,, - But that's not an option (just acViewDS and Edit).

When I saw your solution for Hey Lucy - the form looked right, so I made a mental note to come back here - but couldn't remember what to search on! So, I'm just posting here so I can find it again by searching on my Posts.