Access Project Form Issue

I have a sub form that both presents data based on the master form
(frmvacancy/tblvacancy). When the subform appears it shows data from
a table (tblvacancy) that uses the primary key from the master form
(frmvacancy). I can get the data to appear correctly in the subform
based on the information from the master form but I can only do this
with a stored procedure and the sp does not allow me to update the
table for the subform.

Any suggestions on how to dynamically update the subform record source
and still use the stored procedure?

-C-Note

Re: Access Project Form Issue

Your problem is not real clear. It sounds like you are using an Access
ADP, so my input here will be aimed at ADP stuff.

It sounds like a value from the main form (primary key value) gets
passed to the sql server stored procedure which then populates a table
on the sql server which the subform is based on, and the subform then
displays this data in datasheet view.

And then it sounds like you want the ability to edit this data. I would
suggest using a different form for editing -- where you select a
particular record from your primary subform (capture the ID of this
record in the Subform's current event) and then populate the edit form
with the data just for this record. Then use ADO to submit the edits to
the sql server.

Private Sub GetData()
Dim cmd As New ADODB.Command, RS As New ADODB.RecordSet
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSvr;Database=yourDB;Trusted_Connection=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Select * From yourTbl Where ID = " & txtID
Set RS = cmd.Execute
txt0 = RS(0)
txt1 = RS(1)
txt2 = RS(2)
...
RS.Close
cmd.ActiveConnection.Close
End Sub

Private Sub UpdateData()
Dim cmd As New ADODB.Command, RS As New ADODB.RecordSet
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSvr;Database=yourDB;Trusted_Connection=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Update yourTbl Set fld1 = '" & txt1 & "', fld2 = '" &
fld2 & "', fldDate = '" & txtDate & "'"
cmd.Execute
cmd.ActiveConnection.Close
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Access Project Form Issue

C-Note wrote in news:1ac5a5c7-ff86-48cb-b6f1-
9495c2c9d634@q39g2000hsf.googlegroups.com:

> I have a sub form that both presents data based on the master form
> (frmvacancy/tblvacancy). When the subform appears it shows data from
> a table (tblvacancy) that uses the primary key from the master form
> (frmvacancy). I can get the data to appear correctly in the subform
> based on the information from the master form but I can only do this
> with a stored procedure and the sp does not allow me to update the
> table for the subform.
>
> Any suggestions on how to dynamically update the subform record source
> and still use the stored procedure?
>
> -C-Note

Access must know what table to update. Have you set the UniqueTable
property of the subform?

Access must know what record to update. Does the sproc return a uniquely
indexed field for this table?