hi,
i have created a form with a subform. how do i write the SQL statement in the procedures code for ADD button? how do i insert into two tables at once?
Thanks in advance.

What do you want to add? What is the whole process here?

Usually subforms add data dynamically. You open the form/subform, and add/edit data, and then close the form. What database structure are you using? Are the tables local or remote? Access or SQL dbms?

Normally you call a recordset and then use syntax like:

recordset.AddNew
recordset("Field1") = Me.txtMyTextBox
recordset.Update

You can do this with as many tables as you want. Is this what you are after?



mmcdonal

hi mmcdonal,
Thanks for your reply. i dont know much about VBA as i m new to access.
i wrote the codes as below, with those codes i am just able to do inserting for the main form (table:Master)only.
Now i dont know how to add the data in the subform into [Details] table as it reply cant find the field.
What should i write for the SQL statements so that when the ADD button is pressed both the main form and the subform save under the same record? the subform contains unbound textboxes ..

    Dim strSQL As String
    Dim db As Database
    Dim rs As Recordset

    Set db = CurrentDb

    strSQL = "Select * from Master Where "
    strSQL = strSQL & "[DocNo] = '" & Me.textDocNo & "'"

    Set rs = db.OpenRecordset(strSQL, DB_OPEN_DYNASET)

        If rs.EOF Then
          strSQL = "INSERT INTO Master (DocNo,SName)" & _
                       "VALUES('" & Me.textDocNo & "', '" & Me.textSenderName & "');"
          DoCmd.RunSQL strSQL
    rs.Close
    db.Close

Thanks a lot...

Normally you would do this with a bound form / subform in Access. Can you tell me more about the structure?

If you want to update another table, you have to open another recordset. You would change the stSQL to point to the second table. Are you creating a new record, or updating an old one? That will effect how you get hold of the records.

If this is a new record I would also translate the SQL into more user friendly code like this:


Go from:
strSQL = "INSERT INTO Master (DocNo,SName)" & _
"VALUES('" & Me.textDocNo & "', '" & Me.textSenderName & "');"

To this:

stDocNo = Me.textDocNo
stSName = Me.textSenderName

rs.AddNew
rs("DocNo") = stDocNo
rs("SName") = stSName
rs.Update

But that's just me.





mmcdonal

hi mmcdonal,
ok, now i want to do adding new record. In the form there have several textboxes in the main form and also a subform with several textboxes inside. All textboxes are UNBOUND... (include the subform's textboxes).
1. All the info in main form will be saved in Master table.
2. All the info in subform will be saved in Details table.
All of 1 and 2 will be saved by pressing a ADD button placed in the main form
I have linked up the main form and subform with DocNo with the relationship one (main) to many(subform).

By the way, what is the different between create a new record and update an old record?
Actually I am quite blurr about ADO, what should i do if i want to open 2 recordsets for a command button?
I have totally no idea how the codes look like. or do you have any other suggestions or advices for me to do such action?
I would be very great to learn more.
Thanks ..