Confusion with bound/unbound fields on forms

[Guest]

Hello,

I am trying to develop a data entry form which will insert data into several
tables. When I bind each object (text fields and combo boxes) to the
respective tables & columns, I cannot enter data in the various objects.
When the form loads, "#NAME?" appears in the text boxes. So, in order to
enter data, I must leave them unbound.

Can anyone explain what is happening here, why, and how I can correct it?

Also, when all the data on my field is entered in the text boxes, and values
selected from the combo boxes, I need to execute the queries to save the data
to the appropriate tables. What is the best way to do this? Should I write
the SQL and save it as a query, then call the query after the "Save Data"
command button is clicked?

Thanks in advance,
Rich

 

[Guest]

Hello,

I just read in a book why I cannot enter data in fields with a recordsource
set and it makes sense to me now.

So, what is the best way to save the data to the various tables? Is there a
way to simulate a (SQL Server) transaction? By that I mean, execute several
different SQL queries (updates or inserts) on at a time and ensure that each
completes successfully or each be rolled back?

Thx,
Rich

 

[John W. Vinson]

Hello,

I am trying to develop a data entry form which will insert data into several
tables. When I bind each object (text fields and combo boxes) to the
respective tables & columns, I cannot enter data in the various objects.
When the form loads, "#NAME?" appears in the text boxes. So, in order to
enter data, I must leave them unbound.

Can anyone explain what is happening here, why, and how I can correct it?

A Form has one and only one recordsource - often a Table, often a single-table
Query, sometimes though rarely a multitable query. Only those fields in that
Query can be used as the control sources for textboxes and combo boxes. You
cannot simply pull one field from this table, another field from that table,
this combo box from the other table!

Instead, you need to consider using a Form based on the "one" side table in
your relationship, with Subforms bound to the "many" tables. This requires no
code at all to update multiple tables, and maintain the proper relationships.

Also, when all the data on my field is entered in the text boxes, and values
selected from the combo boxes, I need to execute the queries to save the data
to the appropriate tables. What is the best way to do this? Should I write
the SQL and save it as a query, then call the query after the "Save Data"
command button is clicked?

That's really the hard way to do it. If bound forms (with subforms) cannot do
what you need, then I'd write VBA code to open a Recordset based on the target
table, use the AddNew method to go to a new record, and populate the recordset
fields from the form controls. Complicated but sometimes the best way to do
things.

John W. Vinson [MVP]