Subform displaying existing record won't display new record

[postman]

I have a subform which is bound to a query which will display records using an ID# supplied from a listbox in the parent form.

That same subform is also used to create new records using the following code in a "New Record" commandbutton:

DoCmd.GoToRecord , , acNewRec

Both functions work fine. However, if I have a selected record displayed, and I click the "New Record" button, it does not move to a new record. It just stays on the selected record and any edits I make affect that selected record.

Any help you can provide is appreciated!

[puppydogbuddy]

If your button is executing from the main form, I believe you have to set focus on the subform to execute the new record command for the subform.
Try your code this way:

Me.YourSubformControlName.Form.SetFocus
DoCmd.GoToRecord , , acNewRec

[postman]

Thanks for the suggestion, puppydogbuddy. I tried it, but I get the following error:

Run-time error '2449': There is an invalid method in an expression.

It highlights the line with the "SetFocus" method for the form. I tried various approaches for the SetFocus, but they all end in the same error.

Any idea why?

Thanks for your help!

[puppydogbuddy]

oops, I forgot to set focus on a control on the subform source object(the form embedded in the subform control).

See this link: How to select controls on a subform with setFocus method in Access 2002

Me!YourSubformControlName.Form![ID].SetFocus 
DoCmd.GoToRecord , , acNewRec

[postman]

Thanks for the info. It wasn't working, but after looking at the table, I found that new records were being created, but the bound fields in the subform weren't clearing to display the new record--they were still displaying the selected record.

Ideas?

Thanks!

[puppydogbuddy]

Is the subform's "allow additions" property set to "Yes"?

[NeoPa]

Ideas?

Perhaps you need to requery the subform after the item has been added.

[postman]

I just realized what the problem probably is. The subform is bound to a query which uses a listbox value as the criteria. If a record is selected in the listbox, the subform is going to display those contents regardless of what's happening in the table.

I'll try a few things on that track and post the results...or more questions.

[NeoPa]

That's about the size of it. The requery should help ;)

[postman]

The simplest fix to the problem was just changing the subform's "DataEntry" property to True when the "Create New Record" button is clicked, and to False when the "Display Records" button is clicked.

But that created the opposite problem, now:
If I go first to Display Records, it will display the selected records fine.
But if I first Create a new Record, then go to Display Records, the subform won't display the selected record from the listbox.

(The subform's recordsource is a query that uses the listbox value as criteria. The Listbox's click event does requery the subform.)

Not sure why this is happening.

[ChipR]

From DataEntry Property:
The Data Entry property doesn't determine whether records can be added; it only determines whether existing records are displayed.

[NeoPa]

The simplest fix to the problem was just changing the subform's "DataEntry" property to True when the "Create New Record" button is clicked, and to False when the "Display Records" button is clicked.

But that created the opposite problem, now:
If I go first to Display Records, it will display the selected records fine.
But if I first Create a new Record, then go to Display Records, the subform won't display the selected record from the listbox.

I suggest that your solution possibly wasn't one then.

I would try looking at the .Requery route.

[postman]

I suggest that your solution possibly wasn't one then.

I would try looking at the .Requery route.

The .Requery route did not work.

After further testing, it appears to be a focus issue. I tried the earlier suggestions for moving the focus and I can't get the focus to move to the subform.

I even made test buttons on the parent form with code to just move the focus to a control on the subform and it won't work. The focus stays with the parent form. Not sure why that won't work.

[NeoPa]

After further testing, it appears to be a focus issue. I tried the earlier suggestions for moving the focus and I can't get the focus to move to the subform.

Could we see the code you tried.

[postman]

 
Private Sub Command173_Click()

     Form_AttDetail.aiDate.SetFocus

End Sub

Command173 is the command button in the parent form.
Form_AttDetail is the subform object.
aiDate is a control in that subform.

Thanks.