Using subform in datasheet view to open other form

[Compressor]

Well, I couldn't find this using the search, so here it goes:

I have a form in which I enter data to create a new client. Within that form I have a subform in datasheetview which is based on a query which holds/retrieves certain (important) fields from a table that holds case info. So in that one view the client data is displayed on the form and all cases and the most important data from those cases is displayed nicely in one screen.

Now, when a client calls I navigate to that client and see all previous and all current cases that are open and/or closed for this customer. So the customer says case this and that number is the case I need to discuss with you. Now, what I would like is to be able to click or double click anywhere on the line that, for instance, says case nr 55, so that the form(s) belonging to case nr 55 pop up and all info concerning that case is displayed and can be edited as needed.

My problem is: I'm still a newbie and if it was a simple thing like using the on_click event to open the form that's needed, I would not have to post this question. But that can't be done (as far as I know) since the subform is based on a query which is displayed in dataview.

Am I overlooking a very simple solution to this problem or is it really going to be as difficult as I think it's going to be?

For clarification of the idea I have included a screenshot of the form in question. I assume there are tons of databases out there which use such a layout or idea as this one. Please, never mind the buttons etc. It is still a work in progress.

 

[gemma-the-husky]

no, its not difficult. assuming the selected row in your subform has an id, then you can design a query (and a form based on it) to include all the detials you want to see for the case. In the criteria column for the id you need to create a reference to the appropriate text box on the subform. (see later), If your row doesn't have an the id on the form, put it there in a visible textbox while you are testing it and then make it not visible when its all working - it makes it easier.

you can either explicitly read the reference - this is the tricky bit, as referring to controls on subforms needs careful syntax

its something like forms!mainform!subform.fieldname.

look in access help, or in this forum, or some nice guy will probably post the correct term.

alternatively, you can save the row id in a global variable, and retrieve with a function, and then in your query test the id by calling the function (it has to be this way, because you cannot read a global variable directly in a query.

to open your new form, you can use either a double-click on one of the fields in your selected row, or place a command button in the sub-form footer, or indeed any other way you can think of.

Hope this helps

 

[Compressor]

Thanks for your help once again But.... arhw.... I didn't understand what you meant with the ID stuff. How can I give it an ID or how do I find out which ID it has? I found out the basic syntax of most basic calls to properties and functions or events I think from a VBA programming beginner book, so... I hope that will make the process of making this thing work a bit easier. But what you are saying is somewhat out of my reach I'm afraid. But.... I don't give up easily usually

So, what I did was create a form based on the same query, set the property of the on dbl click event of all fields to open the form needed, replaced the data source of the subform with this newly created form (instead of the data source object used before, which was the query directly) and set it up to be displayed as a datasheet.

Maybe a quick 'n dirty workaround... but it (seems to) works. Too tired now so have to see if it really works tomorrow when I'm all fresh again. If any of you can see any downsides to this compared to using the query itself as a datasource directly for this particular purpose, please let me know.

Thanks once again!

 

[gemma-the-husky]

you need to be able to identify a mechanism to retrieve the single case you want to expand.

your main form includes your clients
each of your clients is linked to a number of cases
when you select one of these cases, there needs to be something in the details that uniquely identifies the case. The information you show on the screen probably includes this unique identifier but you may not have displayed it in a text box. It might just be an autonumber reference. Assuming you have this number, then it is easy to retrieve a detail query consisting of just this one item, and then base a form on it to show all the relevant details of the case, rather than the summary you had included on the subform.

 

[Compressor]

Ok, that makes sense... even pretty obvious but my problem was this:

when I base a subform in my mainform directly on a select-query, I cannot use event driven things, such as double-clicking on a certain field or record since in design view, the subform object doesn't show the separate fields/events/objects. It just shows a white block which can be sized up or down and some minimal properties can be set.
But when I use a subform in my mainform that is based on a seperate form which is based on that same select-query I can do those things, since then, in design view, the subform object does show those fields/events/object and their properties and everything can be set. Then, I choose to display that subform in datasheet view, and the result seems to be the same, but more "programmable".

Or am I missing something? The problem I had seems to be solved, but I still like to know if what I'm saying here is true or that I am missing something which could benefit me later on.

 

[Compressor]

Hmmm... I had gotten it to work, but I deleted some queries, subforms and code during the last few days, so now it won't work anymore. Since I'm still stuck on some other things, I thought to have a go at this one again.

Same story as above... created a query to select certain cases from a customer. Based a form on that query, which is used in a subform and displayed as a datasheet.

As you can see in the image in the first post, this client has cases 55, 60 and 61 linked to him. Which are the unique identifiers you are referring to I suppose (Table -> CaseDateTimeInfoTable; Fieldname -> CaseNumber_CaseDateTimeInfoTable).

Now, the functions given to a textbox in "formview" for example also work in datasheetview, which is nice . Since I don't remember what code I had on the On_Double_Click event I just tried something like this:

DoCmd.OpenForm "CaseDateTimeInfoTable_Form", acActiveDataObject, "", "", , acNormal

And/or

DoCmd.OpenForm "CaseDateTimeInfoTable_Form", acNormal, "", "", , acNormal
DoCmd.GoToRecord acActiveDataObject

Both !seem! to work. But... only for one time. Then, no matter what I do, they won't open the case anymore. Once case 55 is opened, doubleclicking case 60 for example won't open the form displaying case 60 but it will again open the form displaying case 55. Why? When I click on 60, that becomes the activedataobject right?

Another way would be to just say: on doubleclick event, open this and that form, and goto recordnr (casenumber).

Anyone can help me with the syntax for that? I've tried getting it from a macro converted to module:

Function Macro1()
On Error GoTo Macro1_Err

    DoCmd.GoToRecord acForm, "CaseDateTimeInfoTable_Form", acGoTo, xxxxx


Macro1_Exit:
    Exit Function

Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit

End Function

Where xxxxx needs to be a reference to CaseDateTimeInfo_CaseNumber.
I'm thinking something like

Dim CaseNumber As Integer
CaseNumber = the casenumber value from either the query or from the subform based on the query (syntax again?)

 

[Compressor]

Since the database has been redesigned by now and some other stuff is finally working correctly, I've come back to this issue again.

The code now is:

Private Sub Casenumber_DblClick(Cancel As Integer)
Dim GotoCaseNrRecord As Integer
GotoCaseNrRecord = Me.Casenumber
If Casenumber <> "" Then
DoCmd.OpenForm "CaseInvoerForm"
DoCmd.GoToRecord acForm, "CaseInvoerForm", acGoTo, GotoCaseNrRecord
End If
End Sub

Still it won't work. The error is 2105, you can't go to the specified record.

But why? The help window stays completely empty, so that didn't help me much...
The code this is written for is still for a subform based on a query. The subform is still displayed in datasheet mode, so a list of cases for the currently displayed client is shown. The value of the case which needs to be opened will be in the casenumber field itself, so that's why I try to tell access that when the value of casenumber is anything else than "" nothingness, open the form for the casenumber that is displayed. However.... that last part of the sentence I'm not so sure about. I'm assuming that access knows the value of that field since it is displayed in that field and compares it to nothing so the Then part will always be executed, no matter what the number in the casenumber field is. Anyway, the form opens, so that seems to go OK, but then the 2105 you can't go to the specified record appears.

Anyone?

 

[Compressor]

Ok, just found out that the last parameter of the GotoRecord command must be an Offset. What is an offset? Can it be that I need to specify more than just the value of the casenr record?

 

[ansentry]

Have a look at the attached sample. It will do what you want.

 

[Compressor]

Wow, thanks a lot! It works This thing is getting more and more shape everyday. I like it! Too bad that once it's finished... it's finished. Well, of course I could keep on tweaking it, probably will need to anyway, so that's a comforting thought

But first... I need to learn some more of course, so I would like to ask: how does the

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSub"
DoCmd.RunCommand acCmdSaveRecord
stLinkCriteria = "[subID]=" & Me![SubID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

work? I mean, what does the = "[subID]=" & Me![SubID]
part really do?

When I change the DoCmd.OpenForm stDocName, , , stLinkCriteria to DoCmd.OpenForm stDocName, , , 7
the where condition would be "hard coded"(?) to recordnr seven, whichever record I click on I thought. So just to see if that would be true I tried. I thought wrong. When I do change it to a number, whatever number that may be, the form does open, without error but it just opens up on the first record of whichever clientnr I'm on (I guess the clientnr thing happens since the form i'm using is based on a query, so that one will ofcourse be always correct).

Also, the first thing I noticed was that you declared stLinkCriteria as a String, not an Integer. So, what format does the Where condition of the DoCmd.OpenForm like to eat? I couldn't find it in my "beginner" books.

 

[ansentry]

DoCmd.OpenForm stDocName, , , stLinkCriteria

Open the form named FrmSub and when it opens, open it at the same SubID number as the form it is being opened from.

Have a look at the first record and you will see that the Volkswagen is SubID number 3, now double click it and you will see that the form (FrmSub) open on SubID 3.

 

[Compressor]

First of all, it works. So all that I write below of what I've done is just done to enhance my understanding of this little piece of VBA

To your response: Yes, I understand that, but ehrm... let me put my questions in another way:
Why is the data that is stored in stLinkCriteria, stored as a string and not as a number (integer) since it is a number which is always in the corresponding field (Casenumber). I've tried using a "hard coded" integer (an existing casenumber instead of the stLinkCriteria) to see if I would end up in the corresponding case. But that doesn't happen. The form opens, yes. But the case that it opens in the form is always the first case of the series of cases for certain client. So does the OpenForm function or statement (don't know yet which of the two it is called) only like a String as the Where condition?
So hypothetically:
Testclient1 is the only client in the database and has 3 records. Testrec3, Testrec4 and Testrec5, all with corresponding field values (3, 4 and 5 as casenumber). Now when I change the DoCmd.OpenForm stDocName, , , stLinkCriteria in that piece of code to DoCmd.OpenForm stDocName, , , 4 and execute the program, the form that pops up is not Testrec4 (which has casenumber 4 in the (autonumber) field) but Testrec3. How come?

And the other question is:
So what does the = "[subID]=" & Me![SubID] do exactly? Does it re-format the subID to another type of value?
The = symbol is selfexplanatory, but then there is a reference which opens as "and then subid is between [] then another = and then the " closes it up again. I assume the " and " are used since stLinkCriteria is defined as a string. But then the subID is between brackets and after that an =. Why isn't it syntaxed as "subID" = & Me!Casenumber. I'm trying to understand why its form needs to be "[whatever]=" and not "whatever" = .
The & Me![subID] sets the Me object to [subid]. Ok, that's clear.
Ehr... I hope my english is a bit clear and keeps my question understandable....

 

[Compressor]

Just trying to connect the dots....