Solved New Record

[MarcCastignani]

how do i add a new record to existing ID on form. so basically vba for the way you would add a record to a sub form.

 

[GPGeorge]

Please explain the context more fully. Please refer to Primary Key, not just "ID", if that is what you mean. Or explain what "ID" is if it's not the Primary Key.

 

[Gasman]

Just enter it via the subform?

 

[MarcCastignani]

Thanks for the response, The easy way would be to just use a subform which is what iv done. This is the set up:

table 1 Employees
table 2 Clients
table 3 Notes

two cascading combo boxes, combo 1 is employees, combo 2 is clients.
list box with the notes for each client, If i double click on the note in the list box a second form opens with the notes field. The problem i have now is to enter a note for the person I double clicked on. the new note button click code on the add new record is

docmd.gotorecord, acNewRecord.

The problem is that it wants the client id, its making a completely new record not simply adding the record to the client, hence many notes for each client.
Using a sub form is no problem

 

[GPGeorge]

Thanks for the response, The easy way would be to just use a subform which is what iv done. This is the set up:

table 1 Employees
table 2 Clients
table 3 Notes

two cascading combo boxes, combo 1 is employees, combo 2 is clients.
list box with the notes for each client, If i double click on the note in the list box a second form opens with the notes field. The problem i have now is to enter a note for the person I double clicked on. the new note button click code on the add new record is

docmd.gotorecord, acNewRecord.

The problem is that it wants the client id, its making a completely new record not simply adding the record to the client, hence many notes for each client.
Using a sub form is no problem

Show us the fields in these tables and the relationships between them please. I think the problem is starting to look more like a table design issue than a data entry issue.

 

[Gasman]

If you insist on adding a new note via a separate form, then you could pass the id of the parent in to the form via openargs.

 

[MarcCastignani]

Please see attached

 

[MarcCastignani]

If you insist on adding a new note via a separate form, then you could pass the id of the parent in to the form via openargs.

Will see if i can do that. could you give an example of the openargs? thanks

 

[Gasman]

Will see if i can do that. could you give an example of the openargs? thanks

I could, but Google could do it much better.

 

[MarcCastignani]

okay cool no problem will check it out

 

[mike60smart]

Will see if i can do that. could you give an example of the openargs? thanks

Why not just use a Main Form and Subforms?

 

[GPGeorge]

Please see attached

Thank you. I think the most effective way to do this is to design data entry as a main form bound to the Client table, with two subforms. One of those subforms allows you to add one or more employees to the employees table for each client. The other allows you to add one or more notes to the notes table for each client.

 

[LarryE]

Please see attached

1. Create 3 forms. The Employees form is a single form. The Clients form is also a single form. The Notes form is a Continuous form. Each forms Record Source is a query based upon their related tables.
2. The Notes form should be a subform of the Clients form
3. The Clients form should be a subform of the Employees form
4. Make sure each forms Foreign Key reference field is included on the Notes form and the Clients form.
5. Insert the Notes form onto the Clients form as a subform first, then insert the Clients form onto the Employees form as a subform.
6. Create Master/Child field references as each subform is inserted. This allows Foreign keys to be automatically created when new records are created without any code.

You then have one form (Employees form) to open which allows data entry for all 3 tables. As you scroll through Employees, their related Clients will show. As you scroll through Clients, all their related notes will show.

 

[MarcCastignani]

Thanks for the help, appreciate it.

 

[Pat Hartman]

I would not make the employees the master form. I would make it client/notes. The employee would be a combo on the client. The client form should have a combo or text box for searching to pull up the client.