query regarding subform/subreport of msaccess

[vivek.shankar]

Hi,
i am currently automating msaccess forms using a separate VB application.i ahve encountred a Subform/Subreport in msaccess form.the subreport contains a row........how can i get the values the values of the row using my VB code?

regards
vivek.s

[RobDog888]

One way may be to simulate the query and filtering that the form is using.
The form should be attached to a Table or Query. Get that object name.
Then the subform may have additional filtering, so get that filter from the
subforms filter property. Once you know all the sources/filters, you can run
the query with all filters and duplicate the recordset.

If you have more then one record in the recordset then note which record it
is that you want, like record #2. Then in your query recordset just look at #2.

HTH

[vivek.shankar]

GURU,
how shud i code it...can u gimme a example..........this waht i am orginally having........i have also attached the screenshot..........

Set oform = oapp.Forms("frmWorkReports")
    Set obutton = oform.Controls("subCallData")
    obutton.SetFocus

and how can i run query...........i am accessing msaccess forms from a seperate VB aplication........anyway i wudnt like to run the query.......i have to jus get the MIN (u can see in the screen shot) from the subform.....and get hold of those values

ps: accessing msaccess forms from seperate VB application is also called VBA is it?

thanks and regards
vivek.s

[RobDog888]

I think it is more suited to call it "MS Office Automation" or MS Access Automation".
That designates that you are invoking Access from outside the Access VBA
enviroment. Perhaps we should change the VBA Forum to "MS Office Development" ?

Anyways, since you are trying to get info from a continuous form, your better
off running the query because you can not get a particular record instance in
a continuous form. Its like an array, but arrays are not accessible in VBA/Access.

[vivek.shankar]

hi!

"MSOFFICE DEVELOPMENT" ......yes certainly fitting

well how can i run query from VB?and it wudnt affect the ms access application, right?
so no way to get the value without running query?


thanks and regards
vivek.s

[RobDog888]

No it will not affect the datbase objects or data at all.

There probably is a way to get the info from the form, but it would take some
time to figure out. For now we can run the query and if we figure it out and it
seems better then we can switch.

You need to start by obtaining the Form's recordsource and filters (if any).
Then get the subform's filter. Once we have that we will know how to
duplicate it by way of a query.

Let me know when you are at that point.

[vivek.shankar]

Hi GURU!
i am completely new to access programming........well how can i gte the record source and filter?i right clicked the subform(design) and source object was given as "frmCallData"............how shud i proceed........

regards
vivek.s

[RobDog888]

You can either hard code it in, since they dont want any changes to the
db the object names should stay the same, or use the smae methods to
access a form and/or controls to get to the forms recordsource property.

Option Explicit
'Add reference to MS ActiveX Data Objects 2.x Library
'Add reference to MS Access xx.Object Library
Private moCnn As ADODB.Connection
Private moRs As ADODB.Recordset
Private moApp As Access.Application
 
Private Sub Command1_Click()
    
    Dim oForm As Access.Form
    Dim sRecordSource As String
    Dim sFilter As String
    Dim sSQL As String
    
    DoCmd.OpenForm "Form1", acDesign, , , acFormReadOnly, acHidden
    Set oForm = moApp.Forms.Item(0)
    sRecordSource = oForm.RecordSource
    If oForm.FilterOn = True Then
        sFilter = oForm.Filter
    Else
        sFilter = vbNullString
    End If
    MsgBox sRecordSource & vbNewLine & sFilter
    'More to follow...
    
End Sub
 
Private Sub Form_Load()
    Set moCnn = New ADODB.Connection
    moCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\RobDog888.mdb;Persist Security Info=False"
    moCnn.Open
    Set moApp = New Access.Application
    moApp.Visible = True
    moApp.OpenCurrentDatabase "D:\RobDog888.mdb"
End Sub

[vivek.shankar]

Thanks a lot............I'll try this and get back to u

regards
vivek.s

[RobDog888]

Question: Is your attachment image a cropped view of your form or is that all
there is? In other words are you sure its not just a continuous form without a
subform or is it a Form with a continuous subform in it?

[vivek.shankar]

well there is a form and in it there are some tab controls and when i clicked the main part where the records are.......it said it is a subform........well i will attach the entire form now........

is there another method without executing queries.......



Thanks and Regards
s.vivek

[RobDog888]

No, it is a subform nested in a tab control.
We may be able to navigate down to the control but if the subform contains
more than one record we will have trouble getting the right record. This is
whay I suggested executing a query duplicating the form. I am testing the
control theory out, but I am getting ready to leave early for the day. Boss is
letting us go early with full pay.

I will pick this up in about 1-2 hours when I get home.

[vivek.shankar]

THANKS A LOT FOR THE TROUBLE

WELL FESTIVE MOOD IS CATCHING UP

HAPPY NEW YEAR 2005 - 1 DAY

i wud happily use the query route........but i wud also be grateful if u guide me to the other way as well

PS: BOSS!!Dont u have a social life!!i guess ur married to VB
thanks a lot!
warm regards
vivek.s

[RobDog888]

Not sure where your located, but here on the west coast its only Thursday evening.
I get Friday off with pay plus the weekend. 3 1/2 day weekend. I'm
married to my beautiful wife, VB comes after family, dog, & cat. We already taking down x-mas decorations already!

I am working on getting the data the way we have been so fay, but I needed
to know if you only had one record in the subform or multiple?

28.5 hours to go!

[vivek.shankar]

Great to hear that from u

well sometimes there are multiple records too.

ps: i am located in india..still 10 hrs to go herebut no celebrations this year cause killer tsunami has caused great destruction in our country
thanks a lot!
regards
vivek.s