Help me please!!! DAO still same question! 
Author Message
 Help me please!!! DAO still same question!

Hey,

I updated my code based on previous suggestions but cannot get
fldFamilyPlan to update as coded below. The back round:

I have a form with a subform. Form name: frmInquireFamilyMember,
recordsource: unbound; Subform name: Family Members,
recordsource: SELECT * FROM tblFamilyMembers_AM WHERE
((tblFamilyMembers_AM.fldDonorID) = ([Forms]![lookup]![txtDonorID])).

The subform lets you fill in family member names and click save on
frmInquireFamilyMember. When you click save I want to use DAO to create a
recordset that Counts the number of names in tblFamilyMembers_AM WHERE
fldDonorID = Forms!lookup!txtDonorID and another recordset that  Selects
fldFamilyPlan From tblMasterSystem Where fldDonorID =
Forms!lookup!txtDonorID. fldFamilyPlan is a Yes/No field.  The result will
be that if the Count > 0 then tblMasterSystem.fldFamilyPlan = True for the
corresponding record.

HOWEVER, my code below does not work. I am missing something basic I am
sure. Help! TIA.

On Error Resume Next
DoCmd.SetWarnings False

Dim myws As Workspace
Dim MyDB As DATABASE
Dim myrs As Recordset
Dim myqd As QueryDef
Dim mystring As String

Set myws = DBEngine.Workspaces(0)
Set MyDB = CurrentDb()

' Save the current entry
Set myrs = MyDB.OpenRecordset("tblFamilyMembers_AM", DB_OPEN_DYNASET)
With myrs
.MoveFirst
.MoveLast
.AddNew
.fldfamilyfirstname = Me![Family Members].Form!fldfamilyfirstname
.fldfamilylastname = Me![Family Members].Form!fldfamilylastname
.fldFamilyAge = Me![Family Members].Form!Age
.fldRelationship = Me![Family Members].Form!fldRelationship
.fldPlan = Me![Family Members].Form!fldPlan
.UPDATE
.MoveNext
.Close
End With

' Find family names for the end user selected donor id and count them
mystring = "SELECT DISTINCTROW tblFamilyMembers_AM.fldDonorID,
Count(tblFamilyMembers_AM.fldFamilyLastName) AS CountOffldFamilyLastName
FROM tblFamilyMembers_AM GROUP BY tblFamilyMembers_AM.fldDonorID HAVING
(((tblFamilyMembers_AM.fldDonorID) = " & [Forms]![lookup]![txtDonorID] &
"));"

Set myrs = MyDB.OpenRecordset(mystring, DB_OPEN_DYNASET)
myrs.MoveFirst
myrs.MoveLast

'If the count is > 0 then update the yes/no fldFamilyPlan to true
If myrs.RecordCount > 0 Then
myrs.Close
mystring = "SELECT DISTINCTROW tblMasterSystem.fldDonorID,
tblMasterSystem.fldFamilyPlan FROM tblMasterSystem WHERE
(((tblMasterSystem.fldDonorID)=" & [Forms]![lookup]![fldDonorID] & "));"
Set myrs = MyDB.OpenRecordset(mystring, DB_OPEN_DYNASET)
myrs.MoveFirst
myrs.MoveLast
myrs.Edit
myrs!fldFamilyPlan = True
myrs.UPDATE
myrs.MoveNext
MsgBox "Thank you. Record saved."
Else

'If the count is anything else then update the yes/no fldFamilyPlan to
False
mystring = "SELECT DISTINCTROW tblMasterSystem.fldDonorID,
tblMasterSystem.fldFamilyPlan FROM tblMasterSystem WHERE
(((tblMasterSystem.fldDonorID)=" & [Forms]![lookup]![fldDonorID] & "));"
myrs.Close
Set myrs = MyDB.OpenRecordset(mystring, DB_OPEN_DYNASET)
myrs.MoveFirst
myrs.MoveLast
myrs.Edit
myrs!fldFamilyPlan = False
myrs.UPDATE
myrs.MoveNext
MsgBox "Nothing to save."
End If

--
S.E. Hanley

Applications Programmer
Callaway Gardens
Pine Mtn. GA



Sun, 23 Jul 2000 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP,

2. Can anyone HELP me PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE

3. DAO, not ADO, please - Parameter Question, Sub forms ?

4. PLEASE HELP PLEASE HELP PLEASE HELP

5. Looking for tool to make AVI files from JPG stills

6. An Urgent Question Please , Please help me

7. Creating time-lapse AVI movie from SNAPPY stills?

8. create fog on a stills picture

9. Pulling PC Cam stills with an API

10. please help - finding in dao recordset

11. I am stuck with DAO need help please

12. Missing 3.5 DAO Object Library Please HELP

 

 
Powered by phpBB® Forum Software