Opening a form from inside another form using ID (1 Viewer)

[bobunknown]

Hi all,

As the title says I am trying to open a form (as a pop up window) from inside another form. For context the main form is called 'Trade_Waste_Address' and the form im trying to open is called 'Notes'.

Each has their own ID field called 'ID' & 'NoteID'. Im attempting to call up the 'Notes' form based on the current records 'ID' but it doesn't seem to want to play ball so far.

Here is how far I have gotten:

Private Sub Command291_Click()

DoCmd.OpenForm "Notes", , , "NoteID = " & Me.ID

    
End Sub

The issue im encountering is with having the current form 'ID' automatically recognised. As of right now when I click the associated button this code will create a pop up box prompting me to manually entre the 'NoteID' in order for the record to then be called up and displayed.

I have tried a few different variations now but with no success. Any help would be appreciated.

 

[CJ_London]

from your description, your notes table needs 2 ID's

NoteID - unique ID for the record
ParentID - link back to the ID in your main form (called the Family or Foreign key)

Call ParentID what you want

Then modify your WHERE parameter to use this field

I recommend you get in the habit of using meaningful names - ID? ID of what?. NoteID - quite clear what it is

As a personal preference, instead of ID I use a PK (parent key) and FK (family or foreign Key) as a suffix for relational fields so I also know which end of the relationship is which.

 

[isladogs]

Have a look at my example app in this post: https://www.access-programmers.co.uk/forums/showpost.php?p=1601360&postcount=20

It opens a popup form just below the 'calling control' on the first form.
I use it to open a custom zoom box where needed but the same idea can be used for other purposes as well

HTH

 

[bobunknown]

from your description, your notes table needs 2 ID's

NoteID - unique ID for the record
ParentID - link back to the ID in your main form (called the Family or Foreign key)

Call ParentID what you want

Then modify your WHERE parameter to use this field

One of the variations tried was to try and call the ParentID (As this is what 'NoteID' used to be prior to me renaming it for reasons that you stated previous) However I was still left with the issue of needing to manually input the ID for the record to display.

For clarity the WHERE parameter in this case read:

"ID = " & Me.ID

This is why I started playing around to try and get it to work but no luck so far.

 

[JHB]

The form "Notes" is bound to a table, what is the name of the field holding the ID?

 

[bobunknown]

The form 'Notes' holds two fields:

ID
Notes

The field ID in the form 'Notes' is linked to the field 'ID' in the form 'Trade_Waste_Address'

 

[JHB]

Then "ID = " & Me.ID should work.
Post your database with some sample data.

 

[Minty]

As CJ has pointed out you should really have another field

NoteID - Unique PK for this Notes table
WasteAddID - The foreign key that links the note back to your parent TWA record.
Notes - The note data

Personally if space on the form allows I would make the notes a subform, and then all the pain goes away.

Assuming you are adding new records then a Where clause won't work. Your new record won't have a matching FK ID, so how would it be able to filter to it.

The other way of passing the FK ID would be the openargs property;

DoCmd.OpenForm "Notes",OpenArgs:= Me.ID

Then in your pop up forms OnLoad event use

Me.YourFKControl = Me.OpenArgs

To set the FK value for the new record

 

[bobunknown]

Im uploading a copy with all personal info removed but with all the relevant info needed for the query still included.

The button marked 'Not in Use' is the one that holds the code in question.

 

[bobunknown]

Minty,

Why are two unique ID's required?

My thinking on the situation was that as both the TWA & Notes record essentially hold the same ID for each shouldn't it be as simple as saying find both ID's that = 8 and open the form?

sorry if im being dim I just want to keep it simple as possible

 

[CJ_London]

with notes you have a 1 to 1 relationship. So there is no point in having a separate table, just include the notes field in the trade waste table.

Various other issues you will encounter problems with at some point

Spaces in field and table names
use of reserved words (date, year)
you are using lookups in your table design

and for some reason you have not specified ID as a primary key in the trade waste table

 

[isladogs]

The parameter prompt is because the ID field isn't on your Notes form

Add the field ID to the record source of the Notes form and to the form itself.
It will then work without the parameter prompt.

However I agree with comments in the last few posts

 

[CJ_London]

also both your notes ID and ID fields are autonumbers - it will be almost impossible to keep them in sync. Your next trade wast autonumber will be 1212, and your next notes autonumber will be 1211 for example.

You are creating notes records unnecessarily - of 1200 odd records, only 2 have data

 

[bobunknown]

You are a god send sir, I knew it would be something simple.

Just to clarify for future reference. If I reference an field in a form, it must be present in the form even if the data itself is included in the source table?

Is there a way to reference the table? ooooor should I just keep it simple?

 

[isladogs]

The field needs to be in the form record source though not necessarily be used as a control on the form itself.

 

[bobunknown]

also both your notes ID and ID fields are autonumbers - it will be almost impossible to keep them in sync. Your next trade wast autonumber will be 1212, and your next notes autonumber will be 1211 for example.

You are creating notes records unnecessarily - of 1200 odd records, only 2 have data

With reference to the above, would you recommend changing the Note ID to number? or is there a better way to sync everything up? Could I place the ID field from the TWA form in the notes form and use that to call up the form in the same way as before?

Also I removed all the Note records before uploading my database as it contained personal info. The live database holds notes for almost every record.

with notes you have a 1 to 1 relationship. So there is no point in having a separate table, just include the notes field in the trade waste table.

That is how the form was set up prior but im after making a change to both free up some space, and learn something new.

 

[isladogs]

If you intend to ever have multiple notes for the same ID then you need a separate table with a NotesID PK field and the existing ID as a FK field.

However if there will only ever be one Notes record per ID which may be added to as needed, then just use one table.

The Zoom form I mentioned in my first answer is useful to show the entire Notes field on a popup where there isn't enough space for all the text on the main form.

 

[CJ_London]

With reference to the above, would you recommend changing the Note ID to number?

per my first post, you need another (number) field to provide a link to your trade waste table - you have it in other tables, why not this one? And if you only want one note per trade waste record, include in your trade waste table.

That is how the form was set up prior

so perhaps it is set up incorrectly

but im after making a change to both free up some space

free what space?

 

[bobunknown]

If you intend to ever have multiple notes for the same ID then you need a separate table with a NotesID PK field and the existing ID as a FK field.

There's only ever going to be one record so one table is the way to go I think.

Thanks for the explanation dude.

per my first post, you need another (number) field to provide a link to your trade waste table - you have it in other tables, why not this one? And if you only want one note per trade waste record, include in your trade waste table.

This table was originally set up to be a sub form within the Main form with a 1:1 connection with the ID field thus I kept it simple and included only one ID as the form itself was always embedded within the associated data.

I agree its not set up correctly for what im doing now, but I wasn't sure how to approach the problem, hence the post and my undertaking of the problem, as it will enhance my knowledge.

The free space you ask about was where the Notes sub form used to be placed in the Main Form, in the version I uploaded I had already removed it.

Thanks for all the advice, ill crack on from here with your suggestion of a second ID. Have a good Christmas folks.

 

[Minty]

It's possible that being a long text (memo) field having it in a separate table overcame some difficulties in handling the data type.

I've seen it as a possible workaround to potential data corruption involving memo fields, though personally never encountered it.

In addition to what the others have said, if it's possible you wanted more than one note, date/time stamped notes per address, then the NoteID and a separate table are the only sensible way to go, something you might want to consider in your design.