Add Records From Subform to Table

[NFL]

I have a form that links 1 source object to a subform. The subform list may
be different based on which object is selected on the main form. How can I
write a code to select a textbox field from the subform and add them to a
table?

Thank you for your help!

 

[BruceM via AccessMonster.com]

I don't know for sure what you mean by linking a source object to a subform.
I'm sure I don't know what you mean by selecting an object on the main form.
A subform control has a source object (the form that serves as a subform), so
perhaps you are saying you want the subform data to correspond to the main
form record. This is ordinarily accomplished by creating a relationship
between the source tables for the main form and the subform, then selecting
the linking fields as the Link Child and Link Master properties of the
subform control.

If you describe your database's structure and general purpose it may be
possible to offer a more specific suggestion.

 

[NFL]

You're right, I got my terms backward. Sorry about that. The subform does
have a source object and it is linked to the main form. Here's the table set
up. I'll try to make this simple. Table1 is FORM1, Table2 is the
(SUBFORM). I want to be able to create a button to select the items from the
subform and insert it to Table3.

Table1 Table2 Table3
Room(One) Room (Many) Printer (Many)
Computer Room (Many)
Computer

 

[BruceM via AccessMonster.com]

The short answer is that you use the linking properties of the subform
control to add records to the subform's record source table, rather than
using a command button to write to that table via code. You can do it that
way: open a recordset and write records to it, but that approach is probably
needlessly complicated.

OK, so one room may have many computers and printers, and one printer may be
used by many computers, and one computer may have many printers. In one
sense a computer or printer is an attirubute of a room. In another sense the
room is an attribute of the computer or printer. But is a printer an
attribute of a computer, a room, or both? How you set up your forms and
subforms depends on what exactly you need to do. Do you want to look at a
computer's records and see the available or installed printers? Do you want
to look at a room records and see the printers and computers in it? Do you
want to look at a printer record and see the computers using that printer,
the room in which the printer is located, or both.

You need a table for each entity: Rooms, Computers, Printers, Room/computer,
Room/printer, computer/printer. Beyond that the details depend on the
specifics of your situation. There really is no generic answer to the
question of how to use a command button to insert records into a table. In
general it could look something like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Table3", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ComputerID = Me.ComputerID
rs!PrinterID = SomeValue
rs!RoomID = AnotherValue
If Err Then
MsgBox "Error #" & Err.Number & " (" & Err.Description & ")"
rs.Close
Exit Sub
End If
rs.Update
rs.Close

However, that is unlikely to be the best way to go about it. You may not
need code at all.

You're right, I got my terms backward. Sorry about that. The subform does
have a source object and it is linked to the main form. Here's the table set
up. I'll try to make this simple. Table1 is FORM1, Table2 is the
(SUBFORM). I want to be able to create a button to select the items from the
subform and insert it to Table3.

Table1 Table2 Table3
Room(One) Room (Many) Printer (Many)
Computer Room (Many)
Computer

I don't know for sure what you mean by linking a source object to a subform.
I'm sure I don't know what you mean by selecting an object on the main form.

[quoted text clipped - 14 lines]

 

[NFL]

Thank you for your response. The form and subform works very well together.
I just don't know how to pass the values from the subform to a different
table. It might not look right, but there are other inputs the user would
add to that table.

The short answer is that you use the linking properties of the subform
control to add records to the subform's record source table, rather than
using a command button to write to that table via code. You can do it that
way: open a recordset and write records to it, but that approach is probably
needlessly complicated.

OK, so one room may have many computers and printers, and one printer may be
used by many computers, and one computer may have many printers. In one
sense a computer or printer is an attirubute of a room. In another sense the
room is an attribute of the computer or printer. But is a printer an
attribute of a computer, a room, or both? How you set up your forms and
subforms depends on what exactly you need to do. Do you want to look at a
computer's records and see the available or installed printers? Do you want
to look at a room records and see the printers and computers in it? Do you
want to look at a printer record and see the computers using that printer,
the room in which the printer is located, or both.

You need a table for each entity: Rooms, Computers, Printers, Room/computer,
Room/printer, computer/printer. Beyond that the details depend on the
specifics of your situation. There really is no generic answer to the
question of how to use a command button to insert records into a table. In
general it could look something like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Table3", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ComputerID = Me.ComputerID
rs!PrinterID = SomeValue
rs!RoomID = AnotherValue
If Err Then
MsgBox "Error #" & Err.Number & " (" & Err.Description & ")"
rs.Close
Exit Sub
End If
rs.Update
rs.Close

However, that is unlikely to be the best way to go about it. You may not
need code at all.

You're right, I got my terms backward. Sorry about that. The subform does
have a source object and it is linked to the main form. Here's the table set
up. I'll try to make this simple. Table1 is FORM1, Table2 is the
(SUBFORM). I want to be able to create a button to select the items from the
subform and insert it to Table3.

Table1 Table2 Table3
Room(One) Room (Many) Printer (Many)
Computer Room (Many)
Computer

I don't know for sure what you mean by linking a source object to a subform.
I'm sure I don't know what you mean by selecting an object on the main form.

[quoted text clipped - 14 lines]

Thank you for your help!

--



.

 

[BruceM via AccessMonster.com]

You can open a recordset along the lines of the code I posted. Where I
showed ComputerID, SomeValue, and AnotherValue you would use the values you
actually want inserted.

Thank you for your response. The form and subform works very well together.
I just don't know how to pass the values from the subform to a different
table. It might not look right, but there are other inputs the user would
add to that table.

The short answer is that you use the linking properties of the subform
control to add records to the subform's record source table, rather than

[quoted text clipped - 56 lines]