MS Access 2003 - Unbound Form uses INSERT statement to save to table; what about subforms?

Question

So I have an unbound form that I use to save data to a table on button click.

Is there a way I can have subforms for entry that will allow me to save data to the table within that same button click? Basically I want to add more entry options for the user, and while I know other ways to do it, I am particularly curious about doing it this way (if it can be done).

So lets say the 'parent form' is frmMain. And there are two child forms "sub1" and "sub2". Just for example sake lets say on frmMain there are two text boxes: txtTitle & txtAuthor. sub1 and sub2 both have a text Box on them that represent something like prices. The idea is Title & author of a book, and then a price at each store (simplified).

So I tried this (because I thought it was worth a shot):

Dim db as DAO.database
Dim sql as String


sql = "INSERT INTO (Title, Author, PriceA, PriceB) VALUES ("

if not isnull(me.txtTitle) then
    sql = sql & """" & me.txtTitle & ""","
Else
    sql = sql & " NULL,"
End If

if not IsNull(me.txtAuthor) then
    sql = sql & " """ & me.txtAuthor & ""","
else
    sql = sql & " NULL,"
end if

if not IsNull (forms!sub1.txtPrice) then
    sql = sql & " """ & forms!sub1.txtPrice & ""","
else
    sql = sql & " NULL,"
end if

without finishing the code, i think you may see the GOTCHA i am headed for. I tried this and got an "Access cannot find the form "" ". I think I can pretty much see why on this approach too, because when I click the button that calls the new sub form into the parent form, the values that were just entered are not held/saved as sub1 closes and sub2 opens. I should mention that the idea above is not intended to be a one or the other approach, rather both sub forms used everytime.

so this is an example. i want to use this method (if possible) to have about 7 different sub form choices in one form, and be able to save to a table via a SQL statement.

I realize that there may be better ways, but I am just wondering if I can get there with this approach out of curiousity.

Thanks as always!

Answer 1

Your problem is with how you are referring to the controls, instead of

forms!sub1.txtPrice

try

me!sub1.form.txtPrice

You can see more example of how to refer to sub forms here

http://www.mvps.org/access/forms/frm0031.htm