By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Subform displaying existing record won't display new record

P: 63
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:
Expand|Select|Wrap|Line Numbers
  1. 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!
Jan 21 '09 #1
Share this Question
Share on Google+
21 Replies


puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. Me.YourSubformControlName.Form.SetFocus
  2. DoCmd.GoToRecord , , acNewRec
  3.  
Jan 22 '09 #2

P: 63
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!
Jan 22 '09 #3

puppydogbuddy
Expert 100+
P: 1,923
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
Expand|Select|Wrap|Line Numbers
  1. Me!YourSubformControlName.Form![ID].SetFocus 
  2. DoCmd.GoToRecord , , acNewRec 
  3.  
Jan 22 '09 #4

P: 63
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!
Jan 22 '09 #5

puppydogbuddy
Expert 100+
P: 1,923
Is the subform's "allow additions" property set to "Yes"?
Jan 23 '09 #6

NeoPa
Expert Mod 15k+
P: 31,186
@postman
Perhaps you need to requery the subform after the item has been added.
Jan 26 '09 #7

P: 63
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.
Jan 27 '09 #8

NeoPa
Expert Mod 15k+
P: 31,186
That's about the size of it. The requery should help ;)
Jan 27 '09 #9

P: 63
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.
Jan 27 '09 #10

Expert 100+
P: 1,287
From DataEntry Property:
The Data Entry property doesn't determine whether records can be added; it only determines whether existing records are displayed.
Jan 27 '09 #11

NeoPa
Expert Mod 15k+
P: 31,186
@postman
I suggest that your solution possibly wasn't one then.

I would try looking at the .Requery route.
Jan 27 '09 #12

P: 63
@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.
Jan 27 '09 #13

NeoPa
Expert Mod 15k+
P: 31,186
@postman
Could we see the code you tried.
Jan 27 '09 #14

P: 63
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command173_Click()
  3.  
  4.      Form_AttDetail.aiDate.SetFocus
  5.  
  6. End Sub
  7.  
Command173 is the command button in the parent form.
Form_AttDetail is the subform object.
aiDate is a control in that subform.

Thanks.
Jan 28 '09 #15

puppydogbuddy
Expert 100+
P: 1,923
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command173_Click() 
  2.  
  3.      Me!Form_AttDetail.Form!aiDate.SetFocus 
  4.  
  5. End Sub
  6.  
Jan 28 '09 #16

P: 63
@puppydogbuddy

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

I tried changing it to
Expand|Select|Wrap|Line Numbers
  1. Me.attDetail.Form!aiDate.SetFocus
This eliminated the error, but nothing happens ("attDetail" is the subform control name and "AttDetail" is the subform name).
Jan 28 '09 #17

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 28 '09 #18

puppydogbuddy
Expert 100+
P: 1,923
see the suggested syntax from the link I gave you in post #4
Expand|Select|Wrap|Line Numbers
  1. Me!attDetail.SetFocus
  2. Me!attDetail.Form!aiDate.SetFocus
  3.  
Jan 28 '09 #19

P: 63
@puppydogbuddy
Thanks. That did the trick.
The syntax I used was this:
Expand|Select|Wrap|Line Numbers
  1. Me.attDetail.SetFocus
  2. 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!
Jan 28 '09 #20

puppydogbuddy
Expert 100+
P: 1,923
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
Jan 28 '09 #21

P: 63
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! :-)
Jan 30 '09 #22

Post your reply

Sign in to post your reply or Sign up for a free account.