Multiple subforms in datasheet view Results


I am a newcomer to this forum. I have limited experience of database use and form design, but with some background in programming I have so far managed to find a way to achieve what I need to. However I am currently stuck.......

I play in a dance band and have a databas for my bookings, dances and tunes played.

My "bookings" table has a primary "booking id" key and a bunch of other fields of info about the booking (date, venue etc).

I then have a "programme" table that has essentailly an "entry no" primary key, a "booking reference" and "dance name" which is a list of dances on the programme at a particular booking and therefore has a one-to-many relationship set up with the bookings table.

I have a form which displays the booking data and a sub-form that is displayed in datasheet mode listing all the dances associated with that booking and allows me to enter up the dances done at each booking. This all works just fine.

However I now want to go a stage further and display, on the same form, the particular tune titles that we used for the dance. I would like to move down the list in the programme subform and have displayed in a neighboring subform, also in datasheet view, the tunes we played for that dance (there may be 2 or 3 tunes used for each dance).

So I have a "TunesUsed" table which has a "programme entry" field and tune name, again with one-to-many relationship with the programme table.

I have tried unsuccessfully to get the information displayed on the form in the way I want to. I have also added in a booking reference to the TunesUsed table and tried defining a relationship back to the bookings table so that a triangle of relationships exists but I still cannot achieve what I want.

Any help would be appreciated.

Many thanks



First of all, I'm kind of a newbie to all of this, but not entirely, bare this in mind please ;-) Also I'm from the Netherlands and my english might be a bit rusty, so bear with me if you please.... :-)

I'm having a problem for which I cannot seem to find a solution. Below I've outlined the situation:

I have a couple of tables up to now:


CustomerTable has got a primary key: Clientnumber
CaseDateTimeTable has primary key: Casenumber
CaseTechInfoTable has no primary keys.

CustomerTable only includes (apart from all adress info etc) the clientnumber field which is of importance in this case i think.
CaseDateTimeTable includes both Casenumber and clientnumber.
CaseTechInfoTable also includes both Casenumber and clientnumber. Only both other tables have Primary keys, and this one doesn't.

I've created a relation between CustomerTable - Clientnumber and CaseDateTimeTable - Clientnumber.
Another relation I've made is between CaseDateTimeTable - casenumber and CaseTechInfo - casenumber.

Now, I have made a form in which the name and adress data is fed into the database, in normal form view. Below these textboxes etc. I've created a SubForm in datasheet view in which the existing cases from the at that time selected/displayed customer should be displayed. At the moment only fields from the CaseDateTimeTable can be displayed, but I would also like to display some fields from, for example, CaseTechInfoTable in that same subform.

But how?

When I create a (sub)form via the wizard and for example select:
CaseDateTimeTable - clientnumber
CaseDateTimeTable - Casenumber
CaseTechInfoTable - Problem description

and display these fields in the subform as a datasheet, then the separate cases from each customer are not displayed. I have 5 clients in my test dbase, just about all of them have 2 or 3 cases associated with them. In the example above only the data from two clients is displayed and then only from 1 case each, not the actual amount of cases. So for example:

clientnumber 4 has casenumber 5 and casenumber 6. But only casenumber 5 is displayed. The same happens for clientnumber 2, he has casenumbers 9 and 3 but only casenumber 9 is displayed.

The strange thing is though, that when I create the same (sub)form in datasheet view, but only use fields from the CaseDateTimeTable all cases per client are nicely displayed in the subform.

Why? And how do I solve this problem? I need to solve it so that I can add data from other tables into the subform later on so a nice overview of certain important data can be given per customer per case, even before the case form(s) is/are opened.

I've tried making a query and base a form on that, but for some reason I can't get the query to display all my customers, let alone all cases associated per customer. In the past I have made another (smaller and simpler) database for which I also made a lot of queries. But now? Pff... I'm lost at the moment.


I'm new to Access VBA programming but have been programming in ASP.NET for years. I think I'm missing something simple.

I have a form called frmContacts that comes up in datasheet view. It lists multiple contacts. I have two subforms, also in datasheet view, for phone numbers and e-mail addresses.

All forms are linked Master/Child on ContactID.

When I click on a row in frmContacts, I expect the subforms to update to the phone and e-mail addresses associated with the new ContactID of the row that has focus.

Does it not work this way? Am I missing something?

I also tried using a Form.Requery on the GotFocus event, to no avail.

Any ideas or concepts would be greatly appreciated. Thanks!

I built a Form "donor information" from a table containing just that info. I added a Subform to it that contains information about the "donation" that the donors provide -- a donor can make multiple donations. After building the subform, I display the Form in form view (it's fine) but the subform comes up in datasheet view, not form view. I changed all the "Allows" to "no" except for Allow For View.

How can I get the Form and Subform to be displayed according to the designs?

Thank you

I have a subform (in datasheet view) with multiple records. On the main form I have a Delete button. I'm trying to find how I can detect which consecutive records are selected on the subform so that when I press the Delete button, they are all deleted.

I'm getting error 3341 when trying to enter data into subforms.

I have set up a database with mulitple tables, these are the relevant ones:
One vineyard can have many blocks, one block can have many harvests and each harvest can have many sections. I am confident that the relational side of things is all ok, I have built queries which are all returning the desired results.
The form I am setting up to allow data entry:
The main form contains the vineyard name, the next subform contains the BlockHarvest details & the next subform contains the SectionHarvest Details. I have set up a filter to select the vineyard at the top from a combobox, the 2 subforms then display the relevant data. (a list of harvests in datasheet view, if you click on a record within this list it displays info on the SectionHarvest subform). I have also setup the filter to list in a combobox only the fields that belong to the vineyard selected at the top. This combobox is placed into the BlockHarvest section, the trouble is I am getting error 3341 "The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship." whenever I try to add new data to the BlockHarvest subform.

I have tried stripping down the form to allow data entry of the blockIDs (which is how I have entered some of the data thus far directly into the table tblHarvestBlock) & I am actually getting the same error without any of the code to control filtering etc.

If I open the subform directly on it's own without opening the main form, it allows data entry but the filter is not applied to only list blocks from the relevant vineyard. (ie it is a long list and you do not know which vineyard the blocks are from).

I have also tried building the form from a query, however this will only create new vineyard records rather than select from a list.
Any help would be appreciated, I would actually be happy to have pop-ups to enter the data rather than the subforms, however I am having difficultly filtering these.

Hi, Thank you for your time, I looked into this on your forum and this is the closest thing that relates, sortof:

what I am trying to do is get a subform(in datasheet view) to filter its records based on a single field or many fields within a form. as of now I am using one table which has the data (clientrecords), and a query like that in the link given above. My layout of the form is also like that in the example besides trying to have multiple combo boxes and fields.

I am having a issue using being able to use some of the fields and not others, if I use the example above then if I use all the fields its ok, but if I leave one out it does not work.

some of the fields I am using are frmfund, frmserv, and id.

Thank you very much for your help,

UPDATE: for my query I ended up using this in my criteria: IIf([Forms]![SubFrmMain]![frmFund] Is Null,[Fund],[Forms]![SubFrmMain]![frmFund])
([fund] being whatever is in the table) and it looks like it is working.

Dear All,

I have a form that has 2 subforms
The master-child fields are set

In Simple Form view I can see both subforms
but in Datasheet view I can only see the first one (I mean if I expand the records only the records of the first subform appear and those of the second one are not there)

Can you please tell me how I can 'tell' Access to use either the first or the second form in Datasheet view?

ps. the Relations are also set

Many thanks in advance!

After searching the forum and web, there does not seem to be a good solution, so here goes:

A comment form has a List Box. Highlight the list item and the long details (an overkill memo field) appears in the Text box with all the line wrap.

The customer wants the memo List Box to line wrap so they don't have to click on it to see the details.

These are suggestions:
Turn on Tool Tip - they can hover over.
Rating: not at all what they requested, but sounds simple, affordable solution.
(Anyone have implementation code to suggest?)

Flex Grid Control - set to expand all
Rating: Sounds intense and still not sure yet if it will autowrap
Memory leak that may or may not be fixed with patches

Use subform to datasheet view, it will look like a grid
Rating: Have used that, wonder if someone has an Auto Expand example
- Some comments are 1 line, other might be 5 lines long

Break up Field using SQL - create virtual multiple lines - click on any to select
found several people suggesting it might work in theory, like Bigfoot or North Dakota - no actual proof existence

Any suggestions or demo links would be greatly appreciated by me and evidently many other people.

I am relatively new to Access, using 2007. I have a form with a subform, subform in datasheet view. I was successful in setting up NotInList so that when I add a record to the UPC combobox it will update the appropriate table if a new record.

However, I've set up the form in datasheet view specifically so that I could copy and paste (and/or append) large numbers of records from excel. The NotInList works when I manually add one record at a time, but not when I paste multiple.

I'm thinking there is an 'event' in there I need to set up, but after spending quite a bit of time I'm lost as to which one, and how to do that. Any help would be much appreciated? (and please understand that writing code, while not impossible, is not simple for me...)

Hello all how are you doing. I am trying to create a screen that allows the user to add multiple records in a table at one time. I figure that using a subform in datasheet view will solve this problem (stop me if there are better ideas). I created the subform and linked it with the main form. Everything seems to be fine when there is data in the subform table. But I can't do anything if the subform table is empty. It would allow any updates but it keeps telling me my primary key fields are required. On the subform and forms I have "Allow Additions" marked Yes.

Does anyone know what I am doing wrong here.


I want to make a form with 4 subforms. The first form is linked to the master form but the next 3 subforms are linked to the 1st forms.
The first form contains vendor information.
The second form contains contact information for this vendor.
The 3rd and 4th subforms contain data relating to the contact information.

I need all 4 subforms to display datasheet view but have only been able to do this by creating a second master form in form view using my contact information (Wich I want to view in datasheet view)

Here is my character representation of what I want it to look like.
M=Master Form N=Subform by Level 1-4
__________________________________________________ _


1111 111111111 111111111 1111111111111
1111 111111111 111111111 1111111111111

2222 222222222 222222222 222222222222
2222 222222222 222222222 222222222222

3333 33333333 333333333 333333333333

44444 4444444444444444444444444 444444
__________________________________________________ __

I am new to VB and am hoping someone can help me set this up.

I have a subform in datasheet view with one field (Drinks) that is a combo box. I have it set up this way so that multiple drinks can be selected (Milk, water, 100% juice) and be linked to the main form by the FormID field. The Primary key here is DrinksID.

What I want to do is check that for each FormID, there are no duplicate items.
For example: Not:
FormID Drink FormID Drink
5 Milk 5 Milk
5 Water 5 Milk
6 Milk 6 Water
6 Water 6 Water

It seems like there should be an easy way to check for this, but I don't really know where to start. If someone could point me in the right direction, I would appreciate it!

I work at a small mental health clinic and am trying to accomplish the following. Access skills a little rusty, it's been a while.

What I have:I have one table that lists all of the patients, their phone number, address, etc. In another table, it lists the medications that each patient takes, as well as the strength, dosage, etc. The two tables have a relationship via the "Patient ID" column in each table. Most patients have multiple entries in the second table, as most patients take multiple medications.
What I am attempting to accomplish:The main form in my database simply lists all the patients names in datasheet view. I want to be able to click a Patient's name, then go to a form that lists all of that patients medications. I've attached a screenshot that encapsulates precisely what I am looking to accomplish. Since most patients take multiple medications, I need to be able to list multiple records in a single form, grouped by medication name, just like in the attached image.
I am uncertain as to how to accomplish this. I've tried using subforms but it doesn't list the data at all? Also, it looks like an Access report can create precisely what I need, but I need to have a live form that I can use to update data real-time.

I have a Form on which I have a Multi-Select ListBox from which I am able to retrieve IDNumbers that are attached to Names that also show in the ListBox.

What is the best way to take those IDNumbers and use them to display [on the same Form] related data from another Table?

I was thinking of a subform in datasheet view, perhaps?
The other Table also has a matching IDNumber field.

Since the user is able to select multiple IDNumbers - the datasheet has to be able to display detail for all the IDNumbers selected.

I'd appreciate any help.

Hello all I have a dilemma with some code that creates table entries from multiple subforms. There are three subforms that are based on one table, two subforms are in single form view as they only require one entry per project however the third subform is in datasheet view and has the ability to create several rows associated to one project. In the code the row is created combining all of this information but I cannot figure out how to reset the cursor to start a new row for multiple entries in the third subform. In an example say the three subforms all pertain to purchasing costs. One has y/n questions of overall equipment costs that may be incurred per project, the other has an estimate of overall shipment costs for the project and the third table is the datasheet in which you pick your purchase items and cost of that item. Is it possible to use VBA to call a new row and reset the cursor to that new row for data entry? ANY help is definitely appreciated!

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
'Sets the database as well as the file path
cn.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:UsersPretendDocumentsEstimateAccesshypothetical.accdb;"
'This gets the public variable FFProjectNumber. FFProjectNumber is created when the Start Date is selected on the front form
GetPublicVariable = FFProjectNumber
'Opens the desired recordset
rs.Open "tblAdditionalProcurement", cn, adOpenDynamic, adLockOptimistic
'Filters the recordset for ProjectIDs that are like the FFProjectNumber
rs.Filter = "ProjectID Like " & FFProjectNumber
'Sets the fields in the rs equal to the values that the user input or that was calculated on the top of the procurement field
rs.Fields("FedEx") = Me.txtFedEx.Value
rs.Fields("FedExCost") = Me.txtFedExCost.Value
rs.Fields("FedExFee") = Me.txtFedExFee.Value
rs.Fields("PCard") = Me.txtPcard.Value
rs.Fields("PcardCost") = Me.txtPcardCost.Value
rs.Fields("PcardFee") = Me.txtPcardFee.Value
'Updates, saves and closes the recordset

I use this and a similar code for the two single forms and this code for the datasheet form:

'Dims and sets the connections and the recordsets as new
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
'Sets the database as well as the file path
cn.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source= C:UsersPretendDocumentsEstimateAccesshypothetical.accdb;"
'MsgBox FFProjectNumber  The variable was correct when it was passed to this messagebox
Dim strProcurementType  As String
strProcurementType = cboProcurement
'Opens the desired recordset
rs.Open "tblProcurementFee", cn, adOpenDynamic, adLockOptimistic
'MsgBox strProcurementType
Do While Not rs.EOF
    If rs.Fields("ProcurementTransactionName") = strProcurementType Then
    txtFeetotal = rs.Fields("Fee").Value
    'MsgBox "Part 1 of the loop"
    'MsgBox "Part 2 of the loop"
    End If
'MsgBox "You have successfully popped out of the do loop"
End Sub

I have a subformClientOffers on my frmAllProperties. On the subformClientOffers is a field called Outside Agents. This field allows for multiple values to be selected.

The subfrmClientOffers is in datasheet view.

While the field Outside Agents DOES allow me to select more than one, they post in the field one after another:

Ken Shaw, John Doe, Jane Doe, Harlan Bestlyn

Therefore, the datasheet view of the subformClientOffers causes the field Outside Agents to get very long, much too long to view without scrolling. My client wishes for everything on the frmAllProperties to be viewable without scrolling.

Is there anyway, to cause the field Outside Agents to place a return between each selected name (sort of a wrap text kind of thing)?

One thing I might mention is that the field is a drop down field from tblOutsideAgents. The table itself has First Name and Last Name, but I concatenated that into Fullname on the form, so that when the user opens the drop down they see the entire name. I don't know if that would have any effect on making the 'return' thing work or not.

I can't use this field as a subdatasheet because I already have one subdatasheet attached to the subform to show multiple notes on each Client Offer.

Any suggestions?

Hi everyone,
This might be very simple, but I can't see to figure it out. I need to relate two tables, with a third. I have one table called data, one called all, and one called components. Rows in data need to be linked to some rows in all. Using the old autoDB, you can create a DB for workorders, this has a similar feature, IE the parts linked to workorders. Please help me figure out how to populate components ( has many 2 one link with primary keys of other two tables), so that the rows of all can be components of data. The goal of the form is to take the ID number from the data.* displayed, and populate the components table, at components.ProjectID, and then the subform will have the all information to populate components.componentID. I would also like to figure out why my subform only acts as a dropdown in datasheet view, with no possibiliy of adding multiple records. Please Help........

I am hoping someone can help me with this issue:

I have a form with two subforms.

Both subforms are in datasheet structure.

If you highlight a record in the first subform and click a button to 'transfer' I can determine which record is selected, and code the transfer.

If you click on the 'transfer all records' button, I can determine all the records and transfer them all.

The problem is in partial transfer:

Let's say I highlight the first 5 records. I want to be able to determine which and how many records the user has highlighted when they click on a button.

The problem is that even when MULTIPLE records are selected in datasheet view, the records are highlighted, but ONLY THE FIRST record has the current / active arrow in its selected row.

Is there a way of determining how many records, and which ones, the user has selected???

Thank you all for any help on this matter...

- arm1

I have an order entry form where the main form is customer info and subform is in datasheet view bound to orders table. There is a GearName, GearSize, and StockNumber field. GearSize requeries GearName to show only sizes related to GearName selection and StockNumber automatically displays. I run into trouble adding multiple orders on the subform....when I go to make a selection on the next row of the datasheet, my combo box selctions erase the previous orders entered. The requery does not effect only the specific row. Could someone please help me with an expression or code to segregate the rows of the subform?

Not finding an answer? Try a Google search.