Pop Form / Table Insert

[Jerry8989]

Hello,
I would like know the best way to do the following scenario:

form1 is tied to table1. I have a button on form1 that when pushed a modal form will pop up with form2 which is tied to table2. Now within the relationships table1 is one to one with table2.

My question is how to add a new row to table2 when the button is pressed from form1? I need the id from form1 to insert into table2. Can this be done with no code since the relationship is already there?

Please be patient I uses access sporadically

thank you

 

[dcb]

Hi

The easiest way would be setting the default value of the ID in your second form to something like
=[Forms]![frmTable1]![ID]

However this leaves you open to someone pressing the button and having a null value in the ID -> save record issues etc

I would use the openargs method and a check to make sure the record in the first form is saved and the id > 0 - do a help search on openargs

 

[Jerry8989]

dcb,
Thank you for your response. I've used open args what I want to do is as soon as I open the pop up form I want a row to insert into table2 without writing sql code.
Form1 will always have an id.

I have a list of customers and the button will allow me to put a note about this customer. If a row exists I want the data to show up but if there isn't a row in the note table I have to insert one using the form1.id.

 

[jzwp22]

Out of curiosity, why do you have a separate table for 1 field of which you will only have 1 record for each customer? You could move the note field to your customer table and include a control for it on form 1. In the on current event of the form, hide that control but use your button to just make it visible if someone wants to enter a note.

If you are going to have many notes then you will have a one-to-many relationship and you would need the second table.

 

[dcb]

I agree with jzwp22 - you have split data where there is no need

If there is a reason that you have to have it this way
setting the default value and using :

 DoCmd.OpenForm "frmTable2", , , "[ID]=Forms![frmTable1].[ID]", , , "youropenargs"

could do the trick

Thus if you do a new record it will take the ID over and if the record exists it will show - no sql

You state that the form will always have a ID however be careful if a user had to create a new customer and immediatly open the popup without you forcing a save you would end up in the situation where table 2 has a ID that does not yet exist in table 1 - This would cause a conflict with the relationship (same issue with a one to many on the very first records)