This would be so cool...if Access can do it

[JohnGunn]

Here's a challenge for a kind of dataentry form I have been creating for years, but in a different language:

--The form has a pageframe with 2 (or more) pages. 2 is enough most of the time.

--The first page contains a datasheet with all the records in a table, view or dynaset you want to work in. The fields in this datasheet are read-only. It's just for lookup.

--The user scrolls through the datasheet on page 1 and highlights a row by clicking on it anywhere.

--The form then automatically switches to Page 2, which presents the fields of the row the user clicked on. These are represented in form view format and can be edited.

{The way I have been doing this, when the user then clicks Save or Cancel, Page 1 is re-activated. This part I can handle).

In Access, it appeared to me that I wanted to place a subform in the datasheet view on Page 1 of the pageframe, give it a controlsource of one of my tables, and for each of 3 or 4 columns in the datasheet, add Click event code to switch to page 2. Here's what I'm not clear on:

In effect, the table in the datasheet is the parent. The fields on page 2 would be from that table or from a query based on that table and related 1:1. But in Access the assumption seems to be that the subform's datasheet is a child of the table that is the form's recordsource.

It seems I need to do some trickery to the Link properties for child and parent so that the subform always shows ALL rows of the table, but the FORM (i.e. Page 2) shows only the field of the current row.

Any suggestions? Is this something y'all do all the time? If not, when I get it working I'd be happy to post a prototype form somewhere...

 

[Adrianna]

Didn't read through the whole wish list, but you can use page tabs for the multiple pages...and an unbound list box to allow users to select a record from a table like format! When they made a selection, complete the code to use the selected information to fill out the next page.

I know that it works with listboxes and comboboxes, feeding forms, queries, and reports. I just don't use it to often, and I don't know how to make it automatically jump to the next page...I'm sure someone here will help you with more specifics.

 

[JohnGunn]

I ruled out using a listbox or combobox instead of a datasheet because my experience with these (in MS Visual FoxPro) is that when you put in more than a few hundred values you're in trouble. Very slow to populate and very slow to look up in.

That's why I am trying to use a grid-type presentation which in Access I guess is called a datasheet. Access shouldn't have to pre-fill the control with every record in a huge table.

I agree that for a dozen or so rows, that's be a good way to do it.

 

[Adrianna]

John,
Why don't you just use a query to prompt the user for criteria to populate your form or report? I guess you've mentioned that there are a lot of records to thumb through, so I'm wondering why would you want to have them scroll through a huge datasheet. if they are patient enough to do one...they should be patient enough to do the other.
Just an idea. I guess I will leave this one alone. I just haven't had a need and I guess I don't understand exactly what you want to do this for.

 

[KKilfoil]

I associate the following code with a double-click event on my 'lookup' form. This code opens up a completely separate form and filters the contents to display only the current record from the form that is open. Obviously, you should substitute YOUR object names: :


Private Sub Form_DblClick_Click()
On Error GoTo Err_Form_DblClick_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProjects"

stLinkCriteria = "[ProjID]=" & Me![ProjID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Form_DblClick_Click:
Exit Sub

Err_Form_DblClick_Click:
MsgBox Err.Description
Resume Exit_Form_DblClick_Click

End Sub


HTH