473,690 Members | 3,026 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Form/ subform problems

I have a table (Table A). It has one field, a PK. It is in a 1:M with
another table (Table B). I am having trouble with a form/subform setup
to view the 1:M records.

On the parent form, there is a combo box with a Select query to select
one of the records in Table A. (The parent form is bound to Table A.)
I want to be able to first, view all of the corresponding records from
Table B and then second, give an option for editing these records via
a command button.

My first problem is that when I select a record in the combo box on
the parent form, the corresponding records in the subform don't
appear. I have the parent and child fields linked properly, I think. I
have tried to change my key structure in Table B as well, trying both
a single and a composite key. I thought that when I selected a record
on the main form, the subform automatically populates with the data.
BTW, the subform is in single form display mode, not continuous. Does
this matter when attempting to view many records from Table B? Doesn't
Access create a recordset of all the records in Table B so that I can
view them one at a time with the navigation buttons in the subform?

My second problem is that when I move out of the combo box to a field
on the subform, Access tells me that it can't create a record because
it would create duplicates values in the PK field (Table A). I know
this. I don't want to create a record, I just want to view the
records. I have another form for the creation of a new record and it
works fine. What is the preferred way for opening a form/subform in
view mode only?

Thanks in advance.

Troy Lee
Sep 29 '08 #1
1 3598
tr******@comcas t.net wrote:
I have a table (Table A). It has one field, a PK. It is in a 1:M with
another table (Table B). I am having trouble with a form/subform setup
to view the 1:M records.

On the parent form, there is a combo box with a Select query to select
one of the records in Table A. (The parent form is bound to Table A.)
I want to be able to first, view all of the corresponding records from
Table B and then second, give an option for editing these records via
a command button.
Unless you actually intend to edit the single field in tableA you do not
need the main form to be bound. All you really have is a form for selecting
the gropups of records from tableB.

My guess is that you have bound not only the form, but also the ComboBox and
that would be incorrect. When you make a choice in that you are actually
editing the current main record (not what you want I assume).
My first problem is that when I select a record in the combo box on
the parent form, the corresponding records in the subform don't
appear. I have the parent and child fields linked properly, I think. I
have tried to change my key structure in Table B as well, trying both
a single and a composite key. I thought that when I selected a record
on the main form, the subform automatically populates with the data.
BTW, the subform is in single form display mode, not continuous. Does
this matter when attempting to view many records from Table B? Doesn't
Access create a recordset of all the records in Table B so that I can
view them one at a time with the navigation buttons in the subform?

My second problem is that when I move out of the combo box to a field
on the subform, Access tells me that it can't create a record because
it would create duplicates values in the PK field (Table A). I know
this. I don't want to create a record, I just want to view the
records. I have another form for the creation of a new record and it
works fine. What is the preferred way for opening a form/subform in
view mode only?
This confirms that you are editing the current main record and trying to
make its value equal to that of another record in the same table and thus
you ARE trying to make two records have identical keys.

Just remove the RecordSource of the main form and everything will work.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 29 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3054
by: Max Harvey | last post by:
Hi, I made up a nice little form which had its own sub form in it. I made a litle VB code so that when I pressed a button it would move form the form (frmConference) to the subform (frmBookingBookingBased), start a new record, and copy some values form the form into some fields in the subform, and then leave the cursor sitting (in focus) at the begining of the subform.
12
12938
by: swingingming | last post by:
Hi, in the NorthWind sample database, when clicking on the next navigation button on the new order record with nothing on the subform (order details), we got an order with nothing ordered. How can we prevent this from happening? Thanks. ming
4
8501
by: crystal | last post by:
I've checked the threads but haven't been able to come up with a solution to my issue. Help...... I have a simple form based on a table. Within the form is a subform that is also, through a Q, based on the same table. The code: Private Sub Select_BeforeUpdate(Cancel As Integer) On Error GoTo resetselect_Err
2
4289
by: David W. Fenton | last post by:
I think at various times we've all encountered this problem: A subform is on a main form. From the code of the main form we refer to some property of/control on the child form thus: Me!subForm.Form!txtTextBox and for some reason, in certain contexts, we get the error: Error 2455: You entered an expression that has an invalid reference
11
5849
by: BobBanks | last post by:
Hi, Im making a db for my company in Access 2003 usin xp sp2. Ive created an orders form that has a subform in it. The Order form (parent(is linked to a Orders Qry)) and the Orders Subform (Child(is linked to Order Details Extended Qry). This works similar to the Orders form in Northwind, ok ok its pretty much identitcal What happens is i select a client in the main form by selecting the company name and that pulls up their details. In...
11
7886
by: kabradley | last post by:
Hello Everyone, So, thanks to nico's help I was finally able to 'finish' our companies access database. For the past week or so though,I have been designing forms that contain a subform and an option group so that whenever a certain button on the option group is pressed the correct subform source object property is changed to display the correct form. For instance, if they click "add new investment" the subform's source object is now changed to...
11
7156
by: mrowe | last post by:
I am using Access 2003. (I am also using ADO in the vast majority of my code. I recently read a post that indicated that ADO is not all that is was initially cracked up to be. In the back of my mind I am wonder if this is causing my problem, but I don’t want to go through the work to convert to DAO unless I know it is truly in my best interest.) I am having problems getting a requery to show up consistently on a couple of forms. I have...
6
10755
by: solom190 | last post by:
Okay this is the situation I have two forms and they have a 1:M relationship. I don't have enough space screen-wise to do a traditional "drag form to form" to create a subform so what I did was create command buttons that open the subform when I click on it. I added a filter to the VBA that filters the results of the subform to display only records that match the linking field of the two forms. And it those and would filter but..I am having...
0
1706
sickenhoofer
by: sickenhoofer | last post by:
I am having some problems with a form and its subforms. It was designed to click on a list of numbers on a subform, which causes another subform's data to change to data relevant to the number selected. Attached are 2 pictures: 1. snapshot of master form w both subforms; 2. snapshot of relationships for tables involved in problem. Here is the main set up info: Table 1 = tblProviderMain ProviderMainID (PK - autonum) Table 2 =...
0
8598
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8951
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8786
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8793
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7623
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6457
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5811
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2965
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2225
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.