opening a form form within report

[buratti]

So ultimately I would of liked to have a form in continious form view, but this is not possible in this situation because I have a subform in that original form. (Unless I am worng on learning that it is impossible to have continious form view with a subform in the form.) So as a work around I created a report EXACTLY as the form appears (also with subreport). The report shows the records I query/filter/needed perfectly.

What I need now is to be able to double click a specific record in the report and have it open the form with only the record I double clicked in to be able to edit that record.

I tried the following, but it opened ALL records in the form. I need ONLY the record I double clicked:

Private Sub Detail_DblClick(Cancel As Integer)
DoCmd.OpenForm "Invoices", acNormal, , TxnID = Me.TxnID, acFormEdit
End Sub

Any suggestions?

 

[DCrake]

If you are looking at a report in preview mode there is no drill down caperbility in Access - Unless 2007 has, never explored this functionality. What version are you using?

 

[gemma-the-husky]

why do you think you cannot achieve this in the original form, again?

you cannot nest continuous subforms - but you can do it with a little trick.

can you explain what you want again?

 

[buratti]

I created just a basic invoice form.... Has customer data (name address, delivery address, etc.) in the main form, and has a subform that has order detail (Item, Quantity, price, total, Etc.). The subform is in datasheet view, and main form in form view. If I change the default view of the main form (in design view of the form) to continious forms, I get the error "You cant view a form as continious form if it contains a subform, ActiveX control or bound chart".

That is why I just assumed it was not possible.

 

[buratti]

Sorry DCrake I didn't see your post there. I am using Access 2007. The default view is report not preiview, and since I can actually open the form from the report (just not with the criteria specified) I am assuming that a little tweaking would get the criteria working correctly.

 

[DCrake]

Can you post a copy of your database to look at?

 

[Rich]

I created just a basic invoice form.... Has customer data (name address, delivery address, etc.) in the main form, and has a subform that has order detail (Item, Quantity, price, total, Etc.). The subform is in datasheet view, and main form in form view. If I change the default view of the main form (in design view of the form) to continious forms, I get the error "You cant view a form as continious form if it contains a subform, ActiveX control or bound chart".

That is why I just assumed it was not possible.

Drag the subform into the continuous forms footer and link the Master fields etc., but be warned it may not work as envisaged

 

[vbaInet]

So ultimately I would of liked to have a form in continious form view, but this is not possible in this situation because I have a subform in that original form. (Unless I am worng on learning that it is impossible to have continious form view with a subform in the form.) So as a work around I created a report EXACTLY as the form appears (also with subreport). The report shows the records I query/filter/needed perfectly.

What I need now is to be able to double click a specific record in the report and have it open the form with only the record I double clicked in to be able to edit that record.

I tried the following, but it opened ALL records in the form. I need ONLY the record I double clicked:

Private Sub Detail_DblClick(Cancel As Integer)
DoCmd.OpenForm "Invoices", acNormal, , TxnID = Me.TxnID, acFormEdit
End Sub

Any suggestions?

It looks like it's possible. Create a button as the first control in your report, maybe have "Details" as its caption. Set the button's 'Display When' property to Screen Only. Place your (amended) code in the Click event of the button:

DoCmd.OpenForm "Invoices", acNormal, , "[TxnID] = " & Me.[TxnID], acFormEdit

 

[SOS]

It looks like it's possible. Create a button as the first control in your report, maybe have "Details" as its caption. Set the button's 'Display When' property to Screen Only. Place your (amended) code in the Click event of the button:

DoCmd.OpenForm "Invoices", acNormal, , "[TxnID] = " & Me.[TxnID], acFormEdit

You don't need the button, but put in the dblclick event of one, or more, of the CONTROLS that are bound to the data. In fact, if you have the ID field on the report and use that to double click on then you can capture that value very easily as it will be the current record (yes, in Access 2007 Reports and their controls have clickable events).

 

[vbaInet]

You don't need the button, but put in the dblclick event of one, or more, of the CONTROLS that are bound to the data. In fact, if you have the ID field on the report and use that to double click on then you can capture that value very easily as it will be the current record (yes, in Access 2007 Reports and their controls have clickable events).

Only there for aesthetics.

 

[SOS]

Only there for aesthetics.

So how would the button know which record to open to? If this is a report listing several records, you would need to click on the record first before clicking the button. So double clicking on the record you want would be more efficient, wouldn't it (instead of clicking on the record and then clicking on a button)?

 

[vbaInet]

The button appears on every record so the cursor points to the current record. You don't need to click on the record. Try it out. This is simply something for the OP to consider.

 

[gemma-the-husky]

The way to achieve this is to use a single container form

then you can have two continuous forms in the container form. (say you have a left and a right sub form)

You cant have automatic links, but its easy to achieve the same effect. In the current event of the left subform, just have a routine that actively requeries the right subform.

Job Done.


----
you can even have options in the container form that requery the left sub form.

eg , I have one example

the container form, lets you select a date

the left continuous subform shows orders for that date

the right continuous subform shows orderlines for each order

the only catch is - because the right subform refreshes based on the current event of the left form - therei s a speical case if the left sub form is empty - in which case oyu dont get a current event, and the right subform wont display properly. You need a bit of code in the container to chjeck this.

You can extend this idea further if required.

 

[buratti]

Sorry for the delays... I had to go take my son sledding!!! (it was fun)

So back to this problem. I tried the button suggestion... no avail (opens the form but again all records displayed, no "filter")

I tried attaching the code to a field on my report, but same as above.

I tried the amended code of "[TxnID] = " & Me.[TxnID] (the original I had was just [TxnID] = Me.[TxnID]), and then it will ask me to "Enter a Parameter Value"

Additional info on the report and form:
The [TxnID] field contains alpha-numeric data (or field type of just text)
A sample of one TxnID value is: "E61B4-1264789995"
The report and the form is based on the same query
The [TxnID] is a field the query but not displayed in either of the report or the form

I'm pretty sure that it just has something to do with my syntax in the criteria section of my docmd.openform.......
Any ther suggestions?

BTW. Thanks for the help on trying to figure out the continious form problem, but I've decided that I like the option of opening a report first then (atlease if I can get it working correctly) opening the form from the report better.

 

[vbaInet]

The [TxnID] is a field the query but not displayed in either of the report or the form

There lies your problem. Your filter code needs an ID on your report to work.

 

[buratti]

OK, Finally figured it out... It was a problem in the syntax. i went through all possibilities and combinations until it worked and the correct (or at least the one that works) is as follows:

DoCmd.OpenForm "Invoices", acNormal, , "[TxnID] = '" & Me.[TxnID] & "'"

There lies your problem. Your filter code needs an ID on your report to work.

Unless we are misunderstanding each others statements here, this does in deed work without the referenced field being displayed (hidden or not) in the report.

 

[vbaInet]

Ah, when you said "not displayed" I thought you meant it wasn't a field within the report. "Hidden" would have been a more preferred word (just like you rightly used in your last post). You didn't mention that TxnID was text, so we were under the impression it was of a number data type since most IDs are of such a nature.

Glad to know you sorted it out.