Solved

Datasheet view in a columnar form

Posted on 2006-11-09
29
824 Views
Last Modified: 2008-02-01
I have a table consisting of 5 fields.  I used the Form Wizard to create a new form containing the 5 fields.  I used the COLUMNAR layout for the form, which placed the fields and labels on the columnar form, one above the other.  This is all fine and great.  However, the problem with columnar layout is that there's no way to view more than one record at a time for the sake of jumping to another place in the table.  In other words, let's say I have 1000 records in the table.  Well, when I'm viewing my columnar form, I can only see one record (e.g., indexed field: Lastname = record #1: "Anderson").  But let's say I want to jump to the first record where the last name begins with the letter J.  Currently I have to hit the navigation button hundreds of times in order to get to the first record with the letter J.  This is a ridiculous method to navigate through the table, obviously.  I was hoping there was a way to have quick access to the entire table simply for the purpose of being able to jump to another record, and then return back to the columnar form once the current record position has been placed in the first record that begins with the letter J.  There's simply got to be a better way to navigate a columnar form other than clicking "Next>" a hundred times.
0
Comment
Question by:Softtech
  • 15
  • 12
  • 2
29 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 17912108
Open your form in design view and locate "Form View" property to change it to datasheet view.  What you have is not datasheet.

mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 17912123
Change "Defualt View" property to "Datasheet".  What you have is "Single Form".

In my last post I have it by mistake "Form View" property.  "Defualt View" is the correct name (6th property).
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17912157
You have other options as well (for example) :

1. You could have a continuous form view (similiar to a datasheet, but looks a little different)

2. You single form view with an embedded continuous view subform
0
 

Author Comment

by:Softtech
ID: 17914209
>> 1. You could have a continuous form view (similiar to a datasheet, but looks a little different)

Yes, that will allow me to SCROLL quickly to another record in the table, but that falls a bit short of what I was hoping to accomplish.  I'll give you points for satisfying the "got to be a better way to navigate" aspect of my question.

>> 2. You single form view with an embedded continuous view subform

I have tried #2, but it has failed. Adding a subform, which is linked to another form (call if form2) and with form2 set to 'datasheet' for both its DefaultView and ViewsAllowed property, when I display the primary form (in single form view), the subform only displays one record at a time, and that is the very same record I'm viewing in the main form.  So I've accomplished nothing by adding a subform.  For some reason Access forces the subform to a single record display, even though all the properties for the subform are set to datasheet.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17914343
set the subform property to continuous form and set the master/child linking fields of the subform container on the main form
0
 

Author Comment

by:Softtech
ID: 17914445
>> set the subform property to continuous form

I've done that.

>> and set the master/child linking fields of the subform container on the main form

I've done that.  Still, there is nothing more than one record shown in the subform within the main form.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17914818
check the record source of your subform.....the query or table referenced should correspond to the "many" side of a one to many relationship if you viewed it in the relationship window.
0
 

Author Comment

by:Softtech
ID: 17914939
>> the relationship window.

There is only ONE table, and zero queries, so how can there possibly be a relationship when all you have is one table?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17915239
then what are the many records you expected to see in the subform for each record you displayed in the main form??  if you just want display all the records in the one table , then you just need a main form with its property set to continuous form or datasheet, using the one table as a record source.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17915265
PS:  If you want to be able to select one of the records to go to, all you need is a combobox.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17915367
Use the wizard to create your combo;be sure and select the option "Find a record based on the selection"
0
 

Author Comment

by:Softtech
ID: 17915413
>> then what are the many records you expected to see in the subform for each record you displayed in the main form??

I'm trying to see ALL THE RECORDS FOR THE ENTIRE TABLE so that I can reposition the current record and then return back to the main form.  Think of an INDEX in a paper book.  I want to see an index/list of all records, highlight the record, and then return back to the main form with that newly selected record in the main form.

>> all you need is a combobox.

I've tried a combo box on the main form, linked to one of the fields, but that only displays the content of that one field for that one record.  It does not display all of the records in the table, and even if it did, I would suspect you would simply be changing the content of that field, rather than jumping to that record.

>> then you just need a main form with its property set to continuous form or datasheet

A datasheet is not want I want as the focus of the main form.  I want only the current record to be the focus of the main form.  I would like a datasheet to appear temporarily only for the purpose of jumping to a new record, then disappearing as it returns back to the main form.   I would not mind having a datasheet view on a second page of a tabbed form, or as an inset on the main form.
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 250 total points
ID: 17915985
Ok, what you are wanting to do is filter your main form based on the record you select on the subform.  This requires VBA code to accomplish, which I am giving you a link for:

                  http://www.allenbrowne.com/ser-28.html
0
 

Author Comment

by:Softtech
ID: 17916049
>> Ok, what you are wanting to do is filter your main form based on the record you select on the subform

No, that is not what I want to do.  I'm not looking to filter anything.   On top of that, no subform I have seen/configured yet has displayed anything more than the current main form's record.  So what's the purpose of filtering a single record subform?
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17916671
Let's do this so you can see things a little clearer.
1.Remove the master/child link in the subform container.
2. Blank out the record source of the main form to leave your main form record source unbound,
3. Leave your table as the record source for the subform.

When you open your main form you should see all the records in the subform and no records on the main form.

If you've gotten to this point, let's do the following:

4. Leave the main form record source unbound, but add a combo box to the main form (if you are not sure how, use the combo box wizard and select the option "Find Record......" when you come to it).  The row source of your combo box will be your table with the key field (as the bound column) and one or two fields selected for display in the combo box.)

5. Ok, now let's put the master child links back in to the subform container.  The master link will be your combobox (the value selected from it).  The child link will the key field  to your  table.

Now try your form. Open it  up and make a selection from your combo box, and the subform should respond by displaying only the record selected in the subform.  If it doesn't it is only because the wizard did not reference the subform correctly.

0
 

Author Comment

by:Softtech
ID: 17916817
>> If you've gotten to this point, let's do the following:
>> 4. Leave the main form record source unbound, but add a combo box to the main form
>> (if you are not sure how, use the combo box wizard and select the option


I can follow you up to #4, at which point I must point out there is no such thing as a COMBO BOX WIZARD with Access 2000.

And yes, by detaching the main form from the table, and using the table soley with the subform, the subform displays all of the records.  But at this point I'm stuck.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17916874
There is a combo box wizard in Access 2000.......click the toolbox on your toolbar and run your cursor over the icons you see until the tool tip says combo box, then click it. You will be prompted to select different options.  Let me know what happens so we can take the next step.
0
 

Author Comment

by:Softtech
ID: 17917045
>> There is a combo box wizard in Access 2000.......click the toolbox on your toolbar
>> and run your cursor over the icons you see until the tool tip says combo box

You forgot that there is a magic wand button on the tool bar button that has to be depressed (in the down state) before any wizard actions occur with a tool bar item selection.

OK, so now I get the wizard, but there's nothing about "Find record....." in this wizard.

Nonetheless I tried my best and associated a field in the table to the combo box.

When I run the form, I get all of the fields in the main form as blank (unbound) except the newly added combo box (which does show the items in the table) plus the subform shows a datasheet view of all the records.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17917459
Sorry about that wand button...toward the end of the wizard prompts you should have had a choice between Using the combo box selection to Find or select a record   or Store it for later use.

At this point, you should be able to select a record from the combo box and the subform should respond by displaying only the record matching your selection from the combo box.  

If it isn't it is probably because of the following:
1. the master/child links have not put in correctly.  Click the subform container on the main form, make sure that the following is entered :
   Master Link : your combo box name   (something like cboSelector)
   Child Link:  the key field in your subform corresponding to the bound column of your combo box.
           
 If it doesn't work like that, copy and paste any vba code that is behind your form or subform.
0
 

Author Comment

by:Softtech
ID: 17917546
>> toward the end of the wizard prompts you should have had a choice between Using
>> the combo box selection to Find or select a record   or Store it for later use.

Nope.  There is never any such prompt or anything that comes close to it.  Here's what the wizard displays when you drop a combo box on a form:


Wizard steps:

(x)  I want the combo box to look up the values in a table or query
( )  I will type in the values that I want

Next >

------------

Which table or query should provide the values for your combo box:

Table1

View
(x) Tables   ( ) Queries   ( ) Both

Cancel  < Back   Next >

------------

Which fields contain the values you want included in your combo box?

Available fields                Selected fields




Cancel  < Back   Next >

------------

How wide would you like the columns in your combo box?


Cancel  < Back   Next >

------------

What label would you like for your combo box?

Cancel  < Back     Finish

0
 

Author Comment

by:Softtech
ID: 17917654
>>   Master Link : your combo box name   (something like cboSelector)
>>   Child Link:  the key field in your subform corresponding to the bound column of your combo box.

I typed in the name of the combobox in the subform's Link Master Fields.  I typed the field name displayed in the combobox into the subform's Link Child Fields.  I set the combobox's bound column to the field number that I want displayed.

I run the main form, and now the subform only displays only the single record that corresponds to the selected record in the combobox.

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17917855
ok.....I guess I must be thinking of another wizard .. let's do this.

1. place your form in design view , highlight the combo box and go to the combo box's property sheet.
2. look at the name property given to your combobox.....this is the  name that has to be in the master link field.

2 there should be no control source (control source property s/b blank)

3. RowSource Type should be table/query
4.Row source....click build button on the right with 3 dots...should take you to a query grid where you will see your table and the fields selected from it for the combo box (the first field selected should be the key field to your table which will be hidden by giving it a 0 width (and should be the same field as the Child link in your subform control, then you should have the other field or fields you want the user to see in the combo box.
5. # of columns should be columns 2? or 3? depending on fields selected;bound column should be column 1 in this case.
6. col widths 0,2?,2?
7. compile and save and try form out
7. if still does not work tell me what happens
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17917933
ok, all we need to do is add "ALL" as a combo item or create a reset button..let me know what you prefer..will get back to you.
0
 

Author Comment

by:Softtech
ID: 17917962
> 7. compile and save and try form out
> 7. if still does not work tell me what happens

It does exactly what I described in my last reply: "I run the main form, and now the subform only displays only the single record that corresponds to the selected record in the combobox."

The fields on the main form display "#Name?".  "Name" BTW is the field displayed in the combobox.

All we've accomplished in this exercise is to link a combobox to a subform.  This is not what I was trying to accomplish.  The main form itself is now useless.
0
 

Author Comment

by:Softtech
ID: 17918000
>> ok, all we need to do is add "ALL" as a combo item or create a reset button

ALL what?  How does adding "ALL" (whatever ALL is) to a button or a combo item populate all of the text box fields on the main form with the corresponding data in the table?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17918101
A while back I gave you a link to follow if you want to populate your main form from the subform....and you said that wasn't what wanted....now you are back to asking for that.  What do you want to see on main form that is not shown on subform??  If it is only a few fiels, we can populate them from the combo as well.
All will repopulate the subform with all records
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17918139
here is how you would populate the name field on main form:

in control soource put : = [YourComboName].Column(1)
0
 

Author Comment

by:Softtech
ID: 17918573
>> A while back I gave you a link to follow if you want to populate your main form from the subform

No, you gave me a link on how to FILTER a subform....a subform at that time that only displayed 1 record.

In any case, I finally figured it out.

Thanks for your assistance.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17919343
Glad you got it resolved.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now