Tabular Data Design

[Ramsi2001]

Hi,


I am thinking of a design of the forms in a following way:


I have a search criteria. Based on the search results, i want to populate the data in a tabular form on the same page below:

And when the user selects a record in the results pane, he should get redirected to a modify page wherein he can edit the record he selected.


Can any one suggest me the best way to design this?

[pbaldy]

If you have a variety of optional search criteria, the sample db in this FAQ demonstrates one way of doing the search form:

http://www.baldyweb.com/BuildSQL.htm

Once you have the resulting records, this technique can be used to open the second form:

BaldyWeb wherecondition

[Ramsi2001]

Hi There,
Thanks a lot for the reply!!!

I went thru the links and they solve my problem mostly.

The only question i have is:

Where i should write the code of OpenSecond form?

How will the system know which record i have selected? Do i need to use any command button for this purpose?

I am a bit new to access and hence the questions? Sorry to bother you...

Thanks,
Ram

[pbaldy]

Don't be sorry about asking questions. This wouldn't be much of a forum without questions!

Two common places for the code to open the second form would be a button located in the detail section of the form or the double click event of a textbox in the detail section (ie, double click on a person's name to edit their details). In either case, "Me.ControlName" would refer to the record being clicked on.

[Ramsi2001]

Thanks a lot Paul!!!

I think it will work for me.


Thanks,
Ram

[pbaldy]

Happy to help Ram. Post back if you get stuck. Welcome to the site by the way!

[Ramsi2001]

Hi Paul,
I am here after a long time.

As we discussed, I could able to open the new record in a modify screen.

But the problem is with the record navigation and record selectors.

The record no. is always shown as 1 even if i select any other record. This was when i try to navigate to other records, it is giving me duplicate index error.

For example, if i select third record, it's showing as 1 always.

How can i show exact record no in the record navigator?

PLease help.

Thanks,
Ram

[pbaldy]

I confused as to what the problem is. Can you post a sample db, or at least a picture?

[Ramsi2001]

Hi Paul,


I have attached the screenshots in the doc file.

In the first screenshot, i am displaying the search results. When the user double clicks a record, i am opening the modify page.

Here is the code i am using to open the modify page.

reccount = Me.CurrentRecord
'MsgBox (reccount)
varargs = Project_ID.Value & ";" & reccount
DoCmd.OpenForm "frmProj_Modify", , , , , acDialog, varargs

Now, in the modify page, i am writing this code to retrieve the record details.

If Not IsNull(Me.OpenArgs) Then
args = Split(Me.OpenArgs, ";")
strProjectId = args(0)
reccount = CInt(args(1))
End If
DoCmd.GoToRecord , , acGoTo, reccount

Now, when i open it, it's opening fine. But when i try to save it, i am getting the error as you can see the second screenshot.

The command i am using is Runcommand acCmdSaveRecord.

What should i do? PLease help

[pbaldy]

Maybe it's just a matter of how the screen shots were taken, but on the first one record 1756 has focus, but record 1802 was brought up on the second form. Personally I wouldn't use CurrentRecord like that. It would require the 2 forms to have identical sources, and I'm not sure how reliable it is to do what you're trying to do. I'd use the technique in the second link I posted in post 2. That technique uses the explicit key field value rather than the record number returned by CurrentRecord. I think there's less chance for error or confusion.

[Ramsi2001]

Hi Paul,
I understand what you are saying.

If i use the where condition in the first form, do i need to write any extra code in teh seocnd form.

Please let me know... And also i try to use the where condition. I am getting error as:

OpenForm action is cancelled

The code i wrote was:
DoCmd.OpenForm "frmProj_Modify", , , "Project_id=" & Me.Project_ID


Thanks,
Ram

[pbaldy]

Using either of the methods on my link, there is no code necessary in the second form. Are you getting that error after taking the code out of the second form? What is the data type of Project_id?

[Ramsi2001]

I think that's the probelm.

The field is of type Text. So i changed to string type and i got it correctly.

The only thing is in the second form, i am unable to navigate to other records.

But still thanks a lot!!!

[pbaldy]

If you look at the bottom of that page, I have another page that demonstrates a method to open a form to a specific record while still having others available.

[Ramsi2001]

Hi Paul,
I am really sorry that i overlooked this one.

But you are really the MVP

Thanks,
Ram