Adding a text box in a bound form that appends to another table

[JamesWB]

Afternoon all.

I can't quite imagine how I do this.

I have a form AddNewEquipment. This is bound to a table, EquipmentDetails.

EquipmentDetails has a Yes/No field, 'ParentChoice'. So when EquipmentDetails.ParentChoice = Yes, I want to open up a new text box, AddNewEquipment.ParentDescr, into which someone can put some text. This text I want to append as a new row in a different table, ParentList.ParentName. (that table also has an autonumber field)

I only want to do the save when I save the whole form.

Is this something like using an On Lost Focus event from the ParentDescr field which only invokes when the overall form save button is clicked? What would I put in the On Lost Focus event?

Thanks for any help.

 

[burrina]

Why not simply use both tables as the record source for the form? As for the save, you can put code to require your criteria before allowing a save. This has been addresses many times on this forum.

 

[JamesWB]

Why not simply use both tables as the record source for the form? As for the save, you can put code to require your criteria before allowing a save. This has been addresses many times on this forum.

Aha! I didn't know I could have more than one record source for a form. On the Property Sheet for Data, Record Source only seems to have one entry possible? What is the syntax for adding another table there please?

EDIT OK, been looking this up and you are talking about using a Query as the record source rather than a table, is that right?

 

[burrina]

Just add your table to the query grid for the form and of course link correctly.
You can of course set multiple record sources for a form but I believe that is not applicable in this case.
YES, a query!

 

[JamesWB]

Just add your table to the query grid for the form and of course link correctly.
You can of course set multiple record sources for a form but I believe that is not applicable in this case.
YES, a query!

I don't think you can set multiple record sources for a form without using subforms? Or do you know how?

A query will only add to my nightmare as the record source is large and complex. I think the answer is to use another little popup form, but I was just wondering if there was some VBA method I could use to save out one form field to another table when the form is saved.

 

[burrina]

If you prefer, yes you can use a popup form and then using the AfterUpdateEvent call a query or code statement to update your table with the value in the field (control).

You CAN use multiple record sources for the same form, just use a different record source depending on the criteria when it is opened. But again, this is NOT the case here!

Size of table has nothing to do with it. Need details Please, table name and field name(s). Explain with details.

 

[JamesWB]

If you prefer, yes you can use a popup form and then using the AfterUpdateEvent call a query or code statement to update your table with the value in the field (control).

You CAN use multiple record sources for the same form, just use a different record source depending on the criteria when it is opened. But again, this is NOT the case here!

Size of table has nothing to do with it. Need details Please, table name and field name(s). Explain with details.

Yes, I know how to do it via a popup form.

I don't understand your point about being able to use multiple record sources for the same form but dependant on opening criteria. Do you mean do some VBA when the form is opened to decide which recordsource to bind the form to? That's not the same thing as having different fields in the same form pointing to different tables.

I already gave the affected form, table and field names in my first posting.

Maybe it's about using the CurrentDb.OpenRecordset(tablename) in the afterupdate event for the field? My only difficulty is knowing how to do this for an ordinary text box field (eg, not a combo box) when the main form save button is clicked.

 

[burrina]

Create a append query and use the forms/field criteria.

If Me.ParentChoice = True Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "yourqueryname"
DoCmd.SetWarnings True

HTH and Good Luck!

 

[JamesWB]

Create a append query and use the forms/field criteria.


HTH and Good Luck!

Ah, that's interesting. You mean put that in the on click event of my form's save button?

 

[burrina]

Yes.

Hth