Best Practices for Creating New Records for Related Tables

[Boots6]

Hello,

I have a database I use for weekly company meetings. I have different tables for each department so I can use the "Collect Data Via Email" wizard to send the employees their forms. I only allow them to "Update Existing Information" so they don't reply several times (which they do) and create several new unnecessary records. The MeetingID field is the foreign key field in the tables. I've always manually entered MeetingID into the foreign key fields of the tables so the report includes all related tables, but now I need to automate this for another office. They aren't as familiar with Access so I want them to be able to push a button for "New Meeting" which will create records in the related tables and automatically insert the new MeetingID. What is the best way to go about this? I really appreciate any advice so I don't make it way more complicated than it needs to be

Thanks

 

[MajP]

The MeetingID field is the foreign key field in the tables. I've always manually entered MeetingID into the foreign key fields of the tables so the report includes all related tables

That is hardly ever done. 95% of the time you create a master form with with a subform to add child records the other 5% of time you have some code to do it. I have created 1000s of databases and have never build one where the user would add foreign keys by hand.

There is not a lot of info here about the table structure to suggest the best way. By far the easiest would be a main form sub form, the next would be some code to fire the appropriate insert queries.

 

[Boots6]

The reason I want to do it this way is the Collect Data Via Email Part. I have tables: Office, Estimating, Safety, and Meeting. Meeting has the Primary Key MeetingID and all the others have MeetingID as the foreign key. In order for me to use the Collect Data Via Email and restrict it to only allow Updates to Existing Information, I have to include a field for the employee so Access can find the correct email address. I didn't think you could use multiple subforms within a master form - that's the way I would do it if I could, but I'm working with several tables. I really appreciate any suggestion for this problem; if I lost the plot on Access subforms, I would really appreciate enlightenment.

Thanks for the reply,

Boots

 

[MajP]

I kind of missed that you are using the collect data via email. I have to say my knowledge of the email data collection is limited, and I am not certain of its capabilities and limitations. With a normal form you can have multiple subforms on the same main form, or have nested subforms (subforms inside subforms).

I still do not really understand your process of what is sent out, and what data is returned so not sure of the best way to do this. However

so I want them to be able to push a button for "New Meeting" which will create records in the related tables and automatically insert the new MeetingID.

if that makes sense, that is relatively easy to do. The code would be something like this.

public sub somebutton_click()
  dim meetingID as integer
  dim strSql as string
  meetingID = me.meetingID  'assume this button is on a form with the correct meeting
  strSql = "Insert into office (meetingIDForiegnKeyfieldName) values (" & meetingID & ")"
  currentdb.execute strSql
  strSql = "Insert into Estimating (meetingIDForiegnKeyfieldName) values (" & meetingID & ")"
  currentdb.execute strSql
  ....
end sub

There may be other fields that need to get inserted. Here is the general format for an insert query.

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

if You have dates then they need to be enclosed with #, and strings with '

strSql = "INSERT INTO table_name (columnNumber, columnString, columnDate) VALUES (" & numbervalue & ", '" & strValue & "', #" & datevalue & "#)

 

[Boots6]

Thanks for your advice!