ms access insert data into SQL database table from subform

Question

Apologise for my formatting.

I am having issues with an Access database form.

I have a form that calls up participant info from the participant table. Then i require a subform to contain a subset of another table filtered on the id number (PK) of participant table. The subform (created by using access sub form wizard), does this job OK. (The wtwo forms are linked by ID on primary form and GI_ID on subform, but nor relevant here).

Now the tricky part, on the subform I have a button, when pressed it opens another form (clarification) and inserts a new row into then new forms table, linking bY subform (Diary.ID) and clarification.DiaryID. The operater can now fill out the fields in the clarification table, which will always be linked back to the Diary table. In Summary

Participant table; ID (pk)
healthdiary Table; ID (PK) GI_ID (fk - participant table) form= frm_healthdiary_edit_record.ID
Clarification Table ID (PK), DiaryID (fk) form=tbl_healthdiary_clarif

This is the code behind the button which works (on another form) when it is not a subform.

SQL_Txt = "INSERT INTO tbl_healthdiary_clarif (HDR_ID) Values (forms!frm_healthdiary_record.ID)"
DoCmd.RunSQL SQL_Txt
SQL_Txt = ""
DoCmd.OpenForm "frm_healthdiary_clarif", , , "[HDR_ID] = forms!frm_healthdiary_record.ID"

The below SQL insert code does not work when used on the onClick contral on the button in the subform frm_healthdiary_record. No matter what i try it always asks for input for frm_healthdiary_record.ID.

SQL_Txt = "INSERT INTO tbl_healthdiary_clarif (HDR_ID) Values (me.frm_healthdiary_edit_record.form.ID)"
DoCmd.RunSQL SQL_Txt

SQL_Txt = ""

I have tried changing the code to me.frm_healthdiary_edit_record.form.ID and Forms!frm_healthdiary_record..Form.ID, but always get the same result of asking for input and cannot find the control.

Its very frustrating.

Let me be clear, I want the button on the subform (onclick) to 1- Insert a new row into the tbl_healthdiary_clarif then 2 - open another form based on the tbl_healthdiary_clarif at the record just inserted.

If there is a better way please suggest.

Thanks in advance.

Fionnuala · Accepted Answer · 2012-11-12 12:03:49Z

2

You cannot include a reference to the form inside the sql text, it must be outside, as it is a variable. For example, if ID is on a subform of the current form:

SQL_Txt = "INSERT INTO tbl_healthdiary_clarif (HDR_ID) Values ( " _
   & me.frm_healthdiary_edit_record.form.ID & ")"
CurrentDB.Execute SQL_Txt, dbFailOnError

To refer to a subform on a form outside the current form:

SQL_Txt = "INSERT INTO tbl_healthdiary_clarif (HDR_ID) Values ( " _
   & Forms.MyFormName.frm_healthdiary_edit_record.form.ID & ")"

You can also pass the ID as an OpenArg of the OpenForm method:

DoCmd.OpenForm "Clarification", , , , , , Me.ID

You can then refer to Me.OpenArgs on the form called Clarification.

edited Nov 12, 2012 at 12:03

answered Nov 12, 2012 at 11:58

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Darklantern Over a year ago

Thanks will try later tonight.

Darklantern Over a year ago

I get " Error 438; Object doesnt support this object or method. Tried your first code plus modified it to 'SQL_Txt = "INSERT INTO tbl_healthdiary_clarif (HDR_ID) Values ( " _ & Forms.health_diary_edit_filtered.Subfrm_healthdiary_edit_record.Form.ID & ")" CurrentDb.Execute SQL_Txt, dbFailOnError'

Darklantern Over a year ago

When i try your first script as is - "Method or data method not found."

Fionnuala Over a year ago

It seems likely that a name is wrong. What version of MS Access are you using?

Darklantern Over a year ago

This worked...Forms.frm_healthdiary_Edit_Filtered.Subfrm_healthdiary_edit_record.Form.ID...... I had the forms name incorrect - very frustrating, and i should clean up after myself.

|

Collectives™ on Stack Overflow

ms access insert data into SQL database table from subform

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related