Assigning value to subform fields

M

Madhuri

Hi all

I am using access as a front end to sql server backend. I have a form with a
subform and when the form is getting loaded I am assigning the master form
with the field values by using a query to populate fields from sql.

Now I want to assign values to subform using the same query. How do I refer
to subform fields to assign values to each field ?

Thanks
Madhuri
 
C

Chaim

The subform is treated just like another control on the main form. So the
reference would be:

Forms!<name of main form>!<name of subform>!<name of control on
subform>.<property>

Good Luck!
 
R

RuralGuy

=?Utf-8?B?TWFkaHVyaQ==?= said:
Hi all

I am using access as a front end to sql server backend. I have a form
with a subform and when the form is getting loaded I am assigning the
master form with the field values by using a query to populate fields
from sql.

Now I want to assign values to subform using the same query. How do I
refer to subform fields to assign values to each field ?

Thanks
Madhuri
Click to expand...

Here's a source if information on this topic you might want to bookmark:

http://www.mvps.org/access/forms/frm0031.htm
Forms: Refer to Form and Subform properties and controls

hth
 
M

Madhuri

Hi

I have a form and subform created in msaccess database which used to refer
to msaccess tables. I am trying to connect them to sql server ie, the
mainform and subform should show data from sql server tables instead of
msaccess tables. I successfully modified doing for the main form by opening a
recordset and assigning text field value to recordset value for the field.
But in subform which are in datasheet view and are individual forms created
in access

I removed the recordsource property from design properties so that the
fields became unbound but how do I set the new recrodset to the subform so
that it refers to sql instead of access.

This is the code I am using

private sub showdtl()
'ADOPRDBRW function opens the recordset assigning it to valtgrst
'recordset type variable)

Call AdOpRdbrW("SELECT ClmFlg, provid, memberid, name, icn, dos,
billedamount, totalpaid, dxcode, " & _
"procedurecode, insuranceeffdate, insuranceenddate,
Revenuecodedescription, " & _
"ClaimPaidDate, ProviderName FROM claimsdetail", vAltGrst)
If vAltGrst.EOF = False Then
Me.CORERECOUP_CAK0505_M0014_subform.Form.Recordset = vAltGrst
End If

end sub
 
R

RuralGuy

I'm sorry Madhuri,

I would suggest starting a new thread with the following subject:

"Assigning value to subform fields from SQL Server"

This should attract one of the MVP's that also knows SQL.

hth
 
C

Chaim

You'll have to give us some idea of what ADOPRDBRW looks like. Once the
results are in a recordset, whether the rows came from Access or elsewhere
doesn't make much difference.

The connection to SQL Server has to be occurring in that function, or in a
function it calls.

Show us the function code.
 
M

Madhuri

Hi Chaim

This is the adoprdbrw function

'This is to fetch records in read-write mode
'pass the query to the sub routine
Public Function AdOpRdbrW(QryStr As String, Optional Rcrdset) As Boolean
If IsMissing(Rcrdset) = True Then
Set AdGrst = Nothing

With AdGrst
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
End With
AdGrst.Open QryStr, AdGcn
If AdGrst.BOF = True And AdGrst.EOF = True Then
AdOpRdbrW = False
Else
AdOpRdbrW = True
End If
Else
If Rcrdset.State = adStateOpen Then
Rcrdset.Close
End If

With Rcrdset
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
End With
Rcrdset.Open QryStr, AdGcn
If Rcrdset.BOF = True And Rcrdset.EOF = True Then
AdOpRdbrW = False
Else
AdOpRdbrW = True
End If
End If
End Function

The problem I am facing right now is that I am able to get the result in the
recordset but I am not able to assign it to my datasheet view subform. For
the main form I assigned value control by control. for the subform I tried
using this

Me.CORERECOUP_CAK0505_M0014_subform.Controls(0).Value =
vAltGrst!clmflg
Me.CORERECOUP_CAK0505_M0014_subform.Controls(2).Value =
vAltGrst!memberid
Me.CORERECOUP_CAK0505_M0014_subform.Controls(4).Value =
vAltGrst!provid
Me.CORERECOUP_CAK0505_M0014_subform.Controls(6).Value = vAltGrst!name

but then this data dosent come in datasheet view and I am only able to view
single record.

Either I have to use recordsource or recordset property of the subform but I
am not able to get it.

Thanks
Madhuri
 
M

Madhuri

Hi Chaim

How do I assign the output of sql recordset to msaccess subform which is in
a datasheet view ?

me.CORERECOUP_CAK0505_M0014_subform.Form.Recordset = sqlrecordset.source
I should be able to do something like this

but it dosent support any of these

Thanks
Madhuri
 
J

John Griffiths

Hi

Unable to follow thread due to missing posts.

If you are using DAO you can change the Connect property of the tabledef.

Regards John

Const Local_Database_Connect = ";DATABASE=C:\DATA\DATABASE\DATA.MDB"

Const Remote_Database_Connect = "sql server/msde connection string here"

Sub ConnectLocal()

'---------------------------------------------------------------------------
-----
Dim MyWorkspace As Workspace
Dim MyDatabase As Database
Dim MyTableDef As TableDef
Dim i As Integer

'---------------------------------------------------------------------------
-----
Set MyWorkspace = DBEngine.Workspaces(0) ' Get default workspace.
Set MyDatabase = MyWorkspace.Databases(0) ' Get current database.

'---------------------------------------------------------------------------
-----
snip

MyTableDef.Connect = Local_Database_Connect
MyTableDef.RefreshLink
End Sub
 
Top