Add record to subform using a cmd button on the main form

[lkennemur]

I am trying to come up with the code to put behind a command button on my main form to create a new record in my subform. Any ideas?

 

[Bstrick75]

When you say create a new record, it sounds like there is a table that the subquery uses. Is this correct?

If so, all you need to do is write an update query to that table and then update the listbox or whatever you're using to hold the data within the subquery.

if you have a listbox in which you're manually adding records to its items collection, then you'd just use the bang notation to access the subform.

Like: forms!subform!lstbox.items.add(newrecord)

If its a textbox you're updating then try:
forms!subform!textbox.value = "newvalue"

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[lkennemur]

I'm not using any queries/subqueries on the form or the subform. Yes, the subform's source is tblOperatorResponses and the main form's source is tblQstn. Do I still need to try to update tblOperatorResponses on the subform?

Thanks

 

[PHV]

Provided the form and the subform are properly linked:
Me![name of subform control].SetFocus
DoCmd.RunCommand acCmdRecordsGoToNew

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bstrick75]

You don't need to update the record on the subform. You should add the record to the table and then refresh the subform to reflect those changes.

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[lkennemur]

I put the code above in behind my command button on the main form and I got this error message:

The command or action 'RecordsGoToNew' isn't available now.
Any idea on how to debug this?

I have to add the record to the subform because this is a survey db. I have the questions on the main form and the responses are entered in the subform.

I have been struggling with this problem for a while now. It seems like it shouldn't be this hard but it's turning out to be a nightmare.

Thanks for all help!!!

 

[Bstrick75]

But you are storing the responses in a table aren't you? If so, simply write and update query to update the table. When you open the subform the listbox or whatever you're using will refresh. Otherwise you can set a button to refresh the data yourself.

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[lkennemur]

QstnID Qstn Navigation buttons for questions
----------------------------------------------------
Subform

OperatorID(populated by a different form and must stay the same throughout the survey)

Response

This is a basic layout of the form. When I enter a response I click the command button for the next question and I want the subform to add a record. If the user makes a mistake, they need to be able to click the command button on the form and it should bring up their response. I don't want to view all the responses to a question just that operator's response.

I am new at this so I am looking for some coding help. I am struggling.

 

[Bstrick75]

Ok,

If you update the table as I suggested then you would need to creat a query off of that table. The query will have one parameter which would be the Operator's name or ID (whichever you're using) then you link the subform to the query instead of linking it directly to the table. That way when you view the subform, it only shows records that are attached to the operator.

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[lkennemur]

If I do this will I need to include the questionID? QuestionID is in the Link Master and Link Child fields. The main form is populated by tblQuestion. If I do this, will it screw up the main form?

Thanks

 

[Bstrick75]

It would seem to me that if you query off of the persons name or nameID then you will get all of the answers that they gave. If you only want to view certain answers then "yes" you would need to include the QuestionID. It depends on how your tables are setup.

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[lkennemur]

I will try it. If I run into problems I will post it. It sounds like it should work.

Thanks a bunch!

 

[lkennemur]

I want to add new responses not just view them. I want to be able to open the form and enter a new response to each question and have a record of the responses that the user just entered. Do I have to somehow create a new recordset?

Thanks

 

[Bstrick75]

What controls are you using on the subform to view your data? Are you using a listbox?

Are you updating your records from the main form or the sub-form?

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[lkennemur]

I am using combo boxes and text boxes. Currently I have the subform property DataEntry = Yes so that I don't see the other records and I can enter new responses to each question. That works except I can't go back and look at the response that I just entered. I can only go foward. It records my response, operatorID,qstnID, and operatorResponseID in tblOperatorResponses.

 

[Bstrick75]

If I were you, I'd put a listbox on the subform. The listbox would have all the answers to the questions listed in it. When the user submits an answer, I would use the listbox.refresh method to update the listbox with all the user's answers including the one they just entered. If you wanted to get fancy then you could give them the ability to click an item within the listbox and press delete so that they could delete the answer and re-submit a new one. You would use the ListIndex property of the listbox to determine which item the user selected to be deleted.

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[lkennemur]

That would work on the multipe choice responses but not on the narrative feedback responses. These responses can vary so vastly that there is no possible response.

Thanks!

 

[Bstrick75]

If your doing narrative feedback the maybe you should use a textbox instead. Make the textbox large enough to show all the information. If the operatorResponseID is autoincrementing then you can just use ADO to query the reponse table where the operatorResponseID = 1 - the operatorResponseID in the form. Then assign the results to the text box. Make sure that the textbox is either locked or disabled.

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle