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]

@postman
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]

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

I would try looking at the .Requery route.

[postman]

@NeoPa
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]

@postman
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.

[puppydogbuddy]

Private Sub Command173_Click() 
 
     Me!Form_AttDetail.Form!aiDate.SetFocus 
 
End Sub
 

[postman]

@puppydogbuddy

It gives an error stating it "can't find the field 'Form_AttDetail' referred to in your expression."

I tried changing it to

Me.attDetail.Form!aiDate.SetFocus

This eliminated the error, but nothing happens ("attDetail" is the subform control name and "AttDetail" is the subform name).

[NeoPa]

Check out Referring to Items on a Sub-Form. I think it may explain the situation fully enough.

Let us know if you still have problems after reading through it.

[puppydogbuddy]

see the suggested syntax from the link I gave you in post #4

Me!attDetail.SetFocus
Me!attDetail.Form!aiDate.SetFocus
 

[postman]

@puppydogbuddy
Thanks. That did the trick.
The syntax I used was this:

Me.attDetail.SetFocus
Form_AttDetail.aiDate.SetFocus

I use "Form_AttDetail" because it's shorter and the intellisense will display the controls/properties which means less typing. Using the the "!" method does not give the intellisense menus. Is there a disadvantage to this?

I still have to switch the recordsource of the subform to the table if I'm adding new records and to the query if I'm displaying records based on the listbox selection.

Thanks for all your help!

[puppydogbuddy]

Postman,
Glad to see you finally got the syntax to work.

In regards to the bang (!) vs dot (.) operators: Although they are now often interchangable .....there are technical differences between them that could more strictly enforced by the Access compiler in future editions of Access, prompting code changes for compliance.

Technically, the ! operator is used to indicate that what follows is a user-defined item (e.g.an element of a collection). For example, use the ! operator to refer to an open form, an open report, or a control on an open form or report.

On the other hand, the . (dot) operator usually indicates that what follows is an item defined by Microsoft Access. For example, use the . (dot) operator to refer to a property of a form, report, or control.

In regards to your statement about having the proper code for switching between existing records and new records, here is one way you can branch your code:

If Not Me.NewRecord then
-----XXXXX code for existing record
Else
------xxxxx code for new record
End If

[postman]

Thanks for the info. The lingo helped me do some research on the bang vs dot vs quotes thing.

I found a couple interesting articles...
http://my.advisor.com/articles.nsf/aid/05352

"In addition to ControlName being a member of the Controls collection of the form, it's also a property of the form...every time you add a control to a form (or report), Access adds a property with the same name and type as your control to the form's class in your VBA project...Using dots rather than bangs also makes your code run a bit faster."

http://blogs.msdn.com/frice/archive/.../18/75685.aspx

"Using dots rather than bangs also makes your code run a bit faster....[The bang operator] also has the disadvantage of causing a speed penalty. Why? ...Behind the scenes, Access translates the dot-and-bang reference to the parentheses and quotes style anyway. As a result, while the bang will save you a few strokes, the translation will incur a performance penalty. Thus the recommendation is to always use the parentheses and quotes styles when referring to a member of a collection "

Thanks again! :-)