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

Refresh Main form upon selecting an item in Subform

P: 3

I'm new to Access but I've searched for a long time for a solution but to no avail.

What I'm trying to do is create an address database whereby the user is able to enter addresses through a form and then have the abilitly to generate address labels.

I have 1 table called 'addresses'. Linked to this, I have a form called 'address form'. This is the main form. From here, the user is able to enter new contact details and scroll through existing records in the table.

Within the main form, I also have a subform which is a datasheet. Essentially, it is a view of the 'addresses' table. The idea being that the main form can be used for viewing individual records and the subform for viewing a list of all records in the table.

I want the user to be able to click on a row in the subform and the details of that row brought up in the main form.

The fields are common within the main form and subform and as a primary key, I have an autonumber generated by Access each time a new record is inserted.

I am running Access 2002 on win XP.

I am new to all this so I'd appreciate it if someone could explain how to do this in layman's terms.

Many thanks in advance!!
Mar 20 '09 #1
Share this Question
Share on Google+
5 Replies

P: 675
I created a table to simulate yours with fields Key (primary key & autonum), Number (number), and Text (text).
These 3 fields are displayed on form that I named fAddresses, as I don't like names with imbedded blanks. Blanks will always be a problem with everything you do, and I would strongly recommend they not be used. As capitalization is preserved, 'Camel' naming is most common. Not 'address form' but fAddressForm or frmAddressForm or simply frmAddress.
I created a form subfAddresses, datasheet view only.
I created control named 'subfView' containing form subfAddresses, DataSheet view only.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Forms!fAddresses.Filter = "Key=" & Me.Key
  3.     Forms!fAddresses.FilterOn = True
  4.     Forms!fAddresses.Requery
  5. End Sub
is attached as Visual Basic code to the subform = subfAddresses, OnCurrent event.
Line 3 only needs to be executed once, and can be in a form load procedure, but for simplicity I put it in the Current event.
I have removed anything in the control 'subfView' LinkChildFields & LinkMasterFields.
Mar 20 '09 #2

P: 3
Thanks for your reply OldBirdman.

There is however, a slight problem...I get the following error now:

"The expression On Current you entered as the event property setting produced the following error! A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control.

*The expression may not result in the name of a Macro, the name of User Defined Function or [Event Procedure].

*There may have been an error evaluating the function, event or macro.""

Do you have any idea what the problem might be?
Mar 23 '09 #3

P: 3

Had to have 'Option Explicit' at the top so in the end, this is the code that works for me:

Option Compare Database
Option Explicit

Private Sub Form_Current()
Forms!fAddresses.Filter = "Key=" & Me.Key
Forms!fAddresses.FilterOn = True
End Sub

Thanks for your help
Mar 23 '09 #4

P: 675
When I encounter such an error, with no real clue as to what I did wrong, I usually find it is a duplicate procedure name, or a duplicate variable declaration.
Did this message display in response to an action by you on a form?

My best method for locating this type of error is:
1) Close program
2) Make a backup copy
3) Open program, and display VB module for form that is causing this error
4) Copy the text in the module to a text editor (Notepad, Wordpad, ).
5) Remove 1/2 the text in the module (Cut to Clipboard), and try the operation that causes the error. Note: be sure to remove code for entire procedures.
6) If error still occurs, problem is in code not removed, otherwise error is in code removed.
7) Paste code back (Cntl+Z) and remove 1/2 of code in section that has the error. Restore from text editor if clipboard no longer has code.
8) Repeat 6-8 until found.
That is the best I can do. Although it looks lengthy, it usually finds the problem within 5-10 minutes.
Mar 23 '09 #5

P: 675
Glad you found it.

Options Explicit should be your default for all programming.

Any typo will be caught at run-time.
Capitalization is set in the declare, not with the last time entered. I usually have a capital letter somewhere within a name (variable, function, control), and enter into VB without capitals. The correct capitalization must occur as soon as the line of code is entered, or I know I have mis-spelled the name. Great instant check. Doesn't check form name in "Forms!frmName...." however.
Mar 23 '09 #6

Post your reply

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